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)

MySQL sys version 1.0.1 released

I’ve just released the 1.0.1 version of the MySQL sys schema. It contains a few bug fixes (including a contribution from Joe Grasse, thanks Joe!), and a number of new helper procedures around viewing and altering configuration for consumers and instruments, contributed by the MySQL QA team, as they’ve started to incorporate more Performance Schema trace data in to their automated testing.

Next up is the 1.1.0 release, that also includes a bunch of new host summary views that were contributed by Arnaud Adant (of the MySQL Support team). I have a number of new things in development to add as well before then though.

Let me know if there are things you’d like to see as well, maybe I can find time to work on those too.

Enjoy.

MySQL Tech Day, Paris, Slides

I spoke at MySQL Tech Day in Paris yesterday, it was a fun day – apart from the early and late flights in and out on the same day, my boss told me I was crazy, he was probably right.

First Dimitri presented some recent benchmark findings:

Then I got my turn to speak for a few hours around Performance Schema and the MySQL sys schema:

Visible there on the left is also Marc Alff, the principle developer of Performance Schema, who was also able to interject some great points for people as well.

My presentation was a super-set of a few of my previous ones, but updated and with a little added content here and there (particularly around the MySQL sys updates from ps_helper), so it’s pretty long (I had 3 hours to fill), but probably the most complete that I’ve done.

Here are the slides:

MySQL 5.7: Performance Schema Improvements, Percona Live

I had a great time last week at Percona Live, meeting up with lots of old friends, and getting to know lots of new ones.

It was great to meet many of the people that hang around on DBHangOps face to face. Geoff even got a community award (well done)! Unfortunately I had to miss the lunch.

It was also good to see Oracle getting a community award. Our engineers are extremely hard working, and all want to help community and customers alike be successful with their MySQL environments. There was lots of great positive attitude towards the work we’ve been doing, it was pleasing to hear that we are on the right track.

I haven’t been to a conference at that venue since the “old school” MySQL UC. Well done Percona for putting on such a great event.

And as a bonus, I even got to sit in front of Shlomi Noach on my flight from SFO to Newark:

Here’s the slides for my talk, MySQL 5.7: Performance Schema Improvements:

MySQL 5.7: Performance Schema Improvements

I hope to be seeing many of you out at MySQL Connect later this year, not long now to submit your talk!