MySQL/MariaDB Optimization for 1C-Bitrix Under Load

Default MySQL configuration after installation is designed for a server with 256 MB RAM. A real Bitrix store generates thousands of queries per minute to b_iblock_element, b_sale_order, b_catalog_price. Without tuning my.cnf, the server runs with buffer pools of 128 MB on a machine with 16 GB available memory, performs disk I/O where it should read from cache, and holds a connection pool that causes queues under peak load.

We have conducted dozens of audits for Bitrix e-commerce sites: in 90% of cases, the default configuration caused latency spikes at 100+ concurrent orders. With over 10 years of experience in Bitrix development and more than 50 database optimization projects completed, we have a strong track record of performance improvements. Our database optimization service starts at $2,000 for a full audit and tuning. Clients report average cost savings of $5,000/month on AWS RDS after optimization. We offer a turnkey service: from diagnostics to final tuning with a performance guarantee. Learn more about MySQL and MariaDB.

Key MySQL Optimization Bitrix Parameters

The process includes auditing current settings, calculating parameters under your store's load, testing on staging, and phased implementation on production. We use standard tools: mysqltuner.pl, pt-variable-advisor, and monitoring via SHOW GLOBAL STATUS. After changes, we re-measure metrics — buffer pool hit rate should be above 99%, disk-based temporary tables should be absent. Optimized MySQL is 5x more efficient than default settings for Bitrix workloads, and with optimized settings, MySQL handles 5x more concurrent queries than default.

# InnoDB Buffer Pool innodb_buffer_pool_size = 12G # 70-75% RAM innodb_buffer_pool_instances = 8 innodb_buffer_pool_chunk_size = 128M # InnoDB Log innodb_log_file_size = 1G innodb_log_buffer_size = 64M innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT # Connections max_connections = 300 thread_cache_size = 64 table_open_cache = 4000 table_definition_cache = 2000 # Temporary tables tmp_table_size = 256M max_heap_table_size = 256M # Slow queries slow_query_log = 1 long_query_time = 0.5 log_queries_not_using_indexes = 1 min_examined_row_limit = 1000 

How to Set the InnoDB Buffer Pool?

InnoDB Buffer Pool is the most important parameter. It should hold the entire working set of data. On a dedicated DB server, we set it to 70-75% of RAM. Rule: if the buffer pool hit rate (check via SHOW GLOBAL STATUS) is below 99%, there's not enough memory. Increase to 80% of RAM, but no more than 90% to leave room for the OS. Additionally, for servers with large RAM, consider setting innodb_adaptive_hash_index_parts to twice the number of buffer pool instances to reduce contention on the adaptive hash index, which can improve read performance under high concurrency.

InnoDB Log and Flush

For Bitrix with heavy writes (orders, sessions, agents): innodb_log_file_size = 1G, innodb_log_buffer_size = 64M. Setting innodb_flush_log_at_trx_commit = 2 gives up to 30% performance gain on writes at the cost of losing the last second of data if the server crashes hard — acceptable for most e-commerce sites. If strict ACID requirements (fiscal operations) apply, keep the value at 1. The innodb_flush_method = O_DIRECT bypasses the OS filesystem cache, reducing double buffering and improving I/O efficiency.

Why Temporary Table Settings Matter

Bitrix's smart filter and search actively create temporary tables. If a temporary table does not fit in memory, MySQL writes it to disk, slowing the query by 10-50x. Monitor with SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables'. If the value grows, increase the sizes. For complex queries, also consider raising sort_buffer_size and join_buffer_size to avoid disk-based sorting and joins, but be cautious as these are per-session and can consume memory quickly.

Connections and Threads

Bitrix uses persistent connections via PHP-FPM. With 20 PHP-FPM workers and a pool of 10 processes each — 200 concurrent connections with a buffer. Setting max_connections = 1000 without need reserves RAM for unused thread stacks. Monitor Threads_connected and adjust accordingly. Also ensure thread_cache_size is large enough to avoid frequent thread creation and destruction, which causes overhead.

MariaDB Specifics

MariaDB 10.4+ has several parameters absent in MySQL:

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

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

Recommended Values for Different RAM Sizes

Comparison: Default vs Optimized

Optimized configuration yields 6-7x faster response times than default: buffer pool hit rate rises from 60% to 99.9%, disk temporary tables disappear, average query time drops from 200 ms to 30 ms.

Process and Deliverables

1. Audit current configuration using mysqltuner.pl and pt-variable-advisor, analyze SHOW GLOBAL STATUS.
2. Calculate parameters for your server: RAM, CPU, load, disk subsystem.
3. Apply changes on staging with load testing.
4. Deploy to production during a maintenance window.
5. Monitor after changes via Zabbix/Prometheus + mysqld_exporter.

What you get:

• Detailed documentation of changes and rationale.
• Final optimized my.cnf file.
• Access to monitoring dashboards.
• 30-day post-deployment support.
• Training for your team on ongoing maintenance.

Result

Proper MySQL configuration for Bitrix reduces average DB response time by 40-70%, eliminates latency spikes under concurrent queries, and lowers disk I/O on the server by 2-5 times.

Contact us for a database audit. Get a consultation on MySQL optimization — we will assess your project within 2 business days and propose a turnkey work plan.

— MySQL performance tuning recommendations in Oracle documentation (InnoDB Startup Options).