RDS Performance Insights

RDS Performance Insights is a database performance tuning and monitoring feature for Amazon RDS that provides an easy-to-understand visual dashboard to help you quickly assess the load on your database and determine when and where to take action. It collects and displays database load information in a format that helps you identify performance bottlenecks. Performance Insights is available for Amazon RDS for MySQL, MariaDB, PostgreSQL, Oracle, SQL Server, and Amazon Aurora (MySQL and PostgreSQL compatible editions).

Performance Insights expands on existing CloudWatch metrics by providing a more detailed view of database performance. While CloudWatch gives you aggregate metrics like CPU utilization and database connections, Performance Insights shows you the database load (measured in Average Active Sessions, or AAS) broken down by wait events, SQL queries, hosts, and users. This allows you to pinpoint exactly which query or wait event is causing high load.

Performance Insights works by collecting performance data from the database engine. It uses a lightweight agent that runs on the database host and captures wait event information, SQL query statistics, and other performance metrics. This data is then sent to the Performance Insights service, which aggregates and stores it. The key metric is DB Load, measured in Average Active Sessions (AAS). AAS represents the average number of sessions that are actively working (not idle) during a one-second interval. The maximum DB Load is equal to the number of vCPUs on the DB instance; if DB Load consistently exceeds the number of vCPUs, the database is overloaded.

The data is stored in a time-series format and can be visualized in the AWS Management Console. The Performance Insights dashboard includes a top dimension chart that shows the top contributors to DB Load. Dimensions include: - Wait events: Specific events that cause sessions to wait (e.g., IO: DataFileRead, CPU, Lock: row lock contention). - SQL queries: Individual queries or query types (e.g., SELECT, INSERT). - Hosts: The client host from which the connection originated. - Users: The database user associated with the session.

The dashboard also includes a database load chart that shows DB Load over time, and a top SQL tab that lists the most resource-intensive SQL statements along with their execution plans and statistics.

- Retention period: Performance Insights offers two retention tiers: - Free tier: 7 days of performance history. This is enabled by default when you enable Performance Insights. - Full tier: 2 years (731 days) of performance history. This is a paid feature, charged per DB instance per hour. - Exam tip: The default retention is 7 days. To retain data longer, you must explicitly enable the full tier (up to 2 years). - Data granularity:

You can enable Performance Insights via the AWS Management Console, CLI, or API. Using the AWS CLI:

aws rds modify-db-instance \
    --db-instance-identifier mydb \
    --enable-performance-insights \
    --performance-insights-retention-period 7 \
    --apply-immediately

To verify:

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

To retrieve performance data programmatically:

aws pi get-resource-metrics \
    --service-type RDS \
    --identifier db-ABCDEFGHIJKLMNOP1234567890 \
    --metric-query 'Metrics=[{Metric=db.load.avg,GroupBy={Group=db.wait_event,Dimensions=[{Dimension=db.wait_event.name}]}}]' \
    --start-time 2023-01-01T00:00:00Z \
    --end-time 2023-01-01T01:00:00Z

A large e-commerce company runs its product catalog database on RDS for MySQL. During flash sales, the database becomes sluggish, leading to slow page loads and abandoned carts. The DBAs use Performance Insights to diagnose the issue. They enable Performance Insights with a 7-day retention (free tier) on their db.r5.4xlarge instance (16 vCPUs). During the next flash sale, they observe DB Load spiking to 30 AAS, far exceeding the 16 vCPU limit. The top wait event is 'IO:DataFileRead', and the top SQL is a query that searches for products by category without an index. They analyze the execution plan and see a full table scan on the 'products' table (10 million rows). They add a composite index on (category, price). The next flash sale, DB Load drops to 12 AAS, and page load times improve by 80%. Without Performance Insights, they would have guessed the issue was CPU or memory and scaled up unnecessarily.

A financial institution runs its transaction processing database on RDS for PostgreSQL with Multi-AZ. They need to retain performance data for one year for compliance purposes. They enable Performance Insights with a 731-day retention (paid tier). Over time, they use the historical data to identify that every month-end, DB Load increases due to a batch reporting query that locks rows in the 'transactions' table. They use Performance Insights to pinpoint the exact query and then schedule it during off-peak hours. They also use the data to forecast when they will need to upgrade to a larger instance class. The 2-year retention allows them to analyze year-over-year growth trends. They also set up CloudWatch alarms based on Performance Insights metrics (exported via Lambda) to notify the operations team when DB Load exceeds 80% of vCPUs for more than 5 minutes.

A SaaS provider uses one RDS for SQL Server instance per tenant. They want to right-size each instance to minimize costs while maintaining performance. They enable Performance Insights on all instances and use the API to programmatically collect DB Load metrics. They find that many instances have DB Load below 1 AAS for 95% of the time, indicating they are over-provisioned. They downsized those instances to smaller classes, saving 40% on database costs. For a few noisy tenants, they identified specific queries causing high load and worked with the tenant to optimize their application. Performance Insights provided the granular data needed to make informed decisions without guesswork.