MySQL sys version 1.5.0 released

MySQL sys version 1.5.0 has just been released.

It has significant contributions from both external contributors (thank you Daniël and Shlomi) and internal (thank you Jesper and Morgan). Jesper particularly made many significant improvements in this release.

Here’s a full summary of the changes:

Improvements

  • The `format_bytes` function now shows no decimal places when outputting a simple bytes value
  • The `processlist`/`x$processlist` views where improved, changes include:
    • The `pid` and `program_name` of the connection are shown, if set within the `performance_schema.session_connect_attrs` table (Contributed by Daniël van Eeden)
    • Issue #50 – The current statement progress is reported via the new stage progress reporting within Performance Schema stages within 5.7 (such as ALTER TABLE progress reporting)
    • Issue #60 – A new `statement_latency` column was added to all versions, which reports the current statement latency with picosecond precision from the `performance_schema.events_statements_current` table, when enabled
    • Some transaction information was exposed, with the `trx_latency` (for the current or last transaction depending on `trx_state`), `trx_state` (ACTIVE, COMMITTED, ROLLED BACK), and `trx_autocommit` (YES/NO) columns
  • A new `metrics` view has been added. On 5.7 this provides a union view of the performance_schema.global_status and information_schema.innodb_metrics tables, along with P_S memory and the current time, as a single metrics output. On 5.6 it provides a union view of the information_schema.global_status and information_schema.innodb_metrics tables, along with the current time. (Contributed by Jesper Wisborg Krogh)
  • New `session`/`x$session` views have been added, which give the same output as the `processlist` view counterparts, but filtered to only show foreground connections (Contributed by Morgan Tocker)
  • A new `session_ssl_status` view was added, which shows the SSL version, ciper and session resuse statistics for each connection (Contributed by Daniël van Eeden)
  • A new `schema_auto_increment_columns` view was added, that shows statistics on each auto_incrment within the instance, including the `auto_increment_ratio`, so you can easily monitor how full specific auto_increment columns are (Contributed by Shlomi Noach)
  • A new `schema_redundant_indexes` view was added, that shows indexes made redundant (or duplicated) by other more dominant indexes. Also includes the the helper view `x$schema_flattened_keys`. (Contributed by Shlomi Noach)
  • New `schema_table_lock_waits`/`x$schema_table_lock_waits` views have been added, which show any sessions that are waiting for table level metadata locks, and the sessions that are blocking them. Resolves Git Issue #57, inspired by the suggestion from Daniël van Eeden
  • The `innodb_lock_waits` view had the following columns added to it, following a manually merged contribution from Shlomi Noach for a similar view
    • `wait_age_secs` – the current row lock wait time in seconds
    • `sql_kill_blocking_query` – the “KILL QUERY <connection id>” command to run to kill the blocking session current statement
    • `sql_kill_blocking_connection` – the “KILL <connection id>” command to run to kill the blocking session
  • A new `table_exists` procedure was added, which checks for the existence of table, and if it exists, returns the type (BASE TABLE, VIEW, TEMPORARY) (Contributed by Jesper Wisborg Krogh)
  • A new `execute_prepared_stmt()` procedure was added, which takes a SQL statement as an input variable and executes it as a prepared statement (Contributed by Jesper Wisborg Krogh)
  • A new `statement_performance_analyzer()` procedure was added, that allows reporting on the statements that are have been running over snapshot periods (Contributed by Jesper Wisborg Krogh)
  • A new `diagnostics()` procedure was added, which creates a large diagnostics report based upon most of the new instrumentation now available, computed over a configurable number of snapshot intervals (Contributed by Jesper Wisborg Krogh)
  • A 5.7 specific `ps_trace_thread()` procedure was added, which now shows the hierarchy of transactions and stored routines, as well as statements, stages and waits, if enabled
  • Added a new `ps_thread_account()` stored function, that returns the “user@host” account for a given Performance Schema thread id
  • Added a new `ps_thread_trx_info()` stored function which outputs, for a given thread id, the transactions, and statements that those transactions have executed, as a JSON object
  • Added new `list_add()` and `list_drop()` stored functions, that take a string csv list, and either add or remove items from that list respectively. Can be used to easily update variables that take such lists, like `sql_mode`.
  • The `ps_thread_id` stored function now returns the thread id for the current connection if NULL is passed for the in_connection_id parameter
  • Added a new `version_major()` stored function, which returns the major version of MySQL Server (Contributed by Jesper Wisborg Krogh)
  • Added a new `version_minor()` stored function, which returns the minor (release series) version of MySQL Server (Contributed by Jesper Wisborg Krogh)
  • Added a new `version_patch()` stored function, which returns the patch release version of MySQL Server (Contributed by Jesper Wisborg Krogh)
  • The `ps_is_account_enabled` function was updated to take a VARCHAR(32) user input on 5.7, as a part of WL#2284
  • The generate_sql_file.sh script had a number of improvements:
    • Generated files are now output in to a “gen” directory, that is ignored by git
    • Added using a new default “mysql.sys@localhost” user (that has the account locked) for the MySQL 5.7+ integration as the DEFINER for all objects
    • Added a warning to the top of the generated integration file to also submit changes to the sys project
    • Improved the the option of skipping binary logs, so that all routines can load as well – those that used SET sql_log_bin will now select a warning when being used instead of setting the option

Bug Fixes

  • Git Issue #51 – Fixed the `generate_sql_file.sh` script to also replace the definer in the before_setup.sql output
  • Git Issue #52 – Removed apostrophe from the `format_statement` function comment because TOAD no likey
  • Git Issue #56 – Installation failed on 5.6 with ONLY_FULL_GROUP_BY enabled
  • Git Issue #76 – Fixes for the new show_compatibility_56 variable. 5.7 versions of the `format_path()` function and `ps_check_lost_instrumentation` view were added, that use performance_schema.global_status/global_variables instead of information_schema.global_status/global_variables
  • Git Issue #79 – Fixed grammar within `statements_with_runtimes_in_95th_percentile` view descriptions
  • Oracle Bug #21484593 / Oracle Bug #21281955 – The `format_path()` function incorrectly took and returned a VARCHAR(260) instead of VARCHAR(512) (as the underlying is exposed as in Performance Schema) causing sporadic test failures
  • Oracle Bug #21550271 – Fixed the `ps_setup_reset_to_default` for 5.7 with the addition of the new `history` column on the `performance_schema.setup_actors` table
  • Oracle Bug #21550054 – It is possible that the views can show data that overflows when aggregating very large values, reset all statistics before each test to ensure no overflows
  • Oracle Bug #21647101 – Fixed the `ps_is_instrument_default_enabed` and `ps_is_instrument_default_timed` to take in to account the new instruments added within 5.7
  • MySQL Bug #77848 – Added the missing ps_setup_instruments_cleanup.inc
  • Fixed the `ps_setup_reset_to_default()` procedure to also set the new `ENABLED` column within `performance_schema.setup_actors` within 5.7
  • The `user_summary_by_file_io`/`x$user_summary_by_file_io` and `host_summary_by_file_io`/`x$host_summary_by_file_io` tables were incorrectly aggregating all wait events, not just `wait/io/file/%`

Implementation Details

  • Tests were improved via 5.7 integration
  • Template files were added for stored procedures and functions
  • Improved the sys_config_cleanup.inc procedure in tests to be able to reset the sys_config table completely (including the set_by column to NULL). The triggers can now be set to not update the column by setting the @sys.ignore_sys_config_triggers user variable to true

Quickly tell who is writing to a MySQL replica

Many of us have been there in the past, you get an alert telling you that replication has stopped because of an error, you dig in to it to find that you’re getting an error for an update event that is trying to update a non-existent row, or a duplicate key error because the row ID for some INSERT already exists.

Even with the server set to read only (and not using the new super_read_only variable from MySQL 5.7.8), these problems can still happen – how many of you have seen over-zealous ops trying to “quickly fix” some problem only to royally screw up your data integrity?

