MySQL Query Cache Configuration for 1C-Bitrix

Configuring and Optimizing Query Cache for Bitrix

MySQL Query Cache is a mechanism for caching SELECT query results in memory. It sounds like an obvious improvement, but in practice a poorly configured Query Cache becomes a bottleneck rather than an accelerator: on any INSERT/UPDATE/DELETE to a table, MySQL invalidates all cached queries for that table. With a high write frequency, this results in constant cache flushes and performance degradation.

For Bitrix, the task is to correctly determine whether Query Cache is needed at all, and if so — to configure it for the actual load profile.

When Query Cache Helps and When It Hurts

Query Cache is effective with a high read-to-write ratio (>10:1), a stable catalog with infrequent price and stock updates, and a relatively small set of repeated queries.

Query Cache is harmful with active trading and frequent updates to b_catalog_store_product and b_catalog_price, when using Bitrix agents that write to the database every minute, and with master-slave replication (Query Cache is not replicated, causing inconsistencies).

Important: Query Cache was completely removed in MySQL 8.0. MariaDB retained it as an optional component. If you are running MySQL 8.0+ — the alternatives are ProxySQL Query Cache or application-level caching via Redis/Memcached.

Query Cache Configuration for Bitrix

query_cache_type = 1               # ON
query_cache_size = 256M            # no more than 512M — higher yields no benefit
query_cache_limit = 2M             # maximum size of a single result set
query_cache_min_res_unit = 4096    # minimum allocation block

Critically important: query_cache_size above 512 MB actually reduces performance in practice due to fragmentation and cache management overhead. The optimum for most Bitrix sites is 128–256 MB.

Monitoring Effectiveness

After enabling, monitor the status variables:

SHOW GLOBAL STATUS LIKE 'Qcache%';

Key metrics:

• Qcache_hits / (Qcache_hits + Com_select) — hit rate; must be >30% for a positive effect
• Qcache_lowmem_prunes — number of evictions due to insufficient memory; if growing rapidly — increase query_cache_size
• Qcache_not_cached — queries not placed in cache (non-cacheable queries: those with SQL_NO_CACHE, NOW(), RAND(), etc.)

Bitrix actively uses NOW() in queries for content publication dates — such queries are not cached by definition.

What We Do as Part of the Service

We analyze the load profile via pt-query-digest and status variables. We determine the read/write ratio for key Bitrix tables. Based on the analysis we make a decision: enable Query Cache with specific parameters, disable it in favor of Memcached/Redis, or move individual queries to SQL_NO_CACHE.

We configure the settings, verify the hit rate after warm-up under real load, and if necessary calibrate query_cache_size and query_cache_limit.

Result

A correctly configured Query Cache reduces database server CPU load by 15–40% on read-dominant sites. On high-load stores with frequent writes — disabling an incorrectly operating Query Cache reduces query latency by 20–30%.