How do I view server activity for an Amazon RDS for MySQL DB instance?
How can I view the server activity for an Amazon Relational Database Service (Amazon RDS) DB instance that is running MySQL?
"How can I view the server activity for an Amazon Relational Database Service (Amazon RDS) DB instance that is running MySQL?ResolutionYou 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\GSHOW ENGINE INNODB STATUS\GNote: 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.0ON r.trx_id = w.requesting_engine_transaction_id;INNER JOIN information_schema.innodb_trx rON b.trx_id = w.blocking_engine_transaction_idINNER JOIN information_schema.innodb_trx bFROM performance_schema.data_lock_waits wb.trx_query blocking_queryb.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 informationMonitoring metrics in an Amazon RDS instanceFollow"