返回资讯中心
外部精选
软件工程
#开发者实时流

微软分析PostgreSQL迁移中索引扫描性能下降问题

来源标题:An Index Scan Is Only Half the Story

微软工程师协助诊断一起Azure Database for PostgreSQL迁移测试中的查询性能下降:冷缓存下原本几十毫秒的查询变为1-2秒。问题源于单次索引扫描从堆表读取超100页,对比服务器仅60页,IOPS消耗高出10-20%。执行VACUUM FULL后未见改善。

Hacker News 最新plaur7822026/11/14 · 3 天前19 分钟阅读

以下正文由 AI 翻译自 Hacker News 最新(原文版权归原站所有),可能存在偏差,重要内容请以原文为准。

也发布在 Microsoft Blog for PostgreSQL

最近,我与一位计划从本地 PostgreSQL 迁移到 Azure Database for PostgreSQL 的客户合作。在性能测试期间,他们注意到一个令人不安的现象:一个在几十毫秒内完成的查询,现在在评估的 Azure 环境中需要 1-2 秒。

该查询使用的是索引扫描,再次运行很快。但一旦相关数据从缓存中被逐出,较长的执行时间就又回来了。自然,讨论转向了存储性能。IOPS 消耗存在明显差异,待上生产的服务器比他们为对比而搭建的服务器多消耗 10-20% 的 IOPS。一次冷缓存查询执行只做了一次 Index Scan,却从堆中拉取了超过 100 个页面,而另一台服务器只拉取了 60 个页面。很自然地,人们会认为膨胀和/或碎片化是原因,所以他们尝试了 VACUUM FULL,但没有看到任何改善。这时他们找到了我们。

客户的主要担忧——Index Scan 的性能不如预期——引出了两个假设:索引有问题(即膨胀、叶子密度、碎片化),或者存储有问题。用 pg_stattuple 和 pg_statindex 深入检查后,我们没有发现叶子密度或碎片化方面有任何明显问题。我们的遥测数据也没有显示存储有任何问题。既然常见的嫌疑对象都被排除了,我们需要回到索引的基本原则。

重要的是要记住,在对表建立索引时,常规索引扫描使用索引来定位匹配的元组,然后访问表的堆页面来检索它们。索引是有序的;堆不必有序。

假设一个查询返回 200 行。如果这些行位于五个堆页面上,Postgres 可以用相对较少的页面访问来检索它们。如果它们分散在 100 个页面上,检索同样的 200 行就需要大得多的工作集。两个查询在执行计划中都可以显示 Index Scan,但这并不意味着它们做了等效的工作。

我们比较了 EXPLAIN (ANALYZE, BUFFERS) 的输出,不仅关注扫描类型,还关注缓冲区命中和读取。有一点需要注意:共享缓冲区读取意味着该页面不在 PostgreSQL 的共享缓冲区中,并不一定意味着它来自物理存储——操作系统可能仍然缓存着它。启用 track_io_timing 时,I/O 计时有助于区分廉价读取和昂贵读取。

一个有用的线索在 pg_stats 中。这里使用通用的表和列名:

SELECT correlation FROM pg_stats WHERE schemaname = 'public' AND tablename = 'order_items' AND attname = 'order_id';

correlation 描述一列的逻辑顺序与表的物理顺序的接近程度。接近 1 或 -1 的值表示强有序;接近 0 的值表示几乎没有关系。Postgres 在估算索引扫描成本时使用这个统计信息。

在这个案例中,较慢环境的 correlation 约为 0.037,而较快的性能测试环境约为 0.571。这确实表明存在布局差异!

但 correlation 是一个采样的、列范围的统计信息。它不能告诉我们某一次特定查找的行究竟分布在多少个堆页面上。一个表可以将相关行分组打包在一起,而这些组并不以全局排序的顺序出现。我们需要测量查询真正关心的东西。

Postgres 暴露了一个名为 ctid 的系统列,它用 (block_number, tuple_number) 标识元组的物理位置。提取块号可以让我们统计包含匹配行的不同堆页面数:

SELECT count(*) AS rows_found, count(DISTINCT split_part( trim(both '()' FROM ctid::text), ',', 1 )) AS distinct_heap_pages FROM public.order_items WHERE order_id = 12345;

此示例假设是普通的非分区表。对于分区表,应改为统计不同的 (tableoid, block_number) 对,因为不同分区可能有相同的块号。另外,ctid 在这里是诊断工具,不是永久的行标识符——更新和表重写会改变它。

对于两个有代表性的查找值,以下是实际计数,查找值已匿名化:

问题就在这里。对于查找 B,较慢环境中匹配的行占用的堆页面大约是 20 倍。这些计数并不是查询 I/O 的完整核算——索引页面和其他访问仍然重要——但它们直接揭示了结果集的堆占用。

索引并没有找不到行。只是找到行之后,这些行分散在远更多的页面上。

客户是并行将数据加载到 Azure 环境的。这有助于提高加载吞吐量,但并发加载将属于不同查找值的行交错在一起。在源环境中原本打包在一起的相关行,在目标堆中不再落在相邻位置。

并行加载本身并不必然导致表分散。结果取决于工作如何划分以及插入如何到达堆。不过,在这个客户的案例中,加载方法解释了观察到的布局差异。

重建相同的索引并没有恢复这种局部性。B-tree 对其自身的条目排序;构建或重建它不会重新排序底层表。

这也解释了缓存行为。重复执行受益于缓存的页面,掩盖了更大占用带来的成本。一旦这些页面被逐出,查询就必须再次获取它们。更分散的数据也意味着更多页面争夺缓存空间。

(原文较长,此处译出前 6000 字,完整内容请点「打开原文」。)

正文由 探How 从来源站点 RSS 同步,内容未经改写;遇到排版缺失或需要图片、视频时请以原文为准。

打开原文
去社区讨论 →