AWS Performance Insights: Monitoring RDS Databases

AWS Performance Insights is a powerful monitoring tool that helps you assess the load on your RDS databases and identify performance bottlenecks. It provides a simple dashboard to visualize database load, analyze resource-intensive SQL statements, and optimize performance.

Key Benefits:

Simple dashboard to visualize database load
Detailed metrics like CPU usage, wait times, and locks
Seamless integration with CloudWatch and other AWS services
Proactive recommendations to optimize performance
Automated data collection and analysis

Supported Database Engines:

Database Engine	Supported Versions	Instance Class Restrictions
Amazon RDS for MariaDB	See Supported Regions and DB engines	Not supported for smaller instance classes
RDS for MySQL	See Supported Regions and DB engines	Not supported for smaller instance classes
RDS for Microsoft SQL Server	See Supported Regions and DB engines	No restrictions
RDS for PostgreSQL	See Supported Regions and DB engines	No restrictions
RDS for Oracle	See Supported Regions and DB engines	No restrictions

Pricing and Data Retention:

Free tier: 7 days of performance data history and 1 million API requests per month
Paid plans: 1 to 24 months of data retention, starting at $3.50 per instance per month

Key Features:

Load chart to visualize database load over time
Top Activity Table to identify resource-intensive SQL statements
Counter metrics for CPU usage, disk I/O, lock waits, and more
SQL statement analysis and execution plan viewing
Integration with CloudWatch for alarms, dashboards, and advanced monitoring

Performance Insights helps you monitor, analyze, and optimize your RDS databases, ensuring responsive applications, efficient resource utilization, and cost management.

Prerequisites for Using Performance Insights

Supported RDS Database Engines

Performance Insights works with several RDS database engines, including:

Database Engine	Supported Versions and Regions	Instance Class Restrictions
Amazon RDS for MariaDB	See Supported Regions and DB engines for details.	Not supported for: - db.t2.micro - db.t2.small - db.t3.micro - db.t3.small - db.t4g.micro - db.t4g.small
RDS for MySQL	See Supported Regions and DB engines for details.	Not supported for: - db.t2.micro - db.t2.small - db.t3.micro - db.t3.small - db.t4g.micro - db.t4g.small
RDS for Microsoft SQL Server	See Supported Regions and DB engines for details.	No restrictions
RDS for PostgreSQL	See Supported Regions and DB engines for details.	No restrictions
RDS for Oracle	See Supported Regions and DB engines for details.	No restrictions

Enabling Performance Insights

To enable Performance Insights on your RDS instance:

Modify your RDS instance settings
Choose the desired retention period and time zone for performance data
Specify the data retention period using the PerformanceInsightsRetentionPeriod parameter

Required IAM Permissions

To access and manage Performance Insights, you need the following IAM permissions:

rds:DescribeDBInstances
rds:ModifyDBInstance
rds:CreateDBInstanceReadReplica
rds:RestoreDBInstanceFromS3

Create a custom IAM policy granting these permissions for secure, authorized usage.

Enabling Performance Insights for RDS

Turning on Performance Insights for your RDS databases is a straightforward process. You can enable it during instance creation or for existing instances.

Enabling During RDS Instance Creation

When creating a new RDS instance:

Open the Amazon RDS console and choose the instance you want to create.
Click "Modify".
Scroll down to the "Performance Insights" section.
Check the box to "Enable Performance Insights".
Choose the data retention period and time zone.
Click "Continue" and review the changes.
Click "Modify DB instance" to save.

Enabling for Existing RDS Instances

To enable Performance Insights for an existing RDS instance:

Open the Amazon RDS console and choose the instance to modify.
Click "Modify".
Scroll down to the "Performance Insights" section.
Check the box to "Enable Performance Insights".
Choose the data retention period and time zone.
Click "Continue" and review the changes.
Click "Modify DB instance" to save.

Data Retention and Pricing

Performance Insights offers a free tier with 7 days of performance data history and 1 million API requests per month. You can purchase longer retention periods from 1 to 24 months, based on your needs.

