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

Last updated: 2019-12-13

How can I view the server activity for an Amazon Relational Database Service (Amazon RDS) DB instance that is running MySQL?

Resolution

You can use server activity to help identify the source of performance issues by viewing the state of the InnoDB storage engine, identifying running queries, or finding 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 administration privileges to use SHOW ENGINE and to view information about the state of the InnoDB storage engine.

To view the server activity for a DB instance, follow these steps:

1.    Enable the general and slow query logs for your MySQL DB instance.

2.    Connect to the DB instance running the MySQL database engine.

3.    Run the following 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.    To see which transactions are waiting and which transactions are blocking the transactions that are waiting:

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;

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, which can help you troubleshoot problems with performance.


Did this article help you?

Anything we could improve?


Need more help?