An Open Source Performance Monitoring Tool for Aurora PostgreSQL

If you’re an Amazon Aurora PostgreSQL (also known as Postgres) user like Intuit, you’re benefiting from the speed and reliability of a high-end commercial database and the simplicity of an open-source database.

It provides built-in views of performance statistics (pg_stat_statements, pg_stat_activity, pg_stat_database), along with other views like apg_plan_mgmt.dba_plans, all of which are important for database and SQL performance troubleshooting.

However, these views provide only cumulative statistics. The last database restarts or statistics are reset, and the statistics themselves reside only in memory. When we need to troubleshoot a performance issue, we typically need to review statistics for a particular window or compare statistics between different windows.

That’s why we’ve developed a new open source tool: the AWR (automatic workload repository) for Aurora PostgreSQL to help answer Qs like this (and more):

• Why is the database running slower today? What has changed in the last 24 hours, last week, or last month?
• Can we quickly determine what caused the slowness: database infrastructure (CPU, I/O, memory, network, etc.), database software, or application code?
• Are there any sub-optimal SQLs?
• Why is this SQL much slower than before? Has its execution plan been changed? Can we find and use a better execution plan?
• What happened to the database within a specific, small window of time?
• Will a new application release cause any performance issues? Do we need to increase database capacity in terms of CPU and/or IOPS?

This new tool has been built using AWS Lambda function, Aurora IAM authentication, Aurora QPM, AWS CloudWatch event rule, Aurora CloudWatch matrices, pg_stat_statements, pg_partman, foreign tables, and pg8000.

Collecting performance statistics

Here’s how you can use it to generate AWR reports:

Generating AWR reports

Using our open source tool, you’ll be able to produce a variety of reports.

• Generate an AWR database report, including the following sections:

Instance Statistics

Database Load Profile

Database Top Wait Events

Database Session State

SQL Statistics
    SQL ordered by Total Time
    SQL ordered by I/O Time
    SQL ordered by Logical Reads
    SQL ordered by Reads

Object Statistics
    Table ordered by sequential scans
    Table ordered by rows fetched by sequential scans
    Table ordered by index scans
    Table ordered by rows fetched by index scans

Complete List of SQL Text

• Generate an AWR SQL report, including the following sections:

SQL Text

SQL Plan Statistics

SQL Plan Outline

• Generate an AWR ASH (Active Session History) report, including the following sections:

Session Count

Active Session with Wait Event

Active Session with SQL

Top Active Session

Complete List of SQL Text

• Generate reports for both writer and reader instances.
• Generate AWR database reports in non-interactive mode.
• Create and run your own SQLs to query tables in the repository directly.

Getting started!

Like what you see? Try it out using our quick-start guide.

Many thanks to an incredible team…

Many thanks to Narayanan Gopalakrishnan, Ketan Popat, Priti Singh, Vipin Madhusoodanan, Aditya Bhardwaj, Mayank Choubey, and Sudarsan Mukundhan for their invaluable contributions, and to Rajesh Kadle, Ann Catherine Jose, and Lucy Shen for their great help with creating this open source tool!