最佳实践：解读 GaussDB(DWS) 统计信息自动收集方案

摘要：现在商用优化器大多都是基于统计信息进行查询代价评估，因此统计信息是否实时且准确对查询影响很大，特别是分布式数据库场景。本文详细介绍 GaussDB(DWS)如何实现了一种轻量、实时、准确的统计信息自动收集方案。

本文分享自华为云社区《【最佳实践】GaussDB(DWS) 统计信息自动收集方案》，作者： leapdb。

一、统计信息收集痛点

1. 何时做 analyze，多做空耗系统资源，少做统计信息不及时。

2. 多个数据源并发加工一张表，手动 analyze 不能并发。

3. 数据修改后立即查询，统计信息实时性要求高。

4. 需要关心每张表的数据变化和治理，消耗大量人力。

二、基本功能介绍

三、自动收集方案

GaussDB(DWS) 支持统计信息自动收集功能，主要解决统计信息收集不及时和不准确的问题。

手动采样：用户在作业中，手动发起的显示 analyze。

轮询采样：autovacuum 后台线程，轮询发起的 analyze。

动态采样：查询时，优化器触发的 runtime analyze。

前台动态采样：负责统计信息实时准确，信息放内存（有淘汰机制），一级锁（像查询一样轻量）。

autoanalyze=on;autoanalyze_mode='light';

后台轮询采样：负责统计信息的持久化，写系统表（四级锁），不要求特别及时。

autovacuum_mode=mix或analyze;--- 以前只有“后台轮询采样”，都由后台autovacuum线程控制做vacuum或analyze。--- 后来开发“前台动态采样”，叫autoanalyze。--- 请注意二者的区别。

二者都需要开启。

替代场景

统计信息基于收集时表数据生成，数据变化较多后可能失效。自动触发也是基于阈值（50+表大小*10%）。

总结：

1. 小表变化<10%且数据特征变化明显，需要“调低阈值自动收集”。

2. 调整过采样大小且实时性要求高的场景，需要“主动收集统计信息”。

3. 外表和冷热表因访问性能问题，不支持自动，需要“主动收集统计信息”。

四、如何保证及时触发

【触发条件】“无统计信息” or “表的修改量超过一定阈值（默认“50 + 表大小 * 10%”）”

【触发场景】含 stream 计划的 SQL 都可触发动态采样，包括 select 和带条件的 delete, update。

【修改计数】

1. 哪些修改行为会被记录？

DML: Insert, Update, Delete, Copy, Merge，会累加修改计数。

DDL: truncate table，truncate/exchange/drop partition, alter column type, alter distribute，由于 CN 无法获取 DN 修改计数，所以直接记录一个超大修改计数。

2. 跨 CN 查询场景，如何确保修改计数全局一致？

异步广播：autovacuum 后台线程轮询检查时，向所有 CN 广播全局修改计数。修改计数达 2/3 时广播一次，此后每增 10%再广播一次。

实时广播：单 SQL 修改超过 tuple_change_sync_threshold(默认 1W)条时，直接实时广播修改计数到其它 CN。

总结：“修改计数记录”和“修改计数广播”，覆盖都比较全面，能够保证查询及时触发动态采样。

五、最佳实践

GaussDB(DWS) analyze使用指南8.1.3及以下版本

GaussDB(DWS) analyze使用指南8.2.0及以上版本

1.事务块中手动 analyze 堵塞其它业务

【业务场景】

BEGIN;ANALYZE t_ucuser;INSERT INTO t_user_name(project_id, account_id, name_id, uid, etl_time)with t1 AS (select project_id, account_id, name_idfrom t_user_nameWHERE uid is null or uid = '')select a.project_id,a.account_id,a.name_id, b.user_name AS uid, CURRENT_TIMESTAMP AS etl_timefrom t1 a join t_ucuser b ON a.project_id = b.project_id AND a.account_id = b.account_idON CONFLICT(project_id,account_id,name_id) DO UPDATESET project_id=excluded.project_id, account_id=excluded.account_id, name_id=excluded.name_id, uid=excluded.uid, etl_time=excluded.etl_time;END;

