MySQL Useful Queries
v1.0.0 — extracted from my2html v1.0.25

A curated collection of diagnostic and maintenance SQL queries for MySQL 8.0 / MariaDB 10+. Each block can be copied with a single click. Queries are designed to run in the mysql client or DBeaver. Most queries use performance_schema and information_schema.

MySQL can be extended with plugins (e.g. Optional Features) such as the SYS schema and Performance Schema consumers.

1. Database Info

SELECT version() AS full_version,
       @@hostname AS hostname,
       @@port AS port,
       CASE WHEN substring_index(version(), '.', 2) IN ('8.4', '8.0') THEN 'YES'
            ELSE 'NO'
       END AS recent_lts;

SELECT table_schema AS database_name,
       round(sum(data_length + index_length) / 1048576, 2) AS total_mb
  FROM information_schema.tables
 GROUP BY table_schema
 ORDER BY total_mb DESC;

SELECT variable_value AS uptime_seconds,
       date_format(date_sub(now(), INTERVAL variable_value second),
                   '%Y-%m-%d %T') AS started_at
  FROM performance_schema.global_status
 WHERE variable_name = 'UPTIME';

-- Global caches
SELECT variable_name, variable_value, 'Cache' AS category
  FROM performance_schema.global_variables
 WHERE lower(variable_name) IN (
    'innodb_buffer_pool_size', 'query_cache_size',
    'innodb_log_file_size', 'innodb_log_buffer_size',
    'key_buffer_size', 'table_open_cache', 'tmp_table_size',
    'max_heap_table_size')
UNION ALL
-- Tuning and timeouts
SELECT variable_name, variable_value, 'Tuning'
  FROM performance_schema.global_variables
 WHERE lower(variable_name) IN (
    'innodb_flush_log_at_trx_commit', 'innodb_lock_wait_timeout',
    'innodb_thread_concurrency', 'wait_timeout', 'long_query_time',
    'sync_binlog', 'max_connections', 'slow_query_log')
UNION ALL
-- Client cache
SELECT variable_name, variable_value, 'Client Cache'
  FROM performance_schema.global_variables
 WHERE lower(variable_name) IN (
    'binlog_cache_size', 'sort_buffer_size', 'join_buffer_size',
    'read_buffer_size', 'read_rnd_buffer_size', 'thread_stack')
 ORDER BY category, variable_name;

SELECT variable_name, variable_value
  FROM performance_schema.global_variables
 WHERE variable_name LIKE 'character_set_%'
    OR variable_name LIKE 'collation_%'
 ORDER BY variable_name;

SELECT schema_name, default_character_set_name, default_collation_name
  FROM information_schema.schemata
 WHERE schema_name NOT IN ('mysql', 'information_schema', 'sys',
                          'performance_schema')
 ORDER BY schema_name;

2. Schema Objects

SELECT sk AS schema_name,
       sum(IF(otype = 'T', 1, 0)) AS tables,
       sum(IF(otype = 'I', 1, 0)) AS indexes,
       sum(IF(otype = 'R', 1, 0)) AS routines,
       sum(IF(otype = 'E', 1, 0)) AS triggers,
       sum(IF(otype = 'V', 1, 0)) AS views,
       sum(IF(otype = 'P', 1, 0)) AS primary_keys,
       sum(IF(otype = 'F', 1, 0)) AS foreign_keys,
       count(*) AS total
  FROM (
  SELECT 'T' AS otype, table_schema AS sk, table_name AS name
  FROM information_schema.tables
  UNION ALL
  SELECT 'I', constraint_schema, concat(table_name, '.', constraint_name)
  FROM information_schema.key_column_usage
  WHERE ordinal_position = 1
  UNION ALL
  SELECT 'R', routine_schema, routine_name
  FROM information_schema.routines
  UNION ALL
  SELECT 'E', trigger_schema, trigger_name
  FROM information_schema.triggers
  UNION ALL
  SELECT 'V', table_schema, table_name
  FROM information_schema.views
  UNION ALL
  SELECT DISTINCT 'P', constraint_schema, table_name
  FROM information_schema.key_column_usage
  WHERE constraint_name = 'PRIMARY'
  UNION ALL
  SELECT DISTINCT 'F', constraint_schema, concat(table_name, '-', constraint_name)
  FROM information_schema.key_column_usage
  WHERE referenced_table_name IS NOT NULL
) a
 GROUP BY sk WITH ROLLUP;

