Comprehensive MySQL Inspection Checklist and Command Reference

Operating System Level

The inspection starts with basic OS metrics such as CPU, memory, I/O, and disk usage, followed by system information and log checks.

CPU

sar -u 10 3

Memory

sar -r 10 3

I/O

sar -b 10 3

Disk

df -h

System Information

Check NUMA, swap usage, and other relevant system details.

OS Logs

Review recent log entries for potential issues.

tail 200 /var/log/messages
 dmesg | tail 200

MySQL Checks

Focus on key parameters, MySQL status, table health, replication, and high‑availability aspects.

Important Parameters

Parameter

Recommended Value

innodb_buffer_pool_size

50%‑75% of system memory

binlog_format

ROW

sync_binlog

1

innodb_flush_log_at_trx_commit

1

read_only

ON on replica, OFF on primary

super_read_only

ON on replica, OFF on primary

log_slave_updates

1

innodb_io_capacity

200 (HDD), 2000 (SAS RAID10), 8000 (SSD), 25000‑50000 (Flash)

MySQL Status Commands

\s
show full processlist;
show engine innodb status\G
show slave hosts;

Wait Events

show global status like 'Innodb_buffer_pool_wait_free';
show global status like 'Innodb_log_waits';

Locks

# Table locks
show global status like 'Table_locks_waited';
show global status like 'Table_locks_immediate';
# Row locks
show global status like 'Innodb_row_lock_current_waits';
show global status like 'Innodb_row_lock_time';
show global status like 'Innodb_row_lock_time_avg';
show global status like 'Innodb_row_lock_time_max';
show global status like 'Innodb_row_lock_waits';
# Temporary tables/files
show global status like 'Created_tmp_disk_tables';
show global status like 'Created_tmp_files';
# Open files/tables
show global status like 'Open_files';
show global status like 'Open_table_definitions';
show global status like 'Open_tables';
# Thread statistics
show global status like 'Threads_running';
show global status like 'Threads_created';
show global status like 'Threads_cached';
show global status like 'Aborted_clients';
show global status like 'Aborted_connects';

Binary Log Metrics

# Temporary binary‑log cache usage exceeding binlog_cache_size
binlog_cache_disk_use;
# Transactions using binary‑log cache
binlog_cache_use;
# Non‑transactional statements exceeding binlog_stmt_cache_size
binlog_stmt_cache_disk_use;
# Non‑transactional statements stored in cache
binglog_cache_disk_use;

Connection Metrics

# Connection attempts (successful or not)
show global status like 'Connection';

Temporary Table Metrics

# Disk‑based temporary tables
show global status like 'Created_tmp_disk_tables';
# Memory‑based temporary tables
show global status like 'Created_tmp_files';

Index Metrics

# Commits and rollbacks
show global status like 'Handler_commit';
show global status like 'Handler_rollback';
# Index reads
show global status like 'Handler_read_first';
show global status like 'Handler_read_key';
show global status like 'Handler_read_last';
show global status like 'Handler_read_next';
show global status like 'Handler_read_prev';
show global status like 'Handler_read_rnd';
show global status like 'Handler_read_rnd_next';
# Table and index sizes
show global status like 'Open_table_definitions';
show global status like 'Opened_tables';
show global status like 'Open_tables';
# Query statistics
show global status like 'Queries';
show global status like 'Select_full_join';
show global status like 'Select_scan';
show global status like 'Slow_queries';
show global status like 'Sort_merge_passes';

Thread Statistics

# Threads in cache
show global status like 'Threads_cached';
# Currently connected threads
show global status like 'Threads_connected';
# Created threads for connections
show global status like 'Threads_created';
# Running (non‑sleeping) threads
show global status like 'Threads_running';

Database/Table Health

Auto‑Increment Usage

SELECT table_schema, table_name, ENGINE, Auto_increment
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN ("INFORMATION_SCHEMA","PERFORMANCE_SCHEMA","MYSQL","SYS")
LIMIT 30;

Table Size Statistics

SELECT table_schema "Database name",
       SUM(table_rows) "No. of rows",
       SUM(data_length)/1024/1024 "Size data (MB)",
       SUM(index_length)/1024/1024 "Size index (MB)"
FROM information_schema.TABLES
GROUP BY table_schema;

Top 30 Tables by Row Count

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS
FROM information_schema.tables
WHERE TABLE_SCHEMA NOT IN ('information_schema','sys','mysql','performance_schema')
ORDER BY table_rows DESC
LIMIT 30;

Non‑InnoDB Tables

SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE
FROM INFORMATION_SCHEMA.TABLES
WHERE ENGINE != 'innodb'
  AND TABLE_SCHEMA NOT IN ("INFORMATION_SCHEMA","PERFORMANCE_SCHEMA","MYSQL","SYS");

Tables with Fragmentation (Data Free)

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH, DATA_FREE
FROM information_schema.tables
WHERE DATA_FREE IS NOT NULL
ORDER BY DATA_FREE DESC
LIMIT 30;

Tables Without Primary Key

SELECT t1.table_schema, t1.table_name, t1.table_type
FROM information_schema.TABLES t1
LEFT OUTER JOIN information_schema.TABLE_CONSTRAINTS t2
  ON t1.table_schema = t2.TABLE_SCHEMA
 AND t1.table_name = t2.TABLE_NAME
 AND t2.CONSTRAINT_NAME IN ('PRIMARY')
WHERE t2.table_name IS NULL
  AND t1.TABLE_SCHEMA NOT IN ('information_schema','performance_schema','test','mysql','sys')
  AND t1.table_type = "BASE TABLE";

Replication Health

# Replication status
show slave status\G
# Replication delay check
Master_Log_File == Relay_Master_Log_File && Read_Master_Log_Pos == Exec_Master_Log_Pos

High‑Availability Layer

Common HA solutions include MHA and keepalived. Monitor logs for frequent primary‑replica switches and investigate root causes.

Middleware Inspection

For middleware such as Mycat and ProxySQL, follow the OS‑level checklist, then examine middleware logs, status information, and network latency or packet loss.