MySQL Query Cache Tuning for Bitrix: Reduce Database Load by 15-40%

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:

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:

Why Monitoring Matters

After enabling, monitor status variables:

SHOW GLOBAL STATUS LIKE 'Qcache%';

Key metrics:

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

1. Analysis: collect logs, determine load profile, assess current configuration.
2. Design: choose strategy (enable with parameters, disable, replace with Redis/ProxySQL).
3. Implementation: configure MySQL, modify my.cnf, restart MySQL (if possible without downtime).
4. Testing: under load, measure performance before and after.
5. 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_size over 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.

Problems We Solve

rsync -avz to production on Friday evening, restart php-fpm, and the site returns 502 — local database settings remain in .settings.php. Classic “deployment the old way” turns into a lottery. Another scenario: a module update from the admin panel breaks a custom component template — changes aren’t tracked in version control, recovery takes hours. Without a DevOps culture, every release is a gamble.

We design a predictable DevOps cycle for 1C-Bitrix: from Docker environment to Telegram alerts. Each deployment becomes routine, each incident triggers a context‑rich alert. Below is how we solve real Bitrix team problems — with numbers, tools, and proven configurations.

Why DevOps Is Critical for Bitrix Projects?

Bitrix projects carry specific infrastructure requirements: heavy e‑commerce catalogs, 1C exchange via CommerceML, dozens of agents and events. Without CI/CD and monitoring, every change introduces risk. A single stuck agent can silently break a 1C sync for hours; a manual deployment mistake can cost a client lost orders. We’ve seen teams spend 12 hours per month just on manual deployments and crash recovery — after our CI/CD pipeline, that drops to zero.

CI/CD Pipeline: From Commit to Production Without Hands

Git migration – we move the project from FTP to Git (GitLab, GitHub, Bitbucket). Branch structure: main (production), staging, develop, feature branches. A proper .gitignore for Bitrix is non‑trivial:

/bitrix/cache/
/bitrix/managed_cache/
/bitrix/stack_cache/
/upload/
/bitrix/php_interface/dbconn.php
/bitrix/.settings.php
/bitrix/license_key.php

Miss managed_cache/ → the repository bloats to gigabytes. Forget license_key.php → the key leaks.

CI pipeline – automatically runs PHPStan level 5+, PHP_CodeSniffer with Bitrix standard, PHPUnit for business logic, composer audit, frontend build.

CD pipeline – deploys without human intervention. Merge to staging → deploy to staging. Merge to main → deploy to production (with optional manual confirmation). Zero‑downtime via symlink strategy: new version in a separate folder, current → symlink switches in milliseconds. upload/ lives outside release directories. Healthcheck fails → symlink rolls back automatically. Tools: GitLab CI/CD, GitHub Actions, Deployer (PHP). Deployer’s built‑in recipes for Bitrix handle shared directories and symlink deployment out‑of‑the‑box.

Docker Environment: How We Eliminate “It Works on My Machine”

The Docker environment fixes versions of all components: nginx, PHP, MySQL, Redis. Configuration mirrors production — same PHP modules, same php.ini.

Local development – docker-compose.yml includes nginx + php‑fpm 8.1/8.2 + MySQL 8.0 (or MariaDB 10.6) + Redis + Memcached. New developer: git clone + docker-compose up -d → writes code within 5 minutes. Parallel work on different PHP versions via separate compose files.

Bitrix specifics in Docker:

• /upload/ mounted as a named volume (not bind mount — permission and speed issues on Windows/Mac).
• Cron jobs (/bitrix/modules/main/tools/cron_events.php) run via a separate container with supervisord.
• “Proactive Protection” module (security) blocks requests through reverse proxy — need set_real_ip_from and realip_module.
• Database config (dbconn.php, .settings.php) set via environment variables, never through a volume with production configs.

Production – multi‑stage Dockerfile (build stage for assets, production stage with lightweight image), Docker Registry for tagged images, orchestration via Docker Swarm or Kubernetes for large projects.

