Налаштування innodb_buffer_pool для 1С-Бітрікс: прискорення MySQL

Чому buffer pool у 128 МБ гальмує Бітрікс?

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

Ми не раз стикалися з ситуацією, коли власники інтернет-магазинів на 1С-Бітрікс скаржаться на гальма в пік навантаження, а корінь проблеми — саме в buffer pool. Наприклад, нещодавно на проекті з 50 000 товарів hit_ratio становив 87% — кожне сьоме читання йшло на диск. Після налаштування пулу на 4 ГБ hit_ratio піднявся до 99.8%, середній час запиту впав з 350 до 20 мс. За 5 років ми налаштували InnoDB на сотнях проектів і гарантуємо прискорення запитів щонайменше в 2-3 рази після правильної конфігурації.

Що таке InnoDB Buffer Pool і чому його розмір критичний

Buffer pool — це оперативна пам'ять, виділена InnoDB для кешування сторінок даних та індексів. Якщо робочий набір даних вміщується в buffer pool, MySQL читає з RAM. Якщо ні — кожний промах коштує кількох мілісекунд дискової операції. Докладніше про механізм можна прочитати в Wikipedia.

Типовий Бітрікс-сайт середнього розміру:

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

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

Діагностика та розрахунок buffer pool

Як діагностувати нестачу?

Виконайте запити:

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 ГБ.

Налаштування buffer pool в 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 критичні для Бітрікс?

Крім buffer pool, важливо налаштувати:

innodb_log_file_size = 512M innodb_log_buffer_size = 64M innodb_flush_method = O_DIRECT innodb_io_capacity = 2000 innodb_io_capacity_max = 4000 

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

Порівняння продуктивності до і після налаштування

Типові помилки при налаштуванні

• Встановлення розміру більше доступної RAM (призводить до swap).
• Ігнорування innodb_flush_method = O_DIRECT на Linux.
• Зміна розміру на льоту без моніторингу прогресу.
• Вибір некратних chunk_size значень (помилка округлення).

Що входить у налаштування buffer pool під ключ

• Діагностика поточного стану: hit_ratio, розмір даних, конфігурація.
• Розрахунок оптимального розміру з урахуванням RAM та навантаження.
• Зміна параметрів (buffer pool, log, flush method) з гарячим застосуванням.
• Моніторинг після змін та звіт з рекомендаціями.
• Консультація щодо інших вузьких місць (індекси, кешування Бітрікс).
• Гарантія на результат: hit_ratio > 99% після прогріву пулу.

Зв'яжіться з нами для безкоштовної попередньої діагностики. Замовте налаштування buffer pool і отримайте прискорення бази вже сьогодні. Ми допоможемо підібрати конфігурацію під ваш проект і забезпечимо стабільну роботу в пік навантаження.