GaussDB 统计信息自动收集机制详解：策略、分区表处理与性能优化

开发技术
1

GaussDB 统计信息自动收集机制详解，含策略、分区表处理及性能优化建议。

原文标题：GaussDB数据库统计信息自动收集机制

原文作者：牧羊人的方向

冷月清谈：

本文深入探讨了 GaussDB 数据库统计信息自动收集机制，该机制通过 AutoVacuum 定期更新统计信息，确保优化器选择最优执行计划。文章首先介绍了AutoVacuum 的工作原理及相关参数，包括触发 ANALYZE 的阈值设定。接着，重点讨论了分区表的统计信息收集策略，包括分区表和表分区两个维度的收集机制，以及数据倾斜可能带来的性能问题。最后，针对统计信息不准确导致的性能问题，提出了快速发现和应急恢复的建议，强调事前优化和事后处理的重要性，如规避数据倾斜、手动收集统计信息等。

怜星夜思：

1、GaussDB 中 autovacuum_analyze_scale_factor 参数默认值是 10%，这个比例在实际生产环境中是否需要调整？如果需要，基于什么考量？
2、文章提到分区表统计信息收集可能存在滞后问题，除了手动收集统计信息外，有没有其他更自动化的解决方案，比如自定义告警或者任务调度？
3、如果确认是由于统计信息不准确导致SQL执行计划劣化，除了收集统计信息和绑定执行计划外，还有没有其他更灵活的优化手段？例如，是否可以临时调整SQL的优化器参数？

原文内容

数据库的统计信息是数据库中用于描述数据分布和存储特性的关键元数据，主要用于数据库性能优化和查询执行。统计信息提供了数据分布特征（如行数、唯一值数量、数据倾斜等），帮助优化器估算不同执行计划的成本。数据库的优化器会基于这些统计信息从多种可能得执行路径中选择执行计划最优的一个。当统计信息不准确时，比如表数据已经大幅变化但是统计信息未及时更新，优化器可能选择低效的执行计划进而引发性能问题。本文只讨论GaussDB数据库统计信息自动收集其中的技术问题。
1、数据库的统计信息自动收集策略
1.1 统计信息自动收集策略
GaussDB数据库通过AutoVacuum机制来实现统计信息的自动更新。AutoVacuum后台有常驻线程AVClauncher，这个线程会定期唤起AVCWorker线程来执行实际的vacuum与analyze任务。一旦识别出过时数据，AutoVacuum会执行VACUUM操作，将过时数据行标记为可重用的空间，并更新表的统计信息，以确保查询优化器能够做出正确的执行计划。

当autovacuum设置为on时，系统会定时启动autovacuum线程（自动清理线程）自动执行VACUUM和ANALYZE命令，回收被标识为删除状态的记录空间，并更新表的统计数据。两次autovacuum的执行间隔时间由参数autovacuum_naptime控制，默认时间为10分钟。

  • 对于空表而言，当表中插入数据的行数大于50时，会触发表自动进行ANALYZE。
  • 对于表中已有数据的情况，阈值设定为 
autovacuum_analyze_threshold+ autovacuum_analyze_scale_factor*reltuples

其中autovacuum_analyze_threshold表示当表上被删除、插入或更新的记录数超过设定的阈值时才会对这个表执行ANALYZE操作，默认值为50；autovacuum_analyze_scale_factor表示触发一个ANALYZE时增加到autovacuum_analyze_threshold的表大小的缩放系数，默认值为10%；reltuples是表的总行数。

这里简单理解就是，如果是一张空表，当增删改超过50行就会在下一个收集间隔内触发统计信息自动收集，如果是非空表，增删改记录数超过50+10%*总行数，即会触发统计信息自动收集。至于上一个收集周期的增删改行数变化，则是在数据库层记录每个表上实际发生的增、删、改行数 (n_tup_ins，n_tup_upd，n_tup_del)。

1.2 统计信息采样

触发统计信息收集后，会根据系统设定的配置采样，数据库中默认的采样比例是30000行，由GUC参数default_statistics_target控制默认为100*300。这个采样比例也可以设置为百分比，例如1%即按照当前表记录数的1%进行采样，但是对于大表这个采样的数据就过多，可能会带来一些性能损耗。根据最佳实践，这个值设置为默认值即可，毕竟最懂数据库优化器的还是厂商自己。