SELECT table_schema, data_type, count(*) AS column_count
  FROM information_schema.columns
 WHERE table_schema NOT IN ('mysql', 'performance_schema',
                           'information_schema', 'sys')
 GROUP BY table_schema, data_type
 ORDER BY table_schema, data_type;

SELECT routine_schema, routine_type, count(*) AS objects
  FROM information_schema.routines
 GROUP BY routine_schema, routine_type
 ORDER BY routine_schema, routine_type;

-- Tables using reserved keywords
SELECT table_schema, table_name
  FROM information_schema.columns
 WHERE table_name IN ('SELECT', 'INSERT', 'DELETE', 'UPDATE', 'CREATE',
                     'DROP', 'ALTER', 'FROM', 'WHERE', 'ORDER', 'GROUP',
                     'HAVING', 'TABLE', 'INDEX', 'VIEW', 'KEY', 'PRIMARY',
                     'FOREIGN', 'UNION', 'JOIN', 'LEFT', 'RIGHT', 'INNER',
                     'OUTER', 'NULL', 'LIKE', 'BETWEEN', 'EXISTS', 'IN',
                     'NOT', 'AND', 'OR', 'IS', 'ALL', 'ANY', 'DISTINCT',
                     'ASC', 'DESC', 'BOTH', 'LEADING', 'TRAILING',
                     'CURRENT_DATE', 'CURRENT_TIME', 'CURRENT_TIMESTAMP')
 ORDER BY table_schema, table_name;

-- Columns using reserved keywords
SELECT table_schema, table_name, column_name
  FROM information_schema.columns
 WHERE column_name IN ('SELECT', 'INSERT', 'DELETE', 'UPDATE', 'CREATE',
                      'DROP', 'ALTER', 'FROM', 'WHERE', 'ORDER', 'GROUP',
                      'HAVING', 'TABLE', 'INDEX', 'VIEW', 'KEY', 'PRIMARY',
                      'FOREIGN', 'UNION', 'JOIN', 'LEFT', 'RIGHT')
 ORDER BY table_schema, table_name;

3. Space Usage

SELECT table_schema,
       format(sum(table_rows), 0) AS rows,
       format(sum(data_length), 0) AS data_size,
       format(sum(index_length), 0) AS index_size,
       format(sum(data_free), 0) AS free_bytes,
       format(sum(data_length + index_length), 0) AS total_size
  FROM information_schema.tables
 GROUP BY table_schema WITH ROLLUP;

SELECT table_schema, table_name, engine,
       format(data_length + index_length, 0) AS total_bytes,
       format(table_rows, 0) AS est_rows
  FROM information_schema.tables
 ORDER BY data_length + index_length DESC
LIMIT 32;

SELECT substring_index(name, '/', 1) AS schema_name,
       format(sum(file_size), 0) AS total_os_bytes
  FROM information_schema.innodb_tablespaces
 GROUP BY substring_index(name, '/', 1) WITH ROLLUP;

-- Global caches total
SELECT 'Global Caches' AS component,
       format(sum(variable_value) / 1048576, 0) AS size_mb
  FROM performance_schema.global_variables
 WHERE lower(variable_name) IN (
    'innodb_buffer_pool_size', 'query_cache_size',
    'innodb_log_file_size', 'innodb_log_buffer_size',
    'key_buffer_size', 'table_open_cache', 'tmp_table_size')
UNION ALL
-- Performance schema memory
SELECT 'Performance Schema',
       sys.format_bytes(total_allocated) AS size
  FROM sys.memory_global_total;

4. Sessions

SELECT user, count(*) AS sessions
  FROM information_schema.processlist
 GROUP BY user
 ORDER BY sessions DESC;

SELECT user, db, count(*) AS sessions
  FROM information_schema.processlist
 GROUP BY user, db
 ORDER BY sessions DESC;

