duancai7002
2018-09-16 11:11
浏览 118
已采纳

建立与Azure PostgreSQL的连接的PDO非常慢

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

图片转代码服务由CSDN问答提供 功能建议

我将PHP应用程序作为基于Kubernetes中的Alpine Linux的Docker容器运行。 一切顺利,直到我尝试删除带有测试数据库的容器并将其替换为 Azure PostgreSQL 。 这导致了从250ms到1500ms以下的显着延迟增加。

根据剖析器,大部分时间用于 PDO构造函数,它正在建立与数据库的连接。 SQL查询本身,在建立连接后,然后在大约20ms内运行。

  • 我尝试使用IP而不是地址和 它仍然很慢。
  • 我尝试使用psql从容器连接并且速度很慢(请参阅下面的完整命令)
  • 我尝试使用<解析DNS解析 a href =“https://pkgs.alpinelinux.org/package/edge/main/x86/bind-tools”rel =“nofollow noreferrer”> bind-tools ,速度很快。
  • 数据库与Kubernetes节点在同一区域运行,尝试了相同的资源组,不同的网络设置,没有任何帮助。
  • 我尝试在两个客户端上都要求/禁用SSL模式 和服务器
  • 我尝试在已建立的连接中反复运行'select 1'并且速度很快(平均1.2ms,中位数0.9ms)(参见下面的完整查询) < / ul>

    什么会导致这种延迟? 如何进一步调试/调查此问题?


    用于尝试连接的psql命令:

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

    查询速度

      \ timing 
    SELECT; 
     \ watch 1 
       
     
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • doubingling4706 2018-12-18 13:21
    已采纳

    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.

    已采纳该答案
    打赏 评论

相关推荐 更多相似问题