The question then becomes – “who or what is making changes on my replica that shouldn’t be?!?”.

The only way to find this out in the past, and still “the conventional wisdom” (I just saw it recommended in a discussion in the MySQL support group) was to turn on the binary log on the replica, make sure log_slave_updates is not enabled, and then see what events turn up in the binary log.

But if you are using 5.6, you can already get an idea of who is executing what kinds of DML statements very easily, with the default statement instrumentation in Performance Schema, via the performance_schema.events_statements_summary_by_user_by_event_name and performance_schema.events_statements_summary_by_host_by_event_name tables. These have a structure like this:

mysql> desc performance_schema.events_statements_summary_by_user_by_event_name;
+-----------------------------+---------------------+------+-----+---------+-------+
| Field                       | Type                | Null | Key | Default | Extra |
+-----------------------------+---------------------+------+-----+---------+-------+
| USER                        | char(16)            | YES  |     | NULL    |       |
| EVENT_NAME                  | varchar(128)        | NO   |     | NULL    |       |
| COUNT_STAR                  | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_TIMER_WAIT              | bigint(20) unsigned | NO   |     | NULL    |       |
| MIN_TIMER_WAIT              | bigint(20) unsigned | NO   |     | NULL    |       |
| AVG_TIMER_WAIT              | bigint(20) unsigned | NO   |     | NULL    |       |
| MAX_TIMER_WAIT              | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_LOCK_TIME               | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_ERRORS                  | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_WARNINGS                | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_ROWS_AFFECTED           | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_ROWS_SENT               | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_ROWS_EXAMINED           | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_CREATED_TMP_DISK_TABLES | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_CREATED_TMP_TABLES      | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_SELECT_FULL_JOIN        | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_SELECT_FULL_RANGE_JOIN  | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_SELECT_RANGE            | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_SELECT_RANGE_CHECK      | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_SELECT_SCAN             | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_SORT_MERGE_PASSES       | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_SORT_RANGE              | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_SORT_ROWS               | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_SORT_SCAN               | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_NO_INDEX_USED           | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_NO_GOOD_INDEX_USED      | bigint(20) unsigned | NO   |     | NULL    |       |
+-----------------------------+---------------------+------+-----+---------+-------+

The data within them is like the merged output of “SHOW GLOBAL STATUS LIKE ‘Com_%'” (only by user here for example, or by host/account in the other base summary views), with the regular performance schema statement data (latency, lock time, result info, sorting info, temporary table info etc.).

So you can get an idea of the query profile of users:

mysql> select user, event_name, count_star, sum_timer_wait, sum_rows_affected
    ->   from performance_schema.events_statements_summary_by_user_by_event_name
    ->  where sum_timer_wait > 0
    ->  order by user, sum_timer_wait desc;
+------+----------------------------------+------------+-------------------+-------------------+
| user | event_name                       | count_star | sum_timer_wait    | sum_rows_affected |
+------+----------------------------------+------------+-------------------+-------------------+
| root | statement/sql/insert             |    4654750 | 10889839312962492 |           6147833 |
| root | statement/sql/select             |    1763971 |  2685440113284300 |                16 |
| root | statement/sql/commit             |    3549022 |  2076707580306888 |                 0 |
| root | statement/sql/update             |     692793 |   429644976873960 |            692961 |
| root | statement/sql/alter_table        |       2294 |   349258266736668 |                 0 |
| root | statement/sql/set_option         |    3638143 |   175255961793804 |                 0 |
| root | statement/sql/truncate           |       4416 |   169644392962740 |                 0 |
| root | statement/sql/create_table       |       2042 |   119925607500528 |                 0 |
| root | statement/sql/show_fields        |      15795 |   119878604121228 |                 0 |
| root | statement/sql/delete             |     699378 |   116732970789132 |             40298 |
| root | statement/com/Init DB            |     218258 |    90260554011048 |                 0 |
| root | statement/sql/call_procedure     |          5 |    84983311882308 |                 0 |
| root | statement/sql/show_binlogs       |       5832 |    73956599513124 |                 0 |
| root | statement/sql/create_index       |       1530 |    53709192098256 |                 0 |
| root | statement/sql/show_tables        |      37924 |    45532153498296 |                 0 |
| root | statement/sql/show_warnings      |     892204 |    40929165782628 |                 0 |
| root | statement/sql/rollback           |     224391 |    30362071807164 |                 0 |
| root | statement/sql/show_variables     |      30521 |    25090556161620 |                 0 |
| root | statement/sql/show_status        |      17403 |    12531077415552 |                 0 |
| root | statement/com/Ping               |     358412 |    10763876159712 |                 0 |
| root | statement/sql/show_engine_status |       6964 |     9874889717568 |                 0 |
| root | statement/sql/show_keys          |      15739 |     6773811516912 |                 0 |
| root | statement/sql/show_create_table  |      34677 |     4967611443876 |                 0 |
| root | statement/sql/drop_db            |         78 |     3803154575244 |               289 |
| root | statement/com/Quit               |     566175 |     2860317274356 |                 0 |
| root | statement/sql/show_slave_status  |      11592 |     2394918433356 |                 0 |
| root | statement/sql/show_databases     |       1187 |     1722377297184 |                 0 |
| root | statement/sql/delete_multi       |       5737 |      946103948844 |                10 |
| root | statement/sql/show_master_status |       6961 |      861468069984 |                 0 |
| root | statement/sql/execute_sql        |          4 |      649259110140 |                 0 |
| root | statement/sql/create_db          |       1139 |      558651095628 |              1139 |
| root | statement/sql/change_db          |        363 |       49894596144 |                 0 |
| root | statement/sql/prepare_sql        |          4 |       31004518200 |                 0 |
| root | statement/sql/error              |        363 |       28835029596 |                 0 |
| root | statement/sql/create_procedure   |          4 |       27429645936 |                 0 |
| root | statement/sql/show_collations    |         47 |       24375874704 |                 0 |
| root | statement/sql/drop_event         |         77 |       12535227684 |                 0 |
| root | statement/sql/show_processlist   |         42 |        8276037864 |                 0 |
| root | statement/com/Set option         |        134 |        6454170660 |                 0 |
| root | statement/sql/drop_procedure     |          4 |        1955618676 |                 0 |
| root | statement/sql/create_function    |          1 |         329743728 |                 0 |
| root | statement/sql/dealloc_sql        |          4 |         304913628 |                 0 |
| root | statement/sql/empty_query        |          7 |         214863132 |                 0 |
| root | statement/sql/drop_function      |          1 |         137393220 |                 0 |
+------+----------------------------------+------------+-------------------+-------------------+
44 rows in set (0.00 sec)

This data is what forms the basis for the sys.user_summary_by_statement_type view:

