InnoDB Buffer Pool Configuration for 1C-Bitrix
MySQL spends a second on a query that should work in 10 milliseconds. SHOW STATUS LIKE 'Innodb_buffer_pool_reads' returns tens of thousands of physical reads per minute — data constantly read from disk. The reason is almost always one: innodb_buffer_pool_size is set to default 128 MB, but the working dataset of Bitrix site long exceeded gigabyte.
What Is InnoDB Buffer Pool and Why It's Critical for Bitrix
Buffer pool is RAM allocated to InnoDB for caching data and index pages. If working dataset fits in buffer pool, MySQL reads from RAM. If not — each miss costs several milliseconds of disk operation.
Typical medium-size Bitrix site:
-
b_iblock_element_property— 500 MB — 3 GB -
b_iblock_element— 50–300 MB -
b_catalog_price,b_catalog_product— 100–500 MB - indexes for these tables — similar amount
Total working set — 1–8 GB. Default 128 MB covers less than 10%.
Diagnosing Current State
-- Cache hit ratio (should be > 99%)
SELECT (1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100
AS hit_ratio
FROM (
SELECT VARIABLE_VALUE AS Innodb_buffer_pool_reads
FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
) r,
(
SELECT VARIABLE_VALUE AS Innodb_buffer_pool_read_requests
FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
) rr;
-- Pages evicted from cache
SHOW STATUS LIKE 'Innodb_buffer_pool_pages_flushed';
SHOW STATUS LIKE 'Innodb_buffer_pool_wait_free';
If hit_ratio below 99% — buffer pool definitely too small. If Innodb_buffer_pool_wait_free non-zero — critical shortage: write operations wait for free pages.
Calculating Correct Size
Find actual data and index sizes:
SELECT
ROUND(SUM(data_length + index_length) / 1024 / 1024, 0) AS total_mb,
ROUND(SUM(data_length) / 1024 / 1024, 0) AS data_mb,
ROUND(SUM(index_length) / 1024 / 1024, 0) AS index_mb
FROM information_schema.TABLES
WHERE table_schema = 'your_database';
Rule: buffer pool = 70–80% of working dataset size, but not more than 70–80% of total server RAM. On server with 8 GB RAM and 4 GB database — set 4–5 GB.
Configuration in my.cnf
[mysqld]
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4
innodb_buffer_pool_chunk_size = 128M
innodb_buffer_pool_instances — number of independent pools. Reduces thread contention for mutexes. Rule: 1 instance per 1 GB pool, maximum 64. For 4 GB pool — 4 instances.
innodb_buffer_pool_chunk_size must be multiple of instances: innodb_buffer_pool_size = chunk_size * instances * N. In example: 4G = 128M * 4 * 8 — correct.
Hot Change Without Restart
MySQL 5.7.5+ supports changing buffer pool on the fly:
SET GLOBAL innodb_buffer_pool_size = 4294967296; -- 4 GB in bytes
Change process is asynchronous. Track progress:
SHOW STATUS LIKE 'Innodb_buffer_pool_resize_status';
During resize — possible brief performance dip. Change during minimum load window.
Additional InnoDB Parameters for Bitrix
# Log file size (larger = less frequent checkpoint, fewer disk ops)
innodb_log_file_size = 512M
innodb_log_buffer_size = 64M
# Flush method (O_DIRECT avoids double OS caching)
innodb_flush_method = O_DIRECT
# I/O operations per second (adjust for disk type)
innodb_io_capacity = 2000 # SSD
innodb_io_capacity_max = 4000 # SSD
# Page size (16K by default — optimal for Bitrix)
# innodb_page_size = 16K -- change only when creating DB
innodb_flush_method = O_DIRECT critically important on Linux: without it data cached twice — in InnoDB buffer pool and OS page cache. Wastes RAM unnecessarily.
Monitoring After Changes
After 30–60 minutes of work under load:
-- How much data currently in cache
SELECT ROUND(Innodb_buffer_pool_bytes_data / 1024 / 1024 / 1024, 2) AS cached_gb
FROM (SELECT VARIABLE_VALUE AS Innodb_buffer_pool_bytes_data
FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME = 'Innodb_buffer_pool_bytes_data') t;
-- Pool usage percentage
SELECT ROUND(
(SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_data') /
(SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_total') * 100, 1
) AS pool_used_pct;
If pool_used_pct consistently 95–100% — pool full to brim, might increase. If 60–70% — current size with margin.