Retention Period	Price
7 days (free tier)	Free
1 month	$3.50 per database instance per month
12 months	$37.00 per database instance per month
24 months	$63.00 per database instance per month

For more details on pricing and data retention options, see the Pricing and data retention for Performance Insights documentation.

Understanding the Performance Insights Dashboard

Dashboard Overview

The Performance Insights dashboard provides a clear view of your database's performance. It has:

Load Chart: Shows the database load over time, including the average active sessions (AAS) and maximum CPU capacity.
Top Activity Table: Lists the SQL statements using the most resources, with details on wait times and CPU usage.
Counter Metrics: Additional performance data like connections, queries, and locks.

Load Chart Explained

The load chart is a key part of the dashboard. It visually shows your database load over time, helping you spot performance issues and trends. The chart displays:

Average Active Sessions (AAS): The average number of active sessions at a time. This measures the database load.
Maximum vCPU Capacity: The maximum CPU capacity available.

Analyzing the load chart lets you see if your database is experiencing high load and take steps to optimize performance.

Top Activity Table Importance

The top activity table is another essential part of the dashboard. It lists the SQL statements using the most resources, along with:

Wait times
CPU usage

This information helps you find the root cause of performance problems, like slow queries or poor indexing. By analyzing this table, you can optimize performance by tuning the most resource-intensive SQL statements. The table breaks down each statement's impact on wait times and CPU usage, giving you insights into improving database performance.

Analyzing Database Load

Analyzing database load is crucial for understanding your database's performance and identifying areas that need improvement. Performance Insights provides several metrics and dimensions to help you analyze database load effectively.

Average Active Sessions (AAS)

Average Active Sessions (AAS) is a key metric that measures the average number of active sessions at a given time. A high AAS value indicates a high database load, which can lead to performance issues.

To analyze AAS, you can:

View the load chart in the Performance Insights dashboard, which displays the AAS value over time.
Use the Top Activity Table to identify the SQL statements contributing to the high AAS value.

Identifying Performance Bottlenecks

Performance Insights provides metrics to help you identify performance bottlenecks, such as:

Metric	Description
CPU usage	Identifies if your SQL statements or database configuration need optimization.
Wait times	Helps pinpoint the cause of delays, such as CPU waits or I/O waits.
Locks	Indicates if there are issues with concurrent access to resources.
Disk I/O	Shows if disk performance is causing slowdowns.

By analyzing these metrics, you can identify the root cause of performance issues and take corrective action.

Analyzing Load by Dimensions

Performance Insights allows you to analyze load by various dimensions, including:

Waits: Identify the types of waits contributing to performance issues.
SQL statements: Find the most resource-intensive statements and optimize them.
Hosts: Identify performance issues related to specific hosts.
Users: Identify performance issues related to specific users.

Analyzing load by dimensions provides a deeper understanding of database performance and helps you pinpoint areas that need optimization.

In the next section, we'll explore monitoring performance counters in Performance Insights.

Monitoring Performance Counters

Monitoring performance counters is key to understanding your database's performance. Performance Insights provides counters that help identify bottlenecks and optimize your database.

Native and Non-Native Counters

Performance Insights offers two types of counters:

Counter Type	Description
Native	Specific to the database engine, providing detailed performance insights. For example, Amazon Aurora has native counters for CPU usage, disk I/O, and lock waits.
Non-Native	Generic counters that apply to multiple database engines, such as CPU usage and disk I/O.

Understanding the difference between native and non-native counters helps you monitor performance effectively.

Common Performance Counters

Here are some common performance counters in Performance Insights:

Counter	Description
CPU Usage	Percentage of CPU utilized by the database instance.
Disk I/O	Number of disk I/O operations per second.
Lock Waits	Time spent waiting for locks on database resources.
Row Locks	Number of row locks acquired by the database instance.

Monitoring these counters helps identify performance bottlenecks, optimize database configuration, and improve overall performance.

In the next section, we'll explore analyzing SQL statements and execution plans in Performance Insights.