mysql> select * from sys.user_summary_by_statement_type;
+------+--------------------+---------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| user | statement          | total   | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans |
+------+--------------------+---------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| root | insert             | 4656002 | 3.03 h        | 2.31 s      | 16.27 m      |         0 |             0 |       6149526 |          0 |
| root | select             | 1764359 | 44.77 m       | 25.23 s     | 9.03 m       |   4496820 |     132814197 |            16 |     395133 |
| root | commit             | 3549836 | 34.62 m       | 11.31 s     | 0 ps         |         0 |             0 |             0 |          0 |
| root | update             |  693048 | 7.16 m        | 1.19 s      | 39.14 s      |         0 |        727567 |        693216 |          0 |
| root | alter_table        |    2294 | 5.82 m        | 3.26 s      | 47.69 s      |         0 |             0 |             0 |          0 |
| root | set_option         | 3638446 | 2.92 m        | 56.50 ms    | 0 ps         |         0 |             0 |             0 |          0 |
| root | truncate           |    4416 | 2.83 m        | 727.25 ms   | 5.07 s       |         0 |             0 |             0 |          0 |
| root | create_table       |    2042 | 2.00 m        | 1.17 s      | 0 ps         |         0 |             0 |             0 |          0 |
| root | show_fields        |   15795 | 2.00 m        | 556.60 ms   | 1.55 m       |    172209 |        172209 |             0 |      15795 |
| root | delete             |  699378 | 1.95 m        | 10.39 s     | 1.03 m       |         0 |        207604 |         40298 |          0 |
| root | Init DB            |  218291 | 1.50 m        | 116.22 ms   | 0 ps         |         0 |             0 |             0 |          0 |
| root | call_procedure     |       5 | 1.42 m        | 38.21 s     | 58.00 ms     |         0 |             0 |             0 |          0 |
| root | show_binlogs       |    5834 | 1.23 m        | 3.81 s      | 0 ps         |         0 |             0 |             0 |          0 |
| root | create_index       |    1530 | 53.71 s       | 811.27 ms   | 20.99 s      |         0 |             0 |             0 |          0 |
| root | show_tables        |   37925 | 45.53 s       | 626.26 ms   | 1.55 s       |     40239 |         40239 |             0 |      37925 |
| root | show_warnings      |  892204 | 40.93 s       | 97.00 ms    | 0 ps         |         0 |             0 |             0 |          0 |
| root | rollback           |  224427 | 30.36 s       | 1.32 s      | 0 ps         |         0 |             0 |             0 |          0 |
| root | show_variables     |   30527 | 25.09 s       | 43.79 ms    | 1.49 s       |   5712888 |       5712888 |             0 |      30527 |
| root | show_status        |   17406 | 12.53 s       | 57.92 ms    | 837.06 ms    |   3966858 |       3966858 |             0 |      17406 |
| root | Ping               |  358451 | 10.77 s       | 3.01 ms     | 0 ps         |         0 |             0 |             0 |          0 |
| root | show_engine_status |    6966 | 9.88 s        | 106.50 ms   | 0 ps         |         0 |             0 |             0 |          0 |
| root | show_keys          |   15739 | 6.77 s        | 95.82 ms    | 1.14 s       |     52285 |         52285 |             0 |      15739 |
| root | show_create_table  |   34677 | 4.97 s        | 78.85 ms    | 0 ps         |         0 |             0 |             0 |          0 |
| root | drop_db            |      78 | 3.80 s        | 1.88 s      | 3.71 s       |         0 |             0 |           289 |          0 |
| root | Quit               |  566250 | 2.86 s        | 2.25 ms     | 0 ps         |         0 |             0 |             0 |          0 |
| root | show_slave_status  |   11594 | 2.40 s        | 32.97 ms    | 0 ps         |         0 |             0 |             0 |          0 |
| root | show_databases     |    1188 | 1.72 s        | 6.25 ms     | 50.00 ms     |      1488 |          1488 |             0 |       1188 |
| root | delete_multi       |    5737 | 946.10 ms     | 39.19 ms    | 313.02 ms    |         0 |           160 |            10 |       1444 |
| root | show_master_status |    6963 | 861.56 ms     | 22.81 ms    | 0 ps         |         0 |             0 |             0 |          0 |
| root | execute_sql        |       4 | 649.26 ms     | 184.78 ms   | 0 ps         |         0 |             0 |             0 |          0 |
| root | create_db          |    1139 | 558.65 ms     | 22.92 ms    | 0 ps         |         0 |             0 |          1139 |          0 |
| root | change_db          |     363 | 49.89 ms      | 692.26 us   | 0 ps         |         0 |             0 |             0 |          0 |
| root | prepare_sql        |       4 | 31.00 ms      | 29.86 ms    | 0 ps         |         0 |             0 |             0 |          0 |
| root | error              |     364 | 28.92 ms      | 347.29 us   | 0 ps         |         0 |             0 |             0 |          0 |
| root | create_procedure   |       4 | 27.43 ms      | 22.09 ms    | 10.00 ms     |         0 |             0 |             0 |          0 |
| root | show_collations    |      47 | 24.38 ms      | 1.37 ms     | 2.00 ms      |     10293 |         10293 |             0 |         47 |
| root | drop_event         |      77 | 12.54 ms      | 1.01 ms     | 4.00 ms      |         0 |             0 |             0 |          0 |
| root | show_processlist   |      42 | 8.28 ms       | 5.87 ms     | 0 ps         |         0 |             0 |             0 |          0 |
| root | Set option         |     134 | 6.45 ms       | 1.52 ms     | 0 ps         |         0 |             0 |             0 |          0 |
| root | drop_procedure     |       4 | 1.96 ms       | 916.73 us   | 1.00 ms      |         0 |             0 |             0 |          0 |
| root | create_function    |       1 | 329.74 us     | 329.74 us   | 0 ps         |         0 |             0 |             0 |          0 |
| root | dealloc_sql        |       4 | 304.91 us     | 87.73 us    | 0 ps         |         0 |             0 |             0 |          0 |
| root | empty_query        |       7 | 214.86 us     | 43.04 us    | 0 ps         |         0 |             0 |             0 |          0 |
| root | drop_function      |       1 | 137.39 us     | 137.39 us   | 1.00 ms      |         0 |             0 |             0 |          0 |
+------+--------------------+---------+---------------+-------------+--------------+-----------+---------------+---------------+------------+

The replication SQL thread statement activity is also recorded within these tables. Within the base performance schema tables, these will have a user / host of NULL, and in the sys schema these are translated to a generic “background” user (an example from my local sandbox topology where one table was created with one row via replication, shown in the “background” user, and the msandbox user has created newly the sys schema):

slave1 [localhost] {msandbox} (sys) > select * from user_summary_by_statement_type;
+------------+-------------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| user       | statement         | total | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans |
+------------+-------------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| background | create_table      |     1 | 712.32 ms     | 712.32 ms   | 0 ps         |         0 |             0 |             0 |          0 |
| background | insert            |     1 | 546.87 us     | 546.87 us   | 444.00 us    |         0 |             0 |             1 |          0 |
| background | begin             |     1 | 26.83 us      | 26.83 us    | 0 ps         |         0 |             0 |             0 |          0 |
| msandbox   | create_view       |    82 | 13.05 s       | 277.68 ms   | 25.59 ms     |         0 |             0 |             0 |          0 |
| msandbox   | create_trigger    |     2 | 738.17 ms     | 410.11 ms   | 837.00 us    |         0 |             0 |             0 |          0 |
| msandbox   | create_table      |     1 | 315.86 ms     | 315.86 ms   | 0 ps         |         0 |             0 |             0 |          0 |
| msandbox   | Field List        |   135 | 262.68 ms     | 67.72 ms    | 24.98 ms     |         0 |             0 |             0 |          0 |
| msandbox   | select            |    17 | 183.08 ms     | 78.50 ms    | 59.71 ms     |       406 |          2371 |             0 |          7 |
| msandbox   | show_databases    |     5 | 173.19 ms     | 125.45 ms   | 1.53 ms      |        21 |            21 |             0 |          5 |
| msandbox   | drop_function     |    14 | 66.93 ms      | 66.00 ms    | 66.24 ms     |         0 |             0 |             0 |          0 |
| msandbox   | insert            |     2 | 66.18 ms      | 65.09 ms    | 574.00 us    |         0 |             0 |             6 |          0 |
| msandbox   | show_tables       |     2 | 47.10 ms      | 46.69 ms    | 123.00 us    |       135 |           135 |             0 |          2 |
| msandbox   | Init DB           |     3 | 46.19 ms      | 46.09 ms    | 0 ps         |         0 |             0 |             0 |          0 |
| msandbox   | show_create_table |     1 | 36.10 ms      | 36.10 ms    | 0 ps         |         0 |             0 |             0 |          0 |
| msandbox   | show_slave_status |     2 | 34.82 ms      | 34.69 ms    | 0 ps         |         0 |             0 |             0 |          0 |
| msandbox   | create_procedure  |    26 | 24.32 ms      | 6.57 ms     | 18.21 ms     |         0 |             0 |             0 |          0 |
| msandbox   | create_function   |    14 | 5.46 ms       | 965.16 us   | 3.38 ms      |         0 |             0 |             0 |          0 |
| msandbox   | drop_procedure    |    26 | 2.04 ms       | 155.18 us   | 838.00 us    |         0 |             0 |             0 |          0 |
| msandbox   | create_db         |     1 | 733.32 us     | 733.32 us   | 0 ps         |         0 |             0 |             1 |          0 |
| msandbox   | set_option        |     6 | 638.51 us     | 307.45 us   | 0 ps         |         0 |             0 |             0 |          0 |
| msandbox   | drop_trigger      |     2 | 164.66 us     | 93.56 us    | 0 ps         |         0 |             0 |             0 |          0 |
| msandbox   | Quit              |     3 | 94.20 us      | 69.39 us    | 0 ps         |         0 |             0 |             0 |          0 |
+------------+-------------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
22 rows in set (0.00 sec)

