Slow query log analysis for 1C-Bitrix

Slow Query Log Analysis for 1C-Bitrix

Slow query log is a MySQL/MariaDB journal that records queries running longer than a specified threshold. It's the first diagnostic tool when a Bitrix site is slow but specific culprits are unknown.

Enabling and Configuration

In my.cnf (or my.ini on Windows):

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5
log_queries_not_using_indexes = 1
min_examined_row_limit = 100

long_query_time = 0.5 — queries over 500 ms. For active diagnostics set 0.1 or even 0. log_queries_not_using_indexes = 1 — catches queries without indexes regardless of time. min_examined_row_limit = 100 — don't log fast queries on small tables.

Enable without MySQL restart dynamically:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;
SET GLOBAL log_queries_not_using_indexes = 1;

Analysis via mysqldumpslow and pt-query-digest

Raw logs are inconvenient to read. Use aggregation utilities:

mysqldumpslow — built into MySQL:

mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

-s t — sort by total time, -t 10 — top 10 queries. Shows query template and total/average time.

pt-query-digest (Percona Toolkit) — significantly more informative: shows execution time percentiles, count of unique queries, server load.

What to Look for in Bitrix Logs

Typical slow query patterns in Bitrix:

• SELECT ... FROM b_iblock_element WHERE IBLOCK_ID=N without index or with ORDER BY SORT without composite index
• SELECT ... FROM b_iblock_element_property WHERE VALUE LIKE '%text%' — LIKE with leading % doesn't use index
• SELECT COUNT(*) FROM b_sale_order WHERE ... with millions of orders
• Queries to b_search_content without FULLTEXT index during full-text search
• Faceted index recreation (b_iblock_find_*) on every element change

After finding a specific problem query, next step is EXPLAIN, which shows which execution plan the optimizer chose and where to add an index.