Analyzing SQL Statements and Execution Plans

Analyzing SQL statements and execution plans is crucial for optimizing database performance. AWS Performance Insights provides tools to identify and address performance bottlenecks caused by resource-intensive queries.

Understanding SQL Statistics and Digests

Performance Insights collects SQL statistics and digests, which help identify the top SQL statements contributing to database load. A SQL digest is a summarized representation of a SQL statement, grouping similar queries together. This feature allows you to focus on the most resource-intensive queries and optimize them.

Analyzing Top SQL Statements

To analyze top SQL statements:

Open the Performance Insights dashboard and go to the Top SQL tab.
Select a time range for analyzing the top SQL statements.
The top SQL statements are displayed, along with their corresponding database load and average active sessions.

Analyzing the top SQL statements helps you identify and optimize the queries that contribute the most to database load.

Viewing and Analyzing Execution Plans

Execution plans are crucial for query optimization. Performance Insights allows you to view and analyze execution plans for your top SQL statements:

Select a top SQL statement from the Top SQL tab.
Click on the Plans tab to view the execution plan.
Analyze the execution plan to identify performance bottlenecks, such as inefficient joins or indexing issues.

Step	Action
1	Select a top SQL statement from the Top SQL tab
2	Click on the Plans tab to view the execution plan
3	Analyze the execution plan to identify performance bottlenecks, such as inefficient joins or indexing issues

Integrating with CloudWatch

Performance Insights can work with Amazon CloudWatch to improve monitoring and alerting. This integration lets you:

View and set alarms on Performance Insights metrics in CloudWatch
Combine these metrics with other CloudWatch metrics on one dashboard

Publishing Metrics to CloudWatch

You can export Performance Insights metrics to CloudWatch. This allows you to use CloudWatch's advanced monitoring features, like:

Anomaly detection
Metric math
Statistics

You can also export the metrics to external monitoring tools like Prometheus and Grafana.

To publish metrics to CloudWatch:

Use the Performance Insights API to retrieve metrics
Use the CloudWatch API to publish metrics to CloudWatch

You can automate this process by creating a Lambda function and scheduling it in Amazon EventBridge to run at specific times.

Setting Up CloudWatch Alarms and Dashboards

Once Performance Insights metrics are in CloudWatch, you can:

Set up alarms based on key performance indicators (KPIs), such as:
- DB Load
- DB Load CPU
- DB Load Non-CPU
Create custom dashboards to visualize Performance Insights metrics alongside other CloudWatch metrics

This gives you a comprehensive view of your database performance and notifications when thresholds are breached.

Integration with Other AWS Monitoring Tools

Integrating Performance Insights with other AWS monitoring tools like Amazon CloudWatch Logs and AWS X-Ray provides a complete monitoring solution. You can:

Use CloudWatch Logs to analyze log data
Use X-Ray to trace requests and identify performance bottlenecks

Best Practices for Performance Insights

Using Performance Insights Effectively

To get the most out of Performance Insights, follow these practices:

Choose the right dimension to analyze the load, such as users or hosts, to identify the root cause of performance issues.
Use the Top SQL feature to find the most resource-intensive queries and optimize them.
Set up alarms and dashboards in CloudWatch to get notified of performance issues and view your database performance comprehensively.

Optimizing Database Performance

Performance Insights provides insights into database performance, but you need to act on them. Here are some strategies:

Identify and optimize resource-intensive queries to reduce CPU usage and improve overall performance.
Analyze wait events to identify bottlenecks and take corrective action, such as optimizing database configuration or adjusting application design.
Use the Average Active Sessions (AAS) metric to identify periods of high load and adjust database resources accordingly.

Troubleshooting Performance Issues

When performance issues arise, troubleshoot them quickly and effectively:

Use the Performance Insights dashboard to identify the root cause, such as high CPU usage or wait events.
Analyze the Top SQL feature to identify and optimize the most resource-intensive queries.
Use the load chart to identify periods of high load and adjust database resources accordingly.