Nginx and PHP‑FPM Configuration for Bitrix Performance

The difference between “site is slow” and 200 ms TTFB lies in configuration. nginx:

• location blocks for Bitrix handle urlrewrite.php for friendly URLs.
• /bitrix/admin/ IP‑restricted via allow/deny.
• expires 30d for static files — CSS, JS, images cached by the browser.
• Brotli compression (15‑20% better than gzip): brotli on; brotli_comp_level 6;.
• Rate limiting on /bitrix/tools/ protects against brute force.
• HTTP/2 push for critical resources.

php‑fpm: pm = dynamic. Calculate pm.max_children: (RAM - RAM_other_services) / avg_memory_per_process. For Bitrix, avg is 40–80 MB. OPcache: opcache.memory_consumption=256 (default 128 is insufficient — Bitrix loads thousands of files), opcache.max_accelerated_files=20000, opcache.validate_timestamps=0 in production (reset via cachetool opcache:reset on deployment). php.ini: memory_limit=256M (up to 512M for heavy imports), max_execution_time=60, upload_max_filesize=100M. Slowlog with request_slowlog_timeout=5s catches bottlenecks before users complain.

Monitoring and Logging: What We Track

Infrastructure – Prometheus + Grafana: metrics for CPU, RAM, disk, network, service status. Alerts: CPU > 80% for 5 minutes, free RAM < 500 MB, disk > 85%, php‑fpm queue > 0 (worker shortage). Node Exporter, MySQL Exporter, PHP‑FPM Exporter collect data.

Application – Uptime check every 60 seconds → Telegram alert within a minute of downtime. Response time of key URLs: /, /catalog/, /personal/order/make/. Sentry for PHP errors — structured errors with context. Bitrix agents (b_agent): we check NEXT_EXEC < NOW() - INTERVAL 1 HOUR — a stuck agent silently breaks 1C exchange.

Logging – ELK Stack or Loki + Grafana: nginx access/error, php‑fpm slow log, MySQL slow query log, Bitrix errors. Rotation via logrotate — without it, access.log takes 50 GB after six months.

Backup Strategy and Disaster Recovery

Geographic distribution — S3‑compatible storage + separate server in another datacenter. Test restoration monthly — a backup never restored is just an illusion of security. Cron with notifications: if backup fails, alert immediately.

What’s Included in the Service

Our team brings 5+ years of Bitrix DevOps experience (over 50 successful projects) and certified engineers. The service provides:

• DevOps process documentation (deployment scheme, branch policy, infrastructure description)
• Configured CI/CD pipelines (GitLab CI / GitHub Actions) with working triggers
• Docker environment (docker-compose.yml, Dockerfile, configs)
• Ansible playbooks for server reproduction
• Monitoring (Grafana dashboards, alerts in Telegram / Slack)
• Secured access with role‑based model
• Team training: two sessions on CI/CD, Docker, and deployment
• Support during implementation (two weeks after launch)

Infrastructure‑as‑code with Ansible is 5× faster than manual server configuration and eliminates human errors.

Implementation Process and Timelines

1. Audit of current state – assess infrastructure, software, processes (2–3 days).
2. Architecture design – choose stack (Docker / K8s / Ansible), agree on CI/CD policies, set up repository.
3. Environment setup – Docker for local development, staging, production servers.
4. CI/CD implementation – write pipelines, test deployment, integrate with monitoring.
5. Monitoring and alerting – install Prometheus + Grafana, configure dashboards and notifications.
6. Team training – two sessions on tool usage.

DevOps is not a project with an end date — it’s a transition from “upload via FTP and pray” to predictable processes. Each deployment is routine, each incident carries context, each new developer does docker-compose up instead of a three‑day environment setup.

Get a consultation – we’ll prepare a tailored implementation plan within 2–3 days. Order a turnkey DevOps implementation – gain stability and full control over your infrastructure.