You are here

MariaDB and MySQL PERFORMANCE_SCHEMA Hints

Table of Contents

MariaDB

On MariaDB you have to enable the Performance Schema first before use:

[mysqld]
performance_schema_consumer_events_statements_history_long = ON
performance_schema             = ON

Accounts not properly closing connections [ 1 ]

SQL> 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
 ORDER BY not_closed
;
+-----------+---------------+------------+----------------+
| 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 ]

SQL> SELECT DISTINCT s.table_schema, s.table_name, s.index_name
--     , i.count_star
  FROM information_schema.statistics AS s
  LEFT JOIN performance_schema.table_io_waits_summary_by_index_usage AS i
         ON (s.table_schema = i.object_schema AND s.table_name = i.object_name AND s.index_name = i.index_Name)
 WHERE s.table_schema NOT IN ('mysql', 'performance_schema', 'sys', 'information_schema')
   AND s.index_name != 'PRIMARY'
   AND i.count_star = 0
 ORDER BY s.table_schema, s.table_name, s.index_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)

The same result you get from the sys Schema since MariaDB 10.6 and MySQL 5.7 (default) and MariaDB 10.0 and MySQL 5.6 (as add on).

SELECT * FROM sys.schema_unused_indexes;

Who created temporary (disk) tables

SQL> 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)

SQL> 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 ]

SQL> 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

SQL> 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

SQL> 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

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

SQL> 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:

SQL> 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.

SQL> 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', 'sys')
   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:

SQL> 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 NOT IN('VIEW', 'SEQUENCE')
;
+--------------+----------------------+
| 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:

SQL> 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 |
+-------+-------------+---------------+-----------------+-----------+-------------+------------+

SQL> 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.

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

SQL> pager grep history_long
SQL> 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  |

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

SQL> SELECT <our query here...>;

SQL> 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 |
+----------+-----------+-------------------------+

SQL> 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) statements have been executed against a specific table (for example for OPTIMIZE TABLE). This can be found as follows:

SQL> 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)

SQL> 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

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

For MariaDB:

SQL> 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 performance_schema.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
;

For MySQL:

SQL> 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 performance_schema.events_statements_history_long
  JOIN performance_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.

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).
SQL> 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?

SQL> SELECT user, host, SUBSTRING_INDEX(event_name, '/', -1) 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?

SQL> 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?

SQL> SELECT user, host, SUBSTRING_INDEX(event_name, '/', -1) 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?

SQL> 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:

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

SQL> 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).

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

SQL> SHOW GLOBAL VARIABLES LIKE 'query_cache____e';

SQL> SHOW GLOBAL STATUS LIKE 'qcache_hits';

SQL> 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 |
+----------------------------------+--------------+----------+------------------+

Grant access to sys schema to another user

The MySQL documentation on how to grant acces to the sys schema is a bit vague...

The following privileges seem to work:

SQL> DROP USER 'sys_user'@'%';
SQL> CREATE USER 'sys_user'@'%' IDENTIFIED BY 'S3cr3t!!!';

SQL> GRANT SELECT ON sys.* TO 'sys_user'@'%';
SQL> GRANT EXECUTE ON sys.* TO 'sys_user'@'%';
SQL> GRANT INSERT, UPDATE ON sys.sys_config TO 'sys_user'@'%';

-- In small letters!
SQL> GRANT SELECT ON performance_schema.* TO 'sys_user'@'%';

--

SQL> GRANT PROCESS ON *.* TO sys_user@'%';

--

SQL> GRANT SUPER ON *.* TO 'sys_user'@'%';
SQL> GRANT CREATE TEMPORARY TABLES on sys.* TO 'sys_user'@'%';
SQL> GRANT UPDATE ON performance_schema.setup_consumers TO 'sys_user'@'%';
SQL> GRANT UPDATE ON performance_schema.setup_instruments TO 'sys_user'@'%';
SQL> GRANT UPDATE ON performance_schema.threads TO 'sys_user'@'%';
SQL> GRANT DELETE ON performance_schema.setup_actors TO 'sys_user'@'%';
SQL> GRANT INSERT ON performance_schema.setup_actors TO 'sys_user'@'%';
SQL> GRANT DELETE ON performance_schema.setup_objects TO 'sys_user'@'%';
SQL> GRANT INSERT ON performance_schema.setup_objects TO 'sys_user'@'%';

