You are here

MySQL PERFORMANCE_SCHEMA Hints

ToC

Accounts not properly closing connections [ 1 ]

Works since 5.6

SELECT ess.user, ess.host
     , (a.total_connections - a.current_connections) - ess.count_star as not_closed
     , ((a.total_connections - a.current_connections) - ess.count_star) * 100 /
       (a.total_connections - a.current_connections) as pct_not_closed
  FROM performance_schema.events_statements_summary_by_account_by_event_name ess
  JOIN performance_schema.accounts a on (ess.user = a.user and ess.host = a.host)
 WHERE ess.event_name = 'statement/com/quit'
   AND (a.total_connections - a.current_connections) > ess.count_star
;
+-----------+---------------+------------+----------------+
| user      | host          | not_closed | pct_not_closed |
+-----------+---------------+------------+----------------+
| applicat  |   10.0.246.74 |         31 |         0.0001 |
| applicat  |   10.0.246.73 |         59 |         0.0003 |
| replicate |   10.0.246.72 |          1 |       100.0000 |
| applicat  |   10.0.246.76 |          4 |         0.0024 |
| root      | localhost     |          3 |         0.0053 |
| applicat  | localhost     |      51880 |         0.2991 |
| applicat  |   10.0.246.77 |          1 |       100.0000 |
+-----------+---------------+------------+----------------+

Unused indexes [ 2 ]

Works since 5.6

SELECT object_schema, object_name, index_name
  FROM performance_schema.table_io_waits_summary_by_index_usage
 WHERE index_name IS NOT NULL
   AND index_name != 'PRIMARY'
   AND count_star = 0
 ORDER BY object_schema, object_name
;
+---------------+------------------+------------+
| object_schema | object_name      | index_name |
+---------------+------------------+------------+
| applicat      | access           | name       |
| applicat      | access           | owner      |
| applicat      | access           | source     |
| applicat      | access           | password   |
| applicat      | access           | type_2     |
| applicat      | access           | type_3     |
| applicat      | active_customers | scustomer  |
| applicat      | addresses        | domain     |
| applicat      | addresses        | dtype      |
...
| applicat      | timegroups       | name       |
| applicat      | transactions     | customer   |
| applicat      | unlimited_access | name       |
| applicat      | urls             | name       |
| applicat      | vouchers         | batch      |
| applicat      | voucher_batches  | customer   |
+---------------+------------------+------------+
279 rows in set (2.43 sec)

Who created temporary (disk) tables

Works since 5.6

SELECT user, host, event_name, count_star AS cnt, sum_created_tmp_disk_tables AS tmp_disk_tables, sum_created_tmp_tables AS tmp_tables
  FROM performance_schema.events_statements_summary_by_account_by_event_name
 WHERE sum_created_tmp_disk_tables > 0
    OR sum_created_tmp_tables > 0
;
+----------+---------------+---------------------------------+------------+-----------------+------------+
| user     | host          | event_name                      | cnt        | tmp_disk_tables | tmp_tables |
+----------+---------------+---------------------------------+------------+-----------------+------------+
| applicat |   10.0.246.74 | statement/sql/select            |  565349929 |               0 |   10293241 |
| applicat |   10.0.246.74 | statement/sql/show_fields       |          8 |               8 |          8 |
| applicat |   10.0.246.73 | statement/sql/select            |  571206486 |               0 |   10429993 |
| applicat |   10.0.246.73 | statement/sql/show_databases    |          2 |               0 |          2 |
| applicat |   10.0.246.76 | statement/sql/select            |   17814977 |               0 |       2696 |
| applicat |   10.0.246.76 | statement/sql/show_databases    |          7 |               0 |          7 |
| applicat |   10.0.246.76 | statement/sql/show_tables       |         10 |               0 |         10 |
| applicat | localhost     | statement/sql/select            | 1856221142 |             828 |    4142585 |
| applicat | localhost     | statement/sql/show_databases    |          7 |               0 |          7 |
| applicat | localhost     | statement/sql/show_tables       |          7 |               0 |          7 |
| applicat | localhost     | statement/sql/update_multi      |         14 |               2 |          3 |
+----------+---------------+---------------------------------+------------+-----------------+------------+
28 rows in set (0.02 sec)

SELECT schema_name, substr(digest_text, 1, 40) AS statement, count_star AS cnt, sum_created_tmp_disk_tables AS tmp_disk_tables, sum_created_tmp_tables AS tmp_tables
  FROM performance_schema.events_statements_summary_by_digest
 WHERE sum_created_tmp_disk_tables > 0
    OR sum_created_tmp_tables > 0
