How do I view server activity for an Amazon RDS for MySQL DB instance?

Resolution

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
b.trx_query blocking_query
b.trx_mysql_thread_id blocking_thread,
b.trx_id blocking_trx_id,
r.trx_query waiting_query,
r.trx_mysql_thread_id waiting_thread,
r.trx_id waiting_trx_id,

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.

Related information

Monitoring metrics in an Amazon RDS instance