PostgreSQL：在其他客户端中查询速度慢10倍 [英] Postgresql: Query 10x slower in a different client

问题描述

查看postgres服务器日志，我发现从Linux客户端或Windows客户端调用同一Postgres服务器上完全相同的查询会花费更长的时间（大约10倍）。

查询来自运行在具有4GB RAM的Linux机器和具有8GB RAM的Windows机器上的Django应用程序。两种pyhon环境都具有psycopg2库2.4.4版，可以将请求发送到同一postgres服务器。

以下是postgres服务器日志

windows查询（有时间）：

  2013-06-11 12:12:19 EEST [未知] 10.1.3.152（56895）mferreiraLOG：持续时间：3207.195 ms语句：SELECT autotests_tracerperformance。 id， autotests_tracerperformance。 date， autotests_tracerperformance。 video_id， autotests_tracerperformance。 revision_id， autotests_tracerperformance 。 computer_id， autotests_tracerperformance。探针， autotests_tracerperformance。 time_tostart， autotests_tracerperformance。 hang_atstart， autotests_tracerperformance。 time_tohang， autotests_tracerperformance。 hang， autotests_tracerperformance。  crash， autotests_tracerperformance。 stacktrace， autotests_tracerperformance。 framemax， autotests_tracerperformance。 maxtime， autotests_tracerperformance。 avgtime来自 autotests_tracerperforma因为 INNER JOIN修订为ON（ autotests_tracerperformance。 revision_id =修订。 id）WHERE（ autotests_tracerperformance。 computer_id = 61 AND修订。 repo ='Trunk'）
  

Linux查询（更长）：

  2013-06-11 12:12:56 EEST [未知] 10.1.3.154（35325）mferreiraLOG：持续时间：22191.773 ms语句：SELECT autotests_tracerperformance。 id， autotests_tracerperformance 。 date， autotests_tracerperformance。 video_id， autotests_tracerperformance。 revision_id， autotests_tracerperformance。 computer_id， autotests_tracerperformance。 probe， autotests_tracerperformance。 time_tostart， autotests_tracerperformance。 hang_atstart， autotests_tracerperformance。 time_tohang， autotests_tracerperformance。 hang， autotests_tracerperformance。 crash， autotests_tracerperformance。 stacktrace， autotests_tracerperformance。 framemax， autotests_tracerperformance。 maxtime ， autotests_tracerperform ance。 avgtime FROM autotests_tracerperformance INNER JOIN修订 ON（ autotests_tracerperformance。 revision_id =修订。 id）WHERE（ autotests_tracerperformance。 computer_id = 61 AND修订。 repo ='Trunk'）
  

直接从psql执行（最快）：

  2013-06-11 12:19:06 EEST psql [local] mferreiraLOG：持续时间：1332.902 ms语句：SELECT autotests_tracerperformance。 id ， autotests_tracerperformance。 date， autotests_tracerperformance。 video_id， autotests_tracerperformance。 revision_id， autotests_tracerperformance。 computer_id， autotests_tracerperformance。 probe， autotests_tracerperformance。 time_tostart自动测试跟踪性能。 rmance。 maxtime， autotests_tracerperformance。 avgtime来自 autotests_tracerperformance INNER JOIN修订 ON（ autotests_tracerperformance。 revision_id =修订。 id）WHERE（ autotests_tracerperformance。 computer_id = 61 AND revisions .. repo ='Trunk'）; 
  

其他不需要从数据库中加载这么多项目的查询的性能几乎相同。 / p>

为什么该查询的客户端之间的时间差如此大？

注意：传输时间无关紧要，因为所有计算机都在同一个Intranet中。另外，当客户端请求来自运行Postgresql服务器的同一台Linux计算机时，速度会变慢。

Note2 ：Psycopg2在Windows和Linux中的安装方式有所不同。在Windows中，我是从预打包的二进制文件安装的，而在Linux中，我运行的是 pip install psycopg2，它依赖于系统上可用的postgresql安装。这会导致影响客户端性能的参数的不同值（例如'work_mem'参数）吗？

解决方案

检查慢速客户端是否进行SSL加密。默认情况下，在服务器上进行设置且客户端已使用SSL支持进行编译时，就会发生这种情况。

对于检索大量数据的查询，时间差很明显。
还有一些Linux发行版（例如Debian / Ubuntu）默认情况下已启用SSL，即使对于通过本地主机的TCP连接也是如此。

例如，这是一个

未加密：

 
 $ psql host = localhost dbname = mlists sslmode = disable 
密码：
 psql（9.1.7，服务器9.1.9）
输入 help帮帮我。 
 
 mlists => \timing 
计时已到。 
 mlists => / / dev / null 
 mlists =>从邮件中选择主题； 
时间： 1672.258毫秒 
 

使用加密功能：

 
 $ psql host = localhost dbname = mlists 
密码：
 psql（9.1.7，服务器9.1.9）
  SSL连接（密码：DHE-RSA-AES256-SHA，位：256） 
键入帮助以获取帮助。 
 
 mlists => / / dev / null 
 mlists => \timing 
计时已到。 
 mlists =>从邮件中选择主题； 
时间： 7017.935毫秒 
 

要全局关闭此功能，可以将<$ c $在 postgresql.conf 中使用c> SSL = off 。

将其关闭特定的客户地址范围，请在第一个字段的 pg_hba.conf 中添加条目，并在 hostnossl 之前添加更通用的 host 项。

要关闭客户端功能，取决于驱动程序如何公开 sslmode 连接参数。如果没有，则如果驱动程序是在 libpq 之上实现的，则可以使用 PGSSLMODE 环境变量。 / p>

对于通过Unix域套接字（ local ）进行的连接，永远不要使用SSL。