SELECT substring_index(host, ':', 1) AS host_ip,
       count(*) AS connections
  FROM information_schema.processlist
 GROUP BY host_ip
 ORDER BY connections DESC;

SELECT id, user, host, db, command, time, state, info
  FROM information_schema.processlist
 ORDER BY id;

SELECT id, user, host, db, time, state,
       substr(replace(replace(info, '<', '<'), '>', '>'), 1, 2024) AS query
  FROM information_schema.processlist
 WHERE command <> 'Sleep'
 ORDER BY id;

SELECT 'TCP' AS connection_type, variable_value AS count
  FROM performance_schema.global_status
 WHERE variable_name = 'THREADS_CONNECTED'
UNION ALL
SELECT 'Running', variable_value
  FROM performance_schema.global_status
 WHERE variable_name = 'THREADS_RUNNING';

5. Locks

SELECT trx_mysql_thread_id, trx_id, trx_state,
       trx_started, trx_weight, trx_requested_lock_id,
       trx_query, trx_operation_state, trx_isolation_level
  FROM information_schema.innodb_trx;

SELECT requesting_engine_transaction_id AS blocked_trx,
       requesting_engine_lock_id AS blocked_lock,
       blocking_engine_transaction_id AS blocking_trx,
       blocking_engine_lock_id AS blocking_lock
  FROM performance_schema.data_lock_waits;

SELECT engine_transaction_id, engine_lock_id,
       lock_mode, lock_type, lock_status, lock_data
  FROM performance_schema.data_locks;

SELECT object_type, object_schema, object_name,
       count_star, sum_timer_wait,
       sec_to_time(sum_timer_wait / 1000000000000) AS human_timer
  FROM performance_schema.table_lock_waits_summary_by_table
 WHERE count_star > 0
 ORDER BY sum_timer_wait DESC
LIMIT 10;

6. Users & Security

SELECT user, host,
       concat(Select_priv, Lock_tables_priv) AS selock,
       concat(Insert_priv, Update_priv, Delete_priv,
              Create_priv, Drop_priv) AS modif,
       concat(Grant_priv, References_priv, Index_priv,
              Alter_priv) AS meta,
       concat(Super_priv, Shutdown_priv, Process_priv,
              File_priv, Show_db_priv, Reload_priv) AS admin
  FROM mysql.user
 ORDER BY user, host;

-- Empty passwords
SELECT user, host, 'Empty password' AS note
  FROM mysql.user
 WHERE authentication_string = ''
   AND (account_locked <> 'Y' OR password_expired <> 'Y')
UNION ALL
-- Password equals username
SELECT user, host, authentication_string, 'Same as username'
  FROM mysql.user
 WHERE authentication_string = upper(concat('*', cast(sha1(unhex(sha1(user))) AS char)))
    OR authentication_string = upper(concat('*', cast(sha2(unhex(sha2(user, 256)), 256) AS char)))
UNION ALL
-- Old pre-4.1 password format
SELECT user, host, authentication_string, 'Old pre-4.1 format'
  FROM mysql.user
 WHERE authentication_string NOT LIKE '*%'
   AND authentication_string NOT LIKE '$%'
   AND authentication_string <> '';

SELECT user, host, db, select_priv, execute_priv, grant_priv
  FROM mysql.db
 ORDER BY user, host;

SELECT DISTINCT u.user AS role_name,
       IF(r.from_user IS NULL, 0, 1) AS has_assignee
  FROM mysql.user u
LEFT JOIN mysql.role_edges r ON r.from_user = u.user
 WHERE u.account_locked = 'Y'
   AND u.password_expired = 'Y'
   AND u.authentication_string = '';

SELECT schema_name, digest_text, count_star
  FROM performance_schema.events_statements_summary_by_digest
 WHERE digest_text LIKE '% OR %? = ?%'
    OR digest_text LIKE '%mysql.user%'
LIMIT 20;

7. Performance Statistics

-- MyISAM Read Hit Ratio
SELECT 'MyISAM Read Hit Ratio' AS metric,
       format(100 - t1.variable_value * 100 / t2.variable_value, 2) AS value
  FROM performance_schema.global_status t1,
     performance_schema.global_status t2
 WHERE t1.variable_name = 'KEY_READS'
   AND t2.variable_name = 'KEY_READ_REQUESTS'
