MySQL/MariaDB Configuration Optimization for 1C-Bitrix

Optimizing MySQL/MariaDB for Bitrix Workloads

The default MySQL configuration after installation is designed for a server with 256 MB of RAM. Bitrix on a real store means thousands of queries per minute to b_iblock_element, b_sale_order, and b_catalog_price. Without tuning my.cnf, the server operates with 128 MB buffer pools while 16 GB of available memory sits idle, performs disk I/O where it should be reading from cache, and maintains a connection pool that causes queuing under peak load.

Key Configuration Parameters

InnoDB Buffer Pool — the most important parameter. It must accommodate the entire working data set. On a dedicated database server, set it to 70–75% of RAM:

innodb_buffer_pool_size = 12G          # for 16GB RAM
innodb_buffer_pool_instances = 8       # 1 per GB, no more than 64
innodb_buffer_pool_chunk_size = 128M

InnoDB Log and flush. For Bitrix with intensive writes (orders, sessions, agents):

innodb_log_file_size = 1G
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 2     # acceptable for non-financial data
innodb_flush_method = O_DIRECT

innodb_flush_log_at_trx_commit = 2 delivers up to 30% write performance improvement at the cost of losing the last second of data in a hard crash — acceptable for most e-commerce sites.

Connections and threads:

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

Bitrix uses persistent connections via PHP-FPM. With 20 PHP-FPM workers and a pool of 10 processes each — 200 concurrent connections with headroom. Setting max_connections = 1000 unnecessarily reserves RAM for idle thread stacks.

Temporary tables. The Bitrix smart filter and search actively create temporary tables:

tmp_table_size = 256M
max_heap_table_size = 256M

If a temporary table does not fit in memory, MySQL writes it to disk, slowing the query by 10–50x. Monitor via SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables'.

Slow queries:

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

MariaDB Specifics

MariaDB 10.4+ has several parameters absent from MySQL:

innodb_adaptive_hash_index_parts = 8
aria_pagecache_buffer_size = 512M      # for MyISAM/Aria session tables

Bitrix stores PHP sessions in files by default, but when using database sessions or the bitrix.session module, session tables may be MyISAM — account for this when tuning.

What We Do as Part of the Service

We collect baseline metrics via mysqltuner.pl and pt-variable-advisor. We analyze SHOW GLOBAL STATUS over a period under load: reviewing Innodb_buffer_pool_reads vs Innodb_buffer_pool_read_requests (hit rate must be >99%), Sort_merge_passes, and Select_full_join.

We prepare a configuration file tailored to the specific server with a rationale for each parameter. We apply changes with staging testing, then incrementally on production with monitoring via Zabbix or Prometheus+mysqld_exporter.

Result

A correct MySQL configuration for Bitrix reduces average database response time by 40–70%, eliminates latency spikes under concurrent requests, and reduces disk I/O on the server by 2–5x.