Лучше изучить лишнее, чем ничего не изучить (Сенека Старший).

Улучшение производительности MariaDB с использованием профилирования запросов

9 мин для чтения
FavoriteLoadingДобавить в избранное
1 Звезда2 Звезды3 Звезды4 Звезды5 Звезд (1 оценок, среднее: 5,00 из 5)
Загрузка...
28 ноября 2018
Улучшение производительности MariaDB с использованием профилирования запросов
Профилирование запросов – полезный метод анализа общей производительности базы данных. Учитывая, что одно приложение среднего и большого размера может выполнять многочисленные запросы каждую секунду, профилирование запросов является важной частью настройки базы данных, как активной меры, так и диагностики проблем. На самом деле, может быть трудно определить точные источники и причины узких мест и вялой производительности без использования каких-либо методов профилирования запросов. Этот пост предоставит несколько полезных методов профилирования запросов, которые используют собственные встроенные инструменты сервера 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. Она содержит следующие поля:

полеТипПо умолчаниюОписание
start_timetimestamp(6)CURRENT_TIMESTAMP(6)Время начала запроса.
user_hostmediumtextNULLКомбинация пользователя и хоста.
query_timetime(6)NULLОбщее время выполнения запроса.
lock_timetime(6)NULLОбщее время, когда запрос был заблокирован.
rows_sentint(11)NULLКоличество отправленных строк.
rows_examinedint(11)NULLКоличество проверенных строк.
dbvarchar(512)NULLБаза данных по умолчанию.
last_insert_idint(11)NULLlast_insert_id.
insert_idint(11)NULLВставить идентификатор.
server_idint(10) unsignedNULLИдентификатор сервера.
sql_textmediumtextNULLПолный запрос.
thread_idbigint(21) unsignedNULLИдентификатор темы.
rows_affectedint(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_%';

 

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

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

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

Просмотров: 12

Если статья понравилась, то поделитесь ей в социальных сетях:

Добавить комментарий

Войти с помощью: 

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Сообщить об опечатке

Текст, который будет отправлен нашим редакторам:

Заполните форму и наш менеджер перезвонит Вам в самое ближайшее время!

badge
Обратный звонок 1
Отправить
galka

Спасибо! Ваша заявка принята

close
galka

Спасибо! Ваша заявка принята

close