Aurora MySQL vs RDS for MySQL

Measures Aurora storage I/O request rates, buffer pool cache hit ratios, and replica lag across cluster reader instances.

# 1. Audit Aurora buffer pool hit efficiency and read requests
mysql -h cluster-endpoint.rds.amazonaws.com -u dbre_admin -p -e "
SELECT 
  VARIABLE_NAME, VARIABLE_VALUE 
FROM performance_schema.global_status 
WHERE VARIABLE_NAME IN (
  'Innodb_buffer_pool_read_requests',
  'Innodb_buffer_pool_reads',
  'Innodb_buffer_pool_pages_data',
  'Innodb_buffer_pool_pages_total'
);"

# 2. Inspect live Aurora cluster replication lag across reader hosts
mysql -h reader-endpoint.rds.amazonaws.com -u dbre_admin -p -e "
SELECT 
  SERVER_ID, 
  LAST_UPDATE_TIMESTAMP, 
  REPLICA_LAG_IN_MSEC 
FROM information_schema.replica_host_status;"

Identifies thread wait events, synchronous disk fsync latencies, and EBS queue depth saturation on Amazon RDS.

# 1. Audit top wait events and fsync disk latency on primary instance
mysql -h rds-master.rds.amazonaws.com -u dbre_admin -p -e "
SELECT 
  EVENT_NAME, 
  COUNT_STAR, 
  ROUND(SUM_TIMER_WAIT/1000000000, 2) AS wait_ms 
FROM performance_schema.events_waits_summary_global_by_event_name 
WHERE COUNT_STAR > 0 
ORDER BY SUM_TIMER_WAIT DESC 
LIMIT 8;"

# 2. Check active long-running transactions and lock wait chains
mysql -h rds-master.rds.amazonaws.com -u dbre_admin -p -e "
SELECT 
  r.trx_id waiting_trx_id, 
  r.trx_mysql_thread_id waiting_thread,
  b.trx_id blocking_trx_id, 
  b.trx_mysql_thread_id blocking_thread,
  TIMESTAMPDIFF(SECOND, r.trx_wait_started, CURRENT_TIMESTAMP) wait_seconds
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;"