MySQL/MariaDB Configuration for 1C-Bitrix

MySQL/MariaDB Configuration for 1C-Bitrix

"A server with 32 GB RAM, but MySQL only uses 2 GB" — this is a typical situation on a 1C-Bitrix installation with default settings. innodb_buffer_pool_size defaults to 128 MB or 8 MB depending on the distribution. On a catalog with 500,000 SKUs, the working data set is 4–8 GB. Without a properly sized buffer pool, every query for non-cached pages hits the disk: 5–10 ms instead of 0.1 ms from memory.

Key InnoDB Parameters

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

[mysqld]
# ===== InnoDB Buffer Pool =====
# 60-70% of RAM for a 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 on every 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 it (use Memcached/Redis instead)
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 — the transaction log is flushed to disk once per second, not on every COMMIT. The risk of losing one second of transactions on a crash is acceptable for most e-commerce stores. Provides a 3–5× write performance improvement.

innodb_flush_method = O_DIRECT — MySQL writes directly to the block device, bypassing the OS page cache. Eliminates double caching (InnoDB buffer pool + OS cache).

Configuration for NVMe SSD

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

1C-Bitrix Tables: Specifics

b_search_content — full-text index. The 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-value properties. With 1M+ rows and no indexes, the smart filter slows to a crawl.

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

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

InnoDB State Monitoring

-- 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;

buffer_pool_hit_rate < 95% — innodb_buffer_pool_size is too small; data is being read from disk continuously.

Timeline

Auditing the current configuration, tuning parameters, and verification via the slow log takes 4–8 hours. Applying the changes requires a MySQL restart (5–10 seconds of downtime) or some parameters can be applied with SET GLOBAL without a restart.