Битрикс под нагрузкой: индексы и тяжёлые запросы к БД

Сайт на Битриксе спокойно отвечает днём, но во время рассылки или рекламной кампании каталог начинает висеть. PHP-процессы стоят в ожидании, в SHOW FULL PROCESSLIST видны одинаковые SELECT с длинной цепочкой JOIN, процессор сервера базы загружен. Добавление PHP-воркеров только ухудшает дело: они упираются в тот же MySQL. Ниже разобрано, какие места в базе Битрикса сдают первыми, как найти виновников без догадок и когда новый индекс ничего не даёт.

Порядок отказов

Текст написан для MySQL 8.0 и MariaDB 10.x, штатной структуры таблиц «Управления сайтом» и ядра D7. На MySQL 5.7 всё работает так же, кроме EXPLAIN ANALYZE: он появился в 8.0.18. Порядок в таблице не статистика, а типичная последовательность. Проверяйте её на своей базе.

Почему каталог упирается в JOIN

Если свойства хранятся в общей таблице b_iblock_element_property, каждое свойство в фильтре превращается в отдельное соединение с ней. Фильтр по трём свойствам и сортировка по цене из каталога дают запрос на несколько таблиц. База собирает промежуточный результат, сортирует его во временной таблице и только потом отдаёт нужную страницу. Постраничной навигации нужно ещё и общее число элементов, поэтому то же условие выполняется второй раз в виде COUNT.

В инфоблоках второй версии однозначные свойства лежат в таблице b_iblock_element_prop_sN, множественные в b_iblock_element_prop_mN, где N это ID инфоблока. Это убирает часть соединений, но не отменяет сортировку и подсчёт.

Индекс ускоряет выбор строк, но не отменяет работу над ними. Если запрос перебирает сотни тысяч строк ради сортировки, его надо переписать, а не обкладывать индексами.

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

Как найти тяжёлые запросы

Журнал медленных запросов

Команды действуют до перезапуска сервера, для постоянной настройки перенесите параметры в my.cnf. Новое значение long_query_time подхватывают только новые соединения.

-- Включаем журнал медленных запросов и порог в 1 секунду
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

Сводка по запросам

Если включена performance_schema, топ запросов по суммарному времени получается одним SELECT. Время в таблице хранится в пикосекундах, отсюда деление на 1e12. Большое отношение rows_examined к rows_sent значит, что база перебирает много лишнего.

SELECT
  LEFT(DIGEST_TEXT, 120) AS query_start,
  COUNT_STAR AS calls,
  ROUND(SUM_TIMER_WAIT / 1e12, 1) AS total_sec,
  ROUND(AVG_TIMER_WAIT / 1e12, 3) AS avg_sec,
  SUM_ROWS_EXAMINED AS rows_examined,
  SUM_ROWS_SENT AS rows_sent
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

Размеры таблиц и движок

SELECT
  table_name,
  engine,
  table_rows,
  ROUND((data_length + index_length) / 1024 / 1024) AS size_mb
FROM information_schema.TABLES
WHERE table_schema = DATABASE()
ORDER BY data_length + index_length DESC
LIMIT 15;

Значение table_rows для InnoDB приблизительное, но для поиска монстров его хватает. Встретили MyISAM, разбирайте его в первую очередь.

Индексы: что вы покупаете и чем платите

Для запроса из топа сначала выполните EXPLAIN и посмотрите на тип доступа, число оценённых строк и пометки Using temporary и Using filesort. Если индекс нужен, соберите его по правилу: сначала столбцы с условием равенства, потом столбец сортировки или диапазона.

-- Схематично: замените таблицу, столбцы и имя индекса на свои
ALTER TABLE b_your_table
  ADD INDEX ix_your_table_filter_sort (filter_column, sort_column),
  ALGORITHM=INPLACE, LOCK=NONE;

Цена у индекса вполне конкретная:

• Каждый индекс замедляет INSERT и UPDATE. В таблицах корзин и событий, где пишут постоянно, это заметно.
• Индекс занимает место в буферном пуле и вытесняет из него другие данные.
• На большой таблице ALTER держит нагрузку на диск, а на реплике может вызвать отставание.
• Ядро о ваших индексах в штатных таблицах не знает. Записывайте каждый в документацию проекта и проверяйте после обновлений.
• Лишние индексы находит представление sys.schema_unused_indexes. Оно считает с последнего перезапуска сервера, так что доверять ему можно только после недель аптайма.

Служебные таблицы, которые пухнут молча

• b_event: очередь почтовых событий. Если отправка стоит, неотправленные записи копятся, а агент каждый раз выбирает их из таблицы.
• b_event_log: журнал событий. Он растёт, пока не настроена очистка. Проверьте, какие события журналируются.
• b_cache_tag: при файловом кеше здесь хранятся теги. Массовое обновление элементов инвалидирует кеш по тегам и бьёт по этой таблице.
• b_sale_basket и b_sale_fuser: анонимные корзины посетителей и ботов. Очистку устаревших корзин ищите в настройках модуля интернет-магазина, точное место зависит от версии.
• b_stat_*: модуль статистики пишет в базу на каждый хит. На нагруженном сайте его обычно заменяют внешней аналитикой.

Замеры: что и как мерить

Готовых цифр «было и стало» здесь нет: они зависят от каталога, железа и трафика, а выдуманные хуже отсутствующих. Вот как получить свои:

1. Снимите копию продакшн-базы. На маленькой тестовой разница между планами не видна.
2. Зафиксируйте сценарий: один набор URL каталога, одинаковая параллельность, один инструмент нагрузки (ab, wrk или k6). Запишите железо и версию СУБД.
3. До изменений снимите p95 времени ответа, Threads_running, Slow_queries и топ из сводки выше.
4. Меняйте одну вещь за раз: индекс, движок, выборку полей. Перед каждым прогоном прогревайте буферный пул одинаково.
5. Сравните те же показатели. Если p95 не изменился, а rows_examined упал, узкое место в другом месте.

Где подход ломается

• Низкая селективность. Индекс по полю, где 98% значений одинаковы (например, ACTIVE = 'Y'), оптимизатор обычно игнорирует.
• Сортировка по полю другой таблицы, не той, где фильтр. Один индекс такой запрос не покроет.
• Выборка всех полей. Если в CIBlockElement::GetList не передан arSelectFields, а в итоговом SQL видны текстовые поля, временные таблицы чаще уходят на диск. Проверяйте итоговый запрос, а не предполагайте.
• Устаревшая статистика. После крупного импорта выполните ANALYZE TABLE: план мог поменяться не в вашу пользу.
• Глубокая пагинация. Страница с большим OFFSET читает и отбрасывает все предыдущие строки, индекс этого не лечит.
• Память. Если рабочий набор больше innodb_buffer_pool_size, запрос с индексом всё равно ходит на диск.

Что делать дальше

Включите журнал медленных запросов на сутки и выгрузите топ-10 из сводки. Проверьте ENGINE у всех таблиц и размеры служебных. Выберите один запрос, прогоните через EXPLAIN и соберите стенд из копии базы. В продакшне следите за четырьмя показателями: Threads_running, Slow_queries, отношением Innodb_buffer_pool_reads к read_requests и отставанием реплики, если она есть. Настройка my.cnf под Битрикс и кеширование компонентов это отдельные темы, здесь они не рассматриваются.