Профілювання MySQL-запитів 1С-Бітрікс: аудит та оптимізація

Сторінка каталогу з 30 тисячами товарів генерує 150 SQL-запитів, а сервер MySQL упирається в 90% CPU — це не проблема коду, це відсутність профілювання. Без slow_query_log ви гадаєте, який запит з'їдає ресурси — профілювання дає чітку відповідь. За десять років ми провели понад 50 аудитів MySQL на проєктах 1С-Бітрікс: кожен другий проєкт мав схожі патерни — N+1 запити, відсутність індексів на ключових таблицях (b_iblock_element, b_catalog_price), неоптимальні буфери InnoDB. Результат — сторінки вантажаться 5–7 секунд, сервер падає при пікових навантаженнях. Для діагностики ми використовуємо slow query log, pt-query-digest та EXPLAIN ANALYZE, а також вбудований трекер Бітрікс для ORM-запитів. Середня економія часу завантаження після оптимізації — 50–70%. Профілювання MySQL-запитів Бітрікс дозволяє знизити навантаження CPU в 3-5 разів, а TTFB — на 60-80%. Типова економія на серверній інфраструктурі — до 300 000 руб./рік (в перерахунку на гривні близько 120 000 грн).

Покрокова інструкція: включення slow_query_log

1. На сервері MySQL виконайте SET GLOBAL slow_query_log = 'ON';
2. Встановіть поріг: SET GLOBAL long_query_time = 0.5;
3. Вкажіть файл логу: SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
4. Увімкніть логування запитів без індексів: SET GLOBAL log_queries_not_using_indexes = 1;
5. Для постійної конфігурації пропишіть параметри в /etc/mysql/conf.d/slow.cnf.

Як виконується профілювання MySQL-запитів в 1С-Бітрікс?

Основа — slow query log MySQL. Вмикаємо його на 1–2 дні з порогом 0.5 секунди. Для production використовуємо файл конфігурації з параметром min_examined_row_limit = 100, щоб відсікти швидкі запити по первинному ключу.

Аналіз через pt-query-digest

Percona Toolkit — індустріальний стандарт. Утиліта групує запити за шаблоном, показує частоту та загальний час.

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

На Бітрікс-проєктах топ-5 проблемних запитів зазвичай містить:

• N+1 при вибірці властивостей (b_iblock_element_prop_s*)
• Поштучна вибірка цін (b_catalog_price)
• COUNT без індексу
• Повнотекстовий пошук по великій таблиці
• Конкуренція за оновлення сесій (b_user_session)

EXPLAIN та EXPLAIN ANALYZE

Для кожного повільного запиту запускаємо EXPLAIN. Критичні ознаки: 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+) показує реальний час
EXPLAIN ANALYZE SELECT ... ;

EXPLAIN ANALYZE працює вдвічі швидше ручного аналізу плану — одразу видає вузькі місця.

Які індекси критичні для Бітрікс?

Декілька індексів, які часто відсутні в стандартній установці:

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

Після створення індексу перезапускаємо EXPLAIN — тип має змінитися з ALL на ref.

Чому N+1 — часта проблема в ORM Бітрікс?

D7 ORM часто генерує N+1 запитів. Діагностика через вбудований трекер:

\Bitrix\Main\Application::getConnection()->setTracker(new \Bitrix\Main\DB\SqlTracker(50));
// В кінці запиту
$tracker = \Bitrix\Main\Application::getConnection()->getTracker();
foreach ($tracker->getQueries() as $query) {
    if ($query->getTime() > 0.1) error_log($query->getSql() . ' [' . $query->getTime() . 's]');
}

// Виправлення: замість поштучних запитів — batch вибірка
$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 ...

Кейс: оптовий дистриб'ютор

З нашої практики: сайт на Бітрікс «Малий бізнес», каталог 28 000 позицій, 3 000 відвідувачів/день. Сервер 4 CPU, 8 GB RAM. Навантаження на MySQL — 85–90% CPU. pt-query-digest показав, що 92% часу йде на b_iblock_element_prop_s8 (таблиця строкових властивостей) — full scan на 280 000 рядків. Один індекс idx_prop_s8_element_id знизив навантаження до 15–20% CPU без жодних змін у коді. TTFB впав з 5 с до 1,2 с. В середньому за нашими проєктами економія часу завантаження становить 50–70%. Зниження CPU з 85% до 15% заощадило 200 000 руб./рік (близько 80 000 грн) на серверах.

Інструменти моніторингу

Докладніше про slow query log: Wikipedia. Також рекомендуємо офіційну документацію D7 ORM для уникнення N+1.

Що входить в роботу

• Діагностика: включення slow log, збір даних, звіт з топ-20 повільних запитів.
• Оптимізація: створення індексів, рефакторинг N+1, налаштування буферів MySQL.
• Моніторинг: встановлення PMM або Grafana, налаштування алертів.
• Документація та навчання: опис всіх змін, рекомендації для розробників.

