Optimizing MySQL & MariaDB for Bitrix: No Upgrade Needed

We often encounter the situation: a server with 32 GB RAM, but MySQL only uses 2 GB. The default innodb_buffer_pool_size is 128 MB or even 8 MB. On a catalog with 500,000 SKU, the working dataset is 4-8 GB. Without a proper buffer, every request to uncached pages hits the disk: 5-10 ms instead of 0.1 ms from memory. Result: pages load in 10-15 seconds, admins complain, customers leave. Tuning MySQL and MariaDB for Bitrix is a task we solve regularly. Saving on hardware through proper configuration amounts to up to 30% of hosting budget.

Why MySQL Tuning Is Critical for Bitrix

Bitrix heavily uses InnoDB for storing infoblocks, trade catalogs, properties, and events. With default configuration, the database becomes a bottleneck, even if other server resources are abundant. We have optimized MySQL for dozens of Bitrix projects and know which parameters yield maximum gains. Reducing disk load also cuts support and renewal costs.

Proper InnoDB Buffer Pool Configuration

Buffer size is the most important parameter. Set it to 60-70% of RAM for a dedicated DB server. For 32 GB RAM, that is 20 GB. Add multiple buffer pool instances to reduce mutex contention: innodb_buffer_pool_instances = 8. If you have 64 GB RAM, you can set 40-45 GB with 8-16 instances. On servers with high concurrency, the number of pools should roughly equal the number of CPU cores. This gives up to 20% improvement in multi-threaded workloads.

InnoDB parameters: File /etc/mysql/conf.d/bitrix.cnf:

[mysqld] # ===== InnoDB Buffer Pool ===== # 60-70% of RAM for dedicated DB server innodb_buffer_pool_size = 20G innodb_buffer_pool_instances = 8 # ~1 instance per 1-2GB # ===== InnoDB I/O ===== innodb_io_capacity = 2000 # for SSD: 2000-4000 innodb_io_capacity_max = 4000 innodb_flush_method = O_DIRECT # bypass OS page cache innodb_flush_log_at_trx_commit = 2 # no fsync per transaction # ===== Redo Log ===== # MySQL 8.0: managed automatically # MariaDB / MySQL 5.7: innodb_log_file_size = 1G innodb_log_buffer_size = 64M # ===== Connections ===== max_connections = 500 thread_cache_size = 50 wait_timeout = 300 interactive_timeout = 300 # ===== Query Cache ===== # MySQL 8.0: Query Cache removed # MariaDB / MySQL 5.7: disable (better use Memcached/Redis) query_cache_type = 0 query_cache_size = 0 # ===== Temp Tables ===== tmp_table_size = 256M max_heap_table_size = 256M # ===== Slow Log ===== slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow.log long_query_time = 1 log_queries_not_using_indexes = 1 

innodb_flush_log_at_trx_commit = 2 — transaction log is flushed to disk once per second, not on every COMMIT. Risk of losing 1 second of transactions on crash is acceptable for most online stores. Gives 3-5x write speedup. innodb_flush_method = O_DIRECT — MySQL writes directly to block device, bypassing OS page cache. Eliminates double caching.

What Tuning Gives for NVMe SSDs

On modern NVMe drives you can be more aggressive:

innodb_io_capacity = 10000 innodb_io_capacity_max = 20000 innodb_read_io_threads = 8 innodb_write_io_threads = 8 

This increases throughput by up to 30% compared to regular SSDs. For example, on a recent project with a catalog of 200,000 products, page generation time dropped from 12 to 2 seconds after tuning.

Performance comparison across configurations:

Bitrix Table Specifics

b_search_content — full-text index. Table grows to 2-5 GB on large sites. If Elasticsearch is used, this table can be truncated and built-in indexing disabled.

b_iblock_element_prop_m* — multiple properties. With 1M+ rows and no indexes, smart filter slows down. We add indexes on IBLOCK_ELEMENT_ID, IBLOCK_PROPERTY_ID.

b_event — system event log. On active sites it grows 10-50 MB per day. Clean via agent or cron:

DELETE FROM b_event WHERE DATE_COLUMN < DATE_SUB(NOW(), INTERVAL 90 DAY); 

Also watch b_catalog_price and b_sale_basket — without indexes they heavily slow down cart and price lists.

How to Verify Tuning Effectiveness?

Monitor InnoDB status:

-- Buffer pool efficiency (should be > 99%) SELECT (1 - ( (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') / (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests') )) * 100 AS buffer_pool_hit_rate; -- Top waits SELECT * FROM sys.innodb_lock_waits; 

If buffer_pool_hit_rate < 95%, innodb_buffer_pool_size is too small — data is constantly read from disk.

Our Process

1. Analysis — collect current configuration, profile load, measure query times.
2. Design — select parameters for your data volume, traffic, and hardware.
3. Implementation — change config files, restart MySQL (5-10 sec downtime) or apply via SET GLOBAL.
4. Testing — check via slow log, monitor buffer pool hit rate, fix indexes.
5. Deployment — final tuning and documentation.

What's Included

• Optimization of MySQL/MariaDB parameters for your version and load.
• Verification and adjustment of indexes on Bitrix tables.
• Slow log enabled and analysis instructions.
• Report with rationale for each parameter.
• 7 days of support after tuning.
• Savings on hardware through performance improvement.

We have 5+ years of experience tuning MySQL for Bitrix and have optimized over 50 projects. We guarantee a 3-5x database performance boost.

Contact us for a database audit and get the optimal configuration for your tasks. See more about InnoDB on Wikipedia.