PDO establishing connection to Azure PostgreSQL is very slow

9/16/2018

I have PHP application running as a Docker container based on Alpine Linux inside Kubernetes. Everything went well until I tried removing container with test database and replacing it with Azure PostgreSQL. This led to significant latency increase from under 250ms to above 1500ms.

According to profiler most time is spent in PDO constructor which is establishing connection to database. The SQL queries themselves, after connection was established, then run in about 20ms.

  • I tried using IP instead of address and it was still slow.
  • I tried connecting from container using psql and it was slow (see full command below)
  • I tried DNS resolution using bind-tools and it was fast.
  • Database runs in same region as Kubernetes nodes, tried even same resource group, different network settings and nothing helped.
  • I tried requiring/disabling SSL mode on both client and server
  • I tried repeatedly running 'select 1' inside an already established connection and it was fast (average 1.2ms, median 0.9ms) (see full query below)

What can cause such a latency? How can I further debug/investigate this issue?


psql command used to try connection:

psql "sslmode=disable host=host dbname=postgres user=user@host.postgres.database.azure.com password=password" -c "select 1"

Query speed

\timing 
SELECT;
\watch 1
-- Jindra
azure
kubernetes
pdo
php
postgresql

1 Answer

12/18/2018

As far as I can tell it is caused by Azure specific authentication on top of PostgreSQL. Unfortunately Azure support was not able to help from their side.

Using connection pool (PgBouncer) solves this problem. It is another piece of infrastructure we have to maintain (docker file, config/secret management, etc.), which we hoped to outsource to cloud provider.

-- Jindra
Source: StackOverflow