Routines

Description

Takes a source database name and synonym name, and then creates the synonym database with views that point to all of the tables within the source database.

Useful for creating a “ps” synonym for “performance_schema”, or “is” instead of “information_schema”, for example.

Parameters

in_db_name (VARCHAR(64)): The database name that you would like to create a synonym for. in_synonym (VARCHAR(64)): The database synonym name.

Example

mysql> SHOW DATABASES; +——————–+ | Database | +——————–+ | information_schema | | mysql | | performance_schema | | sys | | test | +——————–+ 5 rows in set (0.00 sec)

mysql> CALL sys.create_synonym_db(‘performance_schema’, ‘ps’); +—————————————+ | summary | +—————————————+ | Created 74 views in the ps database | +—————————————+ 1 row in set (8.57 sec)

Query OK, 0 rows affected (8.57 sec)

mysql> SHOW DATABASES; +——————–+ | Database | +——————–+ | information_schema | | mysql | | performance_schema | | ps | | sys | | test | +——————–+ 6 rows in set (0.00 sec)

mysql> SHOW FULL TABLES FROM ps; +——————————————————+————+ | Tables_in_ps | Table_type | +——————————————————+————+ | accounts | VIEW | | cond_instances | VIEW | | events_stages_current | VIEW | | events_stages_history | VIEW | …

Description

Resets the Performance Schema setup to the default settings.

Parameters

in_verbose (BOOLEAN): Whether to print each setup stage (including the SQL) whilst running.

Example

mysql> CALL sys.ps_setup_reset_to_default(true)\G *************************** 1. row *************************** status: Resetting: setup_actors DELETE FROM performance_schema.setup_actors WHERE NOT (HOST = ‘%’ AND USER = ‘%’ AND ROLE = ‘%’) 1 row in set (0.00 sec)

*************************** 1. row *************************** status: Resetting: setup_actors INSERT IGNORE INTO performance_schema.setup_actors VALUES (‘%’, ‘%’, ‘%’) 1 row in set (0.00 sec) …

mysql> CALL sys.ps_setup_reset_to_default(false)\G Query OK, 0 rows affected (0.00 sec)

Description

Formats a normalized statement, truncating it if it is > 64 characters long by default.

To configure the length to truncate the statement to by default, update the statement_truncate_len variable with sys_config table to a different value. Alternatively, to change it just for just your particular session, use SET @sys.statement_truncate_len := <some new value>.

Useful for printing statement related data from Performance Schema from the command line.

Parameters

statement (LONGTEXT): The statement to format.

Returns

LONGTEXT

Example

mysql> SELECT sys.format_statement(digest_text) -> FROM performance_schema.events_statements_summary_by_digest -> ORDER by sum_timer_wait DESC limit 5; +——————————————————————-+ | sys.format_statement(digest_text) | +——————————————————————-+ | CREATE SQL SECURITY INVOKER VI … KE ? AND variable_value > ? | | CREATE SQL SECURITY INVOKER VI … aitIS NOT NULL ,esc. ... | | CREATE SQL SECURITY INVOKER VI ... ait IS NOT NULL , sys . … | | CREATE SQL SECURITY INVOKER VI … , compressed_size ) ) DESC | | CREATE SQL SECURITY INVOKER VI … LIKE ? ORDER BY timer_start | +——————————————————————-+ 5 rows in set (0.00 sec)

Description

Shows all currently enabled Performance Schema configuration.

Parameters

in_show_instruments (BOOLEAN): Whether to print enabled instruments (can print many items)

in_show_threads (BOOLEAN): Whether to print enabled threads

Example

mysql> CALL sys.ps_setup_show_enabled(TRUE, TRUE); +—————————-+ | performance_schema_enabled | +—————————-+ | 1 | +—————————-+ 1 row in set (0.00 sec)

+—————+ | enabled_users | +—————+ | ‘%’@‘%’ | +—————+ 1 row in set (0.01 sec)

+————-+———+———+——-+ | object_type | objects | enabled | timed | +————-+———+———+——-+ | EVENT | %.% | YES | YES | | FUNCTION | %.% | YES | YES | | PROCEDURE | %.% | YES | YES | | TABLE | %.% | YES | YES | | TRIGGER | %.% | YES | YES | +————-+———+———+——-+ 5 rows in set (0.01 sec)

+—————————+ | enabled_consumers | +—————————+ | events_statements_current | | global_instrumentation | | thread_instrumentation | | statements_digest | +—————————+ 4 rows in set (0.05 sec)

+———————————+————-+ | enabled_threads | thread_type | +———————————+————-+ | sql/main | BACKGROUND | | sql/thread_timer_notifier | BACKGROUND | | innodb/io_ibuf_thread | BACKGROUND | | innodb/io_log_thread | BACKGROUND | | innodb/io_read_thread | BACKGROUND | | innodb/io_read_thread | BACKGROUND | | innodb/io_write_thread | BACKGROUND | | innodb/io_write_thread | BACKGROUND | | innodb/page_cleaner_thread | BACKGROUND | | innodb/srv_lock_timeout_thread | BACKGROUND | | innodb/srv_error_monitor_thread | BACKGROUND | | innodb/srv_monitor_thread | BACKGROUND | | innodb/srv_master_thread | BACKGROUND | | innodb/srv_purge_thread | BACKGROUND | | innodb/srv_worker_thread | BACKGROUND | | innodb/srv_worker_thread | BACKGROUND | | innodb/srv_worker_thread | BACKGROUND | | innodb/buf_dump_thread | BACKGROUND | | innodb/dict_stats_thread | BACKGROUND | | sql/signal_handler | BACKGROUND | | sql/compress_gtid_table | FOREGROUND | | root@localhost | FOREGROUND | +———————————+————-+ 22 rows in set (0.01 sec)

+————————————-+——-+ | enabled_instruments | timed | +————————————-+——-+ | wait/io/file/sql/map | YES | | wait/io/file/sql/binlog | YES | … | statement/com/Error | YES | | statement/com/ | YES | | idle | YES | +————————————-+——-+ 210 rows in set (0.08 sec)

Query OK, 0 rows affected (0.89 sec)

Description

Returns the major version of MySQL Server.

Returns

TINYINT UNSIGNED

Example

