26.4.4.25 The statement_performance_analyzer() Procedure — MySQL 5.7

26.4.4.25 Процедура statement_performance_analyzer()

Создаёт отчёт о выполнении запросов на сервере. Представления рассчитываются на основе общей и/или Delta-активности.

Данная процедура отключает двоичный журналик во время своего выполнения, манипулируя значением сессии системной переменной sql_log_bin. Это ограниченная операция, поэтому процедура требует привилегий, достаточных для установки ограниченных переменных сессии. См. Раздел 5.1.8.1, «Привилегии системных переменных».

Параметры

in_action ENUM('snapshot', 'overall', 'delta', 'create_tmp', 'create_table', 'save', 'cleanup'): Действие для выполнения. Допустимые значения:
- snapshot: Сохранить моментальный снимок. По умолчанию создаётся моментальный снимок текущего содержимого таблицы events_statements_summary_by_digest схемы Performance Schema. Установив in_table, это можно переопределить, скопировав содержимое указанной таблицы. Моментальный снимок сохраняется в временной таблице схемы sys tmp_digests.
- overall: Сгенерировать анализ, основанный на содержимом таблицы, указанной параметром in_table. Для общего анализа, in_table может быть NOW() для использования свежего моментального снимка. Это переписывает существующий моментальный снимок. Используйте NULL для in_table, чтобы использовать существующий моментальный снимок. Если in_table имеет значение NULL и моментальный снимок не существует, создаётся новый моментальный снимок. Параметр in_views и конфигурационный параметр statement_performance_analyzer.limit влияют на работу данной процедуры.
- delta: Сгенерировать Delta-анализ. Delta рассчитывается между таблицей-эталоном, указанной параметром in_table, и моментальным снимком, который должен существовать. Эта операция использует временную таблицу схемы sys tmp_digests_delta. Параметр in_views и конфигурационный параметр statement_performance_analyzer.limit влияют на работу данной процедуры.
- create_table: Создать обычную таблицу для хранения моментального снимка для дальнейшего использования (например, для расчёта Delta).
- create_tmp: Создать временную таблицу для хранения моментального снимка для дальнейшего использования (например, для расчёта Delta).
- save: Сохранить моментальный снимок в таблицу, указанную параметром in_table. Таблица должна существовать и иметь правильную структуру. Если моментальный снимок не существует, создаётся новый моментальный снимок.
- cleanup: Удалить временные таблицы, используемые для моментального снимка и Delta.
in_table VARCHAR(129): Параметр таблицы, используемый для некоторых действий, заданных параметром in_action. Используйте формат db_name.tbl_name или tbl_name без использования каких-либо символов кавычек (`) для идентификации. Точки (.) не поддерживаются в именах баз данных и таблиц.

Значение in_table для каждого значения in_action подробно описано в описаниях отдельных значений in_action.
in_views SET ('with_runtimes_in_95th_percentile', 'analysis', 'with_errors_or_warnings', 'with_full_table_scans', 'with_sorting', 'with_temp_tables', 'custom'): Какие представления включить. Этот параметр является SET значением, поэтому он может содержать несколько имён представлений, разделённых запятыми. По умолчанию включаются все представления, кроме custom. Допустимые значения:
- with_runtimes_in_95th_percentile: Использовать представление statements_with_runtimes_in_95th_percentile.
- analysis: Использовать представление statement_analysis.
- with_errors_or_warnings: Использовать представление statements_with_errors_or_warnings.
- with_full_table_scans: Использовать представление statements_with_full_table_scans.
- with_sorting: Использовать представление statements_with_sorting.
- with_temp_tables: Использовать представление statements_with_temp_tables.
- custom: Использовать пользовательское представление. Это представление должно быть указано с помощью конфигурационного параметра statement_performance_analyzer.view, чтобы назвать запрос или существующее представление.

Конфигурационные параметры

Операция statement_performance_analyzer() может быть изменена с помощью следующих конфигурационных параметров или соответствующих пользовательских переменных (см. Раздел 26.4.2.1, «Таблица sys_config»):

debug, @sys.debug

Если этот параметр ON, выводить отладочную информацию. По умолчанию OFF.
statement_performance_analyzer.limit, @sys.statement_performance_analyzer.limit

Максимальное количество строк, возвращаемых для представлений, у которых нет встроенного ограничения. По умолчанию 100.
statement_performance_analyzer.view, @sys.statement_performance_analyzer.view

Пользовательский запрос или представление, которые необходимо использовать. Если значение параметра содержит пробел, оно интерпретируется как запрос. В противном случае это должно быть имя существующего представления, которое запрашивает таблицу events_statements_summary_by_digest схемы Performance Schema. В определении запроса или представления не может быть никакой LIMIT части, если конфигурационный параметр statement_performance_analyzer.limit больше 0. Если вы указываете представление, используйте тот же формат, что и для параметра in_table. По умолчанию NULL (пользовательское представление не определено).

Пример

Для создания отчёта с запросами в 95-м процентиле со времени последней обрезки events_statements_summary_by_digest и периодом Delta в одну минуту:

Создайте временную таблицу для хранения начального моментального снимка.
Создайте начальный моментальный снимок.
Сохраните начальный моментальный снимок во временную таблицу.
Подождите одну минуту.
Создайте новый моментальный снимок.
Выполните анализ, основанный на новом моментальном снимке.
Выполните анализ, основанный на Delta между начальным и новым моментальными снимками.

mysql> CALL sys.statement_performance_analyzer('create_tmp', 'mydb.tmp_digests_ini', NULL);
Query OK, 0 rows affected (0.08 sec)

mysql> CALL sys.statement_performance_analyzer('snapshot', NULL, NULL);
Query OK, 0 rows affected (0.02 sec)

mysql> CALL sys.statement_performance_analyzer('save', 'mydb.tmp_digests_ini', NULL);
Query OK, 0 rows affected (0.00 sec)

mysql> DO SLEEP(60);
Query OK, 0 rows affected (1 min 0.00 sec)

mysql> CALL sys.statement_performance_analyzer('snapshot', NULL, NULL);
Query OK, 0 rows affected (0.02 sec)

mysql> CALL sys.statement_performance_analyzer('overall', NULL, 'with_runtimes_in_95th_percentile');
+-----------------------------------------+
| Next Output                             |
+-----------------------------------------+
| Queries with Runtime in 95th Percentile |
+-----------------------------------------+
1 row in set (0.05 sec)

...

mysql> CALL sys.statement_performance_analyzer('delta', 'mydb.tmp_digests_ini', 'with_runtimes_in_95th_percentile');
+-----------------------------------------+
| Next Output                             |
+-----------------------------------------+
| Queries with Runtime in 95th Percentile |
+-----------------------------------------+
1 row in set (0.03 sec)

...

Создайте общий отчёт о запросах в 95-м процентиле и 10 самых популярных запросах с полными сканированиями таблиц:

mysql> CALL sys.statement_performance_analyzer('snapshot', NULL, NULL);
Query OK, 0 rows affected (0.01 sec)

mysql> SET @sys.statement_performance_analyzer.limit = 10;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL sys.statement_performance_analyzer('overall', NULL, 'with_runtimes_in_95th_percentile,with_full_table_scans');
+-----------------------------------------+
| Next Output                             |
+-----------------------------------------+
| Queries with Runtime in 95th Percentile |
+-----------------------------------------+
1 row in set (0.01 sec)

...

+-------------------------------------+
| Next Output                         |
+-------------------------------------+
| Top 10 Queries with Full Table Scan |
+-------------------------------------+
1 row in set (0.09 sec)

...

Используйте пользовательское представление, показывающее 10 самых популярных запросов, отсортированных по общему времени выполнения, обновляя представление каждую минуту с помощью команды watch в Linux:

mysql> CREATE OR REPLACE VIEW mydb.my_statements AS
       SELECT sys.format_statement(DIGEST_TEXT) AS query,
              SCHEMA_NAME AS db,
              COUNT_STAR AS exec_count,
              sys.format_time(SUM_TIMER_WAIT) AS total_latency,
              sys.format_time(AVG_TIMER_WAIT) AS avg_latency,
              ROUND(IFNULL(SUM_ROWS_SENT / NULLIF(COUNT_STAR, 0), 0)) AS rows_sent_avg,
              ROUND(IFNULL(SUM_ROWS_EXAMINED / NULLIF(COUNT_STAR, 0), 0)) AS rows_examined_avg,
              ROUND(IFNULL(SUM_ROWS_AFFECTED / NULLIF(COUNT_STAR, 0), 0)) AS rows_affected_avg,
              DIGEST AS digest
         FROM performance_schema.events_statements_summary_by_digest
       ORDER BY SUM_TIMER_WAIT DESC;
Query OK, 0 rows affected (0.10 sec)

mysql> CALL sys.statement_performance_analyzer('create_table', 'mydb.digests_prev', NULL);
Query OK, 0 rows affected (0.10 sec)

$> watch -n 60 "mysql sys --table -e \"
> SET @sys.statement_performance_analyzer.view = 'mydb.my_statements';
> SET @sys.statement_performance_analyzer.limit = 10;
> CALL statement_performance_analyzer('snapshot', NULL, NULL);
> CALL statement_performance_analyzer('delta', 'mydb.digests_prev', 'custom');
> CALL statement_performance_analyzer('save', 'mydb.digests_prev', NULL);
> \""

Every 60.0s: mysql sys --table -e "        ...  Mon Dec 22 10:58:51 2014

+----------------------------------+
| Next Output                      |
+----------------------------------+
| Top 10 Queries Using Custom View |
+----------------------------------+
+-------------------+-------+------------+---------------+-------------+---------------+-------------------+-------------------+----------------------------------+
| query             | db    | exec_count | total_latency | avg_latency | rows_sent_avg | rows_examined_avg | rows_affected_avg | digest                           |
+-------------------+-------+------------+---------------+-------------+---------------+-------------------+-------------------+----------------------------------+
...