SQL> SELECT * FROM sys.schema_redundant_indexes;
SQL> SELECT * FROM sys.schema_unused_indexes;
SQL> SELECT * FROM sys.schema_index_statistics;

--

SQL> SELECT * FROM sys.innodb_buffer_stats_by_table;

--

-- Needs super: GRANT SUPER ON *.* TO 'sys_user'@'%';
SQL> CALL sys.ps_setup_save(10);
SQL> CALL sys.ps_setup_reload_saved();

SQL> CALL sys.ps_setup_enable_instrument('wait');
SQL> CALL sys.ps_setup_enable_instrument('stage');
SQL> CALL sys.ps_setup_enable_instrument('statement');
SQL> CALL sys.ps_setup_enable_consumer('current');
SQL> CALL sys.ps_setup_enable_consumer('history_long');

SQL> CALL sys.ps_setup_reset_to_default(TRUE);

Grant access to PERFORMANCE_SCHEMA schema to another user

SQL> GRANT SELECT ON performance_schema.* TO 'usr'@'localhost';
SQL> GRANT SELECT, UPDATE ON performance_schema.setup_instruments TO 'usr'@'localhost';
SQL> GRANT DROP ON performance_schema.* TO 'usr'@'localhost';

Literature: Performance Schema General Table Characteristics

Find InnoDB Locks

SQL> -- MariaDB 10.6, 10.11, 11.4
SQL> -- MySQL 5.7
SELECT lock_trx_id, lock_mode, lock_type, lock_table, lock_index
  FROM information_schema.innodb_locks;
+-------------+-----------+-----------+---------------+------------+
| lock_trx_id | lock_mode | lock_type | lock_table    | lock_index |
+-------------+-----------+-----------+---------------+------------+
| 25382       | X         | RECORD    | `test`.`test` | PRIMARY    |
| 25381       | X         | RECORD    | `test`.`test` | PRIMARY    |
+-------------+-----------+-----------+---------------+------------+

SQL> -- MariaDB not supported
SQL> -- MySQL 8.0, 8.4, 9.0
SELECT dlw.requesting_thread_id, dlw.blocking_thread_id
     , dlr.object_schema AS req_obj_schema, dlr.object_name AS req_obj_name, dlr.index_name AS req_idx_name, dlr.lock_type AS req_lck_type, dlr.lock_mode AS req_lck_mode, dlr.Lock_status AS req_lck_status, dlr.lock_data AS req_lck_data
     , dlb.object_schema AS blk_onj_schema, dlb.object_name AS blk_obj_name, dlb.index_name AS blk_ind_name, dlb.lock_type AS blk_lck_type, dlb.lock_mode AS blk_lck_mode, dlb.Lock_status AS blk_lck_status, dlb.lock_data AS blk_lck_data
  FROM performance_schema.data_lock_waits AS dlw
  JOIN performance_schema.data_locks AS dlr on dlw.requesting_thread_id = dlr.thread_id
  JOIN performance_schema.data_locks AS dlb on dlw.blocking_thread_id = dlb.thread_id;

SQL> -- MariaDB 10.6, 10.11, 11.4
SQL> -- MySQL 5.7, 8.0, 8.4, 9.0
SQL> SELECT trx_id, trx_state, trx_started, trx_wait_started, trx_mysql_thread_id, trx_query, trx_tables_in_use, trx_tables_locked, trx_lock_structs, trx_rows_locked, trx_rows_modified
  FROM information_schema.innodb_trx
 ORDER BY trx_started, trx_wait_started;
