darvecher Mar 21 2018 at 16:30

Ищем причины тормозов БД, используя sys schema в MySQL 5.7

3 min

15K

Есть у нас веб-приложение. Относительно большое и старое — много-много кода, в котором много-много разных запросов к базе данных. При этом мы не гугл, но несколько тысяч запросов в секунду на сервер БД приходится.

Ну и безусловно растёт наше приложение во все стороны — добавляются новые фичи, наворачиваются и усложняются старые, растёт количество клиентов и соответственно количество данных в БД. И в один не очень прекрасный момент приходит понимание, что что-то наше приложение тормозит. А значит надо или найти, что именно нагружает БД, хотя могло бы не нагружать, ну или если ничего такого нет, то взять ещё серверов побольше, да помощнее.

Стандартный совет как найти, что нагружает MySQL — включить slow-query-log и посмотреть, какие запросы будут туда попадать. Но в MySQL 5.7 по умолчанию присутствует куда лучший инструмент — sys schema, которая агрегирует данные из performance schema и позволяет их получить простыми запросами, буквально вида «Ok, MySQL, покажи мне топ запросов по максимальному суммарному времени выполнения»

Для начала, какие проблемы есть с использованием slow-query-log:

Когда MySQL тормозит, туда валятся сотнями запросы, которые в периоды нормальной нагрузки не медленные — например, мы наблюдаем там время от времени выборки по первичному ключу.
Если, к примеру, поставить long_query_time=3 в него не попадёт запрос, который выполняется за 0.5 секунды, но вызовов которого очень много.

В MySQL 5.5 появилась, и в более новых версиях развивалась performance schema, но пользоваться ей не так просто, как хотелось бы.

На этом заканчиваем со введением и переходим к тому, как пользоваться sys schema, и примерам того, что можно увидеть.

Для начала, если у вас запросы сложнее чем “SELECT a FROM b WHERE c = ?”, стоит поправить ограничения на сохраняемую в performance schema длину текста запросов. По умолчанию она 1024 — вдруг вам этого достаточно, но мне оказалось мало.

Добавляем в my.cnf:

max_digest_length=10240
performance_schema_max_sql_text_length=10240
performance_schema_max_digest_length=10240

Переменные не динамические, поэтому после этого потребуется перезагрузка сервера. Это, конечно, минус.

Далее изменяем максимальную отображаемую длину текста запросов в ответах sys schema (по умолчанию она 64 — и тут я совсем не понимаю, кому этого будет достаточно)

use sys;
update sys_config set value=10240 WHERE variable='statement_truncate_len';

Ну а затем можно пользоваться.

Упоминаемый ранее топ запросов по максимальному суммарному времени выполнения:

mysql> select * from statement_analysis limit 10 \G
*************************** 1. row ***************************
            query: SELECT `AccSettingValue` FROM `accsetting` WHERE `accsetting` . `AccSettingName` = ?
               db: mydb
        full_scan:
       exec_count: 2065339
        err_count: 0
       warn_count: 0
    total_latency: 1.75 m
      max_latency: 16.52 ms
      avg_latency: 50.72 us
     lock_latency: 48.90 s
        rows_sent: 0
    rows_sent_avg: 0
    rows_examined: 0
rows_examined_avg: 0
    rows_affected: 0
rows_affected_avg: 0
       tmp_tables: 0
  tmp_disk_tables: 0
      rows_sorted: 0
sort_merge_passes: 0
           digest: 229c950384bddbaa0e537f54beaa1ac4
       first_seen: 2018-03-19 20:20:43
        last_seen: 2018-03-21 12:27:21

На примере этого запроса видим, что значения %_latency возвращаются в человекопонятном виде, что удобно, чтобы читать результат, но неудобно, если захотеть по ним отсортировать. Для этого у всех таблиц в sys schema есть близнецы вида x$table_name.

Выборка запросов, отсортированных по avg_latency, создававших временные таблицы на диске, будет выглядеть как-то так:

mysql> select * from x$statement_analysis WHERE tmp_disk_tables > 0 ORDER BY avg_latency DESC limit 10 \G

Ещё пара примеров, что полезного можно увидеть используя sys schema.

Запросы, среднее время выполнения которых, входит в топ 5%:

mysql> select * from statements_with_runtimes_in_95th_percentile LIMIT 10\G

Запросы, создающие временные таблицы:

mysql> select * from statements_with_temp_tables limit 10\G

Запросы, делающие full table scan

mysql> select * from statements_with_full_table_scans limit 10\G

Неиспользуемые индексы (сервер должен работать достаточно продолжительное время, чтобы этим данным можно было верить):

mysql> select * from schema_unused_indexes limit 10;

Это то, что было наиболее полезным лично для меня, если заинтересовались — подробная документация и примеры использования есть на github или в официальной документации.

Tags:

Hubs:

MySQL