How can I view the server activity for an Amazon Relational Database Service (Amazon RDS) DB instance that is running MySQL?
You can use server activity to help identify the source of performance issues. You can review the state of the InnoDB storage engine, identify running queries, or find deadlocks on the DB instance.
You must have MySQL PROCESS server administration privileges to see all the threads running on a MySQL DB instance. If you don't have admin privileges, SHOW PROCESSLIST shows only the threads associated with the MySQL account that you're using. You must also have MySQL PROCESS server admin privileges to use SHOW ENGINE. And, you need MYSQL PROCESS server admin privileges to view information about the state of the InnoDB storage engine.
To view the server activity for a DB instance, follow these steps:
1. Turn on the general and slow query logs for your MySQL DB instance.
2. Connect to the DB instance running the MySQL database engine.
3. Run these commands:
SHOW FULL PROCESSLIST\G
SHOW ENGINE INNODB STATUS\G
Note: To view more than the first 100 characters of each statement, use the FULL keyword.
4. Check which transactions are waiting and which transactions are blocking the transactions that are waiting. Run one of these commands depending on the version of Amazon RDS for MySQL you're running:
For versions 5.6 and 5.7:
SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;
For version 8.0
ON r.trx_id = w.requesting_engine_transaction_id;INNER JOIN information_schema.innodb_trx r
ON b.trx_id = w.blocking_engine_transaction_id
INNER JOIN information_schema.innodb_trx b
FROM performance_schema.data_lock_waits w
Note: It's a best practice to gather these outputs at short, consecutive intervals (for example, 60 seconds). Reviewing multiple outputs can provide a broader view of the state of the engine. This helps you troubleshoot problems with performance.
Monitoring metrics in an Amazon RDS instance