First published on MSDN on Nov 13, 2018
To make sure that you are running your Azure Database for PostgreSQL at its best capabilities, please review the following recommendations and best practices.
1. Server Resource Utilization
Make sure that your Azure Database for PostgreSQL instance does not run under a high utilization which can cause query execution latencies. Please visit the Azure Portal and navigate to your Azure Database for PostgreSQL Instance and view the Metrics Tab, as shown in figure 1 below.
Figure 1: Metrics for Azure Database for PostgreSQL
You need to consider checking the following metrics:
A. CPU utilization
Please remember that maxing out CPU make cause slowness and connection drops. Check your long running queries to understand what is causing high processing time and update your database tables Statistics regularly by using the “Analyze” command. If you maxed out CPU, scaling up your server by increasing the number of vCores is a good approach to enlarge your resources to be able to accommodate your workload. Enable Azure PostgreSQL Query Store to be able to view your long running queries.
B. IOPS throughput
Please remember that the server has 3 IOPS per 1 GB of Storage. If your application requires higher IOPs, then it is recommended that you scale up you Azure Database for PostgreSQL server storage size to get more IOPS so that your application performance is not impacted by storage throttling.
C. IO waits
If IO waits are observed from PostgreSQL performance troubleshooting, then increasing the storage size should be considered for higher IO throughput. Check the wait queries using the portal.
D. Active connections
Check the number of Active connections, this limitation is related to the number of vCores that you have provisioned, the full list is provided here:
https://docs.microsoft.com/en-us/azure/postgresql/concepts-limits
Note: Setup Azure Alerts to get notified before issues happen
Full tutorial on how to use the Azure portal to set up alerts on metrics for Azure Database for PostgreSQL can be found here:
https://docs.microsoft.com/en-us/azure/postgresql/howto-alert-on-metric
2. Optimizing client applications
We recommend having the application server/client machine in the same region in Azure to reduce network latencies between the client/application server and the database.
A. Network latency
Check the network latency between the client and the database service instance. You can check the network latency by running simple query as ‘SELECT 1’
B. Connection pooling
Use Connection pooling to reduce the latency caused by connection establishment along with reducing the chance of reaching the server limit of active connections.
C. TCP_NODELAY
TCP_NODELAY is a client-side setting that should be considered on a client Virtual Machine (VM). Applications that benefit from the TCP_NODELAY option typically tend to do smaller infrequent writes and are particularly sensitive to latency. As an example, latency can be reduced from 15-40 ms to 2-3 ms with this setting.
D. CPU exhaustion:
Single-threaded applications can result in CPU exhaustion of one CPU while the other CPUs are under-utilized. Consider parallelizing your workload to take advantage of all the vCores available
E. Pg_stat_statements:
it's a PostgreSQL extension that is enabled by default and provides a means to track execution statistics of all SQL statements executed by a server. This module hooks into every query execution and comes with a non-trivial performance cost. Enabling pg_stat_statements forces query text writes to files on disk.
Consider disabling pg_stat_statements, this can be done by visiting the server parameters blade on the Azure Portal and set "pg_stat_statements.track" to NONE.
Some customers workload have seen up to a 50 percent performance improvement with disabling this. The tradeoff you make when you disable pg_stat_statements is the inability to troubleshoot performance issues.
References:
- https://azure.microsoft.com/en-us/blog/performance-updates-and-tuning-best-practices-for-using-azure...
- https://azure.microsoft.com/en-us/blog/performance-best-practices-for-using-azure-database-for-postg...
- https://docs.microsoft.com/en-us/azure/postgresql/concepts-performance-recommendations
- https://docs.microsoft.com/en-us/azure/postgresql/howto-alert-on-metric
- https://docs.microsoft.com/en-us/azure/postgresql/concepts-query-store