UNION ALL
-- InnoDB Read Hit Ratio
SELECT 'InnoDB Read Hit Ratio',
       format(100 - t1.variable_value * 100 / t2.variable_value, 2)
  FROM performance_schema.global_status t1,
     performance_schema.global_status t2
 WHERE t1.variable_name = 'INNODB_BUFFER_POOL_READS'
   AND t2.variable_name = 'INNODB_BUFFER_POOL_READ_REQUESTS'
UNION ALL
-- MyISAM Write Hit Ratio
SELECT 'MyISAM Write Hit Ratio',
       format(100 - t1.variable_value * 100 / t2.variable_value, 2)
  FROM performance_schema.global_status t1,
     performance_schema.global_status t2
 WHERE t1.variable_name = 'KEY_WRITES'
   AND t2.variable_name = 'KEY_WRITE_REQUESTS'
UNION ALL
-- InnoDB Log Write Ratio
SELECT 'InnoDB Log Write Ratio',
       format(100 - t1.variable_value * 100 / t2.variable_value, 2)
  FROM performance_schema.global_status t1,
     performance_schema.global_status t2
 WHERE t1.variable_name = 'INNODB_LOG_WRITES'
   AND t2.variable_name = 'INNODB_LOG_WRITE_REQUESTS';

SELECT 'Questions/sec' AS metric,
       format(g1.variable_value / g2.variable_value, 5) AS value
  FROM performance_schema.global_status g1,
     performance_schema.global_status g2
 WHERE g1.variable_name = 'QUESTIONS'
   AND g2.variable_name = 'UPTIME'
UNION ALL
SELECT 'SELECT/sec',
       format(g1.count_star / g2.variable_value, 5)
  FROM performance_schema.events_statements_summary_global_by_event_name g1,
     performance_schema.global_status g2
 WHERE g1.event_name = 'statement/sql/select'
   AND g2.variable_name = 'UPTIME'
UNION ALL
SELECT 'COMMIT/sec (TPS)',
       format(g1.count_star / g2.variable_value, 5)
  FROM performance_schema.events_statements_summary_global_by_event_name g1,
     performance_schema.global_status g2
 WHERE g1.event_name = 'statement/sql/commit'
   AND g2.variable_name = 'UPTIME'
UNION ALL
SELECT 'Connections/sec',
       format(g1.variable_value / g2.variable_value, 5)
  FROM performance_schema.global_status g1,
     performance_schema.global_status g2
 WHERE g1.variable_name = 'CONNECTIONS'
   AND g2.variable_name = 'UPTIME'
UNION ALL
SELECT 'Bytes sent/sec',
       format(g1.variable_value / g2.variable_value, 5)
  FROM performance_schema.global_status g1,
     performance_schema.global_status g2
 WHERE g1.variable_name = 'BYTES_SENT'
   AND g2.variable_name = 'UPTIME';

SELECT schema_name, digest_text,
       count_star, sum_timer_wait,
       sec_to_time(sum_timer_wait / 1000000000000) AS human_timer,
       round(avg_timer_wait / 1000000000000, 3) AS avg_sec,
       sum_rows_affected, sum_rows_sent, sum_rows_examined
  FROM performance_schema.events_statements_summary_by_digest
 ORDER BY sum_timer_wait DESC
LIMIT 50;

SELECT schema_name, digest_text,
       count_star, sum_timer_wait,
       sec_to_time(avg_timer_wait / 1000000000000) AS avg_human,
       round(avg_timer_wait / 1000000000000, 3) AS avg_sec,
       sum_rows_examined, sum_no_index_used
  FROM performance_schema.events_statements_summary_by_digest
 ORDER BY avg_timer_wait DESC
LIMIT 20;

SELECT event_name, count_star, sum_timer_wait,
       sec_to_time(sum_timer_wait / 1000000000000) AS human_timer
  FROM performance_schema.events_waits_summary_global_by_event_name
 WHERE count_star > 0
   AND event_name != 'idle'
 ORDER BY sum_timer_wait DESC
