Postgresql：在不同客户端中查询速度慢 10 倍

查看 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 [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' )

Linux 查询（更长）：

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' )

直接从 psql 执行（最快）：

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' );

其他不需要从数据库加载这么多项目的查询的执行几乎相同。

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

Note：传输时间不相关，因为所有机器都在同一个 Intranet 中。此外，当客户端请求来自运行 postgresql 服务器的同一台 Linux 计算机时，速度会变慢。

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

您可能想检查慢速客户端是否进行 SSL 加密。当它在服务器上设置并且客户端已编译为支持 SSL 时，默认情况下会发生这种情况。

对于检索大量数据的查询，时间差异很大。 此外，一些 Linux 发行版（例如 Debian/Ubuntu）默认启用 SSL，即使对于通过 localhost 的 TCP 连接也是如此。

例如，以下是使用热缓存检索 150 万行、总大小为 64MB 的查询的时间差。

不加密：

$ 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
  

带加密：

$ 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
  

要全局关闭它，可以设置SSL=off in postgresql.conf.

要针对特定​​范围的客户端地址关闭它，请在pg_hba.conf with hostnossl在更通用之前的第一个字段中host条目。

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

至于通过 Unix 域套接字的连接（local)，SSL 从未与它们一起使用。