写点什么

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

  • 2024-08-08
    福建
  • 本文字数:2788 字

    阅读完需:约 9 分钟

一、统计信息收集痛点


  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即可。
复制代码


文章转载自:华为云开发者联盟

原文链接:https://www.cnblogs.com/huaweiyun/p/18347040

体验地址:http://www.jnpfsoft.com/?from=infoq

用户头像

还未添加个人签名 2023-06-19 加入

还未添加个人简介

评论

发布
暂无评论
最佳实践:解读GaussDB(DWS) 统计信息自动收集方案_Java_不在线第一只蜗牛_InfoQ写作社区