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

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 instance.

You must have MySQL PROCESS server administration privileges to see all threads running on a MySQL 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 an instance, follow these steps:

1.    Enable the general and slow query logs for your MySQL 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 page help you? Yes | No

Back to the AWS Support Knowledge Center

Need help? Visit the AWS Support Center.

Published: 2015-07-07

Updated: 2018-09-18