MySQL 5.7 Monitoring and Troubleshooting Guide: Connections, Long Transactions, Metadata Locks, and More

1. Connection Related Shows how to view session‑level parameters and status variables for a specific user connection (e.g., character set settings for connection ID 19) or all connections, helping diagnose connection‑related issues.

2. Long Transactions Identifies sessions that have held a transaction open for more than 5 seconds without committing, which can block DDL operations.

3. Metadata Locks (MDL) Describes enabling MDL tracing in MySQL 5.7 to locate lock‑related blocking problems, and provides examples of killing sessions that hold MDL locks to allow DDL statements to proceed.

4. Lock Waits Explains how to view blocking and blocked threads, including user, IP, and port information, and notes that the innodb_lock_waits table can be queried directly when detailed user data is not needed.

5. Global Read Locks Details the performance_schema.metadata_locks table columns that indicate explicit FTWRL locks and how to kill sessions that added such locks to restore normal operation.

6. Memory Usage Monitoring Notes that only the performance_schema database is monitored by default; enabling global memory statistics requires manual configuration, and provides views for locating memory‑related issues.

7. Partitioned Tables Shows how to list partitioned tables in an instance and retrieve detailed information for a specific partitioned table (e.g., database db, table e).

8. Database Overview Provides queries to summarize the size of each database, the size of tables within a database, and basic table information.

9. Tables Not Updated for a Long Time Indicates that a NULL UPDATE_TIME means the table has not been updated since the instance started.

10. Primary Keys and Indexes Discusses the impact of missing primary keys, unique keys, or secondary indexes on InnoDB performance, replication lag, and full‑table scans.

11. Storage Engine Distribution Shows the distribution of storage engines used by tables and highlights non‑InnoDB tables.

12. Real‑Time Load Provides a snapshot of current load metrics to assess the instance’s performance at a glance.