一文讀懂autoanalyze使用【這次高斯不是數(shù)學(xué)家】

analyze執(zhí)行的是否及時，在一定程度上直接決定了SQL執(zhí)行的快慢。因此，GaussDB(DWS)引入了自動統(tǒng)計信息收集，可以做到讓用戶不再擔(dān)心統(tǒng)計信息是否過期。

1. 自動收集場景

需要進(jìn)行自動統(tǒng)計信息收集的場景通常有五個：批量DML結(jié)束時，增量DML結(jié)束時，DDL結(jié)束時，查詢開始時和后臺定時任務(wù)。

INSERT,UPDATE,DELETE,UPSERTCOPY,MERGE

所以，為了避免對DML，DDL帶來不必要的性能開銷和死鎖風(fēng)險，我們選擇了在查詢開始前觸發(fā)analzye。

2. 自動收集原理

GaussDB(DWS)在SQL執(zhí)行過程中，會記錄表增刪改查相關(guān)的運行時統(tǒng)計信息，并在事務(wù)提交或回滾后記錄到共享的內(nèi)存種。

這些信息可以通過 “pg_stat_all_tables視圖” 查詢，也可以通過下面函數(shù)進(jìn)行查詢。

pg_stat_get_tuples_inserted --表累積insert條數(shù) pg_stat_get_tuples_updated --表累積update條數(shù) pg_stat_get_tuples_deleted --表累積delete條數(shù) pg_stat_get_tuples_changed --表自上次analyze以來，修改的條數(shù) pg_stat_get_last_analyze_time --查詢最近一次analyze時間

因此，根據(jù)共享內(nèi)存中 "表自上次analyze以來修改過的條數(shù)" 是否超過一定閾值，就可以判定是否需要做analyze了。

3. 自動收集閾值

3.1 全局閾值

autovacuum_analyze_threshold #表觸發(fā)analyze的最小修改量 autovacuum_analyze_scale_factor #表觸發(fā)analyze時的修改百分比

當(dāng)"表自上次analyze以來修改的條數(shù)" >= autovacuum_analyze_threshold + 表估算大小 * autovacuum_analyze_scale_factor時，需要自動觸發(fā)analyze。

3.2 表級閾值

--設(shè)置表級閾值 ALTER TABLE item SET (autovacuum_analyze_threshold=50); ALTER TABLE item SET (autovacuum_analyze_scale_factor=0.1); --查詢閾值 postgres=# select pg_options_to_table(reloptions) from pg_class where relname='item'; pg_options_to_table --------------------------------------- (autovacuum_analyze_threshold,50) (autovacuum_analyze_scale_factor,0.1) (2 rows) --重置閾值 ALTER TABLE item RESET (autovacuum_analyze_threshold); ALTER TABLE item RESET (autovacuum_analyze_scale_factor);

不同表的數(shù)據(jù)特征不一樣，需要觸發(fā)analyze的閾值可能有不同的需求。表級閾值優(yōu)先級高于全局閾值。

3.3 查看表的修改量是否超過了閾值（僅當(dāng)前CN）

postgres=# select pg_stat_get_local_analyze_status('t_analyze'::regclass); pg_stat_get_local_analyze_status ---------------------------------- Analyze not needed (1 row)

4. 自動收集方式

GaussDB(DWS)提供了三種場景下表的自動分析。

當(dāng)查詢中存在“統(tǒng)計信息完全缺失”或“修改量達(dá)到analyze閾值”的表，且執(zhí)行計劃不采取FQS (Fast Query Shipping)執(zhí)行時，則通過autoanalyze控制此場景下表統(tǒng)計信息的自動收集。此時，查詢語句會等待統(tǒng)計信息收集成功后，生成更優(yōu)的執(zhí)行計劃，再執(zhí)行原查詢語句。

當(dāng)autovacuum設(shè)置為on時，系統(tǒng)會定時啟動autovacuum線程，對“修改量達(dá)到analyze閾值”的表在后臺自動進(jìn)行統(tǒng)計信息收集。

6.凍結(jié)統(tǒng)計信息

6.1 凍結(jié)表的distinct值

當(dāng)一個表的distinct總是估算不準(zhǔn)，例如：數(shù)據(jù)扎堆兒重復(fù)場景。如果表的distinct值固定，可以通過以下方式凍結(jié)表的distinct值。

