GCP – Unlock AlloyDB performance secrets with new performance snapshot report
In the world of database management, understanding performance bottlenecks is critical to smooth operations and an optimal user experience. Using managed database services can help alleviate mundane management tasks and let you focus on value-added, strategic tasks, while also offering tools for monitoring database and resource performance. But when performance issues arise, gathering and analyzing various system and database metrics before and during the incident to conduct a thorough analysis can be time-consuming and labor-intensive.
Google Cloud’s AlloyDB for PostgreSQL is a fully managed database service that delivers superior availability, scalability, and performance. Recently, we added a new feature called performance snapshot report that provides deep insights into database performance. In this blog post, we explore how the performance snapshot report lets you identify and solve performance issues with greater clarity, control, and ease. This tool complements other AlloyDB observability features like systems insights, query insights, and the Metrics Explorer, which provide real-time metrics about your instance.
Demystifying the performance snapshot report
The performance snapshot report is a collection of PostgreSQL functions and views that captures snapshots of your AlloyDB system statistics. These snapshots provide detailed diff reports of performance metrics between different points in time, offering a comprehensive view of your database’s activity during the period.
The image above demonstrates an initial portion of the performance snapshot report. For more details on various sections, refer to this table. For additional wait events, refer to the wait events documentation.
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:
- code_block
- <ListValue: [StructValue([(‘code’, ‘SELECT perfsnap.snap();rnsnaprn—-rn14rn(1 row)rnrn– Start your workload and wait for some timern– capture two snapshots to serve as a baseline rnrnSELECT perfsnap.snap(); rnsnaprn—-rn15rn(1 row)rnrn– report between snapshots 14 and 15 serve as a baselinern– now a slowdown is experienced.. rn– create two snapshots during the slowdownrnrnSELECT perfsnap.snap(); — this is snapshot 1 during the slowdownrnsnaprn—-rn16rn(1 row)rnrn– wait for some time of your workloadrnrnSELECT perfsnap.snap(); — capture the econd snapshot during the slowdownrnsnaprn—-rn17rn(1 row)rnrnrnSELECT perfsnap.report(14, 15); — Generate a diff report between snapshot IDs 14 and 15 (normal workload)rnrnrnSELECT perfsnap.report(16, 17); — Generate a diff report between snapshot IDs 16 and 17 (slowdown experiened)rnrnrn–Compare the two reports to find the change in behavior’), (‘language’, ”), (‘caption’, <wagtail.rich_text.RichText object at 0x3e0a79884820>)])]>
That generates a report similar to this example report.
2. Assessing performance impact post system upgrades and configuration changes
Scenario: Sarah has upgraded the AlloyDB version. She’s also modified a few configuration parameters. She wants to understand the system behavior post these changes.
Solution:
-
Make baseline snapshot report: Before making the changes, Sarah captured two snapshots to create a report that shows the baseline stats.
-
Create post-change snapshot report: After making the changes, she captured two snapshots, and generated another report.
-
Analyze changes: She compared the report for any significant changes in performance metrics by comparing the two reports. Pay attention to sections like “CPU Utilization,” “Memory Utilization,” and “I/O Statistics” to assess the impact of the changes.
Know thy database performance
The performance snapshot report is a convenient way for you to get visibility and control over your AlloyDB database performance. By capturing and comparing snapshot reports, you can identify bottlenecks, optimize workloads, assess changes, and diagnose performance regressions with ease. This powerful tool, combined with the inherent performance advantages of AlloyDB, empowers you to unlock the full potential of your database and ensure your applications run smoothly and efficiently.
If you’re new to Google Cloud and want to take AlloyDB for a spin, just sign up for an AlloyDB free trial. If you’re already a Google Cloud user, head over to the AlloyDB console, click “Create a trial cluster” and we’ll guide you through migrating your PostgreSQL data to a new AlloyDB database instance.
Learn more
-
Review the documentation for the performance snapshots rReport
-
Watch this session for a deep dive into the latest innovations in AlloyDB
Read More for the details.