LIMIT 10;

SELECT pool_id, pool_size, free_buffers, database_pages,
       old_database_pages, modified_database_pages,
       number_pages_read, number_pages_created,
       number_pages_written, hit_rate
  FROM information_schema.innodb_buffer_pool_stats;

SELECT event_name, count_star, sum_timer_wait,
       sec_to_time(sum_timer_wait / 1000000000000) AS human_timer
  FROM performance_schema.file_summary_by_event_name
 ORDER BY sum_timer_wait DESC
LIMIT 10;

SELECT 'OPENED_TABLES #/hour' AS metric,
       format((g1.variable_value * 60 * 60) / g2.variable_value, 5) AS value,
       'Increase TABLE_OPEN_CACHE' AS action
  FROM performance_schema.global_status g1, performance_schema.global_status g2
 WHERE g1.variable_name = 'OPENED_TABLES'
   AND g2.variable_name = 'UPTIME'
   AND (g1.variable_value * 60 * 60) / g2.variable_value > 12
UNION ALL
SELECT 'SORT_MERGE_PASSES #/hour',
       format((g1.variable_value * 60 * 60) / g2.variable_value, 5),
       'Increase SORT_BUFFER_SIZE'
  FROM performance_schema.global_status g1, performance_schema.global_status g2
 WHERE g1.variable_name = 'SORT_MERGE_PASSES'
   AND g2.variable_name = 'UPTIME'
   AND (g1.variable_value * 60 * 60) / g2.variable_value > 12
UNION ALL
SELECT 'CREATED_TMP_DISK_TABLES %',
       format(g1.variable_value * 100 / g2.variable_value, 5),
       'Increase MAX_HEAP_TABLE_SIZE and TMP_TABLE_SIZE'
  FROM performance_schema.global_status g1, performance_schema.global_status g2
 WHERE g1.variable_name = 'CREATED_TMP_DISK_TABLES'
   AND g2.variable_name = 'CREATED_TMP_TABLES'
   AND g1.variable_value / g2.variable_value > 0.1
UNION ALL
SELECT 'BINLOG_CACHE_DISK_USE %',
       format(g1.variable_value * 100 / g2.variable_value, 5),
       'Increase BINLOG_CACHE_SIZE'
  FROM performance_schema.global_status g1, performance_schema.global_status g2
 WHERE g1.variable_name = 'BINLOG_CACHE_DISK_USE'
   AND g2.variable_name = 'BINLOG_CACHE_USE'
   AND g1.variable_value / g2.variable_value > 0.2
UNION ALL
SELECT 'INNODB_LOG_WAITS #/hour',
       format((g1.variable_value * 60 * 60) / g2.variable_value, 5),
       'Increase INNODB_LOG_BUFFER_SIZE'
  FROM performance_schema.global_status g1, performance_schema.global_status g2
 WHERE g1.variable_name = 'INNODB_LOG_WAITS'
   AND g2.variable_name = 'UPTIME'
   AND (g1.variable_value * 60 * 60) / g2.variable_value > 1;

8. Table Statistics

SELECT t.table_schema, t.table_name, t.engine, t.table_rows
  FROM information_schema.tables t
LEFT JOIN (
    SELECT table_schema, table_name
    FROM information_schema.statistics
    GROUP BY table_schema, table_name, index_name
    HAVING sum(case when non_unique = 0 and nullable != 'YES'
                    then 1 else 0 end) = count(*)
) puks ON t.table_schema = puks.table_schema
      AND t.table_name = puks.table_name
 WHERE t.table_schema NOT IN ('performance_schema', 'information_schema',
                             'mysql', 'sys')
   AND t.table_rows > 100
   AND t.table_type = 'BASE TABLE'
   AND puks.table_name IS NULL
 ORDER BY t.table_schema, t.table_name;

SELECT concat(table_schema, '.', table_name) AS table_name,
       engine, table_rows,
       round((index_length + data_length) / 1048576, 2) AS size_mb
  FROM information_schema.tables
 WHERE engine != 'InnoDB'
   AND table_schema NOT IN ('information_schema', 'mysql',
                           'performance_schema')
 ORDER BY table_schema, table_name;

