As more and more businesses move their data and applications to the cloud, the need for powerful and reliable database solutions has never been greater. Azure Database for PostgreSQL Flexible Server is a fully managed service that provides users with a highly scalable, available, and performant PostgreSQL database. Today, we are excited to announce the public preview of our new query performance insight feature for Azure Database for PostgreSQL Flexible Server, designed to help users better understand and optimize the performance of their database queries.
Why query performance monitoring matters?
Database queries are the backbone of any application and query slowness can be a major obstacle for any product. A research study from Kissmetrics found that 47% of users expect web pages to load within two seconds or less, and 40% of users are likely to leave a site if it takes > than three seconds to load. ¹ When queries are slow, applications can become unresponsive, which can frustrate app users and cause them to abandon the application altogether. Query performance monitoring is the process of collecting and analyzing data on how queries are performing in your database, enabling you to identify and fix performance issues before they impact your application's performance.
Query performance insight in Azure Database for PostgreSQL - Flexible Server
The new query performance insight feature in Azure Database for PostgreSQL – Flexible Server provides users with real-time insights into how their queries are performing. With this feature, users can track query metrics such as query execution time, execution count, and query resource consumption. The feature also provides users with detailed information on specific queries that are consuming the most resources, allowing them to quickly identify and optimize problematic queries. Query performance insight derives its knowledge from the Azure Database for PostgreSQL query store. Query store is a feature in Azure Database for PostgreSQL - Flexible Server that tracks and stores the statistics and execution plans of SQL queries executed on a database, allowing for query performance analysis over time. To learn more, refer Query Performance Insight (Preview).
Prerequisites
Step 1. Enable Query Store
For query performance insight to function, data must first exist in the query store. Query store is an opt-in feature that can be enabled by turning on pg_qs.query_capture_mode and pg_qs.store_query_plans server parameters. Query store is enabled or disabled globally for all databases on a given server and cannot be turned on or off per database. For more details, refer Enabling query store in Azure database for PostgreSQL Flexible Server.
Step 2. Enable Query Store Wait Sampling
For query performance insight to analyze wait statistics, server parameter pgms_wait_sampling.query_capture_mode must be set to ALL.
Step 3. Configure log diagnostics
Query performance insight is integrated with the Azure Monitor diagnostic settings to allow you to pipe your logs to any of four data sinks including Log Analytics workspace, event hub, storage account or partner solutions. For steps on how to send Query Store data to Log Analytics, refer Configure and Access Logs in Azure PostgreSQL Flexible Server.
For a detailed step-by-step guide, please refer to the query performance insight prerequisites.
Using query performance insight
Visualizing the query workload
Query performance insight has multiple tabs for analyzing the queries. The long running query tab (default) provides you with a graph that Top-N slowest queries by the execution time. Here, you can filter the data by time range dropdown or by using slider/zoom to change the observed interval. This information is a great starting point for identifying potential performance bottleneck queries. Other tabs include Top-N Queries by Calls, by Data-Usage, by IOPS and by Temporary Files.
Analyzing a specific query
To analyze a specific query, click on the QueryID in the graph legend or use the QueryID Snapshot search bar. You will see detailed information about total execution time, execution count, number of rows processed, and other runtime metrics. To get the Query Text of a specific query, connect to the azure_sys database on the server and query query_store.query_texts_view with the QueryID.
SELECT query_sql_text FROM query_store.query_texts_view WHERE query_text_id=QueryID;
Note: Due to security and privacy reasons, Query text is not visible on query performance insights dashboard.
In addition, you can also get the query's execution plan using QueryID and identify any potential performance bottlenecks.
SELECT plan_id, db_id, query_id, plan_text FROM query_store.query_plans_view WHERE query_id = QueryID;
Here in this example, we see a CPU spike that happened in the last 12 hours. Using query performance insight, we can easily see what queries were running during that time. Next, we drill down into the top query to run time stats including execution count, total execution time etc. Finally, we use QueryID of this query to get the query text and query plan from query store views.
Tips for using query performance insight
By analyzing query performance metrics with query performance insight, you can identify slow queries and take steps to improve database performance. Here are some tips for using query performance insight to optimize your database performance:
- Use query performance insight to monitor query runtime metrics regularly and look for any spikes in the number of queries or latency.
- Use the
QueryIDto find specific queries and analyze their performance. Identify queries that are taking a long time to execute and optimize them. Use the execution plans to identify potential performance bottlenecks in your queries. - Adjust your database configuration or rewrite queries to improve performance.
- Use query performance insight to identify which apps or users are generating the most queries.
Conclusion
Query Performance Insight dashboards are incredibly useful when:
- Running a performance test.
- Monitoring a live database for a specified period.
- General query performance monitoring.
Go ahead and configure Query Performance Insight for your Azure Database for PostgreSQL - Flexible Server and start monitoring your application workload. Knowing database health and monitoring your app them should no longer be limited to certain tools. With Query Performance Insights you can now easily identify potential issues before they become problems and keep your application running smoothly.
Your feedback and questions are welcome! You can always reach out to our team of Postgres experts at Ask Azure DB for PostgreSQL.
Acknowledgement
I extend my sincere gratitude to Sergiu Ernu, Sarat Balijepalli, Pradeep Kintali, Kelly OConnor, and Alicja Kucharczyk for their valuable feedback and insightful suggestions, which have significantly enhanced the quality of this blog post.
¹ Research study by Kissmetrics