对于采样的记录数，数据库会对这些行进行统计，计算诸如不同值数量（n_distinct）、空值比例、高频值（MCV）和数据分布直方图（histogram_bounds）等信息。基于样本的统计结果（如平均每页行数、数据分布），结合表的已知总页数（relpages），估算出全表的行数（reltuples）及其他列级特征。其它数据库统计信息收集的方式大体都是这样，也是权衡性能的最佳机制，毕竟统计信息收集也是一种性能消耗，不可能对表尤其是大表进行全部记录的统计。基于抽样的估算统计信息机制大部分时间是准确的，但是针对一些特殊场景可能存在统计信息不准确，尤其是在分区表以及数据倾斜的场景下。

2、分区表的统计信息自动收集策略

分区表（Partitioned Table）指在单节点内对表数据内容按照分区键以及围绕分区键的分区策略对表进行逻辑切分。从数据分区的角度来看是一种水平分区（horizontal partition）策略方式，将表按照指定规则划分为多个数据互不重叠的部分。从数据分布而言，分区表分为主表和子表，主表是一个逻辑上的概念，实际的数据是存放在表分区子表之上，表分区可以增加删除、修改，存放的文件系统也可以不同，主表只需要同步全局的表结构和维护全局分区表的字典信息以及统计信息。

相比较普通表，分区表不仅可以提升数据库查询性能，在分区裁剪的场景下只需要查询指定的分区，不需要对所有分区扫描，从而提升查询效率；另外如果某个分区出现故障，表在其它分区的数据依然可用。那么分区表的统计信息搜集策略是怎样，有两个维度：

  1. 分区表的维度，当所有分区的增删改达到表统计信息自动收集的阈值，会触发统计信息自动收集，对所有满足条件的分区收集统计信息，并汇总到分区表中；
  2. 表分区维度，当全局表没有达到自动收集的阈值，但是单个分区的增删改达到这个阈值时，也会触发该分区的统计信息自动收集。从数据库的表现来看，当单个分区的统计信息收集后，并没有同步更新到分区表上。

举个例子，比如某一张分区表有100w数据，10个分区，每个分区平均有10w条数据，当分区2的新插入了2w条数据，对于整张表而言没有达到10%增量变化的阈值，不会触发全局表的统计信息自动收集。但是对于分区2而言，达到了该分区10%增量变化的阈值，会对该分区单独进行统计信息收集。

备注：在GaussDB数据库505.2.1.SPC0800以前版本中表分区的统计信息自动收集只会计算update和delete的记录数，并没有统计insert。不过用最新505.2.1.SPC0900金融稳定版本测试这一块已经包括了insert的记录数，在这一块已经做了优化。

那么，当表分区数据发生倾斜而统计信息不准确时候会怎样？如果是大表的统计信息没有及时更新到，同时该分区又占了该表的大部分的数据比重（数据发生倾斜），导致优化器在选择的时候，最终选择了全表扫描。因为优化器是基于统计信息做的代价评估，由于统计信息不准确，认为全表扫描的代价比走索引的代价更低，导致应用访问的SQL性能变慢。由于应用访问数据库变慢，表现在应用层就是接口变慢，导致交易处理耗时变长，在应用层出现新的连接请求，进而导致数据库层连接数上涨，最终达到数据库支撑的上限，出现数据库连接数满的现象。
3、如何及时发现和快速应急
对于因统计信息不准确导致的执行计划不准确，引发的性能问题怎么办。当生产出现该问题时，从“1分钟发现、5分钟定界、10分钟恢复”的角度，该如何快速应急恢复。在《》一文中也提到数据库故障分为三个层次：
  • 基础设施层故障：如硬件故障、网络故障和机房环境故障等
  • 数据库实例层故障：数据库连接异常、数据库性能下降、事务问题以及内部资源耗尽等
  • 数据库服务层故障：数据库服务不可用、数据库认证和授权、数据一致性等