;
+-------------+------------------------------------------+----------+-----------------+------------+
| schema_name | statement                                | cnt      | tmp_disk_tables | tmp_tables |
+-------------+------------------------------------------+----------+-----------------+------------+
| applicat    | SELECT * FROM `bulk_campaigns` WHERE `ac | 11858959 |               0 |   11858965 |
| applicat    | SELECT * FROM `machines` WHERE `asterisk |   933720 |               0 |     933720 |
| applicat    | SELECT SOCKET FROM `location` WHERE `use |  3206476 |               0 |    3206476 |
| applicat    | SELECT `g` . `id` , `t` . `dtype` , `t`  |  1408417 |               0 |    1408418 |
| applicat    | SELECT COUNT ( * ) AS `count` FROM ( SEL |    47048 |               0 |      47048 |
| applicat    | SELECT `peer` , COUNT ( * ) AS `count` F |   156720 |               0 |     152536 |
| applicat    | SELECT SUM ( `c` ) AS `count` FROM ( SEL |  3045114 |               0 |    6090350 |
| applicat    | SELECT DISTINCTROW ( `queue` ) FROM `que |   836798 |               0 |     836798 |
| applicat    | SELECT `dtype` , `dnumber` FROM `bulk_de |   897447 |               0 |      22327 |
| applicat    | SELECT * FROM `information_schema` . `PR |       49 |              49 |         49 |
| applicat    | SELECT COUNT ( * ) AS `count` FROM ( SEL |   264196 |               0 |     264240 |
| applicat    | SELECT `p` . * , `p` . `secret` AS PASSW |      590 |             590 |        590 |
| applicat    | SELECT `p` . `name` , `p` . `customer` , |      227 |             227 |        227 |
+-------------+------------------------------------------+----------+-----------------+------------+
79 rows in set (0.15 sec)

Accounts which never connected since last start-up [ 3 ]

Works since 5.6

SELECT DISTINCT m_u.user, m_u.host
  FROM mysql.user m_u
  LEFT JOIN performance_schema.accounts ps_a ON m_u.user = ps_a.user AND m_u.host = ps_a.host
 WHERE ps_a.user IS NULL
ORDER BY m_u.user
;

Users which never connected since last start-up

Works since 5.6

SELECT DISTINCT m_u.user
  FROM mysql.user m_u
  LEFT JOIN performance_schema.users ps_u ON m_u.user = ps_u.user
 WHERE ps_u.user IS NULL
 ORDER BY m_u.user
;
+-----------+-----------+
| user      | host      |
+-----------+-----------+
| applicat  | %         |
| replicate | %         |
| root      | ::1       |
| root      | 127.0.0.1 |
| root      | sip10     |
+-----------+-----------+

Totally unused accounts (never connected since last restart and not used to check Stored Program or View privileges) since last start-up

Works since 5.6

SELECT DISTINCT m_u.user, m_u.host
  FROM mysql.user m_u
  LEFT JOIN performance_schema.accounts ps_a ON m_u.user = ps_a.user AND ps_a.host = m_u.host
  LEFT JOIN information_schema.views is_v ON is_v.DEFINER = CONCAT(m_u.User, '@', m_u.Host) AND is_v.security_type = 'DEFINER'
  LEFT JOIN information_schema.routines is_r ON is_r.DEFINER = CONCAT(m_u.User, '@', m_u.Host) AND is_r.security_type = 'DEFINER'
  LEFT JOIN information_schema.events is_e ON is_e.definer = CONCAT(m_u.user, '@', m_u.host)
  LEFT JOIN information_schema.triggers is_t ON is_t.definer = CONCAT(m_u.user, '@', m_u.host)
 WHERE ps_a.user IS NULL
   AND is_v.definer IS NULL
   AND is_r.definer IS NULL
   AND is_e.definer IS NULL
   AND is_t.definer IS NULL
 ORDER BY m_u.user, m_u.host
;
+-----------+-----------+
| user      | host      |
+-----------+-----------+
| applicat  | %         |
| replicate | %         |
| root      | 127.0.0.1 |
| root      | ::1       |
| root      | sip10     |
+-----------+-----------+
5 rows in set (0.04 sec)

SHOW FULL PROCESSLIST

Works since 5.5 (5.1?)

But with filter on Sleep and sorting by time to find the evil query...

SELECT id, user, host, db, command, time, state, LEFT(info, 80) AS info
  FROM information_schema.processlist
 WHERE command NOT IN ('Sleep', 'Binlog Dump')
 ORDER BY time ASC
;

Non blocking version, since 5.6:

SELECT PROCESSLIST_ID AS id, PROCESSLIST_USER AS user, PROCESSLIST_HOST AS host, PROCESSLIST_DB AS db
     , PROCESSLIST_COMMAND AS command, PROCESSLIST_TIME AS time, PROCESSLIST_STATE AS state, LEFT(PROCESSLIST_INFO, 80) AS info
  FROM performance_schema.threads
 WHERE PROCESSLIST_ID IS NOT NULL
   AND PROCESSLIST_COMMAND NOT IN ('Sleep', 'Binlog Dump')
 ORDER BY PROCESSLIST_TIME ASC
;

Storage Engines per schema

For defining backup strategy, preparing migration to InnoDB or Galera Cluster for MySQL, etc.

Works since 5.5 (5.1?)

SELECT table_schema AS `schema`, engine, COUNT(*) AS `tables`
     , ROUND(SUM(data_length)/1024/1024, 0) AS data_mb, ROUND(SUM(index_length)/1024/1024, 0) index_mb
  FROM information_schema.tables
 WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema')
   AND engine IS NOT NULL
 GROUP BY table_schema, engine
;
+---------------------+--------+--------+---------+----------+
| schema              | engine | tables | data_mb | index_mb |
+---------------------+--------+--------+---------+----------+
| mantis              | MyISAM |     31 |       0 |        0 |
| mpm                 | InnoDB |      3 |       0 |        0 |
| mysql_sequences     | InnoDB |      2 |       0 |        0 |
| mysql_sequences     | MEMORY |      1 |       0 |        0 |
| otrs                | InnoDB |     73 |      13 |        4 |
| quartz              | InnoDB |     12 |       0 |        0 |
| tracking            | MyISAM |      1 |       0 |        0 |
+---------------------+--------+--------+---------+----------+

Tables without a Primary Key

Galera Cluster, InnoDB, M/S replication with row based replication does not work well with tables without a Primary Key. To find those the following query helps:

Works since 5.5 (5.1?)

SELECT DISTINCT t.table_schema, t.table_name
  FROM information_schema.tables AS t
  LEFT JOIN information_schema.columns AS c ON t.table_schema = c.table_schema AND t.table_name = c.table_name AND c.column_key = "PRI"
 WHERE t.table_schema NOT IN ('information_schema', 'mysql', 'performance_schema')
   AND c.table_name IS NULL AND t.table_type != 'VIEW'
;
+--------------+----------------------+
| table_schema | table_name           |
+--------------+----------------------+
| test         | t_wo_pk              |
| test         | t_wo_pk_with_Index   |
| applicat     | active               |
| applicat     | active_customers     |
| applicat     | audit                |
| applicat     | currencies           |
| applicat     | location             |
| applicat     | mailbox_destinations |
| applicat     | missedcalls          |
| applicat     | tbl                  |
| applicat     | version              |
+--------------+----------------------+
9 rows in set (0.07 sec)

Bad SQL queries of users

Sometimes it could be interesting to find users who do evil SQL Queries which do for example created_tmp_disk_tables, select_full_join, select_range_check or sort_merge_passes. Those can be found as follows:

SELECT user, host, event_name
     , sum_created_tmp_disk_tables AS tmp_disk_tables
     , sum_select_full_join AS full_join
     , sum_select_range_check AS range_check
     , sum_sort_merge_passes AS sort_merge
  FROM performance_schema.events_statements_summary_by_account_by_event_name
 WHERE sum_created_tmp_disk_tables > 0
    OR sum_select_full_join > 0
    OR sum_select_range_check > 0
    OR sum_sort_merge_passes > 0
 ORDER BY sum_sort_merge_passes DESC
 LIMIT 10
;        
+-------+-------------+---------------+-----------------+-----------+-------------+------------+
| user  | host        | event_name    | tmp_disk_tables | full_join | range_check | sort_merge |
+-------+-------------+---------------+-----------------+-----------+-------------+------------+
| user1 | 192.168.0.3 | insert_select |               0 |      7033 |           0 |      10947 |
| user2 | 192.168.0.4 | insert_select |               0 |      6837 |           0 |      10792 |
| user1 | 192.168.0.1 | select        |        10742308 |      2095 |       23061 |         16 |
| user2 | 192.168.0.2 | select        |        10958067 |      2639 |       23162 |         14 |
+-------+-------------+---------------+-----------------+-----------+-------------+------------+

SELECT * FROM performance_schema.events_statements_summary_by_digest WHERE SUM_SELECT_FULL_JOIN > 0\G
*************************** 1. row ***************************
                SCHEMA_NAME: applicat
                     DIGEST: 9472953c46e14f37683669177c469849
                DIGEST_TEXT: UPDATE EVENTS JOIN `machines` ON `events` . `server` = `machines` . `server` SET `events` . `machine` = `machines` . `id`
                 COUNT_STAR: 1
          SUM_ROWS_AFFECTED: 9359276
              SUM_ROWS_SENT: 0
          SUM_ROWS_EXAMINED: 56155770
SUM_CREATED_TMP_DISK_TABLES: 1
     SUM_CREATED_TMP_TABLES: 1
       SUM_SELECT_FULL_JOIN: 1
            SUM_SELECT_SCAN: 1
          SUM_NO_INDEX_USED: 1
                 FIRST_SEEN: 2016-11-23 20:55:47
                  LAST_SEEN: 2016-11-23 20:55:47

SHOW PROFILE in PERFORMANCE_SCHEMA

On systems with heavy traffic the PERFORMANCE_SCHEMA tables might be too small.

mysql> SHOW GLOBAL VARIABLES LIKE 'perf%events%stage%hist%long%';
+----------------------------------------------------+-------+
| Variable_name                                      | Value |
+----------------------------------------------------+-------+
| performance_schema_events_stages_history_long_size | 10000 |
+----------------------------------------------------+-------+

mysql> pager grep history_long
mysql> SHOW ENGINE PERFORMANCE_SCHEMA STATUS;
| performance_schema | events_waits_history_long.row_size       | 184      |
| performance_schema | events_waits_history_long.row_count      | 1000     |
| performance_schema | events_waits_history_long.memory         | 184000   |
| performance_schema | events_stages_history_long.row_size      | 88       |
| performance_schema | events_stages_history_long.row_count     | 1000     |
| performance_schema | events_stages_history_long.memory        | 88000    |
| performance_schema | events_statements_history_long.row_size  | 3024     |
| performance_schema | events_statements_history_long.row_count | 1000     |
| performance_schema | events_statements_history_long.memory    | 3024000  |

mysql> UPDATE performance_schema.setup_instruments
   SET ENABLED = 'YES', TIMED = 'YES'
 WHERE NAME LIKE '%statement/%';
mysql> UPDATE performance_schema.setup_instruments
   SET ENABLED = 'YES', TIMED = 'YES'
 WHERE NAME LIKE '%stage/%';
mysql> UPDATE performance_schema.setup_consumers
   SET ENABLED = 'YES'
 WHERE NAME LIKE '%events_statements_%';
mysql> UPDATE performance_schema.setup_consumers
   SET ENABLED = 'YES'
 WHERE NAME LIKE '%events_stages_%';

mysql> SELECT ;

mysql> SELECT eshl.event_id AS Query_ID, TRUNCATE(eshl.timer_wait/1000000000000, 6) as Duration
     , LEFT(eshl.sql_text, 120) AS Query
  FROM performance_schema.events_statements_history_long AS eshl
  JOIN performance_schema.threads AS t ON t.thread_id = eshl.thread_id
 WHERE t.processlist_id = CONNECTION_ID();
+----------+-----------+-------------------------+
| Query_ID | Duration  | Query                   |
+----------+-----------+-------------------------+
|       12 | 13.560737 | select * from test.test |
+----------+-----------+-------------------------+

mysql> SELECT event_name AS Stage, TRUNCATE(timer_wait/1000000000000,6) AS Duration 
  FROM performance_schema.events_stages_history_long
 WHERE nesting_event_id = 12;
+--------------------------------+-----------+
| Stage                          | Duration  |
+--------------------------------+-----------+
| stage/sql/starting             |  0.000043 |
| stage/sql/checking permissions |  0.000004 |
| stage/sql/Opening tables       |  0.002700 |
| stage/sql/init                 |  0.000025 |
| stage/sql/System lock          |  0.000009 |
| stage/sql/optimizing           |  0.000002 |
| stage/sql/statistics           |  0.000014 |
| stage/sql/preparing            |  0.000013 |
| stage/sql/executing            |  0.000000 |
| stage/sql/Sending data         | 13.557683 |
| stage/sql/end                  |  0.000002 |
| stage/sql/query end            |  0.000008 |
| stage/sql/closing tables       |  0.000006 |
| stage/sql/freeing items        |  0.000215 |
| stage/sql/cleaning up          |  0.000001 |
+--------------------------------+-----------+

SELECT, INSERT, UPDATE and DELETE per table

Sometimes it is interesting to know how many SELECT, INSERT, UPDATE or DELETE (DML) statementes have been exectuted against a specifict table (for example for OPTIMZE TABLE). This can be found as follows:

Works since MySQL 5.6

SELECT object_type, object_schema, object_name
     , count_star, count_read, count_write, count_fetch
     , count_insert, count_update, count_delete
  FROM performance_schema.table_io_waits_summary_by_table
 WHERE count_star > 0
 ORDER BY count_star DESC
;
+-------------+---------------+--------------------------+--------------+--------------+-------------+--------------+--------------+--------------+--------------+
| object_type | object_schema | object_name              | count_star   | count_read   | count_write | count_fetch  | count_insert | count_update | count_delete |
+-------------+---------------+--------------------------+--------------+--------------+-------------+--------------+--------------+--------------+--------------+
| TABLE       | applicat      | location                 | 786427885735 | 786099567862 |   328317873 | 786099567862 |      1625431 |    325067666 |      1624776 |
| TABLE       | applicat      | phones                   |   2405560667 |   2045067895 |   360492772 |   2045067895 |         1327 |    360490982 |          463 |
| TABLE       | applicat      | events                   |   1412280597 |    762246599 |   650033998 |    762246599 |    327128550 |      9359276 |    313546172 |
| TABLE       | applicat      | bulk_destinations        |    506067937 |    506062957 |        4980 |    506062957 |         1174 |         2555 |         1251 |
| TABLE       | applicat      | queue_destinations       |    498821286 |    491098645 |     7722641 |    491098645 |           37 |      7722525 |           79 |
| TABLE       | applicat      | cdrcosts                 |    435492375 |    426092647 |     9399728 |    426092647 |      3295888 |      2512478 |      3591362 |
| TABLE       | applicat      | active                   |    405815523 |    390000298 |    15815225 |    390000298 |      3730452 |      8354383 |      3730390 |
| TABLE       | applicat      | config                   |    335967766 |    335813232 |      154534 |    335813232 |           16 |       154517 |            1 |
| TABLE       | applicat      | subscriptions            |    138623149 |    117231406 |    21391743 |    117231406 |          723 |     21390398 |          622 |
...
| TABLE       | applicat      | plan_products            |            1 |            1 |           0 |            1 |            0 |            0 |            0 |
| TABLE       | applicat      | voucher_batches          |            1 |            1 |           0 |            1 |            0 |            0 |            0 |
| TABLE       | applicat      | vouchers                 |            1 |            1 |           0 |            1 |            0 |            0 |            0 |
+-------------+---------------+--------------------------+--------------+--------------+-------------+--------------+--------------+--------------+--------------+
123 rows in set (0.03 sec)

SELECT object_type, object_schema, object_name, index_name
     , count_star, count_read, count_write, count_fetch
     , count_insert, count_update, count_delete
  FROM performance_schema.table_io_waits_summary_by_index_usage
 WHERE count_star > 0
   AND index_name IS NOT NULL
 ORDER BY count_star DESC
;
+-------------+---------------+--------------------------+---------------------+--------------+--------------+-------------+--------------+--------------+--------------+--------------+
| object_type | object_schema | object_name              | index_name          | count_star   | count_read   | count_write | count_fetch  | count_insert | count_update | count_delete |
+-------------+---------------+--------------------------+---------------------+--------------+--------------+-------------+--------------+--------------+--------------+--------------+
| TABLE       | applicat      | phones                   | name                |   2044563494 |   1684480557 |   360082937 |   1684480557 |            0 |    360082474 |          463 |
| TABLE       | applicat      | location                 | username            |   1094446716 |   1094446444 |         272 |   1094446444 |            0 |            0 |          272 |
| TABLE       | applicat      | events                   | time                |    599440206 |    299721243 |   299718963 |    299721243 |            0 |            0 |    299718963 |
| TABLE       | applicat      | bulk_destinations        | PRIMARY             |    506066763 |    506062957 |        3806 |    506062957 |            0 |         2555 |         1251 |
| TABLE       | applicat      | phones                   | customer            |    336101637 |    336101632 |           5 |    336101632 |            0 |            5 |            0 |
| TABLE       | applicat      | config                   | PRIMARY             |    335967246 |    335812727 |      154519 |    335812727 |            0 |       154518 |            1 |
| TABLE       | applicat      | domains                  | domain              |    134049716 |    134049716 |           0 |    134049716 |            0 |            0 |            0 |
| TABLE       | applicat      | active                   | dtype               |    128545758 |    128500448 |       45310 |    128500448 |            0 |        45310 |            0 |
| TABLE       | applicat      | plan_routes              | PRIMARY             |    118272835 |    118272835 |           0 |    118272835 |            0 |            0 |            0 |
...
| TABLE       | applicat      | provisioning             | model               |            1 |            1 |           0 |            1 |            0 |            0 |            0 |
| TABLE       | applicat      | voucher_batches          | PRIMARY             |            1 |            1 |           0 |            1 |            0 |            0 |            0 |
| TABLE       | applicat      | messages                 | callid              |            1 |            1 |           0 |            1 |            0 |            0 |            0 |
+-------------+---------------+--------------------------+---------------------+--------------+--------------+-------------+--------------+--------------+--------------+--------------+
371 rows in set (0.04 sec)

Top long running queries

Works since MySQL 5.6

UPDATE setup_consumers SET enabled = 1 WHERE name = 'events_statements_history_long';

SELECT left(digest_text, 64)
     , ROUND(SUM(timer_end-timer_start)/1000000000, 1) AS tot_exec_ms
     , ROUND(SUM(timer_end-timer_start)/1000000000/COUNT(*), 1) AS avg_exec_ms
     , ROUND(MIN(timer_end-timer_start)/1000000000, 1) AS min_exec_ms
     , ROUND(MAX(timer_end-timer_start)/1000000000, 1) AS max_exec_ms
     , ROUND(SUM(timer_wait)/1000000000, 1) AS tot_wait_ms
     , ROUND(SUM(timer_wait)/1000000000/COUNT(*), 1) AS avg_wait_ms
     , ROUND(MIN(timer_wait)/1000000000, 1) AS min_wait_ms
     , ROUND(MAX(timer_wait)/1000000000, 1) AS max_wait_ms
     , ROUND(SUM(lock_time)/1000000000, 1) AS tot_lock_ms
     , ROUND(SUM(lock_time)/1000000000/COUNT(*), 1) AS avglock_ms
     , ROUND(MIN(lock_time)/1000000000, 1) AS min_lock_ms
     , ROUND(MAX(lock_time)/1000000000, 1) AS max_lock_ms
     , MIN(LEFT(DATE_SUB(NOW(), INTERVAL (isgs.VARIABLE_VALUE - TIMER_START*10e-13) second), 19)) AS first_seen
     , MAX(LEFT(DATE_SUB(NOW(), INTERVAL (isgs.VARIABLE_VALUE - TIMER_START*10e-13) second), 19)) AS last_seen
     , COUNT(*) as cnt
  FROM events_statements_history_long
  JOIN information_schema.global_status AS isgs
 WHERE isgs.variable_name = 'UPTIME'
 GROUP BY LEFT(digest_text,64)
 ORDER BY tot_exec_ms DESC
;

+------------------------------------------------------------------+-------------+-------------+-------------+---------------------+---------------------+-----+
| left(digest_text, 64)                                            | tot_exec_ms | tot_wait_ms | tot_lock_ms | first_seen          | last_seen           | cnt |
+------------------------------------------------------------------+-------------+-------------+-------------+---------------------+---------------------+-----+
| INSERT INTO `test` SELECT ? , DATA , ? FROM `test`               |     50493.5 |     50493.5 |        26.3 | 2015-11-12 16:41:35 | 2015-11-12 16:42:04 |  20 |
| SELECT LEFT ( `digest_text` , ? ) , `ROUND` ( SUM ( `timer_end`  |     14434.6 |     14434.6 |        25.8 | 2015-11-12 16:48:44 | 2015-11-12 17:07:15 |   6 |
| SELECT * FROM `test`                                             |      7483.0 |      7483.0 |         0.2 | 2015-11-12 16:41:16 | 2015-11-12 16:42:34 |   2 |
| SHOW ENGINE INNODB STATUS                                        |      1912.4 |      1912.4 |         0.0 | 2015-11-12 16:37:19 | 2015-11-12 17:07:36 | 687 |
| SHOW GLOBAL VARIABLES                                            |      1091.1 |      1091.1 |        68.8 | 2015-11-12 16:37:19 | 2015-11-12 17:07:36 | 687 |
| SHOW GLOBAL STATUS                                               |       638.7 |       638.7 |        40.8 | 2015-11-12 16:37:19 | 2015-11-12 17:07:36 | 687 |
| SELECT LEFT ( `digest_text` , ? ) , SUM ( `timer_end` - `timer_s |       356.2 |       356.2 |        42.4 | 2015-11-12 16:42:38 | 2015-11-12 16:45:00 |   6 |
| SELECT `digest_text` , SUM ( `timer_end` - `timer_start` ) / ? A |       325.3 |       325.3 |         0.4 | 2015-11-12 16:40:44 | 2015-11-12 16:42:18 |   3 |
| SELECT `DIGEST_TEXT` , ( `TIMER_END` - `TIMER_START` ) / ? AS `e |       163.2 |       163.2 |         1.0 | 2015-11-12 16:37:44 | 2015-11-12 16:39:22 |   9 |
| SELECT LOWER ( REPLACE ( trx_state , ?, ... ) ) AS state , COUNT |       133.9 |       133.9 |        80.2 | 2015-11-12 16:37:19 | 2015-11-12 17:07:36 | 687 |
+------------------------------------------------------------------+-------------+-------------+-------------+---------------------+---------------------+-----+

Tables never written to

If you want to find tables which it was never written to (or read from) since last instance restart you can use the following query. Works since MySQL 5.6

Caution:

  • count_read can only be taken as argument if a physical backup method (NOT mysqldump) is used.
  • If count_write is 0 it does not necessarily mean that there was no write statement (no matching write)!
  • If tables are empty SELECT statements are not counted (count_read = 0).
SELECT t.table_schema, t.table_name, t.table_rows, tio.count_read, tio.count_write
  FROM information_schema.tables AS t
  JOIN performance_schema.table_io_waits_summary_by_table AS tio
    ON tio.object_schema = t.table_schema AND tio.object_name = t.table_name
 WHERE t.table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
   AND tio.count_write = 0
 ORDER BY t.table_schema, t.table_name
;
+--------------+--------------------------+------------+------------+-------------+
| table_schema | table_name               | table_rows | count_read | count_write |
+--------------+--------------------------+------------+------------+-------------+
| applicat     | active_customers         |          0 |    3692444 |           0 |
| applicat     | addresses                |          0 |     601072 |           0 |
| applicat     | cardgroups               |          0 |          0 |           0 |
| applicat     | cards                    |          0 |          0 |           0 |
| applicat     | card_batches             |          0 |          0 |           0 |
| applicat     | cb_customers             |          5 |         14 |           0 |
| applicat     | cdrcost_taxes            |          0 |   27849511 |           0 |
| applicat     | cdrs_archive             |          0 |          0 |           0 |
| applicat     | classes                  |         17 |        156 |           0 |
...
| applicat     | version                  |          5 |        162 |           0 |
| applicat     | vouchers                 |          0 |          1 |           0 |
| applicat     | voucher_batches          |          0 |          1 |           0 |
+--------------+--------------------------+------------+------------+-------------+
43 rows in set (0.14 sec)

DML, DDL and DCL per account

Which accounts execute DML statements?

SELECT user, host, SUBSTR(event_name, 15) AS dml, SUM(count_star) AS sum_dml
  FROM performance_schema.events_statements_summary_by_account_by_event_name
 WHERE count_star > 0
   AND event_name IN ('statement/sql/update', 'statement/sql/insert', 'statement/sql/delete'
     , 'statement/sql/load', 'statement/sql/replace', 'statement/sql/replace_select'
     , 'statement/sql/insert_select', 'statement/sql/delete_multi', 'statement/sql/update_multi'
     , 'statement/sql/do', 'statement/sql/execute_sql')
 GROUP BY user, host, dml
 ORDER BY user, host, dml
;
+-----------------+-----------+---------------+---------+
| user            | host      | dml           | sum_dml |
+-----------------+-----------+---------------+---------+
| root            | localhost | execute_sql   |     240 |
| root            | localhost | insert        |      10 |
| root            | localhost | insert_select |       1 |
| root            | localhost | replace       |       3 |
| root            | localhost | update        |      17 |
| root            | localhost | update_multi  |       1 |
| service_manager | localhost | delete        |    2605 |
| service_manager | localhost | insert        |   20387 |
| service_manager | localhost | update        |    3814 |
+-----------------+-----------+---------------+---------+

How many DML statements are executed per schema?

SELECT object_schema, SUM(count_insert + count_update + count_delete) AS count_dml
  FROM performance_schema.table_io_waits_summary_by_table
 WHERE (count_insert + count_update + count_delete) > 0
 GROUP BY object_schema
 ORDER BY count_dml DESC
;
+------------------+-----------+
| object_schema    | count_dml |
+------------------+-----------+
| zabbix           |     52660 |
| focmm            |     36183 |
| mem__quan        |     29795 |
| mem__inventory   |      3086 |
| mem__instruments |      2783 |
| mem__events      |      1521 |
| sys              |         6 |
+------------------+-----------+

Which accounts execute DDL/DCL statements?

SELECT user, host, SUBSTR(event_name, 15) AS event_name, count_star
  FROM performance_schema.events_statements_summary_by_account_by_event_name
 WHERE count_star > 0 AND
     ( event_name LIKE 'statement/sql/create%'
    OR event_name LIKE 'statement/sql/alter%'
    OR event_name LIKE 'statement/sql/drop%'
    OR event_name LIKE 'statement/sql/rename%'
    OR event_name LIKE 'statement/sql/grant%'
    OR event_name LIKE 'statement/sql/revoke%'
    OR event_name LIKE 'statement/sql/optimize%'
    OR event_name LIKE 'statement/sql/truncate%'
    OR event_name LIKE 'statement/sql/repair%'
    OR event_name LIKE 'statement/sql/check%'
     )
;
+------+-----------+--------------+------------+
| user | host      | event_name   | count_star |
+------+-----------+--------------+------------+
| root | localhost | alter_table  |          1 |
| root | localhost | rename_table |          1 |
+------+-----------+--------------+------------+

How many DML/DDL statements are executed per schema?

SELECT schema_name, IF(SUBSTRING_INDEX(digest_text, ' ', 1) IN ('CREATE', 'DROP', 'ALTER', 'GRANT', 'REVOKE'), 'DDL', 'DML') AS statement_type, COUNT(*) AS count
  FROM performance_schema.events_statements_summary_by_digest
 GROUP BY statement_type, schema_name
 ORDER BY statement_type DESC, count DESC
;
+-------------------------+----------------+-------+
| schema_name             | statement_type | count |
+-------------------------+----------------+-------+
| mem                     | DML            |   478 |
| mysql                   | DML            |   193 |
| mem__instruments        | DML            |   121 |
| mem__inventory          | DML            |   116 |
| zabbix                  | DML            |   115 |
| mem__events             | DML            |    33 |
| sys                     | DML            |    25 |
| mem__config             | DML            |    24 |
| performance_schema      | DML            |    20 |
| mem__quan               | DML            |     9 |
| NULL                    | DML            |     7 |
| mem__advisors           | DML            |     4 |
| mem__bean_config        | DML            |     3 |
| mem__advisor_text       | DML            |     3 |
| mem__enterprise         | DML            |     3 |
| mem__instruments_config | DML            |     2 |
| sys                     | DDL            |   186 |
| mysql                   | DDL            |   179 |
+-------------------------+----------------+-------+

Finding syntactically wrong/erroneous MySQL queries

Up to MySQL 5.5 syntactically wrong queries ended up in the MySQL Error Log as well. In MySQL 5.6 this behaviour has changed. But how can we find them now?

In the MySQL performance_schema, since 5.6, queries with errors or warnings can be found in the events_statements_history tables as follows:

UPDATE performance_schema.setup_consumers
   SET enabled = 'YES'
 WHERE name LIKE 'events_statements_history%';

SELECT * FROM performance_schema.events_statements_history
 WHERE errors != 0 OR warnings != 0\G
*************************** 1. row ***************************
              THREAD_ID: 97
               EVENT_ID: 20
           END_EVENT_ID: 20
             EVENT_NAME: statement/sql/error
                 SOURCE: mysqld.cc:962
            TIMER_START: 858021191531530000
              TIMER_END: 858021191624337000
             TIMER_WAIT: 92807000
               SQL_TEXT: select counter(*) from test
         CURRENT_SCHEMA: test
            MYSQL_ERRNO: 1064
      RETURNED_SQLSTATE: 42000
           MESSAGE_TEXT: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use
                 ERRORS: 1
               WARNINGS: 0

Finding queries served by MySQL Query Cache

Possibly not 100% accurate (also lists queries on P_S). Works since MySQL 5.6.

UPDATE performance_schema.setup_consumers SET enabled = 1 WHERE name = 'events_statements_history_long';

SHOW GLOBAL VARIABLES LIKE 'query_cache____e';

SHOW GLOBAL STATUS LIKE 'qcache_hits';

SELECT sql_text, ROUND(SUM(timer_wait)/100000000, 3) AS time_wait_ms, COUNT(*), ROUND(SUM(timer_wait)/100000000/COUNT(*), 3) AS time_wait_ms_avg
  FROM performance_schema.events_statements_history_long
 WHERE digest IS NULL
   AND sql_text IS NOT NULL
   AND errors = 0
 GROUP BY event_name, sql_text
;
+----------------------------------+--------------+----------+------------------+
| sql_text                         | time_wait_ms | COUNT(*) | time_wait_ms_avg |
+----------------------------------+--------------+----------+------------------+
| select * from test where id = 42 |        0.411 |        1 |            0.411 |
| select count(*) from test        |        1.525 |        3 |            0.508 |
+----------------------------------+--------------+----------+------------------+