主页 / dba / 问题 / 340252
Accepted
Doe Jowns
Asked: 2024-06-15 19:21:42 +0800 CST

PostgreSQL 中 CLUSTER 的性能不稳定

  • 772

我使用的是 PostgreSQL 12，并且有分区表，我想将它们聚类。每个分区的大小大致相同（以 GB 为单位）。但是，性能可能会有很大差异，每个分区的聚类时间从 5 分钟到 1000 分钟不等。

我正在并行对多个分区进行集群，并且我的数据库当前没有收到除 CLUSTER 请求之外的任何请求。

我不完全了解 CLUSTER 过程的来龙去脉。如何解释聚类时间如此大的变化？我可以做些什么来提高性能？

编辑：更准确地说，我有一个 34 GB 的分区，在 13 分钟内完成聚类，还有一个 56 GB 的分区，在 1288 分钟内（几乎一天）完成聚类。这些大小值是聚类前的大小。我正在重新计算大小。

用于检索大小的查询：

SELECT table_name, 
       pg_size_pretty(pg_total_relation_size(table_schema || '."' || table_name || '"')) AS size
FROM information_schema.tables
WHERE table_schema = 'partitionschema' and table_name like 'mytable_%' order by table_name;

不过，在聚类之前我没有执行 ANALYZE 或 VACUUM。

我有 900MB/s 的磁盘 I/O 和 128GB 的​​ RAM（尽管根据我的 datadog 仪表板，似乎只使用了 40 左右）。我的处理器似乎也不是一个限制。

我有 300 个分区需要集群，在流程的每个步骤中，我都尝试并行集群 8 个分区。每个分区都在 ~50GB 中

其他一些信息：

  • 维护工作内存：4GB
  • 工作模因：64MB
  • 共享缓冲区：32GB
  • 最大wal大小：4GB
query-performance

1 个回答

  1. Best Answer

    终于找到了我的答案！

    在文档中提到，CLUSTER 可以使用两种方法从应用 CLUSTER 的表中获取数据。

    • 索引扫描：速度较慢，通过索引扫描以排序的方式直接读取数据，逐步对数据进行排序。

    • 顺序扫描：按顺序读取所有数据，然后对其进行排序。如果内存容量不够，则此操作需要使用临时文件，因此可能会消耗磁盘空间。

    默认情况下，该方法是自动选择的，但你可以强制使用索引扫描set enable_sort = 'off;，并使用顺序扫描set enable_indexscan = 'off;'

    在我的~150M 行表上，检查表pg_stat_progress_cluster，索引扫描读取速度为 100-300 元组/秒，而序列扫描为 100K 元组/秒。

    我使用的是默认的自动规划设置，我认为在我的第一个示例中使用了 Seq Scan，而在第二个聚类中使用了 Index Scan。

    我的最终解决方案是运行

    set enable_indexscan = 'off'; CLUSTER ...;

    我仍然不完全理解为什么我的索引扫描这么慢，但我可能必须忍受它。

    • 1

相关问题