+--------+-----------+---------------------+---------------------+--------------------------------------------+-------------------+-------------------+------------------+-----------------+-------------------+
| trx_id | trx_state | trx_started         | trx_wait_started    | trx_query                                  | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_rows_locked | trx_rows_modified |
+--------+-----------+---------------------+---------------------+--------------------------------------------+-------------------+-------------------+------------------+-----------------+-------------------+
| 25381  | RUNNING   | 2019-12-11 21:52:31 | NULL                | NULL                                       |                 0 |                 1 |                2 |              99 |                 0 |
| 25382  | LOCK WAIT | 2019-12-11 21:52:44 | 2019-12-11 21:55:31 | update test set data = 'bla' where id = 43 |                 1 |                 1 |                2 |               3 |                 1 |
+--------+-----------+---------------------+---------------------+--------------------------------------------+-------------------+-------------------+------------------+-----------------+-------------------+

SQL> -- MariaDB 10.6, 10.11, 11.4
SQL> -- MySQL 5.7, 8.0, 8.4, 9.0
SQL> SELECT * FROM sys.innodb_lock_waits;

Find Metadata Locks

For MySQL

Important: Instrument wait/lock/metadata/sql/mdl must be enabled first!

SQL> SELECT * FROM performance_schema.metadata_locks;
+-------------+--------------------+----------------+---------------------+---------------+-------------+-----------------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | LOCK_TYPE           | LOCK_DURATION | LOCK_STATUS | OWNER_THREAD_ID |
+-------------+--------------------+----------------+---------------------+---------------+-------------+-----------------+
| TABLE       | test               | test           | SHARED_READ         | TRANSACTION   | GRANTED     |           10446 |
| GLOBAL      | NULL               | NULL           | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     |           10447 |
| SCHEMA      | test               | NULL           | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     |           10447 |
| TABLE       | test               | test           | SHARED_UPGRADABLE   | TRANSACTION   | GRANTED     |           10447 |
| TABLE       | test               | test           | EXCLUSIVE           | TRANSACTION   | PENDING     |           10447 |
| TABLE       | performance_schema | metadata_locks | SHARED_READ         | TRANSACTION   | GRANTED     |           10445 |
+-------------+--------------------+----------------+---------------------+---------------+-------------+-----------------+

SQL> SELECT *
  FROM performance_schema.threads
 WHERE thread_id IN (10446, 10447);
+-----------+----------------+------------------+------------------+----------------+---------------------+------------------+---------------------------------+------------------------------------+
| THREAD_ID | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_COMMAND | PROCESSLIST_TIME | PROCESSLIST_STATE               | PROCESSLIST_INFO                   |
+-----------+----------------+------------------+------------------+----------------+---------------------+------------------+---------------------------------+------------------------------------+
|     10446 |          10421 | root             | localhost        | test           | Sleep               |               77 | NULL                            | NULL                               |
|     10447 |          10422 | root             | localhost        | test           | Query               |               66 | Waiting for table metadata lock | alter table test add column c8 int |
+-----------+----------------+------------------+------------------+----------------+---------------------+------------------+---------------------------------+------------------------------------+

SQL> SELECT mdl.*, th.*
  FROM performance_schema.metadata_locks AS mdl
  JOIN performance_schema.threads AS th ON mdl.owner_thread_id = th.thread_id
;

For MariaDB

Metadata Lock Plugin must be enabled first (see here)!

SQL> SELECT * FROM information_schema.metadata_lock_info;
+-----------+-------------------------+----------------------+--------------+------------+
| THREAD_ID | LOCK_MODE               | LOCK_TYPE            | TABLE_SCHEMA | TABLE_NAME |
+-----------+-------------------------+----------------------+--------------+------------+
|       400 | MDL_BACKUP_ALTER_COPY   | Backup lock          |              |            |
|       401 | MDL_SHARED_WRITE        | Table metadata lock  | test         | test       |
|       400 | MDL_SHARED_UPGRADABLE   | Table metadata lock  | test         | test       |
|       400 | MDL_INTENTION_EXCLUSIVE | Schema metadata lock | test         |            |
+-----------+-------------------------+----------------------+--------------+------------+

Caution: Query in MariaDB is slightly different:

SQL> SELECT *
  FROM performance_schema.threads
 WHERE PROCESSLIST_ID IN (400, 401);
