Postgres 10.3：SELECT查询挂起数小时

Question

我的应用程序使用Postgres作为数据库管理系统，我正在使用安装了Postgis扩展的版本为10.3的Postgres。

偶尔我注意到，在随机时间间隔内，DBMS会变得缓慢，并且卡在一些SELECT查询上。

从pg_stat_activity中我注意到这些查询的wait_event_type和wait_event如下：

 select wait_event_type, wait_event from pg_stat_activity where state='active'; 
 wait_event_type |  wait_event  
-----------------+--------------
 IO              | DataFileRead
 IO              | DataFileRead
 IO              | DataFileRead
 IO              | DataFileRead
 LWLock          | buffer_io
 LWLock          | buffer_io
 IO              | DataFileRead
 LWLock          | buffer_io
 LWLock          | buffer_io
 IO              | DataFileRead
 IO              | DataFileRead
 LWLock          | buffer_io
 LWLock          | buffer_io
 IO              | DataFileRead
 LWLock          | buffer_io
 IO              | DataFileRead
 LWLock          | buffer_io
 LWLock          | buffer_io
 LWLock          | buffer_io
 LWLock          | buffer_io
 LWLock          | buffer_io
 LWLock          | buffer_io
 LWLock          | buffer_io
 LWLock          | buffer_io
 LWLock          | buffer_io
 LWLock          | buffer_io
 LWLock          | buffer_io
 IO              | DataFileRead
 IO              | DataFileRead
                 | 
 IO              | DataFileRead
 LWLock          | buffer_io
 LWLock          | buffer_io
(33 rows)

在查看了文档后，我的假设是底层硬件存在问题，因此我面临的问题与应用程序或查询类型无关，而是与硬件本身有关。

有人遇到过这种问题吗？

- Riccardo

---

我同意。你使用什么文件系统？ - Laurenz Albe

DBMS已安装在Kubernetes Pod上，卷已挂载在网络存储上。 - Riccardo

在这种情况下，很可能是网络问题。 - Laurenz Albe

这可能仍然是应用程序/查询的问题。您提到正在使用PostGIS，是否正确索引列以防止顺序读取？当观察到DataFileRead等待事件长时间运行时，可能就是这种情况。我的建议是捕获其中一个长时间运行的查询并运行“EXPLAIN（ANALYZE，BUFFERS，TIMING）query”。参数log_min_duration_statement和扩展auto_explain是您的好朋友。 - Vinnix

观察到的间歇性可能与以下事实有关：有时您使用的谓词/过滤器需要触及比其他时期通常更多的行。当然，这仍然可能是资源约束问题，例如网络或存储、内存甚至CPU。 - Vinnix

你尝试过什么来调试这个问题吗？在当前状态下，这对我来说看起来与编程无关。 - Nico Haase

Answer 1

一般故障排除建议：

• 开始收集服务器的运行时统计数据 - 有很多工具可供选择 - https://munin-monitoring.org/, https://grafana.com/ + influx db + telegraf，还有更多。无论你选择哪种方案，都应该保留历史统计信息，包括：

  • 每秒完成的磁盘操作数量
  • 磁盘存储的延迟[无论是旋转式硬盘、固态硬盘、NVMe 还是网络附加存储]
  • 服务器 CPU 使用率、负载、内存使用情况

• 还要获取有关 PostgreSQL 的统计信息 - https://www.percona.com/downloads/pmm2 可能会有所帮助

根据这些统计数据 - 查看在问题查询发生之前是否存在任何堆积。

偶尔出现的缓慢可能是由以下原因造成的：

• 存储子系统表现不稳定 [ 固态硬盘寿命到期，RAID阵列巡逻读取，硬盘由于坏扇区重新分配数据 ]
• 错误的索引统计信息导致查询计划次优
• 系统负载过大，无法处理传入的查询
• 系统被其他工作负载过载，如在虚拟化环境中运行时的嘈杂邻居

- pQd