9. Index Statistics

SELECT index_type,
       if(non_unique, 'Not Unique', 'UNIQUE') AS uniqueness,
       round(avg(seq_in_index), 2) AS avg_keys,
       max(seq_in_index) AS max_keys,
       count(DISTINCT concat(table_schema, '.', table_name, '.', index_name)) AS index_count,
       count(*) AS total_columns
  FROM information_schema.statistics
 GROUP BY index_type, non_unique;

SELECT concat(table_schema, '.', table_name) AS table_name,
       index_name,
       if(non_unique, '', 'UNIQUE') AS unique_,
       group_concat(column_name ORDER BY seq_in_index ASC separator ', ') AS columns
  FROM information_schema.statistics
 GROUP BY table_schema, table_name, index_name, non_unique
 ORDER BY table_schema, table_name, index_name;

SELECT t.table_schema, t.table_name, t.engine, t.table_rows
  FROM information_schema.tables t
JOIN information_schema.columns c
  ON t.table_schema = c.table_schema AND t.table_name = c.table_name
 WHERE t.table_schema NOT IN ('performance_schema', 'information_schema',
                             'mysql', 'sys')
   AND t.table_rows > 100
   AND t.table_type = 'BASE TABLE'
 GROUP BY t.table_schema, t.table_name, t.engine, t.table_rows
HAVING sum(if(c.column_key IN ('PRI', 'UNI'), 1, 0)) = 0
 ORDER BY t.table_schema, t.table_rows DESC;

10. Partitioning

SELECT table_schema, count(DISTINCT table_name) AS partitioned_tables,
       count(*) AS total_partitions
  FROM information_schema.partitions
 WHERE partition_name IS NOT NULL
 GROUP BY table_schema
 ORDER BY table_schema;

SELECT table_schema, table_name,
       partition_method, subpartition_method,
       count(DISTINCT partition_name) AS partitions,
       count(DISTINCT subpartition_name) AS subpartitions,
       min(partition_name) AS from_partition,
       max(partition_name) AS to_partition,
       sum(table_rows) AS est_rows,
       sum(data_length + index_length) AS total_size
  FROM information_schema.partitions
 WHERE partition_name IS NOT NULL
 GROUP BY table_schema, table_name, partition_method, subpartition_method
 ORDER BY table_schema, table_name;

SELECT (max(variable_value) - min(variable_value)) / 1048576 AS grow_mb,
       datediff(max(timest), min(timest)) AS period_days,
       (max(variable_value) - min(variable_value)) / 1048576
         * 30 / datediff(max(timest), min(timest)) AS mb_per_month
  FROM my2.status
 WHERE variable_name = 'SIZEDB.TOTAL';

11. Replication & Backup

SELECT channel_name, host, port, user,
       auto_position, ssl_allowed, heartbeat_interval
  FROM performance_schema.replication_connection_configuration;

SELECT channel_name, group_name, source_uuid, thread_id,
       service_state, count_received_heartbeats,
       last_heartbeat_timestamp, received_transaction_set,
       last_error_number, last_error_message, last_error_timestamp
  FROM performance_schema.replication_connection_status;

SELECT channel_name, thread_id, service_state,
       last_error_number, last_error_message, last_error_timestamp
  FROM performance_schema.replication_applier_status_by_coordinator;

SELECT channel_name, worker_id, thread_id, service_state,
       last_applied_transaction,
       last_error_number, last_error_message, last_error_timestamp
  FROM performance_schema.replication_applier_status_by_worker;

SELECT member_host, member_port, member_id, member_state
  FROM performance_schema.replication_group_members;

SELECT member_host, member_port, member_id, member_state
  FROM performance_schema.replication_group_member_stats;

SHOW BINARY LOG STATUS;
SHOW BINARY LOGS;

SELECT variable_name, variable_value
  FROM performance_schema.global_variables
 WHERE variable_name LIKE '%gtid%'
    OR variable_name = 'server_uuid'
 ORDER BY variable_name;

12. Environment

SELECT engine, support, comment
  FROM information_schema.engines
 ORDER BY support;