+-----------+----------------+------------------+------------------+----------------+---------------------+------------------+---------------------------------+------------------------------------+
| THREAD_ID | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_COMMAND | PROCESSLIST_TIME | PROCESSLIST_STATE               | PROCESSLIST_INFO                   |
+-----------+----------------+------------------+------------------+----------------+---------------------+------------------+---------------------------------+------------------------------------+
|       416 |            400 | root             | localhost        | test           | Query               |               51 | Waiting for table metadata lock | alter table test add column c6 int |
|       417 |            401 | root             | localhost        | test           | Sleep               |              830 | NULL                            | NULL                               |
+-----------+----------------+------------------+------------------+----------------+---------------------+------------------+---------------------------------+------------------------------------+

If this is a bug or not we do not know (MDEV-21294)...

Or here the finaly query:

SQL> SELECT mdl.thread_id AS connection_id, CONCAT(pl.user, ' from ', pl.host) AS 'user', mdl.lock_mode, mdl.lock_type, mdl.table_schema, mdl.table_name
          , pl.db AS actual_schema, pl.time, DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL time SECOND) AS startet, pl.command, pl.state, pl.info AS query, pl.query_id
       FROM information_schema.metadata_lock_info AS mdl
       JOIN information_schema.processlist AS pl ON pl.id = mdl.thread_id
      WHERE pl.time > 10
;
+---------------+---------------------+-------------------------+----------------------+--------------+------------+---------------+------+---------------------+---------+---------------------------------+-----------------------------------+----------+
| connection_id | user                | lock_mode               | lock_type            | table_schema | table_name | actual_schema | time | startet             | command | state                           | query                             | query_id |
+---------------+---------------------+-------------------------+----------------------+--------------+------------+---------------+------+---------------------+---------+---------------------------------+-----------------------------------+----------+
|          7383 | root from localhost | MDL_BACKUP_ALTER_COPY   | Backup lock          |              |            | test          | 1581 | 2020-05-27 11:25:16 | Query   | Waiting for table metadata lock | alter table test add column a int |    61933 |
|          7383 | root from localhost | MDL_SHARED_UPGRADABLE   | Table metadata lock  | test         | test       | test          | 1581 | 2020-05-27 11:25:16 | Query   | Waiting for table metadata lock | alter table test add column a int |    61933 |
|          7383 | root from localhost | MDL_INTENTION_EXCLUSIVE | Schema metadata lock | test         |            | test          | 1581 | 2020-05-27 11:25:16 | Query   | Waiting for table metadata lock | alter table test add column a int |    61933 |
|          7382 | root from localhost | MDL_SHARED_READ         | Table metadata lock  | test         | test       | test          | 1623 | 2020-05-27 11:24:34 | Sleep   |                                 | NULL                              |    61850 |
+---------------+---------------------+-------------------------+----------------------+--------------+------------+---------------+------+---------------------+---------+---------------------------------+-----------------------------------+----------+

What did a Connection before sleeping?

This should work since MySQL 5.6 and MariaDB 10.0.

SQL> UPDATE performance_schema.setup_consumers SET enabled='yes'
WHERE name LIKE 'events_statements%';

SQL> SET @connection_id = <connection_id>;
SQL> SELECT thread_id INTO @thread_id
FROM performance_schema.threads WHERE PROCESSLIST_ID = @connection_id;

SQL> SELECT event_id, sql_text, current_schema
  FROM performance_schema.events_statements_history
 WHERE thread_id = @thread_id
 ORDER BY event_id DESC
 LIMIT 10;
+----------+-----------------------------------+----------------+
| event_id | sql_text                          | current_schema |
+----------+-----------------------------------+----------------+
|        9 | select * from test where id < 100 | test           |
|        8 | start transaction                 | test           |
+----------+-----------------------------------+----------------+

How many statements did another (sleeping) connection?

This works only in MySQL 5.7. MariaDB does still contain P_S version 5.6 where some tables needed are not present. Possibly MariaDB 10.5 will contain parts of P_S 5.7 or newer.

