ACCOUNTS NOT PROPERLY CLOSING CONNECTIONS [ 1 ]
Works since 5.6
SELECT ess.user, ess.host , (a.total_connections - a.current_connections) - ess.count_star as not_closed , ((a.total_connections - a.current_connections) - ess.count_star) * 100 / (a.total_connections - a.current_connections) as pct_not_closed FROM performance_schema.events_statements_summary_by_account_by_event_name ess JOIN performance_schema.accounts a on (ess.user = a.user and ess.host = a.host) WHERE ess.event_name = 'statement/com/quit' AND (a.total_connections - a.current_connections) > ess.count_star ;
UNUSED INDEXES [ 2 ]
Works since 5.6
SELECT object_schema, object_name, index_name FROM performance_schema.table_io_waits_summary_by_index_usage WHERE index_name IS NOT NULL AND count_star = 0 ORDER BY object_schema, object_name ;
WHO CREATED TEMPORARY (DISK) TABLES
Works since 5.6
SELECT user, host, event_name, count_star AS cnt, sum_created_tmp_disk_tables AS tmp_disk_tables, sum_created_tmp_tables AS tmp_tables FROM performance_schema.events_statements_summary_by_account_by_event_name WHERE sum_created_tmp_disk_tables > 0 OR sum_created_tmp_tables > 0 ;
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 ;
ACCOUNTS WHICH NEVER CONNECTED SINCE LAST START-UP [ 3 ]
Works since 5.6
SELECT DISTINCT m_u.user, m_u.host FROM mysql.user m_u LEFT JOIN performance_schema.accounts ps_a ON m_u.user = ps_a.user AND m_u.host = ps_a.host WHERE ps_a.user IS NULL ORDER BY m_u.user ;
USERS WHICH NEVER CONNECTED SINCE LAST START-UP
Works since 5.6
SELECT DISTINCT m_u.user FROM mysql.user m_u LEFT JOIN performance_schema.users ps_u ON m_u.user = ps_u.user WHERE ps_u.user IS NULL ORDER BY m_u.user ;
TOTALLY UNUSED ACCOUNTS (NEVER CONNECTED SINCE LAST RESTART AND NOT USED TO CHECK STORED PROGRAM OR VIEW PRIVILEGES) SINCE LAST START-UP
Works since 5.6
SELECT DISTINCT m_u.user, m_u.host FROM mysql.user m_u LEFT JOIN performance_schema.accounts ps_a ON m_u.user = ps_a.user AND ps_a.host = m_u.host LEFT JOIN information_schema.views is_v ON is_v.DEFINER = CONCAT(m_u.User, '@', m_u.Host) AND is_v.security_type = 'DEFINER' LEFT JOIN information_schema.routines is_r ON is_r.DEFINER = CONCAT(m_u.User, '@', m_u.Host) AND is_r.security_type = 'DEFINER' LEFT JOIN information_schema.events is_e ON is_e.definer = CONCAT(m_u.user, '@', m_u.host) LEFT JOIN information_schema.triggers is_t ON is_t.definer = CONCAT(m_u.user, '@', m_u.host) WHERE ps_a.user IS NULL AND is_v.definer IS NULL AND is_r.definer IS NULL AND is_e.definer IS NULL AND is_t.definer IS NULL ORDER BY m_u.user, m_u.host ;
SHOW FULL PROCESSLIST
Works since 5.5 (5.1?)
But with filter on Sleep and sorting by time to find the evil query...
SELECT id, user, host, db, command, time, state, LEFT(info, 80) AS info FROM information_schema.processlist WHERE command NOT IN ('Sleep', 'Binlog Dump') ORDER BY time ASC ;
Non blocking version, since 5.6:
SELECT PROCESSLIST_ID AS id, PROCESSLIST_USER AS user, PROCESSLIST_HOST AS host, PROCESSLIST_DB AS db , PROCESSLIST_COMMAND AS command, PROCESSLIST_TIME AS time, PROCESSLIST_STATE AS state, LEFT(PROCESSLIST_INFO, 80) AS info FROM performance_schema.threads WHERE PROCESSLIST_ID IS NOT NULL AND PROCESSLIST_COMMAND NOT IN ('Sleep', 'Binlog Dump') ORDER BY PROCESSLIST_TIME ASC ;
STORAGE ENGINES PER SCHEMA
For defining backup strategy, preparing migration to InnoDB or Galera Cluster for MySQL, etc.
Works since 5.5 (5.1?)
SELECT table_schema AS `schema`, engine, COUNT(*) AS `tables` , ROUND(SUM(data_length)/1024/1024, 0) AS data_mb, ROUND(SUM(index_length)/1024/1024, 0) index_mb FROM information_schema.tables WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema') AND engine IS NOT NULL GROUP BY table_schema, engine ; +---------------------+--------+--------+---------+----------+ | schema | engine | tables | data_mb | index_mb | +---------------------+--------+--------+---------+----------+ | mantis | MyISAM | 31 | 0 | 0 | | mpm | InnoDB | 3 | 0 | 0 | | mysql_sequences | InnoDB | 2 | 0 | 0 | | mysql_sequences | MEMORY | 1 | 0 | 0 | | otrs | InnoDB | 73 | 13 | 4 | | quartz | InnoDB | 12 | 0 | 0 | | tracking | MyISAM | 1 | 0 | 0 | +---------------------+--------+--------+---------+----------+
TABLES WITHOUT A PRIMARY KEY
Galera Cluster, InnoDB, M/S replication with row based replication does not work well with tables without a Primary Key. To find those the following query helps:
Works since 5.5 (5.1?)
SELECT DISTINCT t.table_schema, t.table_name FROM information_schema.tables AS t LEFT JOIN information_schema.columns AS c ON t.table_schema = c.table_schema AND t.table_name = c.table_name AND c.column_key = "PRI" WHERE t.table_schema NOT IN ('information_schema', 'mysql', 'performance_schema') AND c.table_name IS NULL AND t.table_type != 'VIEW' ; +--------------+--------------------+ | table_schema | table_name | +--------------+--------------------+ | test | t_wo_pk | | test | t_wo_pk_with_Index | +--------------+--------------------+
BAD SQL QUERIES OF USERS
Sometimes it could be interesting to find users who do evil SQL Queries which do for examplecreated_tmp_disk_tables
, select_full_join
, select_range_check
or sort_merge_passes
. Those can be found as follows:
SELECT user, host, event_name , sum_created_tmp_disk_tables AS tmp_disk_tables , sum_select_full_join AS full_join , sum_select_range_check AS range_check , sum_sort_merge_passes AS sort_merge FROM performance_schema.events_statements_summary_by_account_by_event_name WHERE sum_created_tmp_disk_tables > 0 OR sum_select_full_join > 0 OR sum_select_range_check > 0 OR sum_sort_merge_passes > 0 ORDER BY sum_sort_merge_passes DESC LIMIT 10 ; +-------+-------------+---------------+-----------------+-----------+-------------+------------+ | user | host | event_name | tmp_disk_tables | full_join | range_check | sort_merge | +-------+-------------+---------------+-----------------+-----------+-------------+------------+ | user1 | 192.168.0.3 | insert_select | 0 | 7033 | 0 | 10947 | | user2 | 192.168.0.4 | insert_select | 0 | 6837 | 0 | 10792 | | user1 | 192.168.0.1 | select | 10742308 | 2095 | 23061 | 16 | | user2 | 192.168.0.2 | select | 10958067 | 2639 | 23162 | 14 | +-------+-------------+---------------+-----------------+-----------+-------------+------------+
SHOW PROFILE IN PERFORMANCE_SCHEMA
On systems with heavy traffic the PERFORMANCE_SCHEMA tables might be too small.
mysql> SHOW GLOBAL VARIABLES LIKE 'perf%events%stage%hist%long%'; +----------------------------------------------------+-------+ | Variable_name | Value | +----------------------------------------------------+-------+ | performance_schema_events_stages_history_long_size | 10000 | +----------------------------------------------------+-------+ mysql> pager grep history_long mysql> SHOW ENGINE PERFORMANCE_SCHEMA STATUS; | performance_schema | events_waits_history_long.row_size | 184 | | performance_schema | events_waits_history_long.row_count | 1000 | | performance_schema | events_waits_history_long.memory | 184000 | | performance_schema | events_stages_history_long.row_size | 88 | | performance_schema | events_stages_history_long.row_count | 1000 | | performance_schema | events_stages_history_long.memory | 88000 | | performance_schema | events_statements_history_long.row_size | 3024 | | performance_schema | events_statements_history_long.row_count | 1000 | | performance_schema | events_statements_history_long.memory | 3024000 | mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME LIKE '%statement/%'; mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME LIKE '%stage/%'; mysql> UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%events_statements_%'; mysql> UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%events_stages_%'; mysql> SELECT ; mysql> SELECT eshl.event_id AS Query_ID, TRUNCATE(eshl.timer_wait/1000000000000, 6) as Duration , LEFT(eshl.sql_text, 120) AS Query FROM performance_schema.events_statements_history_long AS eshl JOIN performance_schema.threads AS t ON t.thread_id = eshl.thread_id WHERE t.processlist_id = CONNECTION_ID(); +----------+-----------+-------------------------+ | Query_ID | Duration | Query | +----------+-----------+-------------------------+ | 12 | 13.560737 | select * from test.test | +----------+-----------+-------------------------+ mysql> SELECT event_name AS Stage, TRUNCATE(timer_wait/1000000000000,6) AS Duration FROM performance_schema.events_stages_history_long WHERE nesting_event_id = 12; +--------------------------------+-----------+ | Stage | Duration | +--------------------------------+-----------+ | stage/sql/starting | 0.000043 | | stage/sql/checking permissions | 0.000004 | | stage/sql/Opening tables | 0.002700 | | stage/sql/init | 0.000025 | | stage/sql/System lock | 0.000009 | | stage/sql/optimizing | 0.000002 | | stage/sql/statistics | 0.000014 | | stage/sql/preparing | 0.000013 | | stage/sql/executing | 0.000000 | | stage/sql/Sending data | 13.557683 | | stage/sql/end | 0.000002 | | stage/sql/query end | 0.000008 | | stage/sql/closing tables | 0.000006 | | stage/sql/freeing items | 0.000215 | | stage/sql/cleaning up | 0.000001 | +--------------------------------+-----------+
SELECT
, INSERT
, UPDATE
AND DELETE
PER TABLE
Sometimes it is interesting to know how many SELECT
, INSERT
, UPDATE
or DELETE
(DML) statementes have been exectuted against a specifict table (for example for OPTIMZE TABLE
). This can be found as follows:
Works since MySQL 5.6
SELECT object_type, object_schema, object_name , count_star, count_read, count_write, count_fetch , count_insert, count_update, count_delete FROM performance_schema.table_io_waits_summary_by_table WHERE count_star > 0 ; 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 ;
TOP LONG RUNNING QUERIES
Works since MySQL 5.6
UPDATE setup_consumers SET enabled = 1 WHERE name = 'events_statements_history_long'; SELECT left(digest_text, 64) , ROUND(SUM(timer_end-timer_start)/1000000000, 1) AS tot_exec_ms , ROUND(SUM(timer_end-timer_start)/1000000000/COUNT(*), 1) AS avg_exec_ms , ROUND(MIN(timer_end-timer_start)/1000000000, 1) AS min_exec_ms , ROUND(MAX(timer_end-timer_start)/1000000000, 1) AS max_exec_ms , ROUND(SUM(timer_wait)/1000000000, 1) AS tot_wait_ms , ROUND(SUM(timer_wait)/1000000000/COUNT(*), 1) AS avg_wait_ms , ROUND(MIN(timer_wait)/1000000000, 1) AS min_wait_ms , ROUND(MAX(timer_wait)/1000000000, 1) AS max_wait_ms , ROUND(SUM(lock_time)/1000000000, 1) AS tot_lock_ms , ROUND(SUM(lock_time)/1000000000/COUNT(*), 1) AS avglock_ms , ROUND(MIN(lock_time)/1000000000, 1) AS min_lock_ms , ROUND(MAX(lock_time)/1000000000, 1) AS max_lock_ms , MIN(LEFT(DATE_SUB(NOW(), INTERVAL (isgs.VARIABLE_VALUE - TIMER_START*10e-13) second), 19)) AS first_seen , MAX(LEFT(DATE_SUB(NOW(), INTERVAL (isgs.VARIABLE_VALUE - TIMER_START*10e-13) second), 19)) AS last_seen , COUNT(*) as cnt FROM events_statements_history_long JOIN information_schema.global_status AS isgs WHERE isgs.variable_name = 'UPTIME' GROUP BY LEFT(digest_text,64) ORDER BY tot_exec_ms DESC ; +------------------------------------------------------------------+-------------+-------------+-------------+---------------------+---------------------+-----+ | left(digest_text, 64) | tot_exec_ms | tot_wait_ms | tot_lock_ms | first_seen | last_seen | cnt | +------------------------------------------------------------------+-------------+-------------+-------------+---------------------+---------------------+-----+ | INSERT INTO `test` SELECT ? , DATA , ? FROM `test` | 50493.5 | 50493.5 | 26.3 | 2015-11-12 16:41:35 | 2015-11-12 16:42:04 | 20 | | SELECT LEFT ( `digest_text` , ? ) , `ROUND` ( SUM ( `timer_end` | 14434.6 | 14434.6 | 25.8 | 2015-11-12 16:48:44 | 2015-11-12 17:07:15 | 6 | | SELECT * FROM `test` | 7483.0 | 7483.0 | 0.2 | 2015-11-12 16:41:16 | 2015-11-12 16:42:34 | 2 | | SHOW ENGINE INNODB STATUS | 1912.4 | 1912.4 | 0.0 | 2015-11-12 16:37:19 | 2015-11-12 17:07:36 | 687 | | SHOW GLOBAL VARIABLES | 1091.1 | 1091.1 | 68.8 | 2015-11-12 16:37:19 | 2015-11-12 17:07:36 | 687 | | SHOW GLOBAL STATUS | 638.7 | 638.7 | 40.8 | 2015-11-12 16:37:19 | 2015-11-12 17:07:36 | 687 | | SELECT LEFT ( `digest_text` , ? ) , SUM ( `timer_end` - `timer_s | 356.2 | 356.2 | 42.4 | 2015-11-12 16:42:38 | 2015-11-12 16:45:00 | 6 | | SELECT `digest_text` , SUM ( `timer_end` - `timer_start` ) / ? A | 325.3 | 325.3 | 0.4 | 2015-11-12 16:40:44 | 2015-11-12 16:42:18 | 3 | | SELECT `DIGEST_TEXT` , ( `TIMER_END` - `TIMER_START` ) / ? AS `e | 163.2 | 163.2 | 1.0 | 2015-11-12 16:37:44 | 2015-11-12 16:39:22 | 9 | | SELECT LOWER ( REPLACE ( trx_state , ?, ... ) ) AS state , COUNT | 133.9 | 133.9 | 80.2 | 2015-11-12 16:37:19 | 2015-11-12 17:07:36 | 687 | +------------------------------------------------------------------+-------------+-------------+-------------+---------------------+---------------------+-----+
TABLES NEVER WRITTEN TO
If you want to find tables which it was never written to (or read from) since last instance restart you can use the following query. Works since MySQL 5.6
Caution:
count_read
can only be taken as argument if a physical backup method (NOTmysqldump
) is used.- If
count_write
is 0 it does not necessarily mean that there was no write statement (no matching write)! - If tables are empty
SELECT
statements are not counted (count_read
= 0).
SELECT t.table_schema, t.table_name, t.table_rows, tio.count_read, tio.count_write FROM information_schema.tables AS t JOIN performance_schema.table_io_waits_summary_by_table AS tio ON tio.object_schema = t.table_schema AND tio.object_name = t.table_name WHERE t.table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys') AND tio.count_write = 0 ORDER BY t.table_schema, t.table_name ;
http://www.fromdual.com/mysql-performance-schema-hints