SELECT variable_name, variable_value
  FROM performance_schema.global_variables
 ORDER BY variable_name;

SELECT plugin_name, plugin_version, plugin_status, plugin_type
  FROM information_schema.plugins
 ORDER BY plugin_name;

SELECT host, current_connections, total_connections
  FROM performance_schema.hosts
 ORDER BY current_connections DESC, total_connections DESC;

SELECT count(DISTINCT host) AS total_hosts,
       sum(current_connections) AS total_current,
       sum(total_connections) AS total_all_time
  FROM performance_schema.hosts;

SELECT host, ip, host_validated,
       sum_connect_errors,
       first_seen, last_seen, last_error_seen,
       count_handshake_errors, count_authentication_errors,
       count_host_acl_errors
  FROM performance_schema.host_cache;

SELECT @@global.max_connect_errors AS max_connect_errors;

13. Optional Features

MySQL offers additional features through the Performance Schema, SYS schema, and storage engine plugins.

SELECT name, enabled
  FROM performance_schema.setup_consumers
 ORDER BY enabled, name;

SELECT event_name, count_alloc, count_free,
       sys.format_bytes(sum_number_of_bytes_alloc) AS total_alloc,
       sys.format_bytes(sum_number_of_bytes_free) AS total_free,
       sys.format_bytes(current_number_of_bytes_used) AS current_used,
       sys.format_bytes(high_number_of_bytes_used) AS max_used
  FROM performance_schema.memory_summary_global_by_event_name
 WHERE current_number_of_bytes_used > 5000000
 ORDER BY current_number_of_bytes_used DESC;

SELECT * FROM sys.user_summary;
SELECT * FROM sys.host_summary;
SELECT * FROM sys.memory_global_by_current_bytes;
SELECT * FROM sys.session_ssl_status;

SELECT table_schema, table_name, create_options
  FROM information_schema.tables
 WHERE create_options LIKE '%ENCRYPTION="Y"%';

SELECT table_schema, table_name, table_rows,
       format((data_length + index_length) / 1048576, 0) AS size_mb
  FROM information_schema.tables
 WHERE (table_name LIKE '%comments'
    OR table_name LIKE '%redirection')
   AND table_rows > 1000
 ORDER BY table_rows DESC;

14. Diagnostics

SELECT if(space = 0, 'System', 'FilePerTable') AS tablespace_type,
       row_format,
       count(*) AS tables,
       sum(n_cols - 3) AS columns
  FROM information_schema.innodb_tables
 GROUP BY row_format, if(space = 0, 'System', 'FilePerTable');

SELECT table_id, name, flag, row_format
  FROM information_schema.innodb_tables
 WHERE name LIKE '%/#%'
LIMIT 100;

SELECT variable_name, variable_value
  FROM performance_schema.global_status
 WHERE variable_name IN ('BINLOG_CACHE_USE', 'BINLOG_CACHE_DISK_USE',
                        'INNODB_LOG_WRITES', 'INNODB_LOG_WRITE_REQUESTS',
                        'INNODB_OS_LOG_WRITTEN');

SELECT format(g1.variable_value / (1024 * 1024), 0) AS innodb_log_written_mb,
       format((g1.variable_value * 60 * 60 * 24) / (g2.variable_value * 1024 * 1024), 2) AS daily_binlog_mb
  FROM performance_schema.global_status g1, performance_schema.global_status g2
 WHERE g1.variable_name = 'INNODB_OS_LOG_WRITTEN'
   AND g2.variable_name = 'UPTIME';

SELECT name, processlist_time
  FROM performance_schema.threads
 WHERE name = 'thread/sql/slave_worker'
   AND (processlist_state IS NULL
       OR processlist_state != 'Waiting for an event from Coordinator')
 ORDER BY processlist_time DESC;

-- Source side
SHOW VARIABLES LIKE 'rpl_semi_sync_master_%';
SHOW STATUS LIKE 'rpl_semi_sync_master_status';

-- Replica side
SHOW VARIABLES LIKE 'rpl_semi_sync_slave_enabled';
SHOW STATUS LIKE 'rpl_semi_sync_slave_status';

Generated from my2html v1.0.25 — github.com/meob/db2html