Профилирование запросов — полезный метод анализа общей производительности базы данных. Учитывая, что одно приложение среднего и большого размера может выполнять многочисленные запросы каждую секунду, профилирование запросов является важной частью настройки базы данных, как активной меры, так и диагностики проблем. На самом деле, может быть трудно определить точные источники и причины узких мест и вялой производительности без использования каких-либо методов профилирования запросов. Этот пост предоставит несколько полезных методов профилирования запросов, которые используют собственные встроенные инструменты сервера 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. Включение журнала медленных запросов.
2. Установка времени в секундах/микросекундах, определяя медленный запрос.
3. Укажите имя файла журнала медленных запросов.
4. Журнальные запросы, которые не используют индексы.

[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. Она содержит следующие поля:

Вот несколько примеров результатов 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_%';

Как только вы сузили то, что вас интересует, есть два способа начать мониторинг:

1. Просмотр исходных данных в сводных представлениях. Это дает общую картину использования экземпляра.
2. Данные моментальных снимков и вычисления дельт с течением времени. Это дает вам представление о темпах изменений событий.

Начнем с просмотра исходных сводных данных:

1. Запустите оператор(ы), который вы хотите профилировать. Например:

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 для широкого спектра информации о производительности.

(1 оценок, среднее: 5,00 из 5)

Загрузка...

0 0 голоса

Рейтинг статьи

Подписаться

авторизуйтесь

Уведомить о

новых последующих комментарияхновых ответах на мои комментарии

Label

{} [+]

Имя*

Email*

Веб-сайт

**ссылки nofollow

Δ

Label

{} [+]

Имя*

Email*

Веб-сайт

**ссылки nofollow

Δ

0 комментариев

Межтекстовые Отзывы

Посмотреть все комментарии