PostgreSQL对极短查询的解析速度异常缓慢

Question

我正在使用Zend框架(PHP)和postgresql作为会话存储后端。有时我会收到大量像这样的日志：

Mar  8 11:07:00 myhost postgres[79149]: [30640132-1] 0 LOG:  00000: duration: 1401.742 ms  parse pdo_stmt_00000005: SELECT "sessions".* FROM "php"."sessions" WHERE ((("sessions"."id" = '3d5tmqutaeuivtf8a1udfa5i04')))
Mar  8 11:07:00 myhost postgres[79150]: [30640151-1] 0 LOG:  00000: duration: 1400.083 ms  parse pdo_stmt_00000007: SELECT "sessions".* FROM "php"."sessions" WHERE ((("sessions"."id" = 'b2vh1r29vnqg1e3600ther40c3')))
Mar  8 11:07:00 myhost postgres[79152]: [30640135-1] 0 LOG:  00000: duration: 1401.261 ms  parse pdo_stmt_00000005: SELECT "sessions".* FROM "php"."sessions" WHERE ((("sessions"."id" = '3d5tmqutaeuivtf8a1udfa5i04')))
Mar  8 11:07:00 myhost postgres[79147]: [30640166-1] 0 LOG:  00000: duration: 1381.648 ms  parse pdo_stmt_00000009: SELECT "sessions".* FROM "php"."sessions" WHERE ((("sessions"."id" = '6uj0955g64mmd9i8ra1q5nbtd5')))

php.sessions表在任何时刻都有大约500-1000行。

这似乎很奇怪，因为执行此语句的时间未被记录为慢速，但解析几乎是“无穷无尽”的。

有任何线索吗？是否有人知道任何postgres查询解析器速度问题？

一些技术背景：

我正在使用CentOS 6.0上的PostgreSQL 8.4.9，它是一台具有128 GB RAM的2x10核Intel机器。此时CPU仅使用20％-25％。磁盘读/写非常快。log_min_statement = 500。

- krawiec.a

---

目录上的锁？缺乏 shared_buffers？尝试查看锁列表，也许使用预处理语句。 - wildplasser

可能还有其他受限资源充当漏斗/热点。至少解析器是一个（合理的）无害等待的地方。 - wildplasser

你能在不同的环境中复制这个吗？ - Kuberchaun

你的共享缓冲区大小为32GB，看起来太大了...共享缓冲区决定了服务器用于保存等待内核缓冲区和CPU处理的请求的数量，32GB太大了，建议暂时将其缩小到64MB。另外，你展示的查询似乎都是非常小的单行查询，而你却针对过度大的查询进行了优化。 - Twelfth

当可用内存的20到25％时，shared_buffers效果最佳，在Linux上最大为8GB。但是您必须根据自己的情况进行测试。 4MB是一个非常奇怪的想法，数据库几乎永远不会在缓冲区中有数据，因为没有空间。这也对所有查询计划产生了巨大影响，它们很糟糕。 - Frank Heikens

Answer 1

这个案例似乎是：很多长时间处于空闲状态的事务，即<IDLE> in transaction。我们已经设法摆脱了其中大部分，结果非常出色。

主要原因不幸的是应用程序逻辑有缺陷。我的意思是，部分事务看起来像这样：

• begin
• query
• query
• 等待
• ...（很长时间的等待）
• 等待
• commit

由于行版本子系统必须保留大量旧版本的行，因此该系统变得越来越不响应（每个简单查询都必须查找适当的行版本）。

- krawiec.a

---

好老的锁。最好将您的会话查找保留在不同的数据库事务中。 - Chris Travers

Answer 2

在以下情况下，我在测试盒上遇到了类似的情况：

• 服务器上运行CPU密集型进程；
• 系统开始将RAM换出到磁盘以进行RAM密集型进程。

PostgreSQL依赖于2层数据缓存：

1. 共享池，通过shared_buffers指定；
2. 操作系统缓存，通过effective_cache_size指定，请告诉我们您的值是多少？

为了了解您的系统实际发生了什么，您应该监视：

• CPU使用率；
• RAM使用率；
• IO和交换卷。

通过“监视”，我不仅指查看当前值，而是使用像sar、iostat、vmstat等工具，结合RRDtool进行更好的数据分析。然后，针对您观察到简单查询中不需要的延迟的时间段，查看生成的报告。

我有一种感觉，您正在遇到IO问题，但如果没有查看系统和报告，就无法得出更多信息。

我建议：

1. 设置监控并查看生成的报告；
2. 在类似的服务器上创建一个备用数据库，以便尝试不同的设置。（我假设您有适当的数据库和WAL备份来执行此操作。）我会研究：内存、自动清理、检查点和WAL设置。
3. 考虑升级到PostgreSQL 9.1，您已经落后了2个主要版本。

- vyegorov

---

1. 这台机器专门用于PostgreSQL
2. 不进行RAM交换
3. 有很多缓冲区和缓存
4. 监控所有内容
5. 不断改进查询计划 :)
6. 由于实时需求，既不考虑复制也不考虑升级。

- krawiec.a