Azure Database for PostgreSQL performance Quick Tips

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

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:

https://docs.microsoft.com/en-us/azure/postgresql/concepts-query-store

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: follow this tutorial

 


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



      (Reference

Performance updates and tuning best practices for using Azure Database for PostgreSQL

      )




      We recommend having the application server/client machine in the same region and resource group 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. Accelerated Networking

      Use Accelerated Networking on your Azure Web Apps as a best practice.



d. 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.



e. 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



f. Pg_stat_statements:

      is 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.



      On some customer workloads we have seen up to a 50 percent performance improvement by disabling ps_stat_statements. However, the tradeoff one makes by disabling pg_stat_statements is the inability to troubleshoot performance issues.




References:

    1. https://azure.microsoft.com/en-us/blog/performance-updates-and-tuning-best-practices-for-using-azure-database-for-postgresql/

 

    1. https://azure.microsoft.com/en-us/blog/performance-best-practices-for-using-azure-database-for-postgresql/

 

    1. https://docs.microsoft.com/en-us/azure/postgresql/concepts-performance-recommendations

 

    1. https://docs.microsoft.com/en-us/azure/postgresql/howto-alert-on-metric

 

    1. https://docs.microsoft.com/en-us/azure/postgresql/concepts-query-store



Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.