SQL Script for Monitoring Query Performance Latency in Amazon RDS for MySQL

Monitoring query performance latency in Amazon RDS for MySQL typically involves capturing and analyzing query execution times. You can use SQL scripts to query the performance schema or the slow query log for this purpose. Here's an example script that you can use to monitor query performance latency:

Script to Monitor Query Performance Latency

This script focuses on the performance_schema tables. It assumes that the performance schema is enabled and configured properly in your RDS instance.

-- Ensure PERFORMANCE_SCHEMA is enabled
SELECT @@performance_schema;

-- Check the status of the Consumer for Statements
SELECT * FROM performance_schema.setup_consumers WHERE NAME LIKE '%statements%';

-- If necessary, enable the consumer for collected statement digests
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME = 'statements_digest';

-- Query to monitor statement performance
SELECT
    schema_name,
    digest_text AS query,
    COUNT_STAR AS exec_count,
    SUM_TIMER_WAIT/1000000000000 AS total_latency_sec,
    AVG_TIMER_WAIT/1000000000000 AS avg_latency_sec,
    MAX_TIMER_WAIT/1000000000000 AS max_latency_sec,
    SUM_LOCK_TIME/1000000000000 AS total_lock_time_sec,
    SUM_ROWS_SENT,
    SUM_ROWS_EXAMINED,
    SUM_ROWS_AFFECTED
FROM
    performance_schema.events_statements_summary_by_digest
WHERE
    schema_name IS NOT NULL -- Filter out NULL schemas
ORDER BY
    total_latency_sec DESC
LIMIT 10; -- Adjust the limit as necessary

Notes

• Performance Schema: This schema provides a way to inspect internal execution of the server at runtime. It's particularly useful for diagnosing performance issues.
• Digests: The script uses statement digests to aggregate similar queries, which is useful for identifying patterns and trends in query performance.
• Metrics: It captures metrics like execution count, total/average/max latency, lock time, and rows affected/sent/examined. These metrics provide a comprehensive view of query performance.
• Customization: You may need to customize the script based on your specific monitoring requirements, such as filtering for certain schemas or queries.
• Performance Impact: Querying the performance schema can have a performance impact, especially on a busy server. Use it judiciously.

Alternative: Slow Query Log

If you prefer to use the slow query log:

1. Enable the slow query log in your RDS instance (can be done via the AWS Management Console or RDS parameter groups).
2. Set an appropriate long query time threshold.
3. Analyze the log using tools like pt-query-digest for detailed insights into slow queries.

AWS CloudWatch Integration

Additionally, for a more holistic approach, integrate AWS CloudWatch with your RDS instance for monitoring and setting alarms on key performance metrics like CPU utilization, database connections, and more. This, combined with SQL-level monitoring, provides a robust solution for performance monitoring.

Also read: https://minervadb.xyz/unveiling-innodbs-secrets-unraveling-read-efficiency-of-select-queries/