SQL> SELECT thread_id, processlist_id AS 'Processlist Id', processlist_user as User, processlist_host as Host
     , processlist_db as db, processlist_command as Command, processlist_time as Time
  FROM performance_schema.threads
 WHERE type = 'FOREGROUND'
   AND processlist_id != CONNECTION_ID()
   AND processlist_command != 'Daemon'
   AND processlist_command = 'Sleep'
   AND processlist_time > 1
;
+-----------+----------------+------+-----------+------+---------+------+
| thread_id | Processlist Id | User | Host      | db   | Command | Time |
+-----------+----------------+------+-----------+------+---------+------+
|      4305 |           4280 | app  | localhost | test | Sleep   |    7 |
+-----------+----------------+------+-----------+------+---------+------+

SQL> SET @thread_id = 4305;
SQL> SET @processlist_id = 4280;

SQL> SELECT variable_name, variable_value
  FROM performance_schema.status_by_thread
 WHERE thread_id = (SELECT thread_id
                      FROM performance_schema.threads
                     WHERE processlist_id = @processlist_id
                   )
  AND (variable_name IN ('Queries', 'Questions') OR variable_name LIKE 'Com\_%')
;
+--------------------+----------------+
| variable_name      | variable_value |
+--------------------+----------------+
| Com_stmt_reprepare | 0              |
| Queries            | 12991          |
| Questions          | 26             |
+--------------------+----------------+

This result is completely wrong! We filed a bug for it: P_S view status_by_thread does not reflect correct results

Workaround: (works also on MariaDB 10.2 and newer!)

SQL> SELECT event_name, COUNT(*)
  FROM performance_schema.events_statements_history
 WHERE thread_id = @thread_id
 GROUP BY event_name
;
+-----------------------------+----------+
| event_name                  | COUNT(*) |
+-----------------------------+----------+
| statement/sql/error         |        1 |
| statement/sql/insert_select |        5 |
| statement/sql/select        |        1 |
| statement/sql/show_status   |        2 |
| statement/sql/update        |        1 |
+-----------------------------+----------+

This result could not reflect the right number of values if some where lost...

SQL> SHOW GLOBAL STATUS LIKE 'performance_schema_thread%lost';
+------------------------------------------+-------+
| Variable_name                            | Value |
+------------------------------------------+-------+
| Performance_schema_thread_classes_lost   | 0     |
| Performance_schema_thread_instances_lost | 0     |
+------------------------------------------+-------+

Connections per user

SQL> SELECT user, total_connections
  FROM performance_schema.users WHERE user IS NOT NULL
;
+-------------+-------------------+
| user        | total_connections |
+-------------+-------------------+
| root        |                 4 |
| fpmmm_agent |                32 |
+-------------+-------------------+

Connections per user and IP

SQL> SELECT user, host, total_connections
  FROM performance_schema.accounts WHERE user IS NOT NULL
 ORDER BY total_connections DESC
;
+-------------+--------------+-------------------+
| user        | host         | total_connections |
+-------------+--------------+-------------------+
| fpmmm_agent | localhost    |                32 |
| root        | localhost    |                 7 |
| app         | 192.168.56.1 |                 3 |
| app         | localhost    |                 1 |
| app         | 172.29.0.85  |                 1 |
+-------------+--------------+-------------------+

Accounts which could not connect or with connection errors

Requires the MariaDB SQL Error Log plugin (MariaDB only).

2022-03-04 13:41:44 [bla] @ localhost [] ERROR 1045: Access denied for user 'bla'@'localhost' (using password: YES) : (null)
2022-03-04 13:42:14 [app] @ localhost [] ERROR 1045: Access denied for user 'app'@'localhost' (using password: YES) : (null)
2022-03-04 13:42:29 [app] @  [127.0.0.1] ERROR 1045: Access denied for user 'app'@'127.0.0.1' (using password: YES) : (null)
2022-03-04 13:42:48 [app] @  [192.168.1.142] ERROR 1045: Access denied for user 'app'@'192.168.1.142' (using password: YES) : (null)
2022-03-04 13:48:09 [app] @  [192.168.56.101] ERROR 1045: Access denied for user 'app'@'192.168.56.101' (using password: YES) : (null)
2022-03-04 13:48:51 [app] @  [192.168.1.124] ERROR 1045: Access denied for user 'app'@'192.168.1.124' (using password: YES) : (null)

