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:

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.

Related video from YouTube

Prerequisites for Using Performance Insights

Supported RDS Database Engines

Performance Insights works with several RDS database engines, including:

Enabling Performance Insights

To enable Performance Insights on your RDS instance:

1. Modify your RDS instance settings
2. Choose the desired retention period and time zone for performance data
3. 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:

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

Enabling for Existing RDS Instances

To enable Performance Insights for an existing RDS instance:

1. Open the Amazon RDS console and choose the instance to modify.
2. Click "Modify".
3. Scroll down to the "Performance Insights" section.
4. Check the box to "Enable Performance Insights".
5. Choose the data retention period and time zone.
6. Click "Continue" and review the changes.
7. 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.

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:

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.

sbb-itb-6210c22

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:

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:

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:

1. Open the Performance Insights dashboard and go to the Top SQL tab.
2. Select a time range for analyzing the top SQL statements.
3. 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:

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:

1. Use the Performance Insights API to retrieve metrics
2. 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

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.

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.

Related posts

• AWS RDS Blog Insights: Performance Tuning
• AWS Redshift Tutorial for Beginners: First Steps
• AWS Lambda Insights: Serverless Monitoring Guide
• 10 AWS Cost Optimization Strategies 2024