mysql> SELECT VERSION(), sys.version_major(); +————————————–+———————+ | VERSION() | sys.version_major() | +————————————–+———————+ | 5.7.9-enterprise-commercial-advanced | 5 | +————————————–+———————+ 1 row in set (0.00 sec)

Description

Enable the given connection/thread in Performance Schema.

Parameters

in_connection_id (BIGINT): The connection ID (PROCESSLIST_ID from performance_schema.threads or the ID shown within SHOW PROCESSLIST)

Example

mysql> CALL sys.ps_setup_enable_thread(3); +——————+ | summary | +——————+ | Enabled 1 thread | +——————+ 1 row in set (0.01 sec)

To enable the current connection:

mysql> CALL sys.ps_setup_enable_thread(CONNECTION_ID()); +——————+ | summary | +——————+ | Enabled 1 thread | +——————+ 1 row in set (0.00 sec)

Description

Determines whether instrumentation of an account is enabled within Performance Schema.

Parameters

in_host VARCHAR(255): The hostname of the account to check. in_user VARCHAR(32): The username of the account to check.

Returns

ENUM(‘YES’, ‘NO’, ‘PARTIAL’)

Example

mysql> SELECT sys.ps_is_account_enabled(‘localhost’, ‘root’); +————————————————+ | sys.ps_is_account_enabled(‘localhost’, ‘root’) | +————————————————+ | YES | +————————————————+ 1 row in set (0.01 sec)

Description

Reloads a saved Performance Schema configuration, so that you can alter the setup for debugging purposes, but restore it to a previous state.

Use the companion procedure - ps_setup_save(), to save a configuration.

Requires the SUPER privilege for “SET sql_log_bin = 0;”.

Parameters

None.

Example

mysql> CALL sys.ps_setup_save(); Query OK, 0 rows affected (0.08 sec)

mysql> UPDATE performance_schema.setup_instruments SET enabled = ‘YES’, timed = ‘YES’; Query OK, 547 rows affected (0.40 sec) Rows matched: 784 Changed: 547 Warnings: 0

/* Run some tests that need more detailed instrumentation here */

mysql> CALL sys.ps_setup_reload_saved(); Query OK, 0 rows affected (0.32 sec)

Description

Takes a list, and a value to add to the list, and returns the resulting list.

Useful for altering certain session variables, like sql_mode or optimizer_switch for instance.

Parameters

in_list (TEXT): The comma separated list to add a value to

in_add_value (TEXT): The value to add to the input list

Returns

TEXT

Example

mysql> select @@sql_mode; +———————————————————————————–+ | @@sql_mode | +———————————————————————————–+ | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +———————————————————————————–+ 1 row in set (0.00 sec)

mysql> set sql_mode = sys.list_add(@@sql_mode, ‘ANSI_QUOTES’); Query OK, 0 rows affected (0.06 sec)

mysql> select @@sql_mode; +———————————————————————————————–+ | @@sql_mode | +———————————————————————————————–+ | ANSI_QUOTES,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +———————————————————————————————–+ 1 row in set (0.00 sec)

Description

Takes an unquoted identifier (schema name, table name, etc.) and returns the identifier quoted with backticks.

Parameters

in_identifier (TEXT): The identifier to quote.

Returns

TEXT CHARSET UTF8MB4

Example

mysql> SELECT sys.quote_identifier(‘my_identifier’) AS Identifier; +—————–+ | Identifier | +—————–+ | my_identifier | +—————–+ 1 row in set (0.00 sec)

