You are here
MariaDB and MySQL PERFORMANCE_SCHEMA Hints
Table of Contents
- Accounts not properly closing connections
- Unused indexes
- Who created temporary (disk) tables
- Accounts which never connected since last start-up
- Users which never connected since last start-up
- Totally unused accounts (never connected since last restart and not used to check Stored Program or View privileges) since last start-up
SHOW FULL PROCESSLIST- Storage Engines per schema
- Tables without a Primary Key
- Bad SQL queries of users
SHOW PROFILEinPERFORMANCE_SCHEMASELECT,INSERT,UPDATEandDELETEper table- Top long running queries
- Tables never written to
- DML, DDL and DCL per account
- Finding syntactically wrong/erroneous MySQL queries
- Finding queries served by MySQL Query Cache
- Grant access to
sysschema to another user - Grant access to
PERFORMANCE_SCHEMAschema to another user - Find InnoDB Locks
- Find Metadata Locks
- What did a Connection before sleeping?
- How many statements did another (sleeping) connection?
- MyISAM locking
- Connections per user
- Connections per user and IP
- Accounts which could not connect or with connection errors
- Temporary tables filling my disk
- Who is writing to the binary log
- Average binlog transaction size
- Result size of
SELECTstatement - Find open long running transactions
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_readcan only be taken as argument if a physical backup method (NOTmysqldump) is used.- If
count_writeis 0 it does not necessarily mean that there was no write statement (no matching write)! - If tables are empty
SELECTstatements 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 | +-----------+------+-----------+----------+---------+------+---------------------+--------------+-------------------------------------+-----------+---------------------+---------------+--------------+-------------+