MySQL Query Profiling for Bitrix: Audit & Optimization

A catalog page with 30,000 products generates 150 SQL queries, and the MySQL server hits 90% CPU — this is not a code problem, it's a lack of profiling. Without slow_query_log, you guess which query consumes resources — profiling gives a clear answer. Over a decade we have conducted more than 50 MySQL audits on 1C-Bitrix projects: every second project had similar patterns — N+1 queries, missing indexes on key tables (b_iblock_element, b_catalog_price), suboptimal InnoDB buffers. The result — pages load in 5–7 seconds, the server crashes under peak load. For diagnostics we use slow query log, pt-query-digest and EXPLAIN ANALYZE, as well as the built-in Bitrix tracker for ORM queries. Average page load time saving after optimization — 50–70%. Profiling MySQL queries in Bitrix reduces CPU load by 3-5 times, and TTFB by 60-80%. Typical savings on server infrastructure — significant depending on scale.

Step-by-step guide: enabling slow_query_log

1. On the MySQL server execute SET GLOBAL slow_query_log = 'ON';
2. Set the threshold: SET GLOBAL long_query_time = 0.5;
3. Specify the log file: SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
4. Enable logging queries without indexes: SET GLOBAL log_queries_not_using_indexes = 1;
5. For permanent configuration, write parameters in /etc/mysql/conf.d/slow.cnf.

How MySQL query profiling is performed in 1C-Bitrix?

The foundation is the MySQL slow query log. We enable it for 1–2 days with a threshold of 0.5 seconds. For production we use a configuration file with the parameter min_examined_row_limit = 100 to filter out fast queries by primary key.

Analysis via pt-query-digest

Percona Toolkit is the industry standard. The utility groups queries by pattern, shows frequency and total time.

pt-query-digest /var/log/mysql/slow.log --limit 20 --report-format query_report > /tmp/slow_report.txt

On Bitrix projects, the top 5 problematic queries usually include:

• N+1 when fetching properties (b_iblock_element_prop_s*)
• Per-record price fetching (b_catalog_price)
• COUNT without index
• Full-text search on a large table
• Contention for session updates (b_user_session)

EXPLAIN and EXPLAIN ANALYZE

For each slow query we run EXPLAIN. Critical signs: type = ALL (full scan), rows > 1000, Extra: Using filesort / temporary.

EXPLAIN SELECT be.ID, be.NAME, bp.VALUE
FROM b_iblock_element be
LEFT JOIN b_iblock_element_prop_s5 bp ON bp.IBLOCK_ELEMENT_ID = be.ID
WHERE be.IBLOCK_ID = 12 AND be.ACTIVE = 'Y' AND be.WF_STATUS_ID = 1
ORDER BY be.SORT ASC LIMIT 48 OFFSET 0;

-- EXPLAIN ANALYZE (MySQL 8.0+) shows actual time
EXPLAIN ANALYZE SELECT ... ;

EXPLAIN ANALYZE works twice as fast as manual plan analysis — it immediately reveals bottlenecks.

Which indexes are critical for Bitrix?

Several indexes that are often missing in a standard installation:

CREATE INDEX idx_iblock_element_active_sort
ON b_iblock_element (IBLOCK_ID, ACTIVE, WF_STATUS_ID, SORT);

CREATE INDEX idx_catalog_price_product_group
ON b_catalog_price (PRODUCT_ID, CATALOG_GROUP_ID);

CREATE INDEX idx_user_session_timestamp
ON b_user_session (TIMESTAMP_X);

After creating the index, re-run EXPLAIN — the type should change from ALL to ref.

Why is N+1 a common problem in Bitrix ORM?

D7 ORM often generates N+1 queries. Diagnostics via the built-in tracker:

\Bitrix\Main\Application::getConnection()->setTracker(new \Bitrix\Main\DB\SqlTracker(50));
// At the end of the request
$tracker = \Bitrix\Main\Application::getConnection()->getTracker();
foreach ($tracker->getQueries() as $query) {
    if ($query->getTime() > 0.1) error_log($query->getSql() . ' [' . $query->getTime() . 's]');
}

// Fix: instead of per-record queries — batch fetch
$ids = array_column($elements->fetchAll(), 'ID');
$prices = PriceTable::getList(['filter' => ['PRODUCT_ID' => $ids]])->fetchAll();
$priceMap = array_column($prices, null, 'PRODUCT_ID');

# Query 1: 12.5k calls, avg 0.8s, 97% of total time
SELECT ... FROM b_iblock_element_prop_s8 ...
# Query 2: 500 calls, avg 2.1s
SELECT ... FROM b_catalog_price ...

Case study: wholesale distributor

From our practice: a Bitrix "Small Business" site, catalog of 28,000 items, 3,000 visitors/day. Server 4 CPU, 8 GB RAM. MySQL load — 85–90% CPU. pt-query-digest showed that 92% of time is spent on b_iblock_element_prop_s8 (string properties table) — full scan on 280,000 rows. A single index idx_prop_s8_element_id reduced load to 15–20% CPU without any code changes. TTFB dropped from 5 s to 1.2 s. On average across our projects, page load time savings are 50–70%. CPU reduction from 85% to 15% results in substantial long-term savings.

Monitoring tools

For more on slow query log: Wikipedia. We also recommend the official D7 ORM documentation for avoiding N+1.

What's included in the work

• Diagnostics: enable slow log, collect data, report with top-20 slow queries.
• Optimization: create indexes, refactor N+1, tune MySQL buffers.
• Monitoring: install PMM or Grafana, set up alerts.
• Documentation and training: describe all changes, recommendations for developers.

If you discover slow queries — order a MySQL profiling audit. We guarantee — after our optimization, database load will drop 3-5 times, and TTFB by 60-80%. Contact us for a free assessment of your project.

Time estimates

Order MySQL query profiling for 1C-Bitrix — get a detailed report and recommendations within a day.

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.