Якщо ви виявили повільні запити — замовте аудит профілювання MySQL. Гарантуємо — після нашої оптимізації навантаження на базу знизиться в 3–5 разів, а TTFB — на 60–80%. Зв'яжіться з нами для безкоштовної оцінки вашого проєкту.

Терміни

Замовте профілювання MySQL-запитів 1С-Бітрікс — отримайте розгорнутий звіт та рекомендації протягом дня.

rsync -avz на продакшен у п’ятницю ввечері, рестарт php-fpm — і сайт відповідає 502, бо в .settings.php залишилися локальні налаштування БД. Деплой «по-старому» — лотерея. Оновлення модуля через адмінку ламає кастомний шаблон компонента, правки не зафіксовані в Git — відновлення займає години.

Ми проєктуємо передбачуваний DevOps-цикл для проєктів на Бітрікс: від Docker-середовища до алертів у Telegram. Кожен деплой стає рутиною, а не стресом.

Проблеми, які ми вирішуємо

Бітрікс історично жив у парадигмі «правимо файли по FTP на бойовому сервері». Результат: двоє розробників одночасно правлять init.php, затираючи зміни один одного. Оновлення модуля через адмінку ламає шаблон компонента — файли не зафіксовані в /local/templates/. Про падіння сайту дізнаються від клієнта, staging-середовища немає, перевірки йдуть на проді. Вартість такого підходу — години відновлення та втрата бізнес-логіки.

Чому DevOps критичний для Бітрікс-проєктів?

Проєкти на Бітрікс мають специфіку: важкий торговий каталог, обмін з 1С через CommerceML, безліч агентів та подій. Без CI/CD та моніторингу кожна зміна — ризик. Ручний деплой може спричинити тижневий простій. Після впровадження DevOps середня економія трудовитрат — до 12 годин ручної роботи на місяць, а бюджету підтримки — 15 000–25 000 грн на місяць. Наш 5-річний досвід (понад 50 проєктів на Бітрікс) і ліцензійна чистота рішень — гарантія стабільності.

Інструменти та конфігурація

CI/CD: від коміту до продакшену без рук

Git — переводимо проєкт з FTP на Git (GitLab, GitHub, Bitbucket). Структура гілок: main, staging, develop, feature-гілки. .gitignore під Бітрікс — нетривіальне завдання:

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

Пропустиш managed_cache/ — репозиторій розпухне на гігабайти. Забудеш виключити license_key.php — ключ витече.

CI-пайплайн перевіряє код: PHPStan level 5+ ловить звернення до неіснуючих методів CIBlockElement, PHP_CodeSniffer з Bitrix-стандартом, PHPUnit для бізнес-логіки, composer audit, збірка фронту. CD-пайплайн розгортає без участі людини. Мерж у staging — деплой на staging. Мерж у main — деплой на production (з ручним підтвердженням або без). Zero-downtime через symlink-стратегію: нова версія в окремій папці, current → symlink перемикається за мілісекунди. upload/ живе поза релізними директоріями. При помилці healthcheck після деплою symlink відкочується. Інструменти: GitLab CI/CD, GitHub Actions, Deployer (PHP). Deployer зручний для Бітрікс — є готові рецепти для symlink-деплою та shared-директорій.

Як Docker вирішує проблему «у мене працює»?

Docker-середовище фіксує версії всіх компонентів: nginx, PHP, MySQL, Redis. Docker скорочує час розгортання нового розробника в 10 разів порівняно з ручним налаштуванням. Локальна розробка — docker-compose.yml включає nginx + php-fpm 8.1/8.2 + MySQL 8.0 (або MariaDB 10.6) + Redis + Memcached. Новий розробник: git clone + docker-compose up -d — за 5 хвилин пише код. Паралельна робота над різними версіями PHP — через окремі compose-файли.

Особливості Бітрікс у Docker: /upload/ монтується як named volume (не bind mount — інакше на Windows/Mac проблеми з правами та швидкістю). Cron-завдання (/bitrix/modules/main/tools/cron_events.php) — через окремий контейнер з тим же образом або supervisord. Модуль «Проактивний захист» (security) блокує запити через reverse proxy — потрібен правильний REMOTE_ADDR через set_real_ip_from та realip_module. bitrix/php_interface/dbconn.php та .settings.php задаються через змінні оточення.

Production: мультистейдж-збірка (build-стадія для асетів, production-стадія з легким образом), Docker Registry для тегованих образів, оркестрація через Docker Swarm або Kubernetes для великих проєктів.

Як правильно налаштувати nginx та php-fpm для Бітрікс?

Різниця між «сайт гальмує» та 200 ms TTFB — у конфігурації. nginx: location-блоки під Бітрікс обробляють urlrewrite.php для ЧПУ. /bitrix/admin/ закриває доступ по IP через allow/deny. expires 30d для статики — CSS, JS, зображення кешуються браузером. Brotli (стиснення на 15-20% ефективніше gzip) вмикається brotli on; brotli_comp_level 6;. Rate limiting на /bitrix/tools/ захищає від брутфорсу. HTTP/2 push для критичних ресурсів.

