远程 Postgresql - 非常慢

Question

我在自己的 VPS 上设置了 PostgreSQL - 访问数据库的软件是一个名为 PokerTracker 的程序。

PokerTracker 会记录您玩在线扑克时的所有手牌和统计数据。

我希望可以从几台不同的计算机上访问它，因此决定将其安装在我的 VPS 上，经过一些小问题后，我设法让它连接，没有错误。

然而，表现却很糟糕。我已经对“远程 postgresql 慢”等进行了大量研究，但尚未找到答案，因此希望有人能够提供帮助。

需要注意的是：

我尝试执行的查询非常小。在 VPS 上本地连接时，查询会立即运行。

远程运行时，运行查询大约需要 1 分 30 秒。

VPS 运行 100MBPS，然后我连接到它的计算机位于 8MB 线路上。

两者之间的网络通信几乎是即时的，我能够很好地远程连接，没有任何延迟，并且托管了几个运行 MSSQL 的网站，并且所有查询都会立即运行，无论是远程连接还是本地连接，因此它似乎是 PostgreSQL 特有的。

我正在运行他们最新版本的软件以及与他们的软件兼容的最新版本 PostgreSQL。

该数据库是一个新数据库，几乎不包含任何数据，我运行了vacuum/analyze等都无济于事，我没有看到任何改进。

我不明白 MSSQL 如何几乎可以立即查询，但 PostgreSQL 却如此挣扎。

我能够毫无问题地远程登录到 VPS IP 上的端口 5432，正如我所说，查询确实执行，只是需要非常长的时间。

我注意到的是，当查询运行时，路由器上几乎没有使用任何带宽 - 但话又说回来，我不希望它用于简单的查询，但不确定这是否是问题所在。我现在尝试在 3 个不同的网络（包括不同的路由器）上进行远程连接，但问题仍然存在。

通过 LAN 通过另一台计算机进行远程连接是即时的。

我还编辑了 postgre conf 文件以允许更多的内存/缓冲区等，但我不认为这是问题 - 我要求它做的事情非常简单 - 它根本不应该是密集的。

谢谢， Ricky

编辑：请注意客户端和服务器都运行 Windows。

这是配置文件中的信息。

pg_hba - currently allowing all traffic:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# IPv4 local connections:
host     all     all     0.0.0.0/0   md5
# IPv6 local connections:
# host   all     all     ::1/128     md5

和 postgresqlconf - 我知道我已经给这个配置提供了一些巨大的缓冲区/内存，只是为了测试这是否是问题 - 仅显示未注释的行：

listen_addresses = '*'
port = 5432
max_connections = 100
shared_buffers = 512MB
work_mem = 64MB
max_fsm_pages = 204800
shared_preload_libraries = '$libdir/plugins/plugin_debugger.dll'
log_destination = 'stderr'
logging_collector = on
log_line_prefix = '%t '
datestyle = 'iso, mdy'
lc_messages = 'English_United States.1252'
lc_monetary = 'English_United States.1252'
lc_numeric = 'English_United States.1252'
lc_time = 'English_United States.1252'
default_text_search_config = 'pg_catalog.english'

需要任何其他信息，请告诉我。感谢您的帮助。

Answer 1

我启用了日志记录并将日志发送给他们的软件开发人员。他们的答案是，该软件最初旨在在本地或近本地数据库上运行，因此由于网络延迟，在 VPS 上运行预计会很慢。

感谢您的帮助，但看起来我已经没有主意了，这是由于软件而不是 VPS 上的 PostgreSQL 造成的。

谢谢，
瑞奇

Answer 2

您可以进行解释分析，它会告诉您在服务器上查询的执行时间（没有将结果发送到客户端的网络开销）。

如果服务器执行时间非常快（与您看到的时间相比），则这是一个网络问题。如果报告的时间与您观察到的非常相似，则这是 PostgreSQL 问题（然后您需要发布执行计划以及可能的 PostgreSQL 配置）

Answer 3

被这个问题困扰了一段时间，这个问题引导我找到了答案，所以我想我会分享，以防它有帮助。

服务器有一个辅助网络接口 (eth1)，被设置为默认路由。执行查询的客户端与 eth0 在同一子网内，因此这应该不会导致任何问题......但确实如此。

禁用默认路由会使查询在正常时间范围内返回。但长期解决方案是将 listen_addresses 从 '*' 更改为正确的 IP。

Answer 4

使用网络监控工具（我推荐wireshark，因为它可以跟踪许多协议，包括postgresql的）来查看网络连接是否正常。如果连接不好，您将看到丢弃/重传的数据包。

Answer 5

也许 Postgres 正在尝试使用 ident 对您进行身份验证，但这不起作用（例如防火墙排除） ，并且必须等待超时才能允许通过其他方式连接。

尝试使用 psql 查询远程服务器的 select version() - 这应该是即时的，因为它不接触磁盘。

如果不是即时的，请发布您的 pg_hba.conf （未注释的行）。

另一个可能的原因：

• 使用 RevDNS 进行身份验证；
• 服务器或客户端上的防病毒软件；
• 其他一些连接正在阻塞表或行，因为它没有明确结束。

Answer 6

这并不是为什么 pg 通过 VPN 访问速度慢的答案，但一个可能的解决方案/替代方案是设置 TeamPostgreSQL 通过浏览器访问 PG。它是一个 AJAX Web 应用程序，包含一些非常方便的功能，用于导航数据和管理数据库。

这也可以避免连接断开，根据我的经验，通过 VPN 使用 pg 时这种情况很常见。

还有用于 Web 访问的 phpPgAdmin，但我提到了 TeamPostgreSQL，因为它对于导航和获取数据库中数据的概述非常有帮助。