【问题根因】

a. 某数据湖用户，多个数据源按照不同的分区进行数据导入加工。

b. 事务块中有手动 analyze，且事务块中后面的查询长时间执行不完。

c. 因 analyze 对表加四级锁长时间不能释放，导致其它相关表上的业务等锁超时报错。

【解决方案】开启 light 动态采样，去掉事务块中的手动 analyze。

2. 多数据源并发加工同一张表的不同分区

【业务场景】

为了保证用户查询表总有数据，需要把加工过程放到一个事务里面。堵塞其它人的动态采样。

begin;alter table tab_partition truncate partition P2023_03;insert into tab_partition select * from t1;end;

【问题根因】alter table truncate parition 对分区加 8 级锁，事务过程中长时间持锁。

【解决方案】使用 exchange partition

CREATE TABLE IF NOT EXISTS tab_tmp1(like tab_partition INCLUDING DROPCOLUMNS INCLUDING DISTRIBUTION INCLUDING STORAGE INCLUDING RELOPTIONS);INSERT INTO tab_tmp1 SELECT * FROM t1;ALTER TABLE tab_partition exchange partition (P2023_03) WITH TABLE tab_tmp1;

3.多表并发反序 analyze 导致统计信息收集失败

【业务场景】

a. 某银行客户，多个表进行批处理数据加工，开启了 normal 类型动态采样。

b. 查询 A 先对 t1 表触发动态采样，再对 t2 表触发动态采样。

c. 查询 B 先对 t2 表触发动态采样，再对 t1 表触发动态采样。

d. 触发动态采样的顺序不一致，互相申请四级锁导致申锁超时，统计信息未收集。

【问题根因】多人同时按不同顺序 analyze 多表导致死锁。

【解决方案】开启 light 动态采样，仅加一级锁不再有四级锁冲突。

4.刚导入的数据不在统计信息中导致查询计划差

【业务场景】

a. 某财经用户，按照月度视为会计期，月初时导入少量数据，然后马上查询。

b. 触发了动态采样，但采集不到最新会计期的少量数据。

【问题根因】新插入数据占比小，及时触发了动态采样但采集不到，导致估算偏差大。

【解决方案】

a. 开启统计信息推算 enable_extrapolation_stats 功能，根据上一个会计期的统计信息推算当前会计期数据特征。

b. 不提高采样大小，利用历史信息增强统计信息准确性。

5.随机函数质量差导致数据特征统计不准

【业务场景】

a. 某银行客户，按月度条件进行关联查询

b. 多次 analyze，最多数据月份在 MCV 中占比从 13%~30%大幅波动

c. 详细输出样本点位置和采样随机数发现，随机数（小数点后 6 位）生成重复度高导致采样扎堆儿严重。

【问题根因】采样随机数不够随机，样本采集不均匀导致 MCV 数据特征统计偏差。

【解决方案】

a. 每次传入随机种子再生成随机数，提高随机性和并发能力。控制参数 random_function_version。

b. 不提高采样大小，提升随机数质量增强统计信息准确性。

6.样本分布不均匀导致数据特征统计不准

【业务场景】

a. tpc-h 的 lineitem 表 l_orderkey 列，数据每 4~8 条批量重复。即同一个订单购买多个商品。

b. 传统采样算法由于采样不均匀，采集到的重复数据稍多，导致采集的 distinct 值偏低。

【问题根因】数据特征分布不均匀，采样无法抓准数据特征，distinct 值高的场景统计出的 distinct 值偏低。

【解决方案】

a. 使用自研的优化蓄水池采样算法，控制参数 analyze_sample_mode=2，让采样更加均匀，以提升统计信息准确性。

b. 如果上述方法没有达到预期效果，可以手动修改 distinct 值。

select APPROX_COUNT_DISTINCT(l_orderkey) from lineitem; --近似计算distinct值alter table lineitem alter l_orderkey set (n_distinct=10000); --手动设置distinct值，然后再analyze即可。

点击关注，第一时间了解华为云新鲜技术~