Optimizing MySQL & MariaDB for Bitrix: No Upgrade Needed

We often encounter the situation: a server with 32 GB RAM, but MySQL only uses 2 GB. The default innodb_buffer_pool_size is 128 MB or even 8 MB. On a catalog with 500,000 SKU, the working dataset is 4-8 GB. Without a proper buffer, every request to uncached pages hits the disk: 5-10 ms instead of 0.1 ms from memory. Result: pages load in 10-15 seconds, admins complain, customers leave. Tuning MySQL and MariaDB for Bitrix is a task we solve regularly. Saving on hardware through proper configuration amounts to up to 30% of hosting budget.

Why MySQL Tuning Is Critical for Bitrix

Bitrix heavily uses InnoDB for storing infoblocks, trade catalogs, properties, and events. With default configuration, the database becomes a bottleneck, even if other server resources are abundant. We have optimized MySQL for dozens of Bitrix projects and know which parameters yield maximum gains. Reducing disk load also cuts support and renewal costs.

Proper InnoDB Buffer Pool Configuration

Buffer size is the most important parameter. Set it to 60-70% of RAM for a dedicated DB server. For 32 GB RAM, that is 20 GB. Add multiple buffer pool instances to reduce mutex contention: innodb_buffer_pool_instances = 8. If you have 64 GB RAM, you can set 40-45 GB with 8-16 instances. On servers with high concurrency, the number of pools should roughly equal the number of CPU cores. This gives up to 20% improvement in multi-threaded workloads.

InnoDB parameters:

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

[mysqld]
# ===== InnoDB Buffer Pool =====
# 60-70% of RAM for 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 per 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 (better use Memcached/Redis)
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 — transaction log is flushed to disk once per second, not on every COMMIT. Risk of losing 1 second of transactions on crash is acceptable for most online stores. Gives 3-5x write speedup. innodb_flush_method = O_DIRECT — MySQL writes directly to block device, bypassing OS page cache. Eliminates double caching.

What Tuning Gives for NVMe SSDs

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

This increases throughput by up to 30% compared to regular SSDs. For example, on a recent project with a catalog of 200,000 products, page generation time dropped from 12 to 2 seconds after tuning.

Performance comparison across configurations:

Bitrix Table Specifics

b_search_content — full-text index. 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 properties. With 1M+ rows and no indexes, smart filter slows down. We add indexes on IBLOCK_ELEMENT_ID, IBLOCK_PROPERTY_ID.

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

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

Also watch b_catalog_price and b_sale_basket — without indexes they heavily slow down cart and price lists.

How to Verify Tuning Effectiveness?

Monitor InnoDB status:

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

If buffer_pool_hit_rate < 95%, innodb_buffer_pool_size is too small — data is constantly read from disk.

Our Process

1. Analysis — collect current configuration, profile load, measure query times.
2. Design — select parameters for your data volume, traffic, and hardware.
3. Implementation — change config files, restart MySQL (5-10 sec downtime) or apply via SET GLOBAL.
4. Testing — check via slow log, monitor buffer pool hit rate, fix indexes.
5. Deployment — final tuning and documentation.

What's Included

• Optimization of MySQL/MariaDB parameters for your version and load.
• Verification and adjustment of indexes on Bitrix tables.
• Slow log enabled and analysis instructions.
• Report with rationale for each parameter.
• 7 days of support after tuning.
• Savings on hardware through performance improvement.

We have 5+ years of experience tuning MySQL for Bitrix and have optimized over 50 projects. We guarantee a 3-5x database performance boost.

Contact us for a database audit and get the optimal configuration for your tasks.

See more about InnoDB on Wikipedia.

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.