Налаштування 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% — поточний розмір з запасом.