title | description | ms.service | ms.subservice | ms.topic | ms.author | ms.date |
---|---|---|---|---|---|---|
Query Performance Insights |
High level spec doc |
postgresql |
flexible-server |
conceptual |
varundhawan |
1/3/2023 |
Query Performance Insights is a feature in Azure Postgres that provides detailed insights into the performance of queries executed against your PostgreSQL database. Customers can use the Query Performance Insights feature in Azure Database for PostgreSQL to visually inspect your database workloads in the Azure portal to identify long-running queries, inspect wait statistics associated with queries, and detect changes in query performance. You can view the performance insights by selecting Query performance Insight under the Intelligent Performance section of the menu bar in the portal page of your Azure Database for PostgreSQL serve.
The Query Store feature in Azure Database for PostgreSQL provides a way to track query performance over time. Query Store simplifies performance-troubleshooting by helping you quickly find the longest running and most resource-intensive queries. Query Store automatically captures a history of queries and runtime statistics, and it retains them for your review. It slices the data by time so that you can see temporal usage patterns. Data for all users, databases and queries is stored in a database named azure_sys in the Azure Database for PostgreSQL instance.
- Determining the number of times a query was executed in a given time window
- Comparing the average execution time of a query across time windows to see large deltas
- Identifying longest running queries in the past few hours
- Identifying top N queries that are waiting on resources
- Understanding wait nature for a particular query To minimize space usage, the runtime execution statistics in the runtime stats store are aggregated over a fixed, configurable time window. The information in these stores can be queried using views.
Query Store data is stored in the azure_sys
database on your Postgres server, and can be accessed using following views
QUERY_STORE.QS_VIEW
- This view returns all the data in Query Store. There is one row for each distinct database ID, user ID, and query ID.QUERY_STORE.QUERY_TEXTS_VIEW
- This view returns query text data in Query Store. There is one row for each distinct query_text.QUERY_STORE.PGMS_WAIT_SAMPLING_VIEW
- This view returns wait events data in Query Store. There is one row for each distinct database ID, user ID, query ID, and event.QUERY_STORE.QUERY_PLANS_VIEW
- This view returns the query plan that was used to execute a query. There is one row per each distinct database ID, and query ID.
Starting with Azure Postgres Single Server, Query store for PostgreSQL is integrated with Azure Monitor diagnostic settings. This integration allows customers to sent the query store data (JSON format) to Azure Monitor Logs for analytics and alerting, Event Hubs for streaming, and Azure Storage for archiving. Once the data is in log analytics, customers can then use this in wide variety of scenarios such as:
- Identifying and tuning top expensive queries
- A/B testing
- Keeping performance stable during upgrades
- Identifying and improving ad hoc workloads
Integrate Azure Postgres Query Store with Log Analytics, and enabled following scenarios:
- Long Running Queries
- Wait Statistics
- Top Queries by Calls
- High Memory Queries
- High IOPS Queries
- Wait Statistics
Monitor Performance with Query Store - https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-query-store
- Engineering - Sergiu.Ernu@microsoft.com; sbalijepalli@microsoft.com; Saikat.Sen@microsoft.com
- Product - varun.dhawan@microsoft.com