postgres=# alter table lineitem alter l_orderkey set (n_distinct=0.9); ALTER TABLE postgres=# select relname,attname,attoptions from pg_attribute a,pg_class c where c.oid=a.attrelid and attname='l_orderkey'; relname | attname | attoptions ----------+------------+------------------ lineitem | l_orderkey | {n_distinct=0.9} (1 row) postgres=# alter table lineitem alter l_orderkey reset (n_distinct); ALTER TABLE postgres=# select relname,attname,attoptions from pg_attribute a,pg_class c where c.oid=a.attrelid and attname='l_orderkey'; relname | attname | attoptions ----------+------------+------------ lineitem | l_orderkey | (1 row)

6.2. 凍結(jié)表的全部統(tǒng)計信息

如果表的數(shù)據(jù)特征基本不變，還可以凍結(jié)表的統(tǒng)計信息，來避免重復(fù)進(jìn)行analyze。

alter table table_name set frozen_stats=true;

7. 手動查看表是否需要做analyze

a. 不想在業(yè)務(wù)高峰期時觸發(fā)數(shù)據(jù)庫后臺任務(wù)，所以不愿意打開autovacuum來觸發(fā)analyze，怎么辦？

b. 業(yè)務(wù)修改了一批表，想立即對這些表馬上做一次analyze，又不知道都有哪些表，怎么辦？

c. 業(yè)務(wù)高峰來臨前想對臨近閾值的表都做一次analyze，怎么辦？

我們將autovacuum檢查閾值判斷是否需要analyze邏輯，抽取成了函數(shù)，幫助用戶靈活主動的檢查哪些表需要做analyze。

7.1 判斷表是否需要analyze（串行版，適用于所有歷史版本）

