Перейти к содержимому
BXWiki

База растёт - журналы, статистика, старые данные и чистка

Разбираемся, что занимает место в базе сайта: служебные журналы, статистика, история и поисковый индекс, и чистим их без потери рабочих данных.

Что нужно знать заранее

База растёт не от товаров и заказов, а от служебных записей. Журнал событий, статистика посещений и история изменений набирают миллионы строк за год работы даже на среднем магазине.

Служебные и рабочие данные чистят по-разному, и путать их между собой нельзя. Первые удаляют по сроку хранения и без сожалений, вторые не трогают вовсе: заказы и клиенты должны остаться, чего бы это ни стоило по месту.

Удаление миллионов строк одним запросом блокирует таблицу очень надолго. Такую чистку делают порциями и в тихое время, иначе витрина встаёт вместе с очередью запросов к базе.

Шаги

  1. Посмотреть размеры таблиц и выписать те из них, что занимают заметное место.
  2. Разобраться, что за данные лежат в верхних таблицах: служебные или рабочие.
  3. Настроить сроки хранения журналов и статистики в настройках нужных модулей.
  4. Разово почистить накопленное порциями и обязательно в тихое время суток.
  5. Проверить свободное место и повторить замер размеров через неделю работы.

Решение

Смотрим самые тяжёлые таблицы:

SELECT table_name, ROUND((data_length + index_length) / 1024 / 1024) AS mb, table_rows
FROM information_schema.tables WHERE table_schema = DATABASE()
ORDER BY (data_length + index_length) DESC LIMIT 15;
-- число строк здесь приблизительное, но для сравнения таблиц его хватает
-- верхние строки почти всегда служебные: журналы, статистика, поисковый индекс

Замер занимает секунду и сразу называет виновника. Дальше разбор идёт предметно: одна таблица на десятки гигабайтов - это не «база выросла», а конкретная подсистема с неверными настройками хранения.

Смотрим, что накопилось в журнале событий:

SELECT AUDIT_TYPE_ID, COUNT(*) AS cnt FROM b_event_log
GROUP BY AUDIT_TYPE_ID ORDER BY cnt DESC LIMIT 10;
-- один тип событий обычно даёт большую часть строк журнала

Журнал событий пишется настройками главного модуля и живёт заданный срок. Часто оказывается, что срок хранения не ограничен, а в журнал пишется отладочное событие, включённое год назад.

Чистим накопленное порциями:

DELETE FROM b_event_log WHERE TIMESTAMP_X < NOW() - INTERVAL 90 DAY LIMIT 20000;
-- запрос повторяют в цикле, пока строки заканчиваются, а не одним заходом

Порции по десятки тысяч строк не блокируют таблицу надолго. Между заходами делают паузу: репликация и резервное копирование тоже должны успевать за такой чисткой.

Ограничиваем сроки хранения в настройках:

printf("журнал=%s статистика=%s\n",
    \Bitrix\Main\Config\Option::get('main', 'event_log_days'),
    \Bitrix\Main\Config\Option::get('statistic', 'DAYS_LOG'));
// сроки хранения задают в настройках модулей, а не разовой чисткой

Разовая чистка без настройки сроков помогает на месяц. Правильный порядок обратный: сначала задать сроки хранения, потом убрать накопленное, потом проверить замер через неделю.

Проверяем поисковый индекс и статистику:

SELECT COUNT(*) FROM b_search_content;          -- размер поискового индекса
SELECT COUNT(*) FROM b_stat_day;                -- накопленная статистика по дням
-- индекс пересобирается, статистика чистится по сроку хранения модуля

Поисковый индекс - восстановимые данные: его чистят и собирают заново. Статистика посещений полезна первые месяцы, а потом чаще всего лежит мёртвым грузом и её отключают целиком.

Типичные проблемы

После чистки сайт встал на несколько минут.

Удаление шло одним запросом на миллионы строк и держало таблицу заблокированной. Чистку делают порциями и с паузами между отдельными заходами удаления.

Место освободилось и снова кончилось через месяц.

Убрали накопленное, но не задали сроки хранения журналов и статистики сайта. Сначала настраивают сроки хранения, а уже потом чистят накопленное за годы.

Место на диске не вернулось после удаления строк.

Файлы таблиц не сжимаются сами после удаления даже части записей. Место на диске возвращает отдельная операция обслуживания этой таблицы базы.

Вместе со служебными данными удалили историю заказов.

Таблицу приняли за служебную по одному лишь похожему на служебное имени. Перед чисткой смотрят содержимое таблицы и обязательно делают резервную копию.

База растёт, хотя журналы отключены.

Место занимает поисковый индекс, статистика или таблицы установленного стороннего решения. Размеры таблиц смотрят замером, а не по памяти о прошлом разборе.

Частые вопросы

Какой размер базы считать нормальным?

Ориентируются не на цифру, а на состав: рабочие данные растут медленно и предсказуемо. Служебные таблицы в верхних строках замера - всегда повод настроить сроки хранения.

Можно ли просто удалить старые заказы?

Нет, это рабочие данные, нужные для отчётности и споров с покупателями. Их архивируют отдельной базой или выгрузкой, а не удаляют.

Нужно ли отключать статистику?

На большинстве магазинов да: внешняя аналитика даёт больше, а таблицы статистики растут быстро. Отключение снимает и запись, и нагрузку.

Как вернуть место на диске после чистки?

Отдельной операцией обслуживания таблицы, и делают её в тихое время. На больших таблицах она долгая и требует свободного места.

Поможет ли чистка ускорить сайт?

Иногда: короткие служебные таблицы обслуживаются быстрее, а резервные копии делаются заметно скорее. Медленные страницы чаще лечатся индексами и кэшем, а не размером базы.

Смежное

Первоисточник