对于数据库性能问题而言，传统的应急几板斧“杀会话、切换、重启、收集统计信息、限流、执行计划绑定”等，真正能快速定位问题并应急的也只有收集统计信息或者执行计划绑定，对于主备切换或者实例重启，并不能根本解决问题，有性能问题依旧；而杀会话或者限流，不可避免带来服务降级，也没解决问题。执行计划劣化的问题最难判定，目前大部分国产数据库并不支持查看历史执行计划信息，也没有类似Oracle数据库执行计划跳变检测的能力，甚至执行计划动态绑定也不支持，仅有类似SQL Patch的绑定功能，复杂的SQL问题场景下并不好使。

因此，在事前事后需要具体实施层面的优化了，比如应用层设计上如何规避数据倾斜的问题、大批量的数据导入手动收集统计信息、长时间未收集统计信息的排查等。

参考资料：

2

我之前遇到过类似的问题，当时尝试过调整SQL的优化器参数，但效果并不明显。后来，我发现是由于表之间的关联关系不合理导致的。我调整了表的关联顺序，并且增加了一些过滤条件，SQL的执行效率立马提升了好几个数量级。所以，我觉得优化SQL的关键是要理解SQL的执行逻辑，找到性能瓶颈，然后采取相应的措施。

3

紧急情况下，调整SQL的优化器参数确实是一种临时抱佛脚的办法。GaussDB 应该也支持类似 Oracle hint 的机制，可以在SQL语句中指定优化器的行为，比如强制使用索引、选择特定的连接方式等。但是，这种方法风险比较大，需要对优化器的工作原理非常了解，否则可能会适得其反。而且，这种方法只对特定的SQL语句有效，不能解决根本问题。所以，我个人建议只在万不得已的情况下使用。

4

关于autovacuum_analyze_scale_factor这个参数，我觉得不能一概而论说要不要调整，得看实际情况。默认的10%可能对某些场景比较通用，但如果你的表数据量特别大，10%的采样可能就显得有些多了，反而会增加数据库的负担。相反，如果表数据量比较小，10%可能又不够，导致统计信息不够准确。所以，最好是结合业务特点，观察数据库的性能指标，比如CPU使用率、IO等待等，逐步调整这个参数，找到一个平衡点。

5

学院派的回答一下，autovacuum_analyze_scale_factor的调整确实需要谨慎。从理论上讲，调整的目的是为了在统计信息的准确性和资源消耗之间找到一个最优解。如果你的数据库资源比较紧张，可以适当降低这个值，减少采样数据量，降低CPU和IO的压力。但如果业务对查询性能要求非常高，可以适当提高这个值，提高统计信息的准确性。另外，还可以考虑使用PG提供的扩展统计信息功能，针对特定的查询场景，收集更详细的统计信息。

6

从DBA的角度来看，自动化方案是必须的。除了脚本和告警，还可以结合数据库的扩展功能，比如PG的auto_explain插件，可以记录执行时间超过阈值的SQL，然后分析这些SQL的执行计划，如果发现是因为统计信息不准确导致的，就可以自动触发ANALYZE。另外，一些数据库管理工具也提供了类似的功能，可以监控数据库的性能，自动诊断问题并给出优化建议。

7

我个人觉得这个参数还是保持默认比较好，除非你对GaussDB的优化器非常了解，并且遇到的SQL查询确实存在因为统计信息不准导致的问题。否则，盲目调整可能会适得其反。厂商默认的设置通常是经过大量测试和实践验证的，更适合大多数场景。如果实在要调整，建议小步快跑，每次调整幅度不要太大，并且密切关注数据库的性能变化。

8

说到自动化，那肯定有搞头。可以考虑写个脚本，定期检查分区表的各个分区的修改量，如果超过一定阈值，就自动触发ANALYZE。这个脚本可以跑在数据库服务器上，也可以跑在外部的监控系统上。另外，还可以设置告警，当发现某个分区表的查询性能出现明显下降时，就自动触发ANALYZE或者通知DBA intervene。关键是要设置合理的阈值和告警规则，避免误报或者漏报。

9

从根本上解决问题才是王道。除了收集统计信息和绑定执行计划，还可以考虑重写SQL语句，优化查询逻辑。有时候，同样的查询目的，不同的SQL写法，执行效率可能会差很多。另外，还可以考虑增加索引，优化表结构，调整数据库参数等。总之，要从多个方面入手，找到性能瓶颈，然后逐个解决。