30.4.4.25 The statement_performance_analyzer() Procedure — MySQL 9.2

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

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

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

Параметры

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

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

Пример

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

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

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                           |
+-------------------+-------+------------+---------------+-------------+---------------+-------------------+-------------------+----------------------------------+
...