Профилирование запросов – полезный метод анализа общей производительности базы данных. Учитывая, что одно приложение среднего и большого размера может выполнять многочисленные запросы каждую секунду, профилирование запросов является важной частью настройки базы данных, как активной меры, так и диагностики проблем. На самом деле, может быть трудно определить точные источники и причины узких мест и вялой производительности без использования каких-либо методов профилирования запросов. Этот пост предоставит несколько полезных методов профилирования запросов, которые используют собственные встроенные инструменты сервера MariaDB: журнал медленных запросов и схему производительности.
MariaDB против MySQL
Излишне говорить, что методы, которые мы рассмотрим здесь сегодня, вероятно, будут одинаково применимы к MySQL из-за тесной взаимосвязи между этими двумя продуктами. В тот день, когда Oracle объявила о покупке Sun в 2010 году, Майкл «Monty» Widenius разветвил MySQL и запустил MariaDB, взяв на себя роль разработчиков MySQL в этом процессе. Его цель заключалась в том, чтобы система управления реляционными базами данных (СУБД) оставалась свободной под GNU GPL. Сегодня MariaDB это замена для MySQL, один с более широкими возможностями и более высокой производительностью. MariaDB основывалась на соответствующей версии MySQL, где она существовала. Например, MariaDB 5.1.53 была основан на MySQL 5.1.53, с некоторыми добавленными исправлениями ошибок, дополнительными механизмами хранения, новыми функциями и улучшением производительности. На момент написания этой статьи последняя версия MariaDB – 10.2.x. Между тем, MySQL 8 все еще находится в режиме RC (Release Candidate).
Журнал медленных запросов
Одной из функций, которую разделяют как MariaDB, так и MySQL, является медленный журнал запросов. Запросы, которые считаются медленными и потенциально проблематичными, записываются в журнал. «Медленный» запрос определяется как запрос, длина которого превышает значение глобальной системной переменной long_query_time (по умолчанию 10 секунд). Микросекунды разрешены для регистрации файлов, но не для ведения журналов.
Настройка журнала медленных запросов через глобальные системные переменные
Помимо упомянутой выше глобальной системной переменной long_query_time, существует несколько других переменных, которые определяют поведение медленного журнала запросов. По умолчанию медленный журнал запросов отключен. Чтобы включить его, установите для системной переменной slow_query_log значение 1. Системная переменная Log_output на сервере определяет, как выходной сигнал будет записан, а также может его отключить. По умолчанию журнал записывается в файл, но его также можно записать в таблицу. Допустимыми значениями для системной переменной сервера log_output являются TABLE, FILE или NONE. По умолчанию имя файла – host_name-slow.log , но также может быть установлено с помощью –slow_query_log_file = имя_файла. В таблице используется slow_log в системной базе данных mysql. Эти переменные лучше всего устанавливаются в конфигурационных файлах my.cnf или mariadb.cnf, обычно хранящихся в каталоге /etc/mysql/ в Linux и в системном каталоге Windows, обычно в C:\Windows\System, в Windows. Следующие настройки, добавленные в разделе [mysqld], будут:
- Включение журнала медленных запросов.
- Установка времени в секундах/микросекундах, определяя медленный запрос.
- Укажите имя файла журнала медленных запросов.
- Журнальные запросы, которые не используют индексы.
[1] slow_query_log = 1 [2] long_query_time = 5 [3] slow_query_log_file = /var/log/mysql/slow-query.log [4] log_queries_not_using_indexes
Настройки вступят в силу после перезапуска сервера.
Просмотр журнала медленных запросов
Медленные журналы запросов, записанные в файл, можно просматривать с помощью любого текстового редактора. Вот несколько примеров содержания:
# Time: 150109 20:20:32 # User@Host: root[root] @ localhost [] # Thread_id: 40 Schema: world Last_errno: 0 Killed: 0 # Query_time: 0.012989 Lock_time: 0.000033 Rows_sent: 4079 Rows_examined: 4079 Rows_affected: 0 Rows_read: 4079 # Bytes_sent: 161085 # Stored routine: world.improved_sp_log SET timestamp=1420803535; SELECT * FROM City; # User@Host: root[root] @ localhost [] # Thread_id: 40 Schema: world Last_errno: 0 Killed: 0 # Query_time: 0.001413 Lock_time: 0.000017 Rows_sent: 4318 Rows_examined: 4318 Rows_affected: 0 Rows_read: 4318 # Bytes_sent: 194601 # Stored routine: world.improved_sp_log SET timestamp=1420803535;
Единственный недостаток в просмотре журнала медленных запросов с текстовым редактором заключается в том, что он может (и делает!) разрастание до такого размера, что становится все труднее разобрать все данные. Следовательно, существует риск того, что проблемные запросы будут потеряны в содержимом журнала. MariaDB предлагает инструмент mysqldumpslow для упрощения процесса путем суммирования информации. Исполняемый файл поставляется вместе с MariaDB. Чтобы использовать его, просто запустите команду и перейдите в путь к журналу. Полученные строки более читабельны, а также сгруппированы по запросу:
mysqldumpslow /tmp/slow_query.log
Reading mysql slow query log from /tmp/slow_query.log
Count: 1 Time=23.99s (24s) Lock=0.00s (0s) Rows_sent=1.0 (1), Rows_examined=0.0 (0), Rows_affected=0.0 (0), root[root](#)@localhost
SELECT * from large_table
Count: 6 Time=6.83s (41s) Lock=0.00s (0s) Rows_sent=1.0 (6), Rows_examined=0.0 (0), Rows_affected=0.0 (0), root[root](#)@localhost
SELECT * from another_large_table
Существуют различные параметры, которые могут использоваться с командой, чтобы помочь настроить вывод. В следующем примере будут отображаться первые 5 запросов, отсортированных по среднему времени запроса:
mysqldumpslow -t 5 -s в /var/log/mysql/localhost-slow.log
Работа с таблицей slow_log
Медленные журналы запросов, записанные в таблицу, можно просмотреть, запросив таблицу slow_log. Она содержит следующие поля:
| поле | Тип | По умолчанию | Описание |
| start_time | timestamp(6) | CURRENT_TIMESTAMP(6) | Время начала запроса. |
| user_host | mediumtext | NULL | Комбинация пользователя и хоста. |
| query_time | time(6) | NULL | Общее время выполнения запроса. |
| lock_time | time(6) | NULL | Общее время, когда запрос был заблокирован. |
| rows_sent | int(11) | NULL | Количество отправленных строк. |
| rows_examined | int(11) | NULL | Количество проверенных строк. |
| db | varchar(512) | NULL | База данных по умолчанию. |
| last_insert_id | int(11) | NULL | last_insert_id. |
| insert_id | int(11) | NULL | Вставить идентификатор. |
| server_id | int(10) unsigned | NULL | Идентификатор сервера. |
| sql_text | mediumtext | NULL | Полный запрос. |
| thread_id | bigint(21) unsigned | NULL | Идентификатор темы. |
| rows_affected | int(11) | NULL | Количество строк, на которые влияет UPDATE или DELETE (в соответствии с MariaDB 10.1.2) |
Вот несколько примеров результатов SELECT ALL по таблице slow_log:
SELECT * FROM mysql.slow_log \ G
*************************** 2. строка ******************** *******
start_time: 2018-11-28 20:25:22.345324
user_host: root [root] @ localhost []
query_time: 00: 00: 12.000215
lock_time: 00: 00: 00.000000
rows_sent: 1
rows_examined: 0
db: test
last_insert_id: 0
insert_id: 0
server_id: 1
sql_text: SELECT * FROM large_table
thread_id: 74
rows_affected: 0
...
Сортировка строк журнала медленных запросов
Если вы хотите подражать команде Linux «tail -100 log-slow.log» с таблицей slow_log, в которой перечислены последние запросы в конце, вы можете осуществить следующий запрос:
SELECT *
FROM (SELECT *
FROM slow_log
ORDER BY start_time
DESC LIMIT 100) sl
ORDER BY start_time;
Он перечислит последние 100 запросов в таблице. Вместо того, чтобы вводить один и тот же оператор SELECT каждый раз, когда вы хотите перечислить последние запросы, мы бы рекомендовали создать хранимую процедуру, например SHOW_LATEST_SLOW_QUERIES, и использовать ее вместо этого. Количество запросов для показа может быть передано вашему процессу в качестве входного параметра.
Тестирование журнала медленных запросов
Прежде чем пытаться просмотреть журнал медленных запросов в рабочей среде, рекомендуется проверить его работу, выполнив несколько тестовых запросов; возможно, некоторые из них должны запускать ведение журнала и другие, которые не должны. Как упоминалось ранее, когда включено ведение журнала, запросы, которые занимают больше времени, чем ожидаемое значение глобальной системной переменной long_query_time, записываются в медленном файле журнала или таблице slow_log на основе значения переменной log_output. Вы можете, конечно, работать с вашими данными, чтобы строить запросы SELECT, для выполнения которых требуется определенное количество времени, но, возможно, более простой подход – использовать функцию sleep(): SLEEP(продолжительность). Функция sleep() приостанавливает выполнение запроса на количества секунд, заданных аргументом продолжительности, а затем возвращает 0. Если SLEEP() прерывается, он возвращает 1. Длительность может иметь дробную часть, заданную в микросекундах, но на самом деле нет в этом необходимости для целей тестирования журналов медленных запросов. Вот пример:
SELECT sleep(5);
+————+
| sleep(5) |
+————+
| 0 |
+————+
1 row in set (5.0 sec)
Предположим, что глобальной системной переменной long_query_time не было явно присвоено значение. В этом случае оно будет иметь значение по умолчанию 10 секунд. Поэтому в медленный журнал записывается следующий оператор SELECT: SELECT SLEEP (11);
Профилирование запросов с помощью схемы производительности
Еще одним инструментом, который мы можем использовать для мониторинга производительности сервера, является схема производительности. Представленная в MariaDB 5.5 схема производительности реализована как механизм хранения данных и поэтому появится в списке доступных движков хранения:
SHOW ENGINES;
+——————–+———+——————————-+————–+——+————+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+——————–+———+——————————-+————–+——+————+
| InnoDB | DEFAULT | Default engine | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| … | | | | | |
+——————–+———+——————————-+————–+——+————+
Он отключен по умолчанию по соображениям производительности, но его можно легко включить следующим образом: сначала добавьте следующую строку в файл my.cnf или my.ini в разделе [mysqld]:
performance_schema = on
Схема производительности не может активироваться во время выполнения – она должна быть установлена при запуске сервера через файл конфигурации. Механизм хранения Performance Schema содержит базу данных под названием performance_schema, которая, в свою очередь, состоит из нескольких таблиц, которые могут быть запрошены с помощью регулярных операторов SQL для широкого спектра информации о производительности. Чтобы собирать данные, вам необходимо настроить всех потребителей (начиная сбор данных) и инструментовки (какие данные собирать). Они могут быть установлены либо при запуске сервера, либо во время выполнения. Следующие инструкции настраивают потребителей и инструменты во время выполнения:
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES';
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES';
Вы можете решить, что включить/отключить WHERE NAME, например «% what_to_enable»; наоборот, вы можете отключить инструменты, установив ENABLED в «NO». Ниже приведены все инструменты всех этапов (вычислительных единиц) в файле конфигурации:
[mysqld] performance_schema=ON performance-schema-instrument='stage/%=ON' performance-schema-consumer-events-stages-current=ON performance-schema-consumer-events-stages-history=ON performance-schema-consumer-events-stages-history-long=ON
Что касается профилирования запросов:
1. Убедитесь, что оператор и инструменты включены, обновив таблицу setup_instruments следующим образом:
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME LIKE '%statement/%'; UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME LIKE '%stage/%';
2. Включите the events_statements_* и events_stages_* :
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%events_statements_%'; UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%events_stages_%';
Как только вы сузили то, что вас интересует, есть два способа начать мониторинг:
- Просмотр исходных данных в сводных представлениях. Это дает общую картину использования экземпляра.
- Данные моментальных снимков и вычисления дельт с течением времени. Это дает вам представление о темпах изменений событий.
Начнем с просмотра исходных сводных данных:
- Запустите оператор(ы), который вы хотите профилировать. Например:
SELECT * FROM acme.employees WHERE emp_no = 54;
+———–+————–+————–+————-+———+————–+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+———–+————–+————–+————-+———+————–+
| 54 | 1989-04-22 | Alex | Murphy | M | 2012-01-17 |
+———–+————–+————–+————-+———+————–+
Определите EVENT_ID оператора, запросив таблицу events_statements_history_long. Этот шаг похож на запуск SHOW PROFILES для идентификации Query_ID. Следующий запрос производит вывод, подобный SHOW PROFILES:
SELECT EVENT_ID,
TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration,
SQL_TEXT
FROM performance_schema.events_statements_history_long
WHERE SQL_TEXT like '%99%';
+----------+----------+--------------------------------------------------------+
| event_id | duration | sql_text |
+----------+----------+--------------------------------------------------------+
| 22 | 0.021470 | SELECT * FROM acme.employees WHERE emp_no = 54 |
+----------+----------+--------------------------------------------------------+
Запросите таблицу events_stages_history_long для получения событий этапа. Этапы связаны с утверждениями, использующими вложенность событий. Каждая запись события этапа имеет столбец NESTING_EVENT_ID, который содержит EVENT_ID родительского оператора.
SELECT event_name AS Stage,
TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration
FROM performance_schema.events_stages_history_long
WHERE NESTING_EVENT_ID=22;
+--------------------------------+----------+
| Stage | Duration |
+--------------------------------+----------+
| stage/sql/starting | 0.000080 |
| stage/sql/checking permissions | 0.000005 |
| stage/sql/Opening tables | 0.027759 |
| stage/sql/init | 0.000052 |
| stage/sql/System lock | 0.000009 |
| stage/sql/optimizing | 0.000006 |
| stage/sql/statistics | 0.000082 |
| stage/sql/preparing | 0.000008 |
| stage/sql/executing | 0.000000 |
| stage/sql/Sending data | 0.000017 |
| stage/sql/end | 0.000001 |
| stage/sql/query end | 0.000004 |
| stage/sql/closing tables | 0.000006 |
| stage/sql/freeing items | 0.000272 |
| stage/sql/cleaning up | 0.000001 |
+--------------------------------+----------+
Заключение
В этом посте представлены несколько полезных методов профилирования запросов, в которых используются несколько встроенных инструментов сервера MariaDB: журнал медленных запросов и схема производительности. Журнал медленных запросов записывает запросы, которые считаются медленными и потенциально проблематичными, то есть запросы, которые занимают больше времени, чем ожидаемое значение глобальной системной переменной long_query_time. Журнал медленных запросов можно просмотреть с помощью любого текстового редактора. В качестве альтернативы инструмент mysqldumpslow MariaDB может упростить процесс, обобщая информацию. Полученные строки более читабельны, а также сгруппированы по запросу. Схема производительности – это механизм хранения, который содержит базу данных с именем performance_schema, которая, в свою очередь, состоит из нескольких таблиц, которые могут быть запрошены с помощью регулярных операторов SQL для широкого спектра информации о производительности.