So the quick answer to the question “who is currently changing data on my replica, that is not the replication SQL thread, and how?” is:

slave1 [localhost] {msandbox} (sys) > SELECT * FROM sys.user_summary_by_statement_type WHERE user != 'background' AND rows_affected > 0;
+----------+-----------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| user     | statement | total | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans |
+----------+-----------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| msandbox | insert    |     2 | 66.18 ms      | 65.09 ms    | 574.00 us    |         0 |             0 |             6 |          0 |
| msandbox | create_db |     1 | 733.32 us     | 733.32 us   | 0 ps         |         0 |             0 |             1 |          0 |
+----------+-----------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
2 rows in set (0.00 sec)

Or direct from Performance Schema:

slave1 [localhost] {msandbox} (sys) > SELECT * FROM performance_schema.events_statements_summary_by_user_by_event_name WHERE user IS NOT NULL AND sum_rows_affected > 0\G
*************************** 1. row ***************************
                       USER: msandbox
                 EVENT_NAME: statement/sql/insert
                 COUNT_STAR: 2
             SUM_TIMER_WAIT: 66179499000
             MIN_TIMER_WAIT: 1090899000
             AVG_TIMER_WAIT: 33089749000
             MAX_TIMER_WAIT: 65088600000
              SUM_LOCK_TIME: 574000000
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 6
              SUM_ROWS_SENT: 0
          SUM_ROWS_EXAMINED: 0
SUM_CREATED_TMP_DISK_TABLES: 0
     SUM_CREATED_TMP_TABLES: 0
       SUM_SELECT_FULL_JOIN: 0
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 0
     SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 0
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 0
     SUM_NO_GOOD_INDEX_USED: 0
*************************** 2. row ***************************
                       USER: msandbox
                 EVENT_NAME: statement/sql/create_db
                 COUNT_STAR: 1
             SUM_TIMER_WAIT: 733319000
             MIN_TIMER_WAIT: 733319000
             AVG_TIMER_WAIT: 733319000
             MAX_TIMER_WAIT: 733319000
              SUM_LOCK_TIME: 0
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 1
              SUM_ROWS_SENT: 0
          SUM_ROWS_EXAMINED: 0
SUM_CREATED_TMP_DISK_TABLES: 0
     SUM_CREATED_TMP_TABLES: 0
       SUM_SELECT_FULL_JOIN: 0
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 0
     SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 0
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 0
     SUM_NO_GOOD_INDEX_USED: 0
2 rows in set (0.00 sec)

You can similarly do this by host, by replacing “user” with “host” in the above view names, if you are trying to track down which host is causing issues, too.

MySQL sys version 1.4.0 released

MySQL sys version 1.4.0 has just been released.

Here’s a full summary of the changes:

Backwards Incompatible Changes

  • The `memory_global_by_current_allocated` views were renamed to `memory_global_by_current_bytes` for consistency with the other memory views
  • The `ps_setup_enable_consumers` procedure was renamed to `ps_setup_disable_consumer` for naming consistency (everything is now singular, not plural)
  • The `format_time` function displayed values in minutes incorrectly, it now rounds to minutes, and uses an ‘m’ suffix, like the rest of the units

Improvements

  • The beginnings of a mysql-test suite have been added
  • The `innodb_lock_waits`/`x$innodb_lock_waits` views were improved (Contributions by both Jesper Wisborg Krogh and Mark Matthews)
    • Added the `wait_started`, `wait_age`, `waiting_trx_started` `waiting_trx_age`, `waiting_trx_rows_locked` and `waiting_trx_rows_modified` columns for waiting transactions
    • Added the `blocking_trx_started`, `blocking_trx_age`, `blocking_trx_rows_locked` and `blocking_trx_rows_modified` for blocking transaction
    • Order the result set so the oldest lock waits are first
    • The `waiting_table` and `waiting_index` were always the same as the `blocking_table` and `blocking_index`. So the blocking_% columns have been removed and the waiting_% columns have been renamed to locked_%
    • The `waiting_lock_type` and `blocking_lock_type` were also always the same. So these were removed and replaced with a single `locked_type` column
    • Renamed the `waiting_thread` and `blocking_thread` to `waiting_pid` and `blocking_pid` respectively to avoid confusion with the threads from the Performance Schema.
  • Added the `sys_get_config` function, used to get configuration parameters from the `sys_config` table – primarily from other sys objects, but can be used individually (Contributed by Jesper Wisborg Krogh)
  • Add an option to generate_sql_file.sh to generate a mysql_install_db / mysqld –initialize format friendly file
  • Added the `ps_is_thread_instrumented` function, to check whether a specified thread is instrumented within Performance Schema
  • Added the `ps_is_consumer_enabled` function, to check whether a specified consumer is enabled within Performance Schema (Contributed by Jesper Wisborg Krogh)
  • Added some further replacements to the `format_path` function (`slave_load_tmpdir`, `innodb_data_home_dir`, `innodb_log_group_home_dir` and `innodb_undo_directory`)