Comparison with Other Monitoring Tools

AWS Performance Insights is not the only tool for monitoring databases. There are several other popular options that offer similar features. In this section, we'll compare Performance Insights with other monitoring tools, highlighting its pros and cons.

Comparison Table

Feature	AWS Performance Insights	New Relic	Datadog	SolarWinds
Database Support	MySQL, PostgreSQL, Oracle, SQL Server	MySQL, PostgreSQL, Oracle, SQL Server, MongoDB	MySQL, PostgreSQL, Oracle, SQL Server, MongoDB	MySQL, PostgreSQL, Oracle, SQL Server
Performance Metrics	CPU, Memory, Disk I/O, Network I/O	CPU, Memory, Disk I/O, Network I/O, Query Analysis	CPU, Memory, Disk I/O, Network I/O, Query Analysis	CPU, Memory, Disk I/O, Network I/O
Query Analysis	Yes	Yes	Yes	Yes
Alerting and Notifications	Yes	Yes	Yes	Yes
Integration with CloudWatch	Yes	No	No	No
Pricing	Free (7 days), Paid (longer retention)	Paid	Paid	Paid

From the table above, we can see that AWS Performance Insights offers similar features to other popular database monitoring tools. However, its integration with CloudWatch and AWS services makes it a better option for AWS users. Additionally, Performance Insights offers a free tier with 7 days of data retention, making it cost-effective for small to medium-sized databases.

While Performance Insights is a powerful tool, it may not be the best choice for databases running on non-AWS platforms. In such cases, tools like New Relic, Datadog, or SolarWinds may be more suitable. The choice of monitoring tool depends on the specific needs of the database and the organization.

Pros and Cons

Here are some pros and cons of using AWS Performance Insights:

Pros:

Seamless integration with other AWS services like CloudWatch
Free tier with 7 days of data retention
Easy to set up and use
Provides detailed performance metrics and query analysis
Supports multiple database engines (MySQL, PostgreSQL, Oracle, SQL Server)

Cons:

Limited support for non-AWS databases
Paid plans can be expensive for longer data retention periods
May not offer as many advanced features as some third-party tools

Making the Right Choice

When choosing a database monitoring tool, consider the following factors:

The database engine(s) you need to monitor
Whether you're using AWS or a different cloud platform
The level of detail and features you require
Your budget and data retention needs
Integration with other tools and services you're using

For AWS users, Performance Insights can be a great choice, especially for smaller databases or those with limited monitoring requirements. However, for more advanced needs or non-AWS databases, third-party tools like New Relic, Datadog, or SolarWinds may be better suited.

Ultimately, the right choice depends on your specific requirements and priorities. It's always a good idea to evaluate multiple options and consider their strengths and weaknesses before making a decision.

Conclusion

Key Points

AWS Performance Insights is a powerful monitoring tool for RDS databases, seamlessly integrating with CloudWatch and other AWS services.
It provides detailed performance metrics, query analysis, and alerting capabilities to help you:
- Identify performance bottlenecks
- Optimize database performance
- Troubleshoot issues quickly
Using Performance Insights can improve database performance, reduce latency, and enhance overall application performance.
It offers a cost-effective solution with a free tier for 7 days of data retention, making it suitable for small to medium-sized databases.

Benefit	Description
Identify Issues	Pinpoint performance bottlenecks and root causes
Optimize Performance	Tune queries and configurations for better efficiency
Troubleshoot Quickly	Resolve issues rapidly with detailed insights
Enhance Applications	Improve overall application performance and responsiveness
Cost-Effective	Free tier with 7 days of data retention

Performance Insights is a valuable tool for database administrators and developers. By leveraging its features, you can:

Monitor database performance effectively
Analyze and optimize resource-intensive queries
Set up alerts for performance issues
Visualize metrics alongside other CloudWatch data

With its user-friendly interface and integration with AWS services, Performance Insights simplifies database monitoring and optimization. It empowers you to deliver high-performing, responsive applications while managing costs efficiently.