Настройка innodb_buffer_pool для 1С-Битрикс

Настройка innodb_buffer_pool для 1С-Битрикс

MySQL тратит секунду на запрос, который должен работать за 10 миллисекунд. SHOW STATUS LIKE 'Innodb_buffer_pool_reads' возвращает десятки тысяч физических чтений в минуту — данные постоянно читаются с диска. Причина почти всегда одна: innodb_buffer_pool_size выставлен в дефолтные 128 МБ, а рабочий набор данных Битрикс-сайта давно перевалил за гигабайт.

Что такое InnoDB Buffer Pool и почему он критичен для Битрикс

Buffer pool — это оперативная память, выделенная InnoDB под кеширование страниц данных и индексов. Если рабочий набор данных умещается в buffer pool, MySQL читает из RAM. Если нет — каждый промах стоит несколько миллисекунд дисковой операции.

Типичный Битрикс-сайт среднего размера:

• b_iblock_element_property — 500 МБ — 3 ГБ
• b_iblock_element — 50–300 МБ
• b_catalog_price, b_catalog_product — 100–500 МБ
• индексы к этим таблицам — ещё столько же

Итого рабочий набор — 1–8 ГБ. Дефолтные 128 МБ покрывают меньше 10%.

Диагностика текущего состояния

-- Коэффициент попаданий в кеш (должен быть > 99%)
SELECT (1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100
       AS hit_ratio
FROM (
    SELECT VARIABLE_VALUE AS Innodb_buffer_pool_reads
    FROM information_schema.GLOBAL_STATUS
    WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
) r,
(
    SELECT VARIABLE_VALUE AS Innodb_buffer_pool_read_requests
    FROM information_schema.GLOBAL_STATUS
    WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
) rr;

-- Сколько страниц вытеснялось из кеша
SHOW STATUS LIKE 'Innodb_buffer_pool_pages_flushed';
SHOW STATUS LIKE 'Innodb_buffer_pool_wait_free';

Если hit_ratio ниже 99% — buffer pool однозначно мал. Если Innodb_buffer_pool_wait_free ненулевое — критическая нехватка: операции записи ждут свободных страниц.

Расчёт правильного размера

Узнать реальный размер данных и индексов:

SELECT
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 0) AS total_mb,
    ROUND(SUM(data_length) / 1024 / 1024, 0) AS data_mb,
    ROUND(SUM(index_length) / 1024 / 1024, 0) AS index_mb
FROM information_schema.TABLES
WHERE table_schema = 'ваша_база';

Правило: buffer pool = 70–80% от размера рабочего набора данных, но не более 70–80% от общего объёма RAM сервера. На сервере с 8 ГБ RAM и базой 4 ГБ — выставлять 4–5 ГБ.

Настройка в my.cnf

[mysqld]
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4
innodb_buffer_pool_chunk_size = 128M

innodb_buffer_pool_instances — количество независимых пулов. Уменьшает конкуренцию потоков за мьютексы. Правило: 1 инстанс на каждый 1 ГБ пула, максимум 64. При 4 ГБ пула — 4 инстанса.

innodb_buffer_pool_chunk_size должен быть кратен числу инстансов: innodb_buffer_pool_size = chunk_size * instances * N. В примере: 4G = 128M * 4 * 8 — корректно.

Горячее изменение без рестарта

MySQL 5.7.5+ поддерживает изменение buffer pool на лету:

SET GLOBAL innodb_buffer_pool_size = 4294967296; -- 4 ГБ в байтах

Процесс изменения асинхронный. Отследить прогресс:

SHOW STATUS LIKE 'Innodb_buffer_pool_resize_status';

Пока идёт resize — возможно кратковременное снижение производительности. Менять в окно минимальной нагрузки.

Дополнительные параметры InnoDB для Битрикс

# Размер лог-файлов (больше = реже checkpoint, меньше дисковых операций)
innodb_log_file_size = 512M
innodb_log_buffer_size = 64M

# Метод сброса на диск (O_DIRECT исключает двойное кеширование ОС)
innodb_flush_method = O_DIRECT

# Операции I/O в секунду (настроить под тип диска)
innodb_io_capacity = 2000         # SSD
innodb_io_capacity_max = 4000     # SSD

# Размер страницы (16K по умолчанию — оптимально для Битрикс)
# innodb_page_size = 16K  -- менять только при создании БД

innodb_flush_method = O_DIRECT критически важен на Linux: без него данные кешируются дважды — в buffer pool InnoDB и в page cache ядра. Это съедает RAM впустую.

Мониторинг после изменений

Через 30–60 минут работы под нагрузкой:

-- Сколько данных сейчас в кеше
SELECT ROUND(Innodb_buffer_pool_bytes_data / 1024 / 1024 / 1024, 2) AS cached_gb
FROM (SELECT VARIABLE_VALUE AS Innodb_buffer_pool_bytes_data
      FROM information_schema.GLOBAL_STATUS
      WHERE VARIABLE_NAME = 'Innodb_buffer_pool_bytes_data') t;

-- Процент использования пула
SELECT ROUND(
    (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS
     WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_data') /
    (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS
     WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_total') * 100, 1
) AS pool_used_pct;

Если pool_used_pct стабильно 95–100% — пул заполнен до краёв, возможно стоит увеличить. Если 60–70% — текущий размер с запасом.