mysql> SELECT sys.quote_identifier(’myidenfier') AS Identifier; +----------------+ | Identifier | +----------------+ |my``idenfier` | +—————-+ 1 row in set (0.00 sec)

Description

Truncates all summary tables within Performance Schema, resetting all aggregated instrumentation as a snapshot.

Parameters

in_verbose (BOOLEAN): Whether to print each TRUNCATE statement before running

Example

mysql> CALL sys.ps_truncate_all_tables(false); +———————+ | summary | +———————+ | Truncated 44 tables | +———————+ 1 row in set (0.10 sec)

Query OK, 0 rows affected (0.10 sec)

Description

Return the Performance Schema THREAD_ID for the specified connection ID.

Parameters

in_connection_id (BIGINT UNSIGNED): The id of the connection to return the thread id for. If NULL, the current connection thread id is returned.

Example

mysql> SELECT sys.ps_thread_id(79); +———————-+ | sys.ps_thread_id(79) | +———————-+ | 98 | +———————-+ 1 row in set (0.00 sec)

mysql> SELECT sys.ps_thread_id(CONNECTION_ID()); +———————————–+ | sys.ps_thread_id(CONNECTION_ID()) | +———————————–+ | 98 | +———————————–+ 1 row in set (0.00 sec)

Description

Disable all background thread instrumentation within Performance Schema.

Parameters

None.

Example

mysql> CALL sys.ps_setup_disable_background_threads(); +——————————–+ | summary | +——————————–+ | Disabled 18 background threads | +——————————–+ 1 row in set (0.00 sec)

Description

Takes a raw picoseconds value, and converts it to a human readable form.

Picoseconds are the precision that all latency values are printed in within Performance Schema, however are not user friendly when wanting to scan output from the command line.

Parameters

picoseconds (TEXT): The raw picoseconds value to convert.

Returns

TEXT CHARSET UTF8MB4

Example

mysql> select format_time(342342342342345); +——————————+ | format_time(342342342342345) | +——————————+ | 00:05:42 | +——————————+ 1 row in set (0.00 sec)

mysql> select format_time(342342342); +————————+ | format_time(342342342) | +————————+ | 342.34 us | +————————+ 1 row in set (0.00 sec)

mysql> select format_time(34234); +——————–+ | format_time(34234) | +——————–+ | 34.23 ns | +——————–+ 1 row in set (0.00 sec)

Description

Shows all currently disable Performance Schema configuration.

Disabled users is only available for MySQL 5.7.6 and later. In earlier versions it was only possible to enable users.

Parameters

in_show_instruments (BOOLEAN): Whether to print disabled instruments (can print many items)

in_show_threads (BOOLEAN): Whether to print disabled threads

Example

mysql> CALL sys.ps_setup_show_disabled(TRUE, TRUE); +—————————-+ | performance_schema_enabled | +—————————-+ | 1 | +—————————-+ 1 row in set (0.00 sec)

+——————–+ | disabled_users | +——————–+ | ‘mark’@‘localhost’ | +——————–+ 1 row in set (0.00 sec)

+————-+———————-+———+——-+ | object_type | objects | enabled | timed | +————-+———————-+———+——-+ | EVENT | mysql.% | NO | NO | | EVENT | performance_schema.% | NO | NO | | EVENT | information_schema.% | NO | NO | | FUNCTION | mysql.% | NO | NO | | FUNCTION | performance_schema.% | NO | NO | | FUNCTION | information_schema.% | NO | NO | | PROCEDURE | mysql.% | NO | NO | | PROCEDURE | performance_schema.% | NO | NO | | PROCEDURE | information_schema.% | NO | NO | | TABLE | mysql.% | NO | NO | | TABLE | performance_schema.% | NO | NO | | TABLE | information_schema.% | NO | NO | | TRIGGER | mysql.% | NO | NO | | TRIGGER | performance_schema.% | NO | NO | | TRIGGER | information_schema.% | NO | NO | +————-+———————-+———+——-+ 15 rows in set (0.00 sec)

+———————————-+ | disabled_consumers | +———————————-+ | events_stages_current | | events_stages_history | | events_stages_history_long | | events_statements_history | | events_statements_history_long | | events_transactions_history | | events_transactions_history_long | | events_waits_current | | events_waits_history | | events_waits_history_long | +———————————-+ 10 rows in set (0.00 sec)

Empty set (0.00 sec)

+—————————————————————————————+——-+ | disabled_instruments | timed | +—————————————————————————————+——-+ | wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_tc | NO | | wait/synch/mutex/sql/LOCK_des_key_file | NO | | wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit | NO | … | memory/sql/servers_cache | NO | | memory/sql/udf_mem | NO | | wait/lock/metadata/sql/mdl | NO | +—————————————————————————————+——-+ 547 rows in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Description

Returns the minor (release series) version of MySQL Server.

Returns

TINYINT UNSIGNED

Example

mysql> SELECT VERSION(), sys.server_minor(); +————————————–+———————+ | VERSION() | sys.version_minor() | +————————————–+———————+ | 5.7.9-enterprise-commercial-advanced | 7 | +————————————–+———————+ 1 row in set (0.00 sec)

Description

Enable all background thread instrumentation within Performance Schema.

Parameters

None.

Example

mysql> CALL sys.ps_setup_enable_background_threads(); +——————————-+ | summary | +——————————-+ | Enabled 18 background threads | +——————————-+ 1 row in set (0.00 sec)

Description

Traces all instrumentation within Performance Schema for a specific Statement Digest.

When finding a statement of interest within the performance_schema.events_statements_summary_by_digest table, feed the DIGEST value in to this procedure, set how long to poll for, and at what interval to poll, and it will generate a report of all statistics tracked within Performance Schema for that digest for the interval.

It will also attempt to generate an EXPLAIN for the longest running example of the digest during the interval. Note this may fail, as:

• Performance Schema truncates long SQL_TEXT values (and hence the EXPLAIN will fail due to parse errors)
• the default schema is sys (so tables that are not fully qualified in the query may not be found)
• some queries such as SHOW are not supported in EXPLAIN.

When the EXPLAIN fails, the error will be ignored and no EXPLAIN output generated.

Requires the SUPER privilege for “SET sql_log_bin = 0;”.

Parameters

in_digest (VARCHAR(64)): The statement digest identifier you would like to analyze in_runtime (INT): The number of seconds to run analysis for in_interval (DECIMAL(2,2)): The interval (in seconds, may be fractional) at which to try and take snapshots in_start_fresh (BOOLEAN): Whether to TRUNCATE the events_statements_history_long and events_stages_history_long tables before starting in_auto_enable (BOOLEAN): Whether to automatically turn on required consumers

Example

mysql> call ps_trace_statement_digest(‘891ec6860f98ba46d89dd20b0c03652c’, 10, 0.1, true, true); +——————–+ | SUMMARY STATISTICS | +——————–+ | SUMMARY STATISTICS | +——————–+ 1 row in set (9.11 sec)

+————+———–+———–+———–+—————+————+————+ | executions | exec_time | lock_time | rows_sent | rows_examined | tmp_tables | full_scans | +————+———–+———–+———–+—————+————+————+ | 21 | 4.11 ms | 2.00 ms | 0 | 21 | 0 | 0 | +————+———–+———–+———–+—————+————+————+ 1 row in set (9.11 sec)

+——————————————+——-+———–+ | event_name | count | latency | +——————————————+——-+———–+ | stage/sql/checking query cache for query | 16 | 724.37 us | | stage/sql/statistics | 16 | 546.92 us | | stage/sql/freeing items | 18 | 520.11 us | | stage/sql/init | 51 | 466.80 us | … | stage/sql/cleaning up | 18 | 11.92 us | | stage/sql/executing | 16 | 6.95 us | +——————————————+——-+———–+ 17 rows in set (9.12 sec)

+—————————+ | LONGEST RUNNING STATEMENT | +—————————+ | LONGEST RUNNING STATEMENT | +—————————+ 1 row in set (9.16 sec)

+———–+———–+———–+———–+—————+————+———–+ | thread_id | exec_time | lock_time | rows_sent | rows_examined | tmp_tables | full_scan | +———–+———–+———–+———–+—————+————+———–+ | 166646 | 618.43 us | 1.00 ms | 0 | 1 | 0 | 0 | +———–+———–+———–+———–+—————+————+———–+ 1 row in set (9.16 sec)

// Truncated for clarity… +—————————————————————–+ | sql_text | +—————————————————————–+ | select hibeventhe0_.id as id1382_, hibeventhe0_.createdTime … | +—————————————————————–+ 1 row in set (9.17 sec)

+——————————————+———–+ | event_name | latency | +——————————————+———–+ | stage/sql/init | 8.61 us | | stage/sql/Waiting for query cache lock | 453.23 us | | stage/sql/init | 331.07 ns | | stage/sql/checking query cache for query | 43.04 us | … | stage/sql/freeing items | 30.46 us | | stage/sql/cleaning up | 662.13 ns | +——————————————+———–+ 18 rows in set (9.23 sec)

+—-+————-+————–+——-+—————+———–+———+————-+——+——-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +—-+————-+————–+——-+—————+———–+———+————-+——+——-+ | 1 | SIMPLE | hibeventhe0_ | const | fixedTime | fixedTime | 775 | const,const | 1 | NULL | +—-+————-+————–+——-+—————+———–+———+————-+——+——-+ 1 row in set (9.27 sec)

Query OK, 0 rows affected (9.28 sec)

Description

Returns the patch release version of MySQL Server.

Returns

TINYINT UNSIGNED

Example

mysql> SELECT VERSION(), sys.version_patch(); +————————————–+———————+ | VERSION() | sys.version_patch() | +————————————–+———————+ | 5.7.9-enterprise-commercial-advanced | 9 | +————————————–+———————+ 1 row in set (0.00 sec)

Description

Takes a raw file path, and attempts to extract the schema name from it.

Useful for when interacting with Performance Schema data concerning IO statistics, for example.

Currently relies on the fact that a table data file will be within a specified database directory (will not work with partitions or tables that specify an individual DATA_DIRECTORY).

Parameters

path (VARCHAR(512)): The full file path to a data file to extract the schema name from.

Returns

VARCHAR(64)

Example

mysql> SELECT sys.extract_schema_from_file_name(‘/var/lib/mysql/employees/employee.ibd’); +—————————————————————————-+ | sys.extract_schema_from_file_name(‘/var/lib/mysql/employees/employee.ibd’) | +—————————————————————————-+ | employees | +—————————————————————————-+ 1 row in set (0.00 sec)

Description

Returns the value for the requested variable using the following logic:

1. If the option exists in sys.sys_config return the value from there.
2. Else fall back on the provided default value.

Notes for using sys_get_config():

• If the default value argument to sys_get_config() is NULL and case 2. is reached, NULL is returned. It is then expected that the caller is able to handle NULL for the given configuration option.
• The convention is to name the user variables @sys.. It is that is stored in the sys_config table and is what is expected as the argument to sys_get_config().
• If you want to check whether the configuration option has already been set and if not assign with the return value of sys_get_config() you can use IFNULL(…) (see example below). However this should not be done inside a loop (e.g. for each row in a result set) as for repeated calls where assignment is only needed in the first iteration using IFNULL(…) is expected to be significantly slower than using an IF (…) THEN … END IF; block (see example below).

Parameters

in_variable_name (VARCHAR(128)): The name of the config option to return the value for.

in_default_value (VARCHAR(128)): The default value to return if the variable does not exist in sys.sys_config.

Returns

VARCHAR(128)

Example

– Get the configuration value from sys.sys_config falling back on 128 if the option is not present in the table. mysql> SELECT sys.sys_get_config(‘statement_truncate_len’, 128) AS Value; +——-+ | Value | +——-+ | 64 | +——-+ 1 row in set (0.00 sec)

– Check whether the option is already set, if not assign - IFNULL(…) one liner example. mysql> SET @sys.statement_truncate_len = IFNULL(@sys.statement_truncate_len, sys.sys_get_config(‘statement_truncate_len’, 64)); Query OK, 0 rows affected (0.00 sec)

– Check whether the option is already set, if not assign - IF … THEN … END IF example. IF (@sys.statement_truncate_len IS NULL) THEN SET @sys.statement_truncate_len = sys.sys_get_config(‘statement_truncate_len’, 64); END IF;

Description

Takes a raw bytes value, and converts it to a human readable format.

Parameters

bytes (TEXT): A raw bytes value.

Returns

TEXT

Example

mysql> SELECT sys.format_bytes(2348723492723746) AS size; +———-+ | size | +———-+ | 2.09 PiB | +———-+ 1 row in set (0.00 sec)

mysql> SELECT sys.format_bytes(2348723492723) AS size; +———-+ | size | +———-+ | 2.14 TiB | +———-+ 1 row in set (0.00 sec)

mysql> SELECT sys.format_bytes(23487234) AS size; +———–+ | size | +———–+ | 22.40 MiB | +———–+ 1 row in set (0.00 sec)

Description

Determines whether a consumer is enabled (taking the consumer hierarchy into consideration) within the Performance Schema.

An exception with errno 3047 is thrown if an unknown consumer name is passed to the function. A consumer name of NULL returns NULL.

Parameters

in_consumer VARCHAR(64): The name of the consumer to check.

Returns

ENUM(‘YES’, ‘NO’)

Example

mysql> SELECT sys.ps_is_consumer_enabled(‘events_stages_history’); +—————————————————–+ | sys.ps_is_consumer_enabled(‘events_stages_history’) | +—————————————————–+ | NO | +—————————————————–+ 1 row in set (0.00 sec)

Description

Shows all currently enabled consumers.

Parameters

None

Example

mysql> CALL sys.ps_setup_show_enabled_consumers();

+—————————+ | enabled_consumers | +—————————+ | events_statements_current | | global_instrumentation | | thread_instrumentation | | statements_digest | +—————————+ 4 rows in set (0.05 sec)

Description

Takes a raw path value, and strips out the datadir or tmpdir replacing with @@datadir and @@tmpdir respectively.

Also normalizes the paths across operating systems, so backslashes on Windows are converted to forward slashes

Parameters

path (VARCHAR(512)): The raw file path value to format.

Returns

VARCHAR(512) CHARSET UTF8MB4

Example

mysql> select @@datadir; +———————————————–+ | @@datadir | +———————————————–+ | /Users/mark/sandboxes/SmallTree/AMaster/data/ | +———————————————–+ 1 row in set (0.06 sec)

mysql> select format_path(‘/Users/mark/sandboxes/SmallTree/AMaster/data/mysql/proc.MYD’) AS path; +————————–+ | path | +————————–+ | @@datadir/mysql/proc.MYD | +————————–+ 1 row in set (0.03 sec)

Description

Dumps all data within Performance Schema for an instrumented thread, to create a DOT formatted graph file.

Each resultset returned from the procedure should be used for a complete graph

Requires the SUPER privilege for “SET sql_log_bin = 0;”.

Parameters

in_thread_id (BIGINT UNSIGNED): The thread that you would like a stack trace for in_outfile (VARCHAR(255)): The filename the dot file will be written to in_max_runtime (DECIMAL(20,2)): The maximum time to keep collecting data. Use NULL to get the default which is 60 seconds. in_interval (DECIMAL(20,2)): How long to sleep between data collections. Use NULL to get the default which is 1 second. in_start_fresh (BOOLEAN): Whether to reset all Performance Schema data before tracing. in_auto_setup (BOOLEAN): Whether to disable all other threads and enable all consumers/instruments. This will also reset the settings at the end of the run. in_debug (BOOLEAN): Whether you would like to include file:lineno in the graph

Example

mysql> CALL sys.ps_trace_thread(25, CONCAT(‘/tmp/stack-’, REPLACE(NOW(), ’ ’, ‘-’), ‘.dot’), NULL, NULL, TRUE, TRUE, TRUE); +——————-+ | summary | +——————-+ | Disabled 1 thread | +——————-+ 1 row in set (0.00 sec)

+———————————————+ | Info | +———————————————+ | Data collection starting for THREAD_ID = 25 | +———————————————+ 1 row in set (0.03 sec)

+———————————————————–+ | Info | +———————————————————–+ | Stack trace written to /tmp/stack-2014-02-16-21:18:41.dot | +———————————————————–+ 1 row in set (60.07 sec)

+——————————————————————-+ | Convert to PDF | +——————————————————————-+ | dot -Tpdf -o /tmp/stack_25.pdf /tmp/stack-2014-02-16-21:18:41.dot | +——————————————————————-+ 1 row in set (60.07 sec)

+——————————————————————-+ | Convert to PNG | +——————————————————————-+ | dot -Tpng -o /tmp/stack_25.png /tmp/stack-2014-02-16-21:18:41.dot | +——————————————————————-+ 1 row in set (60.07 sec)

+——————+ | summary | +——————+ | Enabled 1 thread | +——————+ 1 row in set (60.32 sec)

Description

Returns a JSON object with info on the given threads current transaction, and the statements it has already executed, derived from the performance_schema.events_transactions_current and performance_schema.events_statements_history tables (so the consumers for these also have to be enabled within Performance Schema to get full data in the object).

When the output exceeds the default truncation length (65535), a JSON error object is returned, such as:

{ “error”: “Trx info truncated: Row 6 was cut by GROUP_CONCAT()” }

Similar error objects are returned for other warnings/and exceptions raised when calling the function.

The max length of the output of this function can be controlled with the ps_thread_trx_info.max_length variable set via sys_config, or the @sys.ps_thread_trx_info.max_length user variable, as appropriate.

Parameters

in_thread_id (BIGINT UNSIGNED): The id of the thread to return the transaction info for.

Example

SELECT sys.ps_thread_trx_info(48)\G *************************** 1. row *************************** sys.ps_thread_trx_info(48): [ { “time”: “790.70 us”, “state”: “COMMITTED”, “mode”: “READ WRITE”, “autocommitted”: “NO”, “gtid”: “AUTOMATIC”, “isolation”: “REPEATABLE READ”, “statements_executed”: [ { “sql_text”: “INSERT INTO info VALUES (1, ‘foo’)”, “time”: “471.02 us”, “schema”: “trx”, “rows_examined”: 0, “rows_affected”: 1, “rows_sent”: 0, “tmp_tables”: 0, “tmp_disk_tables”: 0, “sort_rows”: 0, “sort_merge_passes”: 0 }, { “sql_text”: “COMMIT”, “time”: “254.42 us”, “schema”: “trx”, “rows_examined”: 0, “rows_affected”: 0, “rows_sent”: 0, “tmp_tables”: 0, “tmp_disk_tables”: 0, “sort_rows”: 0, “sort_merge_passes”: 0 } ] }, { “time”: “426.20 us”, “state”: “COMMITTED”, “mode”: “READ WRITE”, “autocommitted”: “NO”, “gtid”: “AUTOMATIC”, “isolation”: “REPEATABLE READ”, “statements_executed”: [ { “sql_text”: “INSERT INTO info VALUES (2, ‘bar’)”, “time”: “107.33 us”, “schema”: “trx”, “rows_examined”: 0, “rows_affected”: 1, “rows_sent”: 0, “tmp_tables”: 0, “tmp_disk_tables”: 0, “sort_rows”: 0, “sort_merge_passes”: 0 }, { “sql_text”: “COMMIT”, “time”: “213.23 us”, “schema”: “trx”, “rows_examined”: 0, “rows_affected”: 0, “rows_sent”: 0, “tmp_tables”: 0, “tmp_disk_tables”: 0, “sort_rows”: 0, “sort_merge_passes”: 0 } ] } ] 1 row in set (0.03 sec)

Description

Takes a raw file path, and extracts the table name from it.

Useful for when interacting with Performance Schema data concerning IO statistics, for example.

Parameters

path (VARCHAR(512)): The full file path to a data file to extract the table name from.

Returns

VARCHAR(64)

Example

mysql> SELECT sys.extract_table_from_file_name(‘/var/lib/mysql/employees/employee.ibd’); +—————————————————————————+ | sys.extract_table_from_file_name(‘/var/lib/mysql/employees/employee.ibd’) | +—————————————————————————+ | employee | +—————————————————————————+ 1 row in set (0.02 sec)

Description

Takes a list, and a value to attempt to remove from the list, and returns the resulting list.

Useful for altering certain session variables, like sql_mode or optimizer_switch for instance.

Parameters

in_list (TEXT): The comma separated list to drop a value from

in_drop_value (TEXT): The value to drop from the input list

Returns

TEXT

Example

mysql> select @@sql_mode; +———————————————————————————————–+ | @@sql_mode | +———————————————————————————————–+ | ANSI_QUOTES,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +———————————————————————————————–+ 1 row in set (0.00 sec)

mysql> set sql_mode = sys.list_drop(@@sql_mode, ‘ONLY_FULL_GROUP_BY’); Query OK, 0 rows affected (0.03 sec)

mysql> select @@sql_mode; +—————————————————————————-+ | @@sql_mode | +—————————————————————————-+ | ANSI_QUOTES,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +—————————————————————————-+ 1 row in set (0.00 sec)

Description

Return the user@host account for the given Performance Schema thread id.

Parameters

in_thread_id (BIGINT UNSIGNED): The id of the thread to return the account for.

Example

mysql> select thread_id, processlist_user, processlist_host from performance_schema.threads where type = ‘foreground’; +———–+——————+——————+ | thread_id | processlist_user | processlist_host | +———–+——————+——————+ | 23 | NULL | NULL | | 30 | root | localhost | | 31 | msandbox | localhost | | 32 | msandbox | localhost | +———–+——————+——————+ 4 rows in set (0.00 sec)

mysql> select sys.ps_thread_account(31); +—————————+ | sys.ps_thread_account(31) | +—————————+ | msandbox@localhost | +—————————+ 1 row in set (0.00 sec)

Description

Shows all currently disabled consumers.

Parameters

None

Example

mysql> CALL sys.ps_setup_show_disabled_consumers();

+—————————+ | disabled_consumers | +—————————+ | events_statements_current | | global_instrumentation | | thread_instrumentation | | statements_digest | +—————————+ 4 rows in set (0.05 sec)

Description

Returns whether an instrument is timed by default in this version of MySQL.

Parameters

in_instrument VARCHAR(128): The instrument to check.

Returns

ENUM(‘YES’, ‘NO’)

Example

mysql> SELECT sys.ps_is_instrument_default_timed(‘statement/sql/select’); +————————————————————+ | sys.ps_is_instrument_default_timed(‘statement/sql/select’) | +————————————————————+ | YES | +————————————————————+ 1 row in set (0.00 sec)

Description

Shows all currently enabled instruments.

Parameters

None

Example

mysql> CALL sys.ps_setup_show_enabled_instruments();

Description

Disable the given connection/thread in Performance Schema.

Parameters

in_connection_id (BIGINT): The connection ID (PROCESSLIST_ID from performance_schema.threads or the ID shown within SHOW PROCESSLIST)

Example

mysql> CALL sys.ps_setup_disable_thread(3); +——————-+ | summary | +——————-+ | Disabled 1 thread | +——————-+ 1 row in set (0.01 sec)

To disable the current connection:

mysql> CALL sys.ps_setup_disable_thread(CONNECTION_ID()); +——————-+ | summary | +——————-+ | Disabled 1 thread | +——————-+ 1 row in set (0.00 sec)

Description

Takes the query in the argument and executes it using a prepared statement. The prepared statement is deallocated, so the procedure is mainly useful for executing one off dynamically created queries.

The sys_execute_prepared_stmt prepared statement name is used for the query and is required not to exist.

Parameters

in_query (longtext CHARACTER SET UTF8MB4): The query to execute.

Configuration Options

sys.debug Whether to provide debugging output. Default is ‘OFF’. Set to ‘ON’ to include.

Example

mysql> CALL sys.execute_prepared_stmt(‘SELECT * FROM sys.sys_config’); +————————+——-+———————+——–+ | variable | value | set_time | set_by | +————————+——-+———————+——–+ | statement_truncate_len | 64 | 2015-06-30 13:06:00 | NULL | +————————+——-+———————+——–+ 1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Description

Enables instruments within Performance Schema matching the input pattern.

Parameters

in_pattern (VARCHAR(128)): A LIKE pattern match (using “%in_pattern%”) of events to enable

Example

To enable all mutex instruments:

mysql> CALL sys.ps_setup_enable_instrument(‘wait/synch/mutex’); +————————-+ | summary | +————————-+ | Enabled 155 instruments | +————————-+ 1 row in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

To enable just a specific TCP/IP based network IO instrument:

mysql> CALL sys.ps_setup_enable_instrument(‘wait/io/socket/sql/server_tcpip_socket’); +———————–+ | summary | +———————–+ | Enabled 1 instruments | +———————–+ 1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

To enable all instruments:

mysql> CALL sys.ps_setup_enable_instrument(''); +————————-+ | summary | +————————-+ | Enabled 547 instruments | +————————-+ 1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Description

Checks whether the provided connection id is instrumented within Performance Schema.

Parameters

in_connection_id (BIGINT UNSIGNED): The id of the connection to check.

Returns

ENUM(‘YES’, ‘NO’, ‘UNKNOWN’)

Example

mysql> SELECT sys.ps_is_thread_instrumented(CONNECTION_ID()); +————————————————+ | sys.ps_is_thread_instrumented(CONNECTION_ID()) | +————————————————+ | YES | +————————————————+

Description

Disables instruments within Performance Schema matching the input pattern.

Parameters

in_pattern (VARCHAR(128)): A LIKE pattern match (using “%in_pattern%”) of events to disable

Example

To disable all mutex instruments:

mysql> CALL sys.ps_setup_disable_instrument(‘wait/synch/mutex’); +————————–+ | summary | +————————–+ | Disabled 155 instruments | +————————–+ 1 row in set (0.02 sec)

To disable just a specific TCP/IP based network IO instrument:

mysql> CALL sys.ps_setup_disable_instrument(‘wait/io/socket/sql/server_tcpip_socket’); +————————+ | summary | +————————+ | Disabled 1 instruments | +————————+ 1 row in set (0.00 sec)

To disable all instruments:

mysql> CALL sys.ps_setup_disable_instrument(''); +————————–+ | summary | +————————–+ | Disabled 547 instruments | +————————–+ 1 row in set (0.01 sec)

Description

Enables consumers within Performance Schema matching the input pattern.

Parameters

consumer (VARCHAR(128)): A LIKE pattern match (using “%consumer%”) of consumers to enable

Example

To enable all consumers:

mysql> CALL sys.ps_setup_enable_consumer(''); +————————-+ | summary | +————————-+ | Enabled 10 consumers | +————————-+ 1 row in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

To enable just “waits” consumers:

mysql> CALL sys.ps_setup_enable_consumer(‘waits’); +———————–+ | summary | +———————–+ | Enabled 3 consumers | +———————–+ 1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Description

Tests whether the table specified in in_db and in_table exists either as a regular table, or as a temporary table. The returned value corresponds to the table that will be used, so if there’s both a temporary and a permanent table with the given name, then ‘TEMPORARY’ will be returned.

Parameters

in_db (VARCHAR(64)): The database name to check for the existance of the table in.

in_table (VARCHAR(64)): The name of the table to check the existance of.

out_exists ENUM('', ‘BASE TABLE’, ‘VIEW’, ‘TEMPORARY’): The return value: whether the table exists. The value is one of: * '' - the table does not exist neither as a base table, view, nor temporary table. * ‘BASE TABLE’ - the table name exists as a permanent base table table. * ‘VIEW’ - the table name exists as a view. * ‘TEMPORARY’ - the table name exists as a temporary table.

Example

mysql> CREATE DATABASE db1; Query OK, 1 row affected (0.07 sec)

mysql> use db1; Database changed mysql> CREATE TABLE t1 (id INT PRIMARY KEY); Query OK, 0 rows affected (0.08 sec)

mysql> CREATE TABLE t2 (id INT PRIMARY KEY); Query OK, 0 rows affected (0.08 sec)

mysql> CREATE view v_t1 AS SELECT * FROM t1; Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TEMPORARY TABLE t1 (id INT PRIMARY KEY); Query OK, 0 rows affected (0.00 sec)

mysql> CALL sys.table_exists(‘db1’, ‘t1’, @exists); SELECT @exists; Query OK, 0 rows affected (0.00 sec)

+————+ | @exists | +————+ | TEMPORARY | +————+ 1 row in set (0.00 sec)

mysql> CALL sys.table_exists(‘db1’, ‘t2’, @exists); SELECT @exists; Query OK, 0 rows affected (0.00 sec)

+————+ | @exists | +————+ | BASE TABLE | +————+ 1 row in set (0.01 sec)

mysql> CALL sys.table_exists(‘db1’, ‘v_t1’, @exists); SELECT @exists; Query OK, 0 rows affected (0.00 sec)

+———+ | @exists | +———+ | VIEW | +———+ 1 row in set (0.00 sec)

mysql> CALL sys.table_exists(‘db1’, ‘t3’, @exists); SELECT @exists; Query OK, 0 rows affected (0.01 sec)

+———+ | @exists | +———+ | | +———+ 1 row in set (0.00 sec)

Description

Outputs a JSON formatted stack of all statements, stages and events within Performance Schema for the specified thread.

Parameters

thd_id (BIGINT UNSIGNED): The id of the thread to trace. This should match the thread_id column from the performance_schema.threads table. in_verbose (BOOLEAN): Include file:lineno information in the events.

Example

(line separation added for output)

mysql> SELECT sys.ps_thread_stack(37, FALSE) AS thread_stack\G *************************** 1. row *************************** thread_stack: {“rankdir”: “LR”,“nodesep”: “0.10”,“stack_created”: “2014-02-19 13:39:03”, “mysql_version”: “5.7.3-m13”,“mysql_user”: “root@localhost”,“events”: [{“nesting_event_id”: “0”, “event_id”: “10”, “timer_wait”: 256.35, “event_info”: “sql/select”, “wait_info”: "select @@version_comment limit 1\nerrors: 0\nwarnings: 0\nlock time: …

Description

Outputs a textual histogram graph of the average latency values across all normalized queries tracked within the Performance Schema events_statements_summary_by_digest table.

Can be used to show a very high level picture of what kind of latency distribution statements running within this instance have.

Parameters

None.

Example

mysql> CALL sys.ps_statement_avg_latency_histogram()\G *************************** 1. row *************************** Performance Schema Statement Digest Average Latency Histogram:

. = 1 unit * = 2 units # = 3 units

(0 - 38ms) 240 | ################################################################################ (38 - 77ms) 38 | ……………………………….. (77 - 115ms) 3 | … (115 - 154ms) 62 | ******************************* (154 - 192ms) 3 | … (192 - 231ms) 0 | (231 - 269ms) 0 | (269 - 307ms) 0 | (307 - 346ms) 0 | (346 - 384ms) 1 | . (384 - 423ms) 1 | . (423 - 461ms) 0 | (461 - 499ms) 0 | (499 - 538ms) 0 | (538 - 576ms) 0 | (576 - 615ms) 1 | .

Total Statements: 350; Buckets: 16; Bucket Size: 38 ms;

Description

Disables consumers within Performance Schema matching the input pattern.

Parameters

consumer (VARCHAR(128)): A LIKE pattern match (using “%consumer%”) of consumers to disable

Example

To disable all consumers:

mysql> CALL sys.ps_setup_disable_consumer(''); +————————–+ | summary | +————————–+ | Disabled 15 consumers | +————————–+ 1 row in set (0.02 sec)

To disable just the event_stage consumers:

mysql> CALL sys.ps_setup_disable_comsumers(‘stage’); +————————+ | summary | +————————+ | Disabled 3 consumers | +————————+ 1 row in set (0.00 sec)

Description

Returns whether an instrument is enabled by default in this version of MySQL.

Parameters

in_instrument VARCHAR(128): The instrument to check.

Returns

ENUM(‘YES’, ‘NO’)

Example

mysql> SELECT sys.ps_is_instrument_default_enabled(‘statement/sql/select’); +————————————————————–+ | sys.ps_is_instrument_default_enabled(‘statement/sql/select’) | +————————————————————–+ | YES | +————————————————————–+ 1 row in set (0.00 sec)

Description

Create a report of the current status of the server for diagnostics purposes. Data collected includes (some items depends on versions and settings):

• The GLOBAL VARIABLES
• Several sys schema views including metrics or equivalent (depending on version and settings)
• Queries in the 95th percentile
• Several ndbinfo views for MySQL Cluster
• Replication (both master and slave) information.

Some of the sys schema views are calculated as initial (optional), overall, delta:

• The initial view is the content of the view at the start of this procedure. This output will be the same as the the start values used for the delta view. The initial view is included if @sys.diagnostics.include_raw = ‘ON’.
• The overall view is the content of the view at the end of this procedure. This output is the same as the end values used for the delta view. The overall view is always included.
• The delta view is the difference from the beginning to the end. Note that for min and max values they are simply the min or max value from the end view respectively, so does not necessarily reflect the minimum/maximum value in the monitored period. Note: except for the metrics views the delta is only calculation between the first and last outputs.

Requires the SUPER privilege for “SET sql_log_bin = 0;”.

Parameters

in_max_runtime (INT UNSIGNED): The maximum time to keep collecting data. Use NULL to get the default which is 60 seconds, otherwise enter a value greater than 0. in_interval (INT UNSIGNED): How long to sleep between data collections. Use NULL to get the default which is 30 seconds, otherwise enter a value greater than 0. in_auto_config (ENUM(‘current’, ‘medium’, ‘full’)) Automatically enable Performance Schema instruments and consumers. NOTE: The more that are enabled, the more impact on the performance. Supported values are: * current - use the current settings. * medium - enable some settings. This requires the SUPER privilege. * full - enables all settings. This will have a big impact on the performance - be careful using this option. This requires the SUPER privilege. If another setting the ‘current’ is chosen, the current settings are restored at the end of the procedure.

Configuration Options

sys.diagnostics.allow_i_s_tables Specifies whether it is allowed to do table scan queries on information_schema.TABLES. This can be expensive if there are many tables. Set to ‘ON’ to allow, ‘OFF’ to not allow. Default is ‘OFF’.

sys.diagnostics.include_raw Set to ‘ON’ to include the raw data (e.g. the original output of “SELECT * FROM sys.metrics”). Use this to get the initial values of the various views. Default is ‘OFF’.

sys.statement_truncate_len How much of queries in the process list output to include. Default is 64.

sys.debug Whether to provide debugging output. Default is ‘OFF’. Set to ‘ON’ to include.

Example

To create a report and append it to the file diag.out:

mysql> TEE diag.out; mysql> CALL sys.diagnostics(120, 30, ‘current’); … mysql> NOTEE;

Description

Shows all currently disabled instruments.

Parameters

None

Example

mysql> CALL sys.ps_setup_show_disabled_instruments();

Description

Saves the current configuration of Performance Schema, so that you can alter the setup for debugging purposes, but restore it to a previous state.

Use the companion procedure - ps_setup_reload_saved(), to restore the saved config.

The named lock “sys.ps_setup_save” is taken before the current configuration is saved. If the attempt to get the named lock times out, an error occurs.

The lock is released after the settings have been restored by calling ps_setup_reload_saved().

Requires the SUPER privilege for “SET sql_log_bin = 0;”.

Parameters

in_timeout INT The timeout in seconds used when trying to obtain the lock. A negative timeout means infinite timeout.

Example

mysql> CALL sys.ps_setup_save(-1); Query OK, 0 rows affected (0.08 sec)

mysql> UPDATE performance_schema.setup_instruments -> SET enabled = ‘YES’, timed = ‘YES’; Query OK, 547 rows affected (0.40 sec) Rows matched: 784 Changed: 547 Warnings: 0

/* Run some tests that need more detailed instrumentation here */

mysql> CALL sys.ps_setup_reload_saved(); Query OK, 0 rows affected (0.32 sec)

Description

Create a report of the statements running on the server.

The views are calculated based on the overall and/or delta activity.

Requires the SUPER privilege for “SET sql_log_bin = 0;”.

Parameters

in_action (ENUM(‘snapshot’, ‘overall’, ‘delta’, ‘create_tmp’, ‘create_table’, ‘save’, ‘cleanup’)): The action to take. Supported actions are: * snapshot Store a snapshot. The default is to make a snapshot of the current content of performance_schema.events_statements_summary_by_digest, but by setting in_table this can be overwritten to copy the content of the specified table. The snapshot is stored in the sys.tmp_digests temporary table. * overall Generate analyzis based on the content specified by in_table. For the overall analyzis, in_table can be NOW() to use a fresh snapshot. This will overwrite an existing snapshot. Use NULL for in_table to use the existing snapshot. If in_table IS NULL and no snapshot exists, a new will be created. See also in_views and @sys.statement_performance_analyzer.limit. * delta Generate a delta analysis. The delta will be calculated between the reference table in in_table and the snapshot. An existing snapshot must exist. The action uses the sys.tmp_digests_delta temporary table. See also in_views and @sys.statement_performance_analyzer.limit. * create_table Create a regular table suitable for storing the snapshot for later use, e.g. for calculating deltas. * create_tmp Create a temporary table suitable for storing the snapshot for later use, e.g. for calculating deltas. * save Save the snapshot in the table specified by in_table. The table must exists and have the correct structure. If no snapshot exists, a new is created. * cleanup Remove the temporary tables used for the snapshot and delta.

in_table (VARCHAR(129)): The table argument used for some actions. Use the format ‘db1.t1’ or ‘t1’ without using any backticks (`) for quoting. Periods (.) are not supported in the database and table names.

The meaning of the table for each action supporting the argument is:

• snapshot The snapshot is created based on the specified table. Set to NULL or NOW() to use the current content of performance_schema.events_statements_summary_by_digest.
  • overall The table with the content to create the overall analyzis for. The following values can be used: - A table name - use the content of that table. - NOW() - create a fresh snapshot and overwrite the existing snapshot. - NULL - use the last stored snapshot.
  • delta The table name is mandatory and specified the reference view to compare the currently stored snapshot against. If no snapshot exists, a new will be created.
  • create_table The name of the regular table to create.
  • create_tmp The name of the temporary table to create.
  • save The name of the table to save the currently stored snapshot into.

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:

• with_runtimes_in_95th_percentile Based on the sys.statements_with_runtimes_in_95th_percentile view
  • analysis Based on the sys.statement_analysis view
  • with_errors_or_warnings Based on the sys.statements_with_errors_or_warnings view
  • with_full_table_scans Based on the sys.statements_with_full_table_scans view
  • with_sorting Based on the sys.statements_with_sorting view
  • with_temp_tables Based on the sys.statements_with_temp_tables view
  • custom Use a custom view. This view must be specified in @sys.statement_performance_analyzer.view to an existing view or a query

Default is to include all except ‘custom’.

Configuration Options

sys.statement_performance_analyzer.limit The maximum number of rows to include for the views that does not have a built-in limit (e.g. the 95th percentile view). If not set the limit is 100.

sys.statement_performance_analyzer.view Used together with the ‘custom’ view. If the value contains a space, it is considered a query, otherwise it must be an existing view querying the performance_schema.events_statements_summary_by_digest table. There cannot be any limit clause including in the query or view definition if @sys.statement_performance_analyzer.limit > 0. If specifying a view, use the same format as for in_table.

sys.debug Whether to provide debugging output. Default is ‘OFF’. Set to ‘ON’ to include.

Example

To create a report with the queries in the 95th percentile since last truncate of performance_schema.events_statements_summary_by_digest and the delta for a 1 minute period:

1. Create a temporary table to store the initial snapshot.
2. Create the initial snapshot.
3. Save the initial snapshot in the temporary table.
4. Wait one minute.
5. Create a new snapshot.
6. Perform analyzis based on the new snapshot.
7. Perform analyzis 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)

…

To 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 query 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, -> format_pico_time(SUM_TIMER_WAIT) AS total_latency, -> format_pico_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.01 sec)

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

shell$ 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 | +——————-+——-+————+—————+————-+—————+——————-+——————-+———————————-+ …