Bug Fixes

  • The 5.6 `host_summary` and `x$host_summary` views incorrectly had the column with `COUNT(DISTINCT accounts.user)` named `unique_hosts` instead of `unique_users` (Contributed by Jesper Wisborg Krogh)
  • Both the `format_time` and `format_bytes` took a BIGINT as input, and output VARCHAR, but BIGINT could be too small for aggregated values for the inputs. Now both functions both use TEXT as their input (Issue #34, Issue #38)
  • The `format_time` function displayed values in minutes incorrectly, it now rounds to minutes, and uses an ‘m’ suffix, like the rest of the units
  • The `sys_config` related triggers had no DEFINER clause set
  • The `ps_setup_disable_thread` procedure always disabled the current thread and was ignoring the connection id given as an argument (Contributed by Jesper Wisborg Krogh)
  • The `ps_trace_thread` procedure had an incorrect calculation of how long the procedure has been running (Contributed by Jesper Wisborg Krogh)

Implementation Details

Various changes were made to allow better generation of integration sql files:

  • The formatting for all comments has been standardized on — line comments. C-style /* comments */ have been removed
    • Issue #35 had one instance of this resolved in this release (contributed by Joe Grasse), but the entire code base has now been done
  • Each object has been created within it’s own file. No longer do x$ views live with their non-x$ counterparts
  • DELIMITERs were standardized to $$

Getting connection information with MySQL 5.7

MySQL 5.7 has had some great improvements within Performance Schema to be able to better trace what connections are doing, from adding memory information, through to transaction information, metadata locking, prepared statements, and even user variables, so far (there is still more to come in the next release – stay tuned).

Of course there are other improvements on top of this as well, such as the new replication tables, but I won’t go in to that here. Today I want to focus on the kind of data you can now get in MySQL 5.7 for debugging what a foreground user connection has been doing.

I’ve written before about how Performance Schema allows you to create a much better alternative to SHOW FULL PROCESSLIST, however that solution was never really satisfying to me. It’s fine for a high level overview of the connections to the database, but there was so much more information available within Performance Schema, even in MySQL 5.6, that can be exposed on a per thread basis.

The problem is that a lot of that extra data that I wanted exposed is really of a structured form, such as the last N statements (or in 5.7, last N transactions even) that a connection has executed, or a summary of wait information, for example. Everybody loves JSON now’a’days, so that was the obvious choice to me – I wanted a JSON object, per thread, of the available data from Performance Schema.

And so started the writing of a query from hell. But the output is, if I may say so myself, a thing of beauty.

Lets whet that appetite a little (and you may need to scroll to the right):

thread_info:
{
  "user": "root@localhost",
  "thd_id": 106950,
  "connection_id": 106927,
  "database": "memtrunkio",
  "current_statement": {
    "time": 1,
    "rows_examined": 0,
    "rows_affected": 0,
    "rows_sent": 0,
    "tmp_tables": 0,
    "tmp_disk_tables": 0,
    "sort_rows": 0,
    "sort_merge_passes": 0,
    "text": "/* mem dbpool.default */ insert into `memtrunkio__quan`.`normalized_statements_by_server_by_schema` (firstSeen, lastSeen, normalized_statement_id, `schema`, server, id) values (1424379489000, 1424430252000, x'083ADE6E
3A814355A823472B787CF079', 'memtrunkio', 'cd6fe2ae-a6ce-11e4-87eb-180373e22685', x'22D1E8D4ECDB30848B8C39456204E29F')  ON DUPLICATE KEY UPDATE
    firstSeen = COALESCE( LEAST(firstSeen, VALUES(firstSeen)), firstSeen),
    lastSeen = COALESCE( GREATEST(lastSeen, VALUES(lastSeen)), lastSeen)"
  },
  "last_transactions": [
    {
      "time": "1.86 s",
      "state": "COMMITTED",
      "mode": "READ WRITE",
      "autocommitted": "NO",
      "gtid": "",
      "isolation": "REPEATABLE READ",
      "statements": [
        {
          "time": "1.37 s",
          "schema": "memtrunkio",
          "rows_examined": 0,
          "rows_affected": 1,
          "rows_sent": 0,
          "tmp_tables": 0,
          "tmp_disk_tables": 0,
          "sort_rows": 0,
          "sort_merge_passes": 0,
          "statement": "/* mem dbpool.default */ insert into `memtrunkio__quan`.`normalized_statements_by_server_by_schema_data` (bytesMax, bytesMin, bytesTotal, collectionDuration, createdTmpDiskTables, createdTmpTables, errorCou
nt, execCount, execTimeMax, execTimeMin, execTimeTotal, lockTimeTotal, noGoodIndexUsedCount, noIndexUsedCount, rowsExaminedTotal, rowsMax, rowsMin, rowsTotal, selectFullJoin, selectFullRangeJoin, selectRange, selectRangeCheck, sel
ectScan, sortMergePasses, sortRange, sortRows, sortScan, timestamp, warningCount, round_robin_bin, normalized_statement_by_server_by_schema_id) values (null, null, null, 120000, 0, 4, 0, 4, 2858, 801, 4231, 1000, 0, 4, 1856, null,
 null, 1856, 0, 0, 0, 0, 4, 0, 0, 0, 0, 1424430258000, 0, 32344, x'CDD64BD9418A30C0B310095788B9DD41') ON DUPLICATE KEY UPDATE bytesMax = IF(VALUES(`timestamp`) >= `timestamp`, VALUES(bytesMax), bytesMax), bytesMin = IF(VALUES(`tim
estamp`) >= `timestamp`, VALUES(bytesMin), bytesMin), bytesTotal = IF(VALUES(`timestamp`) >= `timestamp`, VALUES(bytesTotal), by"
        },
        {
          "time": "269.82 us",
          "schema": "memtrunkio",
          "rows_examined": 0,
          "rows_affected": 1,
          "rows_sent": 0,
          "tmp_tables": 0,
          "tmp_disk_tables": 0,
          "sort_rows": 0,
          "sort_merge_passes": 0,
          "statement": "/* mem dbpool.default */ insert into `memtrunkio__quan`.`example_statements` (bytes, comments, connectionId, errors, execTime, hostFrom, hostTo, noGoodIndexUsed, noIndexUsed, rows, source_location_id, `text
`, timestamp, user, warnings, round_robin_bin, normalized_statement_by_server_by_schema_id) values (null, null, 13949, 0, 1178, 'localhost', null, 0, 1, 464, null, 'SHOW /*!50000 GLOBAL */ VARIABLES', 1424430227189, 'root', 0, 323
43, x'CDD64BD9418A30C0B310095788B9DD41')  ON DUPLICATE KEY UPDATE bytes = IF(VALUES(`timestamp`) >= `timestamp`, VALUES(bytes), bytes), comments = IF(VALUES(`timestamp`) >= `timestamp`, VALUES(comments), comments), connectionId =
IF(VALUES(`timestamp`) >= `timestamp`, VALUES(connectionId), connectionId), errors = IF(VALUES(`timestamp`) >= `timestamp`, VALUES(errors), errors), execTime = IF(VALUES(`timestamp`) >= `timestamp`, VALUES(execTime), execTime), ho
stFrom = IF(VALUES(`timestamp`) >= `timestamp`, VALUES(hostFrom), hostFrom), hostTo = IF(VALUES(`timestamp`) >= `timestamp`, VAL"
        },
        {
          "time": "42.07 ms",
          "schema": "memtrunkio",
          "rows_examined": 0,
          "rows_affected": 0,
          "rows_sent": 0,
          "tmp_tables": 0,
          "tmp_disk_tables": 0,
          "sort_rows": 0,
          "sort_merge_passes": 0,
          "statement": "/* mem dbpool.default */ commit"
        }
      ]
    }
  ],
  "metadata_locks": [
    { "lock_type": "SHARED_WRITE", "lock_duration": "TRANSACTION", "lock_status": "GRANTED", "scope": "memtrunkio__quan.normalized_statements_by_server_by_schema" },
    { "lock_type": "INTENTION_EXCLUSIVE", "lock_duration": "STATEMENT", "lock_status": "GRANTED", "scope": "*.*" }
  ],
  "table_handles": [
    { "schema": "memtrunkio__quan", "table": "normalized_statements_by_server_by_schema" }
  ],
  "memory_summary": {
    "total_memory": "70.30 KiB",
    "memory_details": [
      { "type": "sql/thd::main_mem_root", "count": 3, "allocated": "23.95 KiB", "total_allocated": "2.20 MiB", "high_allocated": "175.36 KiB" },
      { "type": "sql/NET::buff", "count": 1, "allocated": "16.01 KiB", "total_allocated": "16.01 KiB", "high_allocated": "16.01 KiB" },
      { "type": "sql/String::value", "count": 1, "allocated": "16.00 KiB", "total_allocated": "32.01 KiB", "high_allocated": "32.01 KiB" },
      { "type": "mysys/array_buffer", "count": 6, "allocated": "8.92 KiB", "total_allocated": "9.05 KiB", "high_allocated": "8.92 KiB" },
      { "type": "sql/THD::transactions::mem_root", "count": 1, "allocated": "4.02 KiB", "total_allocated": "4.02 KiB", "high_allocated": "4.02 KiB" },
      { "type": "vio/vio", "count": 1, "allocated": "608 bytes", "total_allocated": "608 bytes", "high_allocated": "608 bytes" },
      { "type": "innodb/mem0mem", "count": 1, "allocated": "232 bytes", "total_allocated": "4.29 MiB", "high_allocated": "25.69 KiB" },
      { "type": "sql/THD::Session_tracker", "count": 6, "allocated": "209 bytes", "total_allocated": "209 bytes", "high_allocated": "209 bytes" },
      { "type": "innodb/std", "count": 6, "allocated": "208 bytes", "total_allocated": "38.63 KiB", "high_allocated": "592 bytes" },
      { "type": "sql/THD::variables", "count": 1, "allocated": "64 bytes", "total_allocated": "64 bytes", "high_allocated": "64 bytes" },
      { "type": "innodb/ha_innodb", "count": 1, "allocated": "48 bytes", "total_allocated": "48 bytes", "high_allocated": "48 bytes" },
      { "type": "sql/MYSQL_LOCK", "count": 1, "allocated": "32 bytes", "total_allocated": "800 bytes", "high_allocated": "32 bytes" },
      { "type": "sql/THD::db", "count": 1, "allocated": "11 bytes", "total_allocated": "11 bytes", "high_allocated": "11 bytes" },
      { "type": "sql/Security_context", "count": 1, "allocated": "10 bytes", "total_allocated": "10 bytes", "high_allocated": "10 bytes" },
      { "type": "sql/THD::Session_sysvar_resource_manager", "count": 1, "allocated": "8 bytes", "total_allocated": "8 bytes", "high_allocated": "8 bytes" },
      { "type": "sql/MPVIO_EXT::auth_info", "count": 1, "allocated": "5 bytes", "total_allocated": "5 bytes", "high_allocated": "5 bytes" },
      { "type": "innodb/os0event", "count": 0, "allocated": "0 bytes", "total_allocated": "34.23 KiB", "high_allocated": "112 bytes" },
      { "type": "innodb/dict0mem", "count": 0, "allocated": "0 bytes", "total_allocated": "8.25 KiB", "high_allocated": "27 bytes" },
      { "type": "innodb/btr0sea", "count": 0, "allocated": "0 bytes", "total_allocated": "1.95 KiB", "high_allocated": "1.58 KiB" },
      { "type": "innodb/btr0pcur", "count": 0, "allocated": "0 bytes", "total_allocated": "281 bytes", "high_allocated": "0 bytes" }
    ]
  },
  "stage_summary": [
    { "stage": "update", "count": 24, "total_time": "6.14 s", "max_time": "2.17 s" },
    { "stage": "starting", "count": 36, "total_time": "100.37 ms", "max_time": "41.97 ms" },
    { "stage": "freeing items", "count": 34, "total_time": "4.76 ms", "max_time": "667.76 us" },
    { "stage": "init", "count": 25, "total_time": "1.27 ms", "max_time": "108.59 us" },
    { "stage": "Opening tables", "count": 29, "total_time": "269.16 us", "max_time": "24.17 us" },
    { "stage": "closing tables", "count": 34, "total_time": "217.84 us", "max_time": "95.68 us" },
    { "stage": "query end", "count": 34, "total_time": "80.45 us", "max_time": "7.28 us" },
    { "stage": "System lock", "count": 25, "total_time": "63.90 us", "max_time": "5.30 us" },
    { "stage": "checking permissions", "count": 25, "total_time": "49.99 us", "max_time": "4.30 us" },
    { "stage": "cleaning up", "count": 35, "total_time": "29.80 us", "max_time": "1.32 us" },
    { "stage": "end", "count": 24, "total_time": "24.17 us", "max_time": "3.97 us" }
  ],
  "wait_summary": {
    "total_wait_time": "6.17 s",
    "wait_details": [
      { "wait": "io/table/sql/handler", "count": 38, "time_total": "6.16 s", "time_pct": "99.7837", "time_max": "2.03 s" },
      { "wait": "idle", "count": 36, "time_total": "8.90 ms", "time_pct": "0.1443", "time_max": "3.28 ms" },
      { "wait": "io/socket/sql/client_connection", "count": 156, "time_total": "4.36 ms", "time_pct": "0.0707", "time_max": "666.64 us" },
      { "wait": "lock/table/sql/handler", "count": 25, "time_total": "32.98 us", "time_pct": "0.0005", "time_max": "2.52 us" },
      { "wait": "synch/sxlock/innodb/index_tree_rw_lock", "count": 91, "time_total": "15.49 us", "time_pct": "0.0003", "time_max": "864.76 ns" },
      { "wait": "synch/mutex/innodb/trx_mutex", "count": 156, "time_total": "12.37 us", "time_pct": "0.0002", "time_max": "536.22 ns" },
      { "wait": "synch/mutex/sql/THD::LOCK_query_plan", "count": 118, "time_total": "9.13 us", "time_pct": "0.0001", "time_max": "547.24 ns" },
      { "wait": "synch/mutex/sql/THD::LOCK_thd_data", "count": 87, "time_total": "7.04 us", "time_pct": "0.0001", "time_max": "224.21 ns" },
      { "wait": "synch/mutex/sql/THD::LOCK_thd_query", "count": 70, "time_total": "5.06 us", "time_pct": "0.0001", "time_max": "187.92 ns" },
      { "wait": "synch/mutex/innodb/trx_undo_mutex", "count": 25, "time_total": "2.15 us", "time_pct": "0.0000", "time_max": "230.04 ns" }
    ]
  },
  "user_variables": [
    { }
  ]
}

This is a snapshot of one of the connections from a MySQL Enterprise Monitor server, with this particular connection having inserted some data related to the Query Analyzer, with one past transaction that inserted a normalized and example statement in a single transaction with a final commit, and currently inserting another normalized statement.

Because it is currently executing an INSERT on the `memtrunkio__quan`.`normalized_statements_by_server_by_schema` table, you can also see that it holds a SHARED_WRITE metadata lock (for the duration of the TRANSACTION) on that table, and holds a table handle on that table as well.

Finally, you can see a summary of all of that connections memory usage, and timing information for both stages and lower level waits for those transactions and statements that it has executed.

Whilst developing this output, I’ve been using it almost exclusively against our own MEM schema. In the process it’s managed to help me find some interesting bugs. For instance, here’s a previous incarnation of that output, that helped find a bug where we were not committing between DELETE statements when purging data from all of our instrument tables. Ultimately this meant we were holding open huge transactions, with many many metadata locks being held across all of the instrument tables:

session_info:
{
  user: "mem@localhost",
  thd_id: 86676,
  current_statement: "",
  last_statements: [
    {"/* mem dbpool.default */ SET foreign_key_checks=1"},
    {"/* mem dbpool.default */ SET foreign_key_checks=0"},
    {"/* mem dbpool.default */ DELETE FROM `mem__instruments`.`QueryCacheFragmentationData` WHERE `timestamp` < 1393598543538 LIMIT 1000"},
    {"/* mem dbpool.default */ SET foreign_key_checks=1"},
    {"/* mem dbpool.default */ SET foreign_key_checks=0"},
    {"/* mem dbpool.default */ DELETE FROM `mem__instruments`.`ClusterDataNodeRedoLogspaceUsageData` WHERE `timestamp` < 1393598543538 LIMIT 1000"},
    {"/* mem dbpool.default */ SET foreign_key_checks=1"},
    {"/* mem dbpool.default */ SET foreign_key_checks=0"},
    {"/* mem dbpool.default */ DELETE FROM `mem__instruments`.`PreparedStatementUtilizationData` WHERE `timestamp` < 1393598543539 LIMIT 1000"},
    {"/* mem dbpool.default */ SET foreign_key_checks=1"}
  ],
  metadata_locks: [
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.preparedstatementutilizationdata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.clusterdatanoderedologspaceusagedata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.querycachefragmentationdata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.semisyncmastertransactionsdata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.innodbredologiousagedata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.connectionsaborteddata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.innodbrowdetailsdata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.semisyncmasterwaitsdata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.querycachequeriesincachedata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.rowaccessesdata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.tablelocksdata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.agentluamemusagedata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.cpupercentage" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.avgrowaccessesdata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.temporarytablesdata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.innodbrowlocktimedata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.querycachememorydata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.rowwritesdata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.clusterdiskpagebufferhitratiodata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.clusterdatanodesinreadystatedata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.connectionsmaxdata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.inventorystatisticssummarizeddata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.threadpoolqueriesdata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.innodbrowlockdetailsdata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.binlogcachedata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.slaverelaylogspaceusagedata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.innodbsemaphoresdata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.diskiototaldata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.loadaveragedata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.innodbcheckpointagedata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.querycacheaveragefreeblocksizedata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.querycachelowmemprunesdata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.rowsreturneddata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.httpbytes" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.hitratiosdata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.mysqlconnectionoperationsstatsdata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.myisamkeybufferactivitydata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.diskioopstotaldata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.supportdiagnosticsadvisor_variables" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.innodbadaptivehashsearchesdata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.innodbbufferpoolactivitydata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.querycacheefficiencydata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.binlogiousagedata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.httpcounts_ssl" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.statementstatisticsdata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.innodbdatafileiousagedata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.semisyncmasterwaitingsessionsdata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.connectionpooldata_uipool" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.clusterdatanodeundobufferusagedata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.kbytesinoutdata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.agentlatencytime" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.jvmmemoryusage" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.clusterdatanodeundologspaceusagedata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.preparedstatementsqldata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.lockwaitratiodata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.httpclientactivity" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.httptimes_ssl" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.ramusagedata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.databasetransactionsdata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.statementerrorsandwarningsdata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.slavesecondsbehinddata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.binlogspaceusagedata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.innodbdatadictionarymemorydata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.ehcachebacklogactivity" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.innodbbufferpoolpendingopsdata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.threadpoolqueueagedata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.innodbchangebufferusagedata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.jvmgctime" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.httpcounts" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.innodbchangebufferactivitydata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.jvmgccounts" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.clusterdatanodedatamemoryusagedata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.innodbtransactionsdata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.mebruntime" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.assetselectorcachesdata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.cpuaverage" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.httptimes" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.threadpoolqueuesdata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.myisamindexfileiousagedata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.innodbrowlocktimestatisticsdata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.myisamkeybufferusagedata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.preparedstatementapidata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.innodbadaptivehashmemorydata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.openedtablesdata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.threadcachedata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.slowqueriesdata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.networktrafficadvisor_networktraffic" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.threadpoolqueriesstalleddata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.innodblockmemorydata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.clusterdatanoderedobufferusagedata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.innodbcompressiontimedata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.connectionpooldata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.innodbrowlockwaitsdata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.qrtidata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.innodbundologsdata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.swapioopsdata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.connectionsdata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.rowscanratiodata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.clusterdatanodeindexmemoryusagedata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.purgeoperationsdata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.sortingdata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.swapusagedata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.threadpoolcurrentthreadsdata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.mysqlprocessactivity" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.innodbdoublewritebufferactivitydata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.fsstatistics" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.innodbpendinglogwritesdata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.querycacheaveragequerysizedata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.innodbhistlengthdata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.preparedstatementapiinvalidationsdata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.myisamdatafileiousagedata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.innodbosfileaccessdata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.innodbbufferpooldata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.databaseactivitydata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.semisyncmasteravgwaittimesdata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__instruments.innodbopenfilesdata" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__quan.explains" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__quan.histograms" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__quan.normalized_statements_by_server_by_schema_data" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__quan.explain_stages" },
    { lock_type: "SHARED_WRITE", lock_duration: "TRANSACTION", lock_status: "GRANTED", scope: "mem__quan.example_statements" }
  ],
  memory: [
    { type: "sql/NET::buff", count: "1", allocated: "16.01 KiB", total_allocated: "16.01 KiB" },
    { type: "sql/String::value", count: "1", allocated: "16.00 KiB", total_allocated: "32.05 KiB" },
    { type: "mysys/array_buffer", count: "7", allocated: "8.98 KiB", total_allocated: "9.11 KiB" },
    { type: "sql/thd::main_mem_root", count: "1", allocated: "8.02 KiB", total_allocated: "1.42 MiB" },
    { type: "sql/THD::transactions::mem_root", count: "1", allocated: "4.02 KiB", total_allocated: "4.02 KiB" },
    { type: "vio/vio", count: "1", allocated: "488 bytes", total_allocated: "488 bytes" },
    { type: "sql/THD::Session_tracker", count: "6", allocated: "209 bytes", total_allocated: "209 bytes" },
    { type: "sql/THD::variables", count: "1", allocated: "64 bytes", total_allocated: "64 bytes" },
    { type: "sql/TABLE", count: "0", allocated: "26 bytes", total_allocated: "25.82 KiB" },
    { type: "sql/Security_context", count: "1", allocated: "10 bytes", total_allocated: "10 bytes" },
    { type: "sql/THD::Session_sysvar_resource_manager", count: "1", allocated: "8 bytes", total_allocated: "8 bytes" },
    { type: "sql/MPVIO_EXT::auth_info", count: "1", allocated: "4 bytes", total_allocated: "4 bytes" },
    { type: "sql/THD::db", count: "1", allocated: "4 bytes", total_allocated: "4 bytes" },
    { type: "myisam/record_buffer", count: "0", allocated: "0 bytes", total_allocated: "1.37 KiB" },
    { type: "myisam/MI_INFO", count: "0", allocated: "0 bytes", total_allocated: "6.44 KiB" },
    { type: "myisam/MYISAM_SHARE", count: "0", allocated: "0 bytes", total_allocated: "8.36 KiB" },
    { type: "mysys/my_file_info", count: "0", allocated: "0 bytes", total_allocated: "584 bytes" },
    { type: "mysys/IO_CACHE", count: "0", allocated: "0 bytes", total_allocated: "32.00 KiB" },
    { type: "sql/MYSQL_LOCK", count: "0", allocated: "0 bytes", total_allocated: "11.23 KiB" },
    { type: "sql/my_bitmap_map", count: "0", allocated: "0 bytes", total_allocated: "648 bytes" },
    { type: "sql/SQL_SELECT::test_quick_select", count: "0", allocated: "0 bytes", total_allocated: "603.25 KiB" },
    { type: "sql/QUICK_RANGE_SELECT::alloc", count: "0", allocated: "0 bytes", total_allocated: "583.41 KiB" }
  ],
  stage_summary: [
    { stage: "starting", count: "486", total_time: "122.07 ms", max_time: "26.19 ms" },
    { stage: "updating", count: "146", total_time: "14.52 ms", max_time: "11.06 ms" },
    { stage: "statistics", count: "40", total_time: "10.23 ms", max_time: "8.18 ms" },
    { stage: "freeing items", count: "485", total_time: "8.20 ms", max_time: "133.35 us" },
    { stage: "System lock", count: "196", total_time: "4.34 ms", max_time: "163.46 us" },
    { stage: "Sending data", count: "40", total_time: "3.79 ms", max_time: "1.60 ms" },
    { stage: "init", count: "196", total_time: "2.82 ms", max_time: "212.24 us" },
    { stage: "Opening tables", count: "442", total_time: "2.54 ms", max_time: "48.02 us" },
    { stage: "removing tmp table", count: "2", total_time: "1.52 ms", max_time: "955.25 us" },
    { stage: "update", count: "10", total_time: "983.24 us", max_time: "281.10 us" },
    { stage: "closing tables", count: "485", total_time: "747.98 us", max_time: "27.34 us" },
    { stage: "query end", count: "485", total_time: "489.16 us", max_time: "4.79 us" },
    { stage: "end", count: "196", total_time: "409.23 us", max_time: "63.80 us" },
    { stage: "cleaning up", count: "486", total_time: "363.86 us", max_time: "4.67 us" },
    { stage: "checking permissions", count: "205", total_time: "340.21 us", max_time: "6.87 us" },
    { stage: "preparing", count: "40", total_time: "257.71 us", max_time: "20.84 us" },
    { stage: "optimizing", count: "40", total_time: "182.29 us", max_time: "10.39 us" },
    { stage: "Creating tmp table", count: "2", total_time: "75.55 us", max_time: "38.71 us" },
    { stage: "executing", count: "40", total_time: "15.72 us", max_time: "1.21 us" }
  ]
}

It’s been an eye opener for many of our transaction semantics (especially as we use Hibernate, it’s not always clear what is happening under the covers).

I’ve implemented this as a stored function, that takes the MySQL connection id as a parameter to dump the info for. So it can be used as part of any statement against for instance the performance_schema.threads or INFORMATION_SCHEMA.PROCESSLIST tables, such as:

mysql> SELECT sys.ps_thread_info(id) AS thread_info
    ->   FROM INFORMATION_SCHEMA.PROCESSLIST
    ->  WHERE Command != 'Sleep'
    ->  ORDER BY Time DESC\G
*************************** 1. row ***************************
thread_info:
{
  "user": "root@localhost",
  "thd_id": 122149,
  "connection_id": 122127,
  "database": "sys",
  "current_statement": {
    "time": 0,
    "rows_examined": 0,
    "rows_affected": 0,
    "rows_sent": 0,
    "tmp_tables": 1,
    "tmp_disk_tables": 1,
    "sort_rows": 1,
...

You can get a copy of the function here.

I’m considering adding it to the sys schema – what do you think?

MySQL sys 1.3.0 released – The Facepalm Release

With a last minute change to the host_summary_by_stages view for the 1.2.0 release of the sys schema, I managed to break the views without noticing it.

So, in the shortest release cycle for the sys schema ever (less than 24 hours), 1.3.0 has now been released.

This comes with a bonus though, there was also a new view that was contributed by Jesper Wisborg Krogh waiting to be merged as well, so I’ve added that to the 1.3.0 version (hence the 1.3.0 instead of 1.2.1).

This is the innodb_lock_waits view, which shows all sessions that are waiting for a lock within InnoDB, as well as the details of who is blocking them, here’s an example output:

mysql> SELECT * FROM innodb_lock_waits\G
*************************** 1. row ***************************
     waiting_trx_id: 805505
     waiting_thread: 78
      waiting_query: UPDATE t1 SET val = 'c2' WHERE id = 3
    waiting_lock_id: 805505:132:3:28
  waiting_lock_mode: X
  waiting_lock_type: RECORD
 waiting_lock_table: `db1`.`t1`
 waiting_lock_index: PRIMARY
    blocking_trx_id: 805504
    blocking_thread: 77
     blocking_query: UPDATE t1 SET val = CONCAT('c1', SLEEP(10)) WHERE id = 3
   blocking_lock_id: 805504:132:3:28
 blocking_lock_mode: X
 blocking_lock_type: RECORD
blocking_lock_table: `db1`.`t1`
blocking_lock_index: PRIMARY

Sorry for the breakage, but enjoy the new view!

Facepalm

MySQL sys version 1.2.0 released

MySQL sys version 1.2.0 has just been released.

All views now work with MySQL 5.7.5, and the ONLY_FULL_GROUP_BY changes.

There is also a new script available (generate_sql_file.sh) that will allow RDS users to easily generate a single SQL file, using a specified user, that can be loaded in to an RDS instance. See the README for details on how to use that.

Here’s a full summary of the other changes:

Backwards Incompatible Changes

  • The host_summary_by_stages and user_summary_by_stages wait_sum and wait_avg columns were renamed to total_latency and avg_latency respectively, for consistency.
  • The host_summary_by_file_io_type and user_summary_by_file_io_type latency column was renamed to total_latency, for consistency.

Improvements

  • Made the truncation length for the format_statement view configurable. This includes adding a new persistent sys_config table to store the new variable – statement_truncate_len – see the README for usage
  • Added total_latency to the schema_tables_with_full_table_scans view, and added an x$ counterpart
  • Added innodb_buffer_free to the schema_table_statistics_with_buffer view, to summarize how much free space is allocated per table in the buffer pool
  • The schema_unused_indexes view now ignores indexes named PRIMARY (primary keys)
  • Added rows_affected and rows_affected_avg stats to the statement_analysis views
  • The statements_with_full_table_scans view now ignores any SQL that starts with SHOW
  • Added a script, generate_sql_file.sh, that can be used to generate a single SQL file, also allowing substitution of the MySQL user to use, and/or whether the SET sql_log_bin … statements should be omitted. This is useful for those using RDS, where the root@localhost user is not accessible, and sql_log_bin is disabled (Issue #5)
  • Added a set of memory_by_thread_by_current_bytes views, that summarize memory usage per thread with MySQL 5.7’s memory instrumentation
  • Improved each of the host specific views to return aggregate values for background threads, instead of ignoring them, in the same way as the user summary views

Bug Fixes

  • Added the missing memory_by_host view for MySQL 5.7
  • Added missing space for hour notation within the format_time function
  • Fixed views affected by MySQL 5.7 ONLY_FULL_GROUP_BY and functional dependency changes

Speaking at Oracle OpenWorld 2014

For those of you lucky enough to come and listen to all the great talks within the MySQL Central @ Oracle OpenWorld this year, I’ll also be giving a talk about the MySQL sys schema this year.

This builds upon the talks that I’ve given in the past around Performance Schema, as well as some of the great looking talks on Performance Schema that are on the schedule this year by other MySQL @ Oracle engineers (see below).

The schedule builder is now live, here are some of the other interesting ones on my list that I’d like to attend:

And of course, if you’re in to monitoring as much as me (and why wouldn’t you be?), be sure to check these out:

Hope to see you there – come find me, and lets have a beverage of your choice together!

MySQL sys version 1.1.0 released

I’ve just released the 1.1.0 version of the MySQL sys schema.

This release is hugely pleasing to me, in that I actually didn’t have to do too much work on it myself! There were a significant number of contributions from Jesper Wisborg Krogh and Arnaud Adant, both MySQL Support Engineers (at the time at least, Arnaud has moved on to pastures new now), as well as again from Joe Grasse.

Thank you all for your contributions!

Here’s a summary of the changes:

Improvements

  • Added host summary views, which have the same structure as the user summary views, but aggregated by host instead (Contributed by Arnaud Adant)
    • host_summary
    • host_summary_by_file_io_type
    • host_summary_by_file_io
    • host_summary_by_statement_type
    • host_summary_by_statement_latency
    • host_summary_by_stages
    • waits_by_host_by_latency
  • Added functions which return instruments are either enabled, or timed by default (#15) (Contributed by Jesper Wisborg Krogh)
    • ps_is_instrument_default_enabled
    • ps_is_instrument_default_timed
  • Added a ps_thread_id function, which returns the thread_id value exposed within performance_schema for the current connection (Contributed by Jesper Wisborg Krogh)
  • Improved each of the user specific views to return aggregate values for background threads, instead of ignoring them (Contributed by Joe Grasse)
  • Optimized the schema_table_statistics and schema_table_statistics_with_buffer views, to use a new view that will get materialized (x$ps_schema_table_statistics_io), along with the changes to the RETURN types for extract_schema_from_file_name and extract_table_from_file_name, this results in a significant performance improvement – in one test changing the run time from 14 minutes to 20 seconds. (Conceived by Roy Lyseng, Mark Leith and Jesper Wisborg Krogh, implemented and contributed by Jesper Wisborg Krogh)

Bug Fixes

  • Removed unintentially committed sys_56_rds.sql file (See Issue #5, which is still outstanding)
  • Fixed the ps_trace_statement_digest and ps_trace_thread procedures to properly set sql_log_bin, and reset the thread INSTRUMENTED value correctly (Contributed by Jesper Wisborg Krogh)
  • Removed various sql_log_bin disabling from other procedures that no longer require it – DML against the performance_schema data is no longer replicated (Contributed by Jesper Wisborg Krogh)
  • Fixed EXPLAIN within ps_trace_statement_digest procedure (Contributed by Jesper Wisborg Krogh)
  • Fixed the datatype for the thd_id variable within the ps_thread_stack procedure (Contributed by Jesper Wisborg Krogh)
  • Fixed datatypes used for temporary tables within the ps_trace_statement_digest procedure (Contributed by Jesper Wisborg Krogh)
  • Fixed the RETURN datatype extract_schema_from_file_name and extract_table_from_file_name to return a VARCHAR(64) (Contributed by Jesper Wisborg Krogh)
  • Added events_transactions_current to the default enabled consumers in 5.7 (#25)