Temporary tables filling my disk

Sometimes we see cases where some queries with huge implicit temporary tables (up to 30 - 50 Gibyte) fill up the disk until the database gets an error:

[ERROR] [MY-012644] [InnoDB] Encountered a problem with file '.\#innodb_temp\temp_9.ibt'
[ERROR] [MY-012645] [InnoDB] Disk is full. Try to clean the disk to free space.
[Warning] [MY-012145] [InnoDB] Error while writing 4194304 zeroes to .\#innodb_temp\temp_9.ibt starting at offset 14680064000

Often our customers do not know which query caused the problem. So it would be helpful to find post-mortem the evil query:

Works since MySQL 8.0 / MariaDB 10.6.

SQL> SELECT * FROM sys.statements_with_temp_tables WHERE disk_tmp_tables > 0\G
*************************** 1. row ***************************
                   query: SELECT `ts` , DATA , COUNT ( * ...  ORDER BY DATA DESC , `ts` ASC
                      db: test
              exec_count: 1
           total_latency: 1.16 min
         disk_tmp_tables: 1
  tmp_tables_to_disk_pct: 100
              first_seen: 2022-04-01 15:58:54.331061
               last_seen: 2022-04-01 15:58:54.331061

Works since MySQL 8.0 / MariaDB 10.5.

 0\G
*************************** 1. row ***************************
                schema_name: test
                digest_text: SELECT `ts` , DATA , COUNT ( * ) FROM `test` GROUP BY `ts` , DATA ORDER BY DATA DESC , `ts` ASC
                 count_star: 1
          sum_rows_affected: 0
              sum_rows_sent: 20
          sum_rows_examined: 16777236
sum_created_tmp_disk_tables: 1
                 first_seen: 2022-04-01 15:58:54.331061
                  last_seen: 2022-04-01 15:58:54.331061

Unfortunately I have not found yet a place where the size of the temporary tables is recorded...

Who is writing to the binary log

If you have a shared hosting or multi-tenant environment you sometimes want to know who is writing to the binary log the most. Unfortunately we only have these information in counts and time but not in bytes...

SQL> SELECT * FROM sys.user_summary_by_file_io_type
 WHERE event_name LIKE '%binlog'
;
+------------+-------------------------+-------+-----------+-------------+
| user       | event_name              | total | latency   | max_latency |
+------------+-------------------------+-------+-----------+-------------+
| a          | wait/io/file/sql/binlog |  1836 | 201.91 ms | 170.02 ms   |
| b          | wait/io/file/sql/binlog |    43 | 13.20 ms  | 12.41 ms    |
| background | wait/io/file/sql/binlog |    24 | 18.39 ms  | 18.23 ms    |
| c          | wait/io/file/sql/binlog |   439 | 68.00 ms  | 59.56 ms    |
| root       | wait/io/file/sql/binlog |    22 | 98.63 ms  | 13.91 ms    |
+------------+-------------------------+-------+-----------+-------------+

Average binlog transaction size

Works since MySQL 8.0 (MySQL only).

SQL> SELECT FORMAT_BYTES(UNCOMPRESSED_BYTES_COUNTER/TRANSACTION_COUNTER) size
     , FORMAT_BYTES(COMPRESSED_BYTES_COUNTER/TRANSACTION_COUNTER) compressed
     , TRANSACTION_COUNTER
  FROM performance_schema.binary_log_transaction_compression_stats
;
+------------+------------+---------------------+
| size       | compressed | TRANSACTION_COUNTER |
+------------+------------+---------------------+
| 664.64 KiB | 664.64 KiB |                 589 |
+------------+------------+---------------------+

Source: lefred's Blog, 5 May 2023: Query and Transaction size in MySQL

Result size of SELECT statement

Works since MariaDB 10.5 and MySQL 8.0.

SQL> SELECT variable_value
  FROM performance_schema.status_by_thread
  JOIN performance_schema.threads USING(thread_id)
 WHERE processlist_id = CONNECTION_ID()
  AND variable_name = 'Bytes_sent' INTO @before
;

SQL> SELECT * FROM test.test;

SQL> SELECT ROUND((variable_value - @before)/1024/1024, 1) query_size_mb
  FROM performance_schema.status_by_thread
  JOIN performance_schema.threads USING(thread_id)
 WHERE processlist_id = CONNECTION_ID()
   AND variable_name='Bytes_sent'
;
+---------------+
| query_size_mb |
+---------------+
|         408.3 |
+---------------+

Source: lefred's Blog, 5 May 2023: Query and Transaction size in MySQL

Find open long running transactions

Works at least since MariaDB 10.3 and MySQL 5.7.

Sometimes we have the situation, that customer applications forget to close there connections or when reusing connections forget to close transacions (COMMIT or ROLLBACK). This can lead to subsequent errors (like metadata locks (MDL), InnoDB locks, lock wait timeout erros, system stalls, system overflows (max connections reached), etc.).
So the question is, how can we find out which connection did not close it transaction properly or which connections still has open transactions right now. Here is one way to answer the question:

SQL> SELECT trx.trx_mysql_thread_id AS thread_id
     , pl.user, pl.host, pl.db AS 'database', pl.command, pl.time, FROM_UNIXTIME(UNIX_TIMESTAMP()-pl.time) AS doing_since
     , pl.state AS query_state, IFNULL(pl.info, '') as query
     , trx.trx_state, trx.trx_started
--   , IFNULL(trx.trx_query, '') AS query   /* seems to be redundant! */
     , trx.trx_tables_locked AS tables_locked, trx.trx_lock_structs AS lock_structs, trx.trx_rows_locked AS rows_locked
  FROM information_schema.innodb_trx AS trx
  JOIN information_schema.processlist AS pl ON pl.id = trx.trx_mysql_thread_id
;

This query can give different outputs:

Empty set (0.00 sec)

...
 
+-----------+------+-----------+----------+---------+------+---------------------+-------------+----------------------------------------------------+-----------+---------------------+---------------+--------------+-------------+
| thread_id | user | host      | database | command | time | doing_since         | query_state | query                                              | trx_state | trx_started         | tables_locked | lock_structs | rows_locked |
+-----------+------+-----------+----------+---------+------+---------------------+-------------+----------------------------------------------------+-----------+---------------------+---------------+--------------+-------------+
|      9698 | root | localhost | test     | Query   |    3 | 2024-01-19 16:59:09 | updating    | update test set data = 'Bla bla' where id % 10 = 1 | RUNNING   | 2024-01-19 16:59:09 |             1 |         6281 |     1638700 |
+-----------+------+-----------+----------+---------+------+---------------------+-------------+----------------------------------------------------+-----------+---------------------+---------------+--------------+-------------+

...

+-----------+------+-----------+----------+---------+------+---------------------+-------------+-------+-----------+---------------------+---------------+--------------+-------------+
| thread_id | user | host      | database | command | time | doing_since         | query_state | query | trx_state | trx_started         | tables_locked | lock_structs | rows_locked |
+-----------+------+-----------+----------+---------+------+---------------------+-------------+-------+-----------+---------------------+---------------+--------------+-------------+
|      9698 | root | localhost | test     | Sleep   |    9 | 2024-01-19 16:59:09 |             |       | RUNNING   | 2024-01-19 16:59:09 |             1 |         8068 |     2105219 |
+-----------+------+-----------+----------+---------+------+---------------------+-------------+-------+-----------+---------------------+---------------+--------------+-------------+

...

+-----------+------+-----------+----------+---------+------+---------------------+-------------+----------+--------------+---------------------+---------------+--------------+-------------+
| thread_id | user | host      | database | command | time | doing_since         | query_state | query    | trx_state    | trx_started         | tables_locked | lock_structs | rows_locked |
+-----------+------+-----------+----------+---------+------+---------------------+-------------+----------+--------------+---------------------+---------------+--------------+-------------+
|      9698 | root | localhost | test     | Query   |    5 | 2024-01-19 16:59:29 | starting    | rollback | ROLLING BACK | 2024-01-19 16:59:09 |             1 |         8068 |     2105219 |
+-----------+------+-----------+----------+---------+------+---------------------+-------------+----------+--------------+---------------------+---------------+--------------+-------------+

...

Empty set (0.00 sec)

If you are in state 2 (RUNNING but command is Sleep and query/query_state is empty then continue with the post MariaDB General Query Log or What did a Connection before sleeping?

Other example outputs can be seen here:

+-----------+------+-----------+----------+---------+------+---------------------+--------------+------------------------------------------------------------+-----------+---------------------+---------------+--------------+-------------+
| thread_id | user | host      | database | command | time | doing_since         | query_state  | query                                                      | trx_state | trx_started         | tables_locked | lock_structs | rows_locked |
+-----------+------+-----------+----------+---------+------+---------------------+--------------+------------------------------------------------------------+-----------+---------------------+---------------+--------------+-------------+
|        11 | root | localhost | test     | Query   |   11 | 2024-01-19 17:08:28 | Sending data | insert into test select null, 'Some data', now() from test | RUNNING   | 2024-01-19 17:08:27 |             3 |        37492 |     3981598 |
+-----------+------+-----------+----------+---------+------+---------------------+--------------+------------------------------------------------------------+-----------+---------------------+---------------+--------------+-------------+

+-----------+------+-----------+----------+---------+------+---------------------+-------------+----------+--------------+---------------------+---------------+--------------+-------------+
| thread_id | user | host      | database | command | time | doing_since         | query_state | query    | trx_state    | trx_started         | tables_locked | lock_structs | rows_locked |
+-----------+------+-----------+----------+---------+------+---------------------+-------------+----------+--------------+---------------------+---------------+--------------+-------------+
|        11 | root | localhost | test     | Query   |   11 | 2024-01-19 17:11:13 | Init        | rollback | ROLLING BACK | 2024-01-19 17:09:43 |             1 |        10333 |     4204636 |
+-----------+------+-----------+----------+---------+------+---------------------+-------------+----------+--------------+---------------------+---------------+--------------+-------------+

+-----------+------+-----------+----------+---------+------+---------------------+--------------+------------------------------------------------+-----------+---------------------+---------------+--------------+-------------+
| thread_id | user | host      | database | command | time | doing_since         | query_state  | query                                          | trx_state | trx_started         | tables_locked | lock_structs | rows_locked |
+-----------+------+-----------+----------+---------+------+---------------------+--------------+------------------------------------------------+-----------+---------------------+---------------+--------------+-------------+
|        11 | root | localhost | test     | Query   |    1 | 2024-01-19 17:12:21 | Sending data | select * from test where id % 7 = 1 for update | RUNNING   | 2024-01-19 17:12:20 |             1 |         6813 |     2772061 |
+-----------+------+-----------+----------+---------+------+---------------------+--------------+------------------------------------------------+-----------+---------------------+---------------+--------------+-------------+

+-----------+------+-----------+----------+---------+------+---------------------+--------------+-------------------------------------+-----------+---------------------+---------------+--------------+-------------+
| thread_id | user | host      | database | command | time | doing_since         | query_state  | query                               | trx_state | trx_started         | tables_locked | lock_structs | rows_locked |
+-----------+------+-----------+----------+---------+------+---------------------+--------------+-------------------------------------+-----------+---------------------+---------------+--------------+-------------+
|        11 | root | localhost | test     | Query   |    1 | 2024-01-19 17:13:56 | Sending data | select * from test where id % 7 = 1 | RUNNING   | 2024-01-19 17:13:56 |             0 |            0 |           0 |
+-----------+------+-----------+----------+---------+------+---------------------+--------------+-------------------------------------+-----------+---------------------+---------------+--------------+-------------+