Tutorial: Monitor and tune Azure Database for PostgreSQL - Single Server
Azure Database for PostgreSQL has features that help you understand and improve your server performance. In this tutorial you will learn how to:
- Enable query and wait statistics collection
- Access and utilize the data collected
- View query performance and wait statistics over time
- Analyze a database to get performance recommendations
- Apply performance recommendations
Before you begin
You need an Azure Database for PostgreSQL server with PostgreSQL version 9.6 or 10. You can follow the steps in the Create tutorial to create a server.
Query Store, Query Performance Insight, and Performance Recommendation are in Public Preview.
Enabling data collection
The Query Store captures a history of queries and wait statistics on your server and stores it in the azure_sys database on your server. It is an opt-in feature. To enable it:
Open the Azure portal.
Select your Azure Database for PostgreSQL server.
Select Server parameters which is in the Settings section of the menu on the left.
Set pg_qs.query_capture_mode to TOP to start collecting query performance data. Set pgms_wait_sampling.query_capture_mode to ALL to start collecting wait statistics. Save.
Allow up to 20 minutes for the first batch of data to persist in the azure_sys database.
The Query Performance Insight view in the Azure portal will surface visualizations on key information from Query Store.
In the portal page of your Azure Database for PostgreSQL server, select Query performance Insight under the Support + troubleshooting section of the menu on the left.
The Long running queries tab shows the top 5 queries by average duration per execution, aggregated in 15 minute intervals.
You can view more queries by selecting from the Number of Queries drop down. The chart colors may change for a specific Query ID when you do this.
You can click and drag in the chart to narrow down to a specific time window.
Use the zoom in and out icons to view a smaller or larger period of time respectively.
View the table below the chart to learn more details about the long-running queries in that time window.
Select the Wait Statistics tab to view the corresponding visualizations on waits in the server.
Owner or Contributor permissions required to view the text of the queries in Query Performance Insight. Reader can view charts and tables but not query text.
The Performance Recommendations feature analyzes workloads across your server to identify indexes with the potential to improve performance.
Open Performance Recommendations from the Support + troubleshooting section of the menu bar on the Azure portal page for your PostgreSQL server.
Select Analyze and choose a database. This will begin the analysis.
Depending on your workload, this may take several minutes to complete. Once the analysis is done, there will be a notification in the portal.
The Performance Recommendations window will show a list of recommendations if any were found.
A recommendation will show information about the relevant Database, Table, Column, and Index Size.
To implement the recommendation, copy the query text and run it from your client of choice.
Owner or Contributor permissions required to run analysis using the Performance Recommendations feature.
- Learn more about monitoring and tuning in Azure Database for PostgreSQL.