Inside the AlloyDB performance snapshot report

Here’s how it works

The performance snapshot report captures snapshots of your database’s performance metrics at any point during workload execution and generates reports based on any two snapshots. This gives you granular control over what periods you want to analyze, whether it’s the entire workload, specific intervals, or moments of peak activity. Each snapshot captures a wealth of information, including changes in key performance indicators like wait events, I/O statistics, and query execution times. It also captures system configuration details such as CPU usage, memory allocation, and parameter details.

You can also access and analyze performance by generating reports between two snapshots directly within your PostgreSQL environment, using familiar psql queries, as in familiar Oracle AWR reports, facilitating easy adoption and integration into existing processes. Then, you can save the reports for historical comparisons to identify regressions, evaluate changes, and monitor performance trends. 

Performance snapshot report in action

The performance snapshot report equips you with the tools you need to diagnose and resolve performance issues effectively. Let’s explore some common scenarios where a performance snapshot report proves to be invaluable.

1. Identifying performance bottlenecks

Scenario: The Cymbal Shop’s database, managed by DBA Sarah, is experiencing an unexpected performance degradation. A previously stable system became significantly slower this morning. Sarah needs to investigate this sudden slowness to determine its cause and implement a solution to address the changes that led to it.

Solution: Sarah had already established a baseline by creating a report comparing two performance snapshots taken two days prior during normal system operation. To investigate the unexpected slowdown, Sarah first uses the `perfsnap.snap()` function to capture performance snapshots at two distinct time points, separated by approximately one hour. Subsequently, she utilizes the `perfsnap.report(start_snap_id, end_snap_id)` function to generate a differential report, which highlights variations in critical performance indicators such as wait events, I/O activity, and query execution times. Finally, Sarah analyzes the generated report, focusing on significant increases in particular wait events — such as `lwlock` for lock contention or `io` for storage delays — and changes in query execution durations. This detailed examination allows her to precisely identify the root cause of the sudden performance degradation.

Example: