How to analyze poor performance from Azure PostGreSQL-PaaS

Question

I'm experiencing poor performance from Azure PostGreSQL-PaaS and need help with how to proceed.

I'm trying out Azure PostGreSQL-PaaS in a project. I'm experiencing an intolerable performance from the database (or at least it seems like the database is the problem).

Our application is running in an Azure-VM and both the VM and the database is located in western Europe. The network between the VM and the database seems to perform ok. (Using psping (from Sysinternals) on the database port 5432 I get latency between 2 ms and 4 ms) PostGreSQL incorporates a benchmark tool called pgbench. This tool runs a sequence of simple sql statements on a test dataset and provides timing. I ran pgbench on the VM against the database. Pgbench reports latency between 800 ms and 1600 ms. If I do the same test with pgbench in-house on our local network against an in-house database I typically get latency below 10 ms.

I tried to contact Microsoft support regarding this, but I've basically been told that since the network seems to perform ok this must be a PostGreSQL-software-problem and not related to Microsoft. Since the database is PostGreSQL-Paas I've only got limited access to logs and metrics.

Can anyone please help or advice me with how to proceed with this?

Answer 1

Performance of Azure PostgreSQL PaaS offering depends on different server and client configuration, including the SKU provisioned along with storage IOPS. Microsoft engineering has published series of performance blog which helps customer gain measurable and empirical gains by following these steps based on their workload. Please review these blog post:

Performance best practices for Azure PostgreSQL

Performance tuning of Azure PostgreSQL

Performance quick tips for Azure PostgreSQL

Is your in-house Postgres set up similar to the set up in Azure ?

Answer 2

I had the same issue. We moved from a dedicated VM (Ubuntu, Size Standard B2s 2 vcpus, 4 GiB memory, ~35€ p.m. ) running PostgreSQL to the Azure managed PostgreSQL instance (General Purpose, single server, 2vcpus, 10GB Memory, ~130€ p.m. ).

I first noticed the bad performance when the main API request of our webapplication suddenly took 3s instead of 1.7s / 2s.

I ran some very simple timing tests on my old setup with dedicated VM:

select count(*) from mytable;
 count
-------
  4686
Time: 0.940 ms

And those are the timings of the new setup with Azure managed PostgreSQL:

select count(*) from mytable;
 count
-------
  4686
Time: 21,353 ms

I think I do not have to explain these numbers :)

I have created a support ticket, and got some insights:

"In Azure PostgreSQL single server, we have a gateway to manage and route connections and there are always 3 copies of the data to ensure your data is not lost, and all of this will create latency."

I also asked what the benefits are of the managed database:

A: Being a instance running on azure, you’ve benefit of:

-Automatic patching, your instance is automatically upgraded.
-Crash recovery, in case our system detects the instance is not running, it tries to perform a restart/swithover to a new host. If all this fails, an oncall engineer is activated to manually restore the instance.
-Automatic backups and one click point in time restore. -Redundancy of data."

They suggested that I switch from Single Server to a Flexible server, where the gateway is ditched and the performance apparently should be better, but not as good as on a managed instance:

"In several tests we’ve made, the performance comparing to single server is much better. But to setup the right expectactions, you will not get 1 to 1 performance as having PostgreSQL running in a dedicated virtual machine."

I asked for the results of those tests, I will post them here as soon as I get them.

I think you have to decide if the benefits mentioned above are so high that you are willing to pay at least 4 times more compared to a dedicated VM and if you can live with the worse performance. We will now switch back to a master / slave configuration with 2 dedicated VMs.