RDS Performance Insights

RDS Performance Insights is a managed database performance monitoring and tuning feature available for Amazon RDS (including Aurora, MySQL, MariaDB, PostgreSQL, Oracle, and SQL Server) and Amazon Aurora. It provides an easy-to-understand visual dashboard that helps you quickly assess the load on your database and determine when and where the load is coming from. The primary metric is Database Load (DB Load), which is measured in Average Active Sessions (AAS). DB Load represents the number of active sessions (queries or connections) that are currently being processed by the database engine. A higher DB Load indicates more contention and potential performance issues.

Traditional RDS monitoring (CloudWatch metrics) gives you aggregate metrics like CPU utilization, DatabaseConnections, and Read/Write IOPS. These metrics show you *what* is happening but not *why*. For example, a spike in CPU utilization could be caused by a single expensive query, a lock contention, or a burst of connections. Performance Insights fills this gap by providing per-second granularity of DB Load and breaking it down by: - Wait events (e.g., CPU, IO, lock, latch, network) - SQL queries (top SQL by load) - Hosts (client IP addresses) - Users (database users)

This breakdown allows you to drill down to the root cause of a performance issue within minutes.

Performance Insights is built on the RDS Performance Insights Agent, which runs on the database host (for RDS) or within the Aurora cluster. The agent collects wait event statistics from the database engine at high frequency (up to once per second) and aggregates them into a time-series dataset. For example, in PostgreSQL, it queries pg_stat_activity and pg_wait_events; in MySQL, it reads performance_schema; for Oracle, it uses V$ views. The agent normalizes these wait events into a common set of categories (like CPU, IO, Lock, etc.) and stores the data in a circular buffer that retains the last hour of data at no extra cost. For longer retention (up to 2 years), you pay per GB-month of data stored.

- DB Load (Average Active Sessions): The primary metric. A value of 1.0 means on average one session is active. If your DB load consistently exceeds the number of vCPUs, your database is likely overloaded. - Wait Events: Categorized into: - CPU: The session is actively using CPU. - IO: The session is waiting for disk or network I/O. - Lock: The session is waiting for a database lock (row, table, or transaction). - Latch: The session is waiting for a low-level internal lock (e.g., memory structure). - Network: The session is waiting for data transfer over the network. - Other: Miscellaneous waits. - Top SQL: Shows the SQL queries contributing the most to DB Load. You can see the full query text, load contribution, and execution plan (if available). - Retention Periods: - Free tier: Last hour of data (7 days of aggregated data at 1-hour granularity). - Paid tier: 7 days, 1 month, 2 months, 3 months, 6 months, 12 months, or 24 months. Pricing is per GB-month of data stored. - Performance Insights API: You can programmatically retrieve data using the GetResourceMetrics, GetResourceMetadata, and ListAvailableResourceDimensions API calls.

To enable Performance Insights on an existing RDS instance, you can use the AWS Management Console, CLI, or SDK. The CLI command to modify an existing DB instance:

aws rds modify-db-instance \
    --db-instance-identifier mydb \
    --enable-performance-insights \
    --performance-insights-retention-period 7 \
    --performance-insights-kms-key-id my-kms-key-id

To enable during creation:

aws rds create-db-instance \
    --db-instance-identifier mydb \
    --engine mysql \
    --enable-performance-insights \
    --performance-insights-retention-period 7

To verify the status:

aws rds describe-db-instances --db-instance-identifier mydb --query 'DBInstances[0].PerformanceInsightsEnabled'

You can also use the Performance Insights dashboard in the RDS console to view the data. The dashboard typically shows a line chart of DB Load over time, with color-coded areas for different wait event categories. Below the chart, you see a table of top SQL queries, hosts, or users.

Performance Insights works alongside other monitoring tools: - CloudWatch Metrics: Performance Insights does not replace CloudWatch; it complements it. CloudWatch gives you aggregate metrics (like CPUUtilization, FreeableMemory), while Performance Insights gives you session-level granularity. - RDS Enhanced Monitoring: Enhanced Monitoring provides OS-level metrics (CPU, memory, disk I/O, network) for the RDS host. Performance Insights focuses on database engine internals. Together, they provide a full stack view. - RDS Event Notifications: You can set up CloudWatch alarms on DB Load or other metrics to trigger automated actions (e.g., scale up). - AWS Lambda: You can integrate Performance Insights with Lambda for automated remediation (e.g., kill a runaway query).

A large e-commerce company runs its product catalog database on RDS MySQL. During Black Friday, they observe increased page load times and checkout failures. CloudWatch shows high CPU utilization (90%+), but they cannot pinpoint the cause. They enable Performance Insights and see that DB Load is 12 AAS on a 4-vCPU instance, with 80% of the load from CPU wait events. The Top SQL tab reveals a single query SELECT * FROM products WHERE category_id = ? ORDER BY price DESC is consuming 60% of the load. This query lacks an index on category_id, causing a full table scan. The DBA adds an index, and the DB Load drops to 4 AAS. Performance Insights helped them resolve the issue in minutes instead of hours. In production, they also set up a CloudWatch alarm on DB Load > 8 to automatically notify the on-call engineer. The key lesson: Performance Insights is invaluable for real-time troubleshooting during traffic spikes.

A SaaS provider uses RDS PostgreSQL for a multi-tenant application. They notice that one tenant's workload is causing performance degradation for all tenants. They use Performance Insights to filter by host (tenant IP) and see that a specific tenant is running a heavy reporting query every hour. The query causes lock waits on a shared table, blocking other tenants. They identify the query and work with the tenant to schedule it during off-peak hours. Additionally, they use Performance Insights to set a baseline of normal DB Load (e.g., 2 AAS) and alert when it exceeds 5 AAS. This proactive monitoring helps them maintain SLAs. Common mistake: Not enabling Performance Insights on all instances due to cost concerns, but the free tier provides the last hour of data, which is often sufficient for troubleshooting.

A company migrates from Oracle to Aurora PostgreSQL. After migration, they use Performance Insights to compare DB Load patterns. They notice high IO wait events on Aurora that were not present on Oracle. Investigation reveals that Aurora's default buffer pool size is smaller, causing more disk reads. They adjust the innodb_buffer_pool_size parameter (Aurora MySQL) or increase the instance size. Performance Insights provides the necessary data to validate that the new database performs as expected. Without it, they might have blamed the migration for performance issues that were actually configuration-related.