Looking at the postgres server log, I see that the exact same query on the same postgres server takes much longer (about 10x longer) when invoked from a Linux client or from a Windows client.

The queries come from a Django application running on a Linux machine with 4GB RAM and on a Windows machine with 8GB RAM. Both pyhon environments have psycopg2 library version 2.4.4 to send requests to the same postgres server.

Below are the postgres server logs

The windows query (with time):

2013-06-11 12:12:19 EEST [unknown] 10.1.3.152(56895) mferreiraLOG:  duration: 3207.195 ms  statement: SELECT "autotests_tracerperformance"."id", "autotests_tracerperformance"."date", "autotests_tracerperformance"."video_id", "autotests_tracerperformance"."revision_id", "autotests_tracerperformance"."computer_id", "autotests_tracerperformance"."probe", "autotests_tracerperformance"."time_tostart", "autotests_tracerperformance"."hang_atstart", "autotests_tracerperformance"."time_tohang", "autotests_tracerperformance"."hang", "autotests_tracerperformance"."crash", "autotests_tracerperformance"."stacktrace", "autotests_tracerperformance"."framemax", "autotests_tracerperformance"."maxtime", "autotests_tracerperformance"."avgtime" FROM "autotests_tracerperformance" INNER JOIN "revisions" ON ("autotests_tracerperformance"."revision_id" = "revisions"."id") WHERE ("autotests_tracerperformance"."computer_id" = 61  AND "revisions"."repo" = 'Trunk' )

The linux query (much longer):

2013-06-11 12:12:56 EEST [unknown] 10.1.3.154(35325) mferreiraLOG:  duration: 22191.773 ms  statement: SELECT "autotests_tracerperformance"."id", "autotests_tracerperformance"."date", "autotests_tracerperformance"."video_id", "autotests_tracerperformance"."revision_id", "autotests_tracerperformance"."computer_id", "autotests_tracerperformance"."probe", "autotests_tracerperformance"."time_tostart", "autotests_tracerperformance"."hang_atstart", "autotests_tracerperformance"."time_tohang", "autotests_tracerperformance"."hang", "autotests_tracerperformance"."crash", "autotests_tracerperformance"."stacktrace", "autotests_tracerperformance"."framemax", "autotests_tracerperformance"."maxtime", "autotests_tracerperformance"."avgtime" FROM "autotests_tracerperformance" INNER JOIN "revisions" ON ("autotests_tracerperformance"."revision_id" = "revisions"."id") WHERE ("autotests_tracerperformance"."computer_id" = 61  AND "revisions"."repo" = 'Trunk' )

executing straight from psql (the fastest):

2013-06-11 12:19:06 EEST psql [local] mferreiraLOG:  duration: 1332.902 ms  statement: SELECT "autotests_tracerperformance"."id", "autotests_tracerperformance"."date", "autotests_tracerperformance"."video_id", "autotests_tracerperformance"."revision_id", "autotests_tracerperformance"."computer_id", "autotests_tracerperformance"."probe", "autotests_tracerperformance"."time_tostart", "autotests_tracerperformance"."hang_atstart", "autotests_tracerperformance"."time_tohang", "autotests_tracerperformance"."hang", "autotests_tracerperformance"."crash", "autotests_tracerperformance"."stacktrace", "autotests_tracerperformance"."framemax", "autotests_tracerperformance"."maxtime", "autotests_tracerperformance"."avgtime" FROM "autotests_tracerperformance" INNER JOIN "revisions" ON ("autotests_tracerperformance"."revision_id" = "revisions"."id") WHERE ("autotests_tracerperformance"."computer_id" = 61  AND "revisions"."repo" = 'Trunk' );

Other queries which do not need to load so many items from the database are performing almost the same.

Why so big time differences between clients for this query?

Note: Transmission times are not relevant, since all machines are in the same intranet. Also, the slower times are seen when the client request comes from the same Linux machine where the postgresql server is running.

Note2: Psycopg2 was installed differently in Windows and Linux. Whereas in Windows I installed it from a pre-packaged binary, in Linux I ran 'pip install psycopg2' which relies on a postgresql installation available on the system. Could this result in different values for parameters affecting performance on the client side (e.g. 'work_mem' parameter) ?

解决方案

You may want to check if the slow client does SSL encryption or not. It happens by default when it's set up on the server and the client has been compiled with SSL support.

For queries that retrieve large amounts of data, the time difference is significant. Also some Linux distributions like Debian/Ubuntu have SSL on by default, even for TCP connections through localhost.

As an example, here's the time difference for a query retrieving 1,5M rows weighing a total of 64Mbytes, with a warm cache.

Without encryption:

$ psql "host=localhost dbname=mlists sslmode=disable"
Password: 
psql (9.1.7, server 9.1.9)
Type "help" for help.

mlists=> \timing
Timing is on.
mlists=> \o /dev/null
mlists=> select subject from mail;
Time: 1672.258 ms

With encryption:

$ psql "host=localhost dbname=mlists"
Password: 
psql (9.1.7, server 9.1.9)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

mlists=> \o /dev/null
mlists=> \timing
Timing is on.
mlists=> select subject from mail;
Time: 7017.935 ms

To turn it off globally, one might set SSL=off in postgresql.conf.

To turn it off for specific ranges of client addresses, add entries in pg_hba.conf with hostnossl in the first field before the more generic host entries.

To turn if off client-side, it depends on how the driver exposes the sslmode connection parameter. If it doesn't, the PGSSLMODE environment variable may be used if the driver is implemented on top of libpq.

As for connections through Unix domain sockets (local), SSL is never used with them.

这篇关于PostgreSQL：在其他客户端中查询速度慢10倍的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！