php-fpm: pm = dynamic, розрахунок pm.max_children за формулою (RAM - RAM_інших_сервісів) / avg_memory_per_process. Для Бітрікс avg зазвичай 40-80 MB. OPcache: opcache.memory_consumption=256 (стандартних 128 MB недостатньо — Бітрікс тягне тисячі файлів), opcache.max_accelerated_files=20000, opcache.validate_timestamps=0 у продакшені (скидання через cachetool opcache:reset при деплої). php.ini: memory_limit=256M (для важких операцій імпорту до 512M), max_execution_time=60, upload_max_filesize=100M. Slowlog з request_slowlog_timeout=5s знаходить вузькі місця до скарг користувачів.

Інфраструктура та автоматизація

Моніторинг, логування та резервне копіювання

Як відбувається моніторинг та реагування на інциденти? Prometheus + Grafana: метрики CPU, RAM, диска, мережі, стану сервісів. Алерти: CPU > 80% за 5 хвилин, вільна RAM < 500 MB, диск > 85%, php-fpm queue > 0. Node Exporter, MySQL Exporter, PHP-FPM Exporter збирають дані з кожного компонента. Uptime check кожні 60 секунд — алерт у Telegram за хвилину при падінні сайту. Sentry для PHP-помилок — структуровані помилки з контекстом. Моніторинг агентів Бітрікс (b_agent): завислий агент може тихо ламати обмін з 1С годинами — перевіряємо NEXT_EXEC < NOW() - INTERVAL 1 HOUR. Логування: ELK Stack або Loki + Grafana — nginx access/error, php-fpm slow log, MySQL slow query log, помилки Бітрікс. Ротація через logrotate — без неї через півроку access.log займе 50 ГБ.

Staging-середовище ідентичне production: ті ж версії nginx, PHP, MySQL, модулі, налаштування php.ini. Автоматичне оновлення при мержі у staging-гілку. Періодичний клон БД з production з знеособленням персональних даних — UPDATE b_user SET EMAIL = CONCAT('user', ID, '@test.local'), PHONE = ''. HTTP Basic Auth або IP-фільтрація. Robots.txt з Disallow: /. Sandbox-режим платіжних шлюзів для тестування оплати.

Ansible як інфраструктура як код: ansible-playbook site.yml -l production — через 15 хвилин сервер налаштований ідентично. Ролі: common (users, SSH, NTP), web (nginx + php-fpm), db (MySQL + backup), monitoring (Prometheus + exporters). Ansible забезпечує налаштування сервера за 15 хвилин замість 3 годин вручну.

Географічна розподіленість — S3-сумісне сховище + окремий сервер в іншому ЦОД. Тестове відновлення щомісяця — бекап без перевірки лише ілюзія безпеки. Cron з повідомленнями: якщо беказ не пройшов — алерт одразу.

Що входить в роботу

• Документація DevOps-процесу (схема деплою, політика гілок, опис інфраструктури)
• Налаштовані CI/CD пайплайни (GitLab CI / GitHub Actions) з робочими тригерами
• Docker-середовище (docker-compose.yml, Dockerfile, конфіги)
• Ansible-плейбуки для відтворення серверів
• Моніторинг (Grafana дашборди, алерти в Telegram/Slack)
• Захищені доступи з ролевою моделлю
• Навчання команди: два заняття з роботи з CI/CD, Docker та деплоєм
• Підтримка на етапі впровадження (два тижні після запуску)

Приклад GitLab CI для Бітрікс (фрагмент):

stages:
  - test
  - build
  - deploy

cache:
  paths:
    - vendor/

unit_tests:
  stage: test
  script:
    - composer install --no-progress
    - php vendor/bin/phpunit

deploy_staging:
  stage: deploy
  script:
    - ansible-playbook deploy.yml -l staging
  only:
    - staging

Як проходить впровадження?

1. Аудит поточного стану — оцінка інфраструктури, софту, процесів. Займає 2-3 дні.
2. Проектування архітектури — вибір стеку (Docker/K8s/Ansible), узгодження політик CI/CD, налаштування репозиторію.
3. Налаштування середовищ — Docker-середовище для локальної розробки, staging-середовище, production-сервери.
4. Реалізація CI/CD — написання пайплайнів, тестування деплою, інтеграція з моніторингом.
5. Моніторинг та алертинг — встановлення Prometheus + Grafana, налаштування дашбордів та оповіщень.
6. Навчання команди — два заняття з роботи з інструментами.

Типові терміни впровадження

DevOps — не проєкт з фінальною датою, а перехід від «закинув по FTP і молюся» до передбачуваних процесів. Кожен деплой — рутина, кожен інцидент — алерт з контекстом, кожен новий розробник — docker-compose up замість триденного налаштування середовища. Отримайте консультацію — зв'яжіться з нами, і за 2-3 дні підготуємо план впровадження. Замовте DevOps-впровадження під ключ — отримайте стабільність і контроль над інфраструктурою.