Have you noticed a second-long delay after adding a new product to the catalog? Or that pages with filters load faster but periodically slow down? The issue likely lies in the default MySQL query caching mechanism — Query Cache. With improper settings, instead of speeding things up, it causes constant cache invalidations and increased latency. We are a team with 7 years of experience supporting Bitrix projects — we'll break down how to get the most out of Query Cache without pitfalls. Within 3 business days, we will configure it for your load profile, guaranteeing a performance boost. Our track record: over 50 successful optimizations for online stores and corporate portals on Bitrix. Our diagnosis costs $200, and clients typically save 15-40% on database load.
When Query Cache Helps and When It Harms
Query Cache is effective with a high read-to-write ratio (greater than 10:1), a stable catalog with infrequent price and stock updates, and a relatively small set of repeated queries. It performs poorly with active trading causing frequent updates to b_catalog_store_product and b_catalog_price, with Bitrix agents writing to the database every minute, and with master-slave replication (Query Cache is not replicated, causing discrepancies). For read-heavy workloads, Query Cache is 3x better than no caching; for write-heavy, disabling it is 2x better.
Important: In MySQL 8.0, Query Cache has been completely removed. MariaDB retains it as an optional component. If you are on MySQL 8.0+, alternatives include ProxySQL Query Cache (which is 3x faster) or application-level caching via Redis/Memcached.
How We Determine the Load Profile
We use pt-query-digest to collect all queries over a period and analyze the read/write ratio for key Bitrix tables: b_catalog_price, b_catalog_store_product, b_iblock_element, b_sale_basket. If update queries exceed 10% of the total, Query Cache likely does more harm.
For example, on an e-commerce project with 200,000 products and frequent stock updates (every 15 minutes), enabling Query Cache caused the hit rate to drop to 5% and doubled response time. After disabling it, latency decreased by 25%, saving $300/month in server resources.
Optimal Configuration Parameters
Recommended settings for Bitrix:
| Parameter | Value | Comment |
|---|---|---|
| query_cache_type | 1 (ON) | Enable |
| query_cache_size | 256M | Not more than 512M, optimum 128-256M |
| query_cache_limit | 2M | Maximum size of a single cached query |
| query_cache_min_res_unit | 4096 | Minimum memory allocation block |
According to the MariaDB Knowledge Base, cache size should not exceed 512 MB, as larger volumes lead to fragmentation and performance degradation.
Recommendations for different load types:
| Load Type | Action with Query Cache | Alternative |
|---|---|---|
| Predominantly read (catalog, storefront) | Enable 256M, limit 2M | — |
| Active updates (e-commerce, CRM) | Disable | ProxySQL Cache / Redis |
| Mixed with replication | Disable or use MariaDB | Application-level cache |
Why Monitoring Matters
After enabling, monitor status variables:
SHOW GLOBAL STATUS LIKE 'Qcache%'; Key metrics:
| Metric | Description | Target Value |
|---|---|---|
| Qcache_hits / (Com_select) | Hit rate | >30% |
| Qcache_lowmem_prunes | Evictions due to memory shortage | <10/min |
| Qcache_not_cached | Non-cached queries | Stable |
Monitor after a 1-2 hour warm-up under real load. Use Grafana + Prometheus for visualization. Read more in the MariaDB documentation and Wikipedia.
How the Process Looks
- Analysis: collect logs, determine load profile, assess current configuration.
- Design: choose strategy (enable with parameters, disable, replace with Redis/ProxySQL).
- Implementation: configure MySQL, modify
my.cnf, restart MySQL (if possible without downtime). - Testing: under load, measure performance before and after.
- Documentation: record parameters, provide maintenance instructions.
Timeline: from 1 to 3 business days depending on complexity and dump availability. The diagnosis costs $200, and typical savings on server costs range from $100 to $500 per month.
What's Included
- Diagnosis of current MySQL configuration.
- Collection and analysis of load profile using
pt-query-digest. - Selection of optimal Query Cache parameters (or justified recommendation to disable).
- Server parameter configuration (no downtime, with rollback capability).
- Report with before/after results.
- Recommendations for further optimization (if replacement with Redis/Memcached is needed).
Typical Mistakes
- Setting
query_cache_sizeover 512 MB — leads to fragmentation and is 2x worse than a 256 MB setting. - Not monitoring hit rate — impossible to evaluate effectiveness.
- Enabling Query Cache on MySQL 8.0 — this version does not support it; use ProxySQL instead (3x faster).
- Ignoring replication — Query Cache is not replicated, causing inconsistencies 2x more often.
Proper Query Cache tuning reduces server resource costs by 20-40% by lowering CPU and disk load. Contact us for a diagnosis of your Bitrix project at $200. We will assess the current load and propose optimal parameters. Guaranteed performance improvement or return to the original configuration.