-- the function for get all pg_stat_activity information in all CN of current cluster. CREATE OR REPLACE FUNCTION pg_catalog.pgxc_stat_table_need_analyze(in table_name text) RETURNS BOOl AS $$ DECLARE row_data record; coor_name record; fet_active text; fetch_coor text; relTuples int4; changedTuples int4:= 0; rel_anl_threshold int4; rel_anl_scale_factor float4; sys_anl_threshold int4; sys_anl_scale_factor float4; anl_threshold int4; anl_scale_factor float4; need_analyze bool := false; BEGIN --Get all the node names fetch_coor := 'SELECT node_name FROM pgxc_node WHERE node_type=''C'''; FOR coor_name IN EXECUTE(fetch_coor) LOOP fet_active := 'EXECUTE DIRECT ON (' || coor_name.node_name || ') ''SELECT pg_stat_get_tuples_changed(oid) from pg_class where relname = ''''|| table_name ||'''';'''; FOR row_data IN EXECUTE(fet_active) LOOP changedTuples = changedTuples + row_data.pg_stat_get_tuples_changed; END LOOP; END LOOP; EXECUTE 'select pg_stat_get_live_tuples(oid) from pg_class c where c.oid = '''|| table_name ||'''::REGCLASS;' into relTuples; EXECUTE 'show autovacuum_analyze_threshold;' into sys_anl_threshold; EXECUTE 'show autovacuum_analyze_scale_factor;' into sys_anl_scale_factor; EXECUTE 'select (select option_value from pg_options_to_table(c.reloptions) where option_name = ''autovacuum_analyze_threshold'') as value from pg_class c where c.oid = '''|| table_name ||'''::REGCLASS;' into rel_anl_threshold; EXECUTE 'select (select option_value from pg_options_to_table(c.reloptions) where option_name = ''autovacuum_analyze_scale_factor'') as value from pg_class c where c.oid = '''|| table_name ||'''::REGCLASS;' into rel_anl_scale_factor; --dbms_output.put_line('relTuples='||relTuples||'; sys_anl_threshold='||sys_anl_threshold||'; sys_anl_scale_factor='||sys_anl_scale_factor||'; rel_anl_threshold='||rel_anl_threshold||'; rel_anl_scale_factor='||rel_anl_scale_factor||';'); if rel_anl_threshold IS NOT NULL then anl_threshold = rel_anl_threshold; else anl_threshold = sys_anl_threshold; end if; if rel_anl_scale_factor IS NOT NULL then anl_scale_factor = rel_anl_scale_factor; else anl_scale_factor = sys_anl_scale_factor; end if; if changedTuples > anl_threshold + anl_scale_factor * relTuples then need_analyze := true; end if; return need_analyze; END; $$ LANGUAGE 'plpgsql';

7.2 判斷表是否需要analyze（并行版，適用于支持并行執(zhí)行框架的版本）

-- the function for get all pg_stat_activity information in all CN of current cluster. --SELECT sum(a) FROM pg_catalog.pgxc_parallel_query('cn', 'SELECT 1::int FROM pg_class LIMIT 10') AS (a int); 利用并發(fā)執(zhí)行框架 CREATE OR REPLACE FUNCTION pg_catalog.pgxc_stat_table_need_analyze(in table_name text) RETURNS BOOl AS $$ DECLARE relTuples int4; changedTuples int4:= 0; rel_anl_threshold int4; rel_anl_scale_factor float4; sys_anl_threshold int4; sys_anl_scale_factor float4; anl_threshold int4; anl_scale_factor float4; need_analyze bool := false; BEGIN --Get all the node names EXECUTE 'SELECT sum(a) FROM pg_catalog.pgxc_parallel_query(''cn'', ''SELECT pg_stat_get_tuples_changed(oid)::int4 from pg_class where relname = ''''|| table_name ||'''';'') AS (a int4);' into changedTuples; EXECUTE 'select pg_stat_get_live_tuples(oid) from pg_class c where c.oid = '''|| table_name ||'''::REGCLASS;' into relTuples; EXECUTE 'show autovacuum_analyze_threshold;' into sys_anl_threshold; EXECUTE 'show autovacuum_analyze_scale_factor;' into sys_anl_scale_factor; EXECUTE 'select (select option_value from pg_options_to_table(c.reloptions) where option_name = ''autovacuum_analyze_threshold'') as value from pg_class c where c.oid = '''|| table_name ||'''::REGCLASS;' into rel_anl_threshold; EXECUTE 'select (select option_value from pg_options_to_table(c.reloptions) where option_name = ''autovacuum_analyze_scale_factor'') as value from pg_class c where c.oid = '''|| table_name ||'''::REGCLASS;' into rel_anl_scale_factor; dbms_output.put_line('relTuples='||relTuples||'; sys_anl_threshold='||sys_anl_threshold||'; sys_anl_scale_factor='||sys_anl_scale_factor||'; rel_anl_threshold='||rel_anl_threshold||'; rel_anl_scale_factor='||rel_anl_scale_factor||';'); if rel_anl_threshold IS NOT NULL then anl_threshold = rel_anl_threshold; else anl_threshold = sys_anl_threshold; end if; if rel_anl_scale_factor IS NOT NULL then anl_scale_factor = rel_anl_scale_factor; else anl_scale_factor = sys_anl_scale_factor; end if; if changedTuples > anl_threshold + anl_scale_factor * relTuples then need_analyze := true; end if; return need_analyze; END; $$ LANGUAGE 'plpgsql';

7.3 判斷表是否需要analyze（自定義閾值）

-- the function for get all pg_stat_activity information in all CN of current cluster. CREATE OR REPLACE FUNCTION pg_catalog.pgxc_stat_table_need_analyze(in table_name text, int anl_threshold, float anl_scale_factor) RETURNS BOOl AS $$ DECLARE relTuples int4; changedTuples int4:= 0; need_analyze bool := false; BEGIN --Get all the node names EXECUTE 'SELECT sum(a) FROM pg_catalog.pgxc_parallel_query(''cn'', ''SELECT pg_stat_get_tuples_changed(oid)::int4 from pg_class where relname = ''''|| table_name ||'''';'') AS (a int4);' into changedTuples; EXECUTE 'select pg_stat_get_live_tuples(oid) from pg_class c where c.oid = '''|| table_name ||'''::REGCLASS;' into relTuples; if changedTuples > anl_threshold + anl_scale_factor * relTuples then need_analyze := true; end if; return need_analyze; END; $$ LANGUAGE 'plpgsql';

通“優(yōu)化器觸發(fā)的實時analyze”和“后臺autovacuum觸發(fā)的輪詢analyze”，GaussDB(DWS)已經(jīng)可以做到讓用戶不再關(guān)心表是否需要analyze。建議在最新版本中試用。

征文活動

【這次高斯不是數(shù)學(xué)家】有獎?wù)魑幕馃徇M(jìn)行中：https://bbs.huaweicloud.com/blogs/345260

GaussDB數(shù)據(jù)庫 云端實踐 數(shù)據(jù)倉庫服務(wù) GaussDB(DWS)

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。