postgresql - 我们有间歇性的缓慢查询。我们的 PostgreSQL 是否在内存不足？

Question

我正在调查一些缓慢的查询，我需要一些帮助来读取我获得的数据。

我们有一个特定的查询，它使用索引并且大部分时间运行得非常快，但有时它运行缓慢(700 毫秒以上)，不知道为什么。

Limit  (cost=8.59..8.60 rows=1 width=619) (actual time=5.653..5.654 rows=1 loops=1)
   ->  Sort  (cost=8.59..8.60 rows=1 width=619) (actual time=5.652..5.652 rows=1 loops=1)
         Sort Key: is_main DESC, id
         Sort Method: quicksort  Memory: 25kB
         ->  Index Scan using index_pictures_on_imageable_id_and_imageable_type on pictures  (cost=0.56..8.58
rows=1 width=619) (actual time=3.644..5.587 rows=1 loops=1)
               Index Cond: ((imageable_id = 12345) AND ((imageable_type)::text = 'Product'::text))
               Filter: (tag = 30)
               Rows Removed by Filter: 2
 Planning Time: 1.699 ms
 Execution Time: 5.764 ms

如果我理解正确，我会说查询的几乎所有开销都在索引扫描上，对吧？这对我来说听起来不错，那么为什么同一个查询有时运行起来很慢呢？

我开始想，也许我们的实例无法将整个索引保存在内存中，所以它时不时地使用磁盘。这可以解释缓慢的查询。但是，这超出了我的理解范围。这有意义吗？

该表有大约 1500 万行和 5156 MB 大小。索引为 1752 MB。顺便说一句，它是一个 btree 索引。

我们的 PostgreSQL 位于“高度可用”的 Google Cloud SQL 实例上。它有 2 个 vCPU 和 7.5 GB 的 RAM。我们整个数据库的大小约为 35 GB。

CPU 消耗几乎从不超过 40%。它通常稳定在 20-30% 左右。

检查实例内存图，我注意到消耗量增长到约 4 GB，然后下降到约 700 MB，然后又开始增长。那是一个重复的模式。

理论上，该实例有 7.5 GB 的 RAM，但我不知道是否所有这些都应该可用于 PostgreSQL。无论如何，大约 3.5 GB 仅用于操作系统听起来很高，对吧？

Memory graph

我读到这些配置很重要，所以把它们扔在这里(Cloud SQL 默认值):

shared_buffers                             | 318976
temp_buffers                               | 1024
work_mem                                   | 4096

考虑到我们有一堆其他的表和索引，如果一个索引单独是 1.7 GB，那么整个实例的 7.5 GB 是否太低了？

有什么方法可以断言我们是否有内存问题？

感谢您的帮助。

Answer 1

可以帮助您的三件事:

1. 此功能会在您的内存中永久地对 table 进行“预热”。这大大减少了您的磁盘访问，对性能有很大帮助。预热的限制是资源。所以，并不是所有的表都可以放在内存中。如果表很小或不经常访问，则不建议这样做。每次您的数据库停止时，在下一次启动数据库时，您需要再次运行 pg_prewarm()

   https://www.postgresql.org/docs/current/pgprewarm.html

2. 在您的索引上创建一个 CLUSTER。您可以为每个表创建一个集群。聚类索引是获得良好数据访问的好方法。数据的存储方式与集群有关，因此，访问先前排序的数据的确定位置非常快。

   CLUSTER [VERBOSE] table_name [ USING index_name ]
   

   引用:https://www.postgresql.org/docs/current/sql-cluster.html

3. 在表上定期运行 VACUUM ANALYZE。 Postgresql 收集有关您的查询的统计信息，并使用专注于优化您的查询的分析选项在真空中对信息进行分类。