EDB博客

PostgreSQL中的索引损坏查询的隐藏成本

可以

PostgreSQL中的索引损坏查询的隐藏成本

对受影响的关系执行DDL或DML操作时,数据库库中的损坏通常以错误的形式表现出来。由于缺少文件,错误通常显示为读取表的错误,这当然很简单,但是损坏的索引不是就像损坏的表一样明显经常要查找是否确实存在索引损坏,这需要观察行为执行时间和查询本身的结果

您可能在数据库中看到索引损坏的一些症状是什么

使用索引的查询正在执行更多顺序扫描

B一些更加警惕的DBA密切关注pg stat用户表,发现idx扫描列的增长速度比seq扫描慢得多,或者根本没有变化

C用户抱怨他们接收到的某些数据不是他们期望的

在大多数情况下,在情况A和B中,您很容易被误认为结论是您的统计信息已过时。在情况C中,此行为将引导我们更趋向于损坏的索引,但是如果没有更多的知识,很难确定是一种情况,对于那些寻求解决方案的人来说,“走走走走”的方法通常收效甚微。因此,我们决定将其转化为博客(对于普通PostgreSQL用户而言如此罕见),我们的目标是帮助您做到这一点难以捉摸的问题更容易确定

注意由于博客的隐私和合规性限制,我们当然不使用实际的用户数据,而是使用示例测试用例来展示与所见相似的结果

意外的查询结果

我们的用户有一个查询,这是他常规工作流程的一部分,这是一项工作的一部分,该工作根据应用程序的需求清除了旧数据。在该工作的最后几次执行中,用户看到要清除的行数大大减少了这项工作,但没有像保留规则这样的通常可疑的例子,没有改变任何解释问题的方法。此外,看起来运行该工作的表的大小也在稳步增加

消除显而易见的

由于在作业执行过程中未记录任何错误,因此我们开始查看数据库日志中是否有任何错误警告,该作业在某一天工作正常,没有任何问题,但是有一天突然停止工作。很好,出了什么问题

我们从消除明显的

  1. 业务逻辑表结构是否发生了任何变化
  2. 保留规则是否有任何变化
  3. 是否有任何批量操作,例如数据加载或截断

下一步是检查在最后执行清除作业时生成的日志的内容,我们遇到了以下内容

错误无法读取文件中的块xxx

错误是什么意思

让我们快速看一下什么是块以及为什么无法读取块导致查询失败在PostgreSQL中,所有表和索引都存储为页面集合,这些页面默认为KB,尽管可以在服务器期间自定义编译由于在编译过程中定义页面后页面的大小不会改变,因此当我们谈论表页面时,这些页面在逻辑上都是等效的。但是,使用索引时,第一页通常保留为元页面,不同之处在于它在内部承载控制信息结果是,单个索引中可能存在不同类型的页面。在这种情况下,该KB页面在错误中称为块

因此,我们可以得出结论,该查询正在块页面中查找信息,但由于某种原因无法读取该信息。好消息由于该错误指向特定的块,因此该块本身可能是唯一的损坏。表而不是整个表

注意如果您希望获得有关PostgreSQL页面结构的更多信息,请参考数据库页面布局PostgreSQL文档部分

缩小范围

查看Postgres数据库日志,我们发现与此类似

CEST用户postgres db postgres应用程序psql bin客户端本地STATEMENT解释分析从pgbench帐户中选择的位置CEST用户postgres db postgres应用程序psql bin客户端本地ERROR无法读取文件库中的块,仅读取字节

我们可以使用类似的查询来找出包含该块的文件实际上属于什么文件

postgres选择n个nspname AS模式c relname来自pg类c内部连接pg命名空间n在c relnamespace n oid上,其中c relfilenode模式关系公共idx pgbench帐户行postgres

注意在上面的查询中,我们忽略了文件名的一部分,因为只有与第一段的文件名相对应的前面的数字与filenode相同。有关更多详细信息,请参阅数据库文件布局在PostgreSQL文档中

现在我们知道无法读取的块是在查询中调用的索引的一部分,但是我们仍然不知道表本身是否还可以

