Creates a report of the statements running on the server. The views are calculated based on the overall and/or delta activity.
This procedure disables binary logging during its execution by
manipulating the session value of the
sql_log_bin
system variable.
That is a restricted operation, so the procedure requires
privileges sufficient to set restricted session variables. See
Section 7.1.9.1, “System Variable Privileges”.
in_action ENUM('snapshot', 'overall', 'delta', 'create_tmp', 'create_table', 'save', 'cleanup')
: The action to take. These values are permitted:snapshot
: Store a snapshot. The default is to make a snapshot of the current content of the Performance Schemaevents_statements_summary_by_digest
table. By settingin_table
, this can be overwritten to copy the content of the specified table. The snapshot is stored in thesys
schematmp_digests
temporary table.overall
: Generate an analysis based on the content of the table specified byin_table
. For the overall analysis,in_table
can beNOW()
to use a fresh snapshot. This overwrites an existing snapshot. UseNULL
forin_table
to use the existing snapshot. Ifin_table
isNULL
and no snapshot exists, a new snapshot is created. Thein_views
parameter and thestatement_performance_analyzer.limit
configuration option affect the operation of this procedure.delta
: Generate a delta analysis. The delta is calculated between the reference table specified byin_table
and the snapshot, which must exist. This action uses thesys
schematmp_digests_delta
temporary table. Thein_views
parameter and thestatement_performance_analyzer.limit
configuration option affect the operation of this procedure.create_table
: Create a regular table suitable for storing the snapshot for later use (for example, for calculating deltas).create_tmp
: Create a temporary table suitable for storing the snapshot for later use (for example, for calculating deltas).save
: Save the snapshot in the table specified byin_table
. The table must exist and have the correct structure. If no snapshot exists, a new snapshot is created.cleanup
: Remove the temporary tables used for the snapshot and delta.
in_table VARCHAR(129)
: The table parameter used for some of the actions specified by thein_action
parameter. Use the formatdb_name.tbl_name
ortbl_name
without using any backtick (`
) identifier-quoting characters. Periods (.
) are not supported in database and table names.The meaning of the
in_table
value for eachin_action
value is detailed in the individualin_action
value descriptions.in_views SET ('with_runtimes_in_95th_percentile', 'analysis', 'with_errors_or_warnings', 'with_full_table_scans', 'with_sorting', 'with_temp_tables', 'custom')
: Which views to include. This parameter is aSET
value, so it can contain multiple view names, separated by commas. The default is to include all views exceptcustom
. The following values are permitted:with_runtimes_in_95th_percentile
: Use thestatements_with_runtimes_in_95th_percentile
view.analysis
: Use thestatement_analysis
view.with_errors_or_warnings
: Use thestatements_with_errors_or_warnings
view.with_full_table_scans
: Use thestatements_with_full_table_scans
view.with_sorting
: Use thestatements_with_sorting
view.with_temp_tables
: Use thestatements_with_temp_tables
view.custom
: Use a custom view. This view must be specified using thestatement_performance_analyzer.view
configuration option to name a query or an existing view.
statement_performance_analyzer()
operation can be modified using the following configuration
options or their corresponding user-defined variables (see
Section 30.4.2.1, “The sys_config Table”):
debug
,@sys.debug
If this option is
ON
, produce debugging output. The default isOFF
.statement_performance_analyzer.limit
,@sys.statement_performance_analyzer.limit
The maximum number of rows to return for views that have no built-in limit. The default is 100.
statement_performance_analyzer.view
,@sys.statement_performance_analyzer.view
The custom query or view to be used. If the option value contains a space, it is interpreted as a query. Otherwise, it must be the name of an existing view that queries the Performance Schema
events_statements_summary_by_digest
table. There cannot be anyLIMIT
clause in the query or view definition if thestatement_performance_analyzer.limit
configuration option is greater than 0. If specifying a view, use the same format as for thein_table
parameter. The default isNULL
(no custom view defined).
To create a report with the queries in the 95th percentile
since the last truncation of
events_statements_summary_by_digest
and with a one-minute delta period:
Create a temporary table to store the initial snapshot.
Create the initial snapshot.
Save the initial snapshot in the temporary table.
Wait one minute.
Create a new snapshot.
Perform analysis based on the new snapshot.
Perform analysis based on the delta between the initial and new snapshots.
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)
...
Create an overall report of the 95th percentile queries and the top 10 queries with full table scans:
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)
...
Use a custom view showing the top 10 queries sorted by total execution time, refreshing the view every minute using the watch command in 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 |
+-------------------+-------+------------+---------------+-------------+---------------+-------------------+-------------------+----------------------------------+
...