Оптимізація конфігурації MySQL/MariaDB для 1С-Бітрікс

Оптимізація MySQL/MariaDB під навантаження Бітрікс

Дефолтна конфігурація MySQL після встановлення розрахована на сервер з 256 МБ RAM. Бітрікс на реальному магазині — це тисячі запитів на хвилину до b_iblock_element, b_sale_order, b_catalog_price. Без налаштування my.cnf сервер працює з буферними пулами в 128 МБ при 16 ГБ доступної пам'яті, виконує дискові I/O там, де повинен читати з кешу, і тримає пул з'єднань на рівні, що викликає черги при піковому навантаженні.

Ключові параметри конфігурації

InnoDB Buffer Pool — найважливіший параметр. Повинен вміщати робочий набір даних повністю. На виділеному сервері БД встановлюємо 70–75% RAM:

innodb_buffer_pool_size = 12G          # для 16GB RAM
innodb_buffer_pool_instances = 8       # 1 на кожен GB, але не більше 64
innodb_buffer_pool_chunk_size = 128M

InnoDB Log і flush. Для Бітрікс з інтенсивними записами (замовлення, сесії, агенти):

innodb_log_file_size = 1G
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 2     # припустимо для нефінансових даних
innodb_flush_method = O_DIRECT

innodb_flush_log_at_trx_commit = 2 дає до 30% приросту продуктивності при записі ціною втрати даних останньої секунди при жорсткому падінні сервера — для більшості e-commerce сайтів це прийнятно.

З'єднання та потоки:

max_connections = 300
thread_cache_size = 64
table_open_cache = 4000
table_definition_cache = 2000

Бітрікс використовує постійні з'єднання через PHP-FPM. При 20 воркерах PHP-FPM і пулі в 10 процесів кожен — 200 одночасних з'єднань із запасом. Ставити max_connections = 1000 без потреби — значить резервувати RAM під невикористовувані thread stacks.

Тимчасові таблиці. Розумний фільтр і пошук Бітрікс активно створюють тимчасові таблиці:

tmp_table_size = 256M
max_heap_table_size = 256M

Якщо тимчасова таблиця не поміщається в пам'ять — MySQL пише її на диск, що сповільнює запит у 10–50 разів. Моніторимо через SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables'.

Повільні запити:

slow_query_log = 1
long_query_time = 0.5
log_queries_not_using_indexes = 1
min_examined_row_limit = 1000

Специфіка MariaDB

MariaDB 10.4+ має ряд параметрів, відсутніх у MySQL:

innodb_adaptive_hash_index_parts = 8
aria_pagecache_buffer_size = 512M      # для MyISAM/Aria таблиць сесій

Бітрікс за замовчуванням зберігає PHP-сесії у файлах, але при використанні сесій у БД або модуля bitrix.session таблиці сесій можуть бути MyISAM — враховуємо це при налаштуванні.

Що ми робимо в рамках послуги

Знімаємо базові метрики через mysqltuner.pl і pt-variable-advisor. Аналізуємо SHOW GLOBAL STATUS за період із навантаженням: дивимося на Innodb_buffer_pool_reads vs Innodb_buffer_pool_read_requests (hit rate має бути >99%), Sort_merge_passes, Select_full_join.

Готуємо конфігураційний файл під конкретний сервер з обґрунтуванням кожного параметра. Застосовуємо зміни з тестуванням на staging, потім поетапно на prod із моніторингом через Zabbix або Prometheus+mysqld_exporter.

Результат

Коректна конфігурація MySQL під Бітрікс знижує середній час відповіді БД на 40–70%, прибирає піки latency при конкурентних запитах, знижує дисковий I/O на сервері у 2–5 разів.