检查表是否健全的可靠方法是对索引所属的表进行pg dump,因为pg dump不使用任何索引,而是直接读取表数据。下面是输出示例

bash选择PostgresPlus AS bin pg转储p v t pgbench帐户postgres备份pgbench帐户sql备份pgbench帐户日志bash尾备份pgbench帐户日志CREATE INDEX idx pgbench帐户在pgbench帐户上使用btree辅助功能在EDT EnterpriseDB数据库上完成bump

因此,我们表中的数据很好,我们需要做的就是重建索引以解决问题。我们将在本文的后面部分中展示如何重建索引

如果

让我们添加一个假设但可能的转折,其中日志中没有错误下一步该怎么办下一步我们没有错误,查询运行正常,但用户坚持认为返回的数据不正确我们可以生成查询s解释分析计划这应该显示它从哪里获取数据

postgres解释分析从pgbench帐户中进行选择的情况,在哪里进行帮助和辅助pgbench帐户上的查询和计划位图堆扫描成本行宽度实际时间行循环重新检查cond辅助和援助堆块对idx pgbench帐户成本行宽度实际时间行循环索引精确扫描辅助和辅助规划时间ms执行时间ms行

因此查询正在使用索引

下一步将是强制查询直接命中表格,然后查看结果是否有任何不同。可以通过设置参数来完成启用索引扫描在psql会话中关闭参数并再次执行查询

postgres设置启用indexscan off SET postgres解释分析从pgbench帐户中选择援助的情况pgbench帐户上的查询计划seq扫描成本行宽度实际时间行循环过滤助剂行被过滤器计划时间删除执行时间ms执行时间ms行postgres

如果在这种情况下,使用索引扫描和顺序扫描的输出计数存在差异,则您有理由相信索引有问题

解决问题

现在,我们有足够的数据可以得出结论,索引存在问题并且它已损坏。要解决此问题,我们必须重建索引,并且我们有几种选择方法,每种方法各有利弊

使用REINDEX名称使用此名称将允许读取但锁定索引父表的所有写入。还将在良性处理的索引上放置排他锁,这意味着即使对该索引的读取尝试也将在此操作期间失败。使用它是预定的停机时间或精益活动时间

B用途立即重新索引这是一个更好的选择,但仅在Postgres版本以上可用

C用途同时创建索引并删除旧的损坏的索引如果您使用的版本比最好的版本早,则最好使用CREATE INDEX CONCURRENTLY创建新索引,这不会阻止您在表上的现有操作,然后使用下降指数删除旧版本最好的使用时间是精打细算的时间,因为CREATE INDEX CONCURRENTLY由于大量更新而变慢,因此在工作时会在表上插入删除

未来建议

任何DBA从未陷入腐败的总体机会很少,因此,如果它们很可能在某个时刻发生,并且您的恢复取决于早期发现问题的能力,您该怎么办?答案不仅是一回事,而且还有许多监控校验和和pg catcheck是解决问题的几种方法,可以使环境恢复健康,但是取决于我们在先前文章中谈到的内容,PITR备份以及WAL流备用服务器和延迟备用服务器都可以在各种方面提供帮助在遇到问题时从问题中解救出来的方法在所有策略中,谨慎是可用来确保数据生存和放心的最佳特征。

加入Postgres Pulse Live

我们利用解决的问题和与Postgres的交谈来帮助Postgres,虽然这种情况很少见,但是对于我们来说,发现解决方案对于那些遇到索引成本无形的人特别有用腐败在这里你可以找到所有事情Postgres Pulse包括我们所有的博客文章和YouTube系列

请在5月1日星期一加入我们的下一个Pulse Live课程我们将深入探讨本周围绕索引损坏等问题和难题,并从参加会议的任何人那里提问。您可以通过以下电子邮件发送问题:postgrespulse enterprisedb comTwitter上的主题标签或活动期间的现场直播就在这儿你在等什么

该博客文章由Ajay Patel和Deepanshu Sharma共同撰写

ajaypatel的头像

Ajay Patel是RemoteDBA团队的交付经理,通过大型和大型Postgres部署为全球客户提供支持。他在数据库顾问方面的专业服务经验超过十年,在加入Patel的EDB之前是Pro的DBA顾问。