Categories
程式開發

Greenplum 性能優化之路–(三)ANALYZE


一、為什麼需要ANALYZE

首先介紹下RBO 和CBO,這是數據庫引擎在執行SQL 語句時的2種不同的優化策略。

RBO(基於規則的優化器)

基於規則的優化器,就是優化器在優化查詢計劃的時候,是根據預先設置好的規則進行的,這些規則無法靈活改變。舉個例子,索引優先於掃描,這是一個規則,優化器在遇到所有可以利用索引的地方,都不會選擇掃描。這在多數情況下是正確的,但也不完全如此:

比如一張個人信息表中性別欄目加上索引,由於性別是只有2個值的枚舉類,也就是常說的基數非常低的列,在這種列上使用索引往往效果還不如掃描

SELET count(*) FROM person WHERE gender="M";

因此RBO 的優化方式是死板的,粗放的,目前已逐漸被CBO 方式取代。

CBO(基於成本的優化器)

基於代價的優化器,就是優化器在優化查詢計劃的時候,是根據動態計算出來的Cost(代價)來判斷如何進行選擇。那如何計算代價呢?這裡一般是基於代價模型和統計信息,代價模型是否合理,統計信息是否準確都會影響優化的效果。

還是拿上面員工性別統計為例,在CBO 的優化方式下,物理計劃就不會選擇走索引。當然上面的例子比較簡單,在Greenplum 運行的複雜SQL 中,優化器最核心的還是在scan 和join 的各種實現方式中做出選擇,這才是能大幅提升性能的關鍵點。

前面提到CBO 需要一個代價模型和統計信息,代價模型和規則一樣,需要預先設置好,那統計信息是如何收集的?多數基於CBO 優化的計算引擎,包括Greenplum,Oracle,Hive,Spark 等都類似,除了可以按一定規則自動收集統計信息外,還都支持手動輸入命令進行收集,通常這個命令都叫ANALYZE。

結論:由於CBO 優化的需求,因此我們需要使用ANALYZE 命令去收集統計信息。

二、ANALYZE 怎麼使用

說明

ANALYZE 是Greenplum 提供的收集統計信息的命令。

ANALYZE 支持三種粒度,列,表,庫,如下:

CREATE TABLE foo (id int NOT NULL, bar text NOT NULL) DISTRIBUTED BY (id); // 创建测试表fooANALYZE foo(bar); // 只搜集bar列的统计信息ANALYZE foo; // 搜集foo表的统计信息ANALYZE; // 搜集当前库所有表的统计信息,需要有权限才行

限制

ANALYZE 會給目標表加SHARE UPDATE EXCLUSIVE 鎖,也就是與UPDATE,DELETE,還有DDL 語句衝突。

速度

ANALYZE 是一種採樣統計算法,通常不會掃描表中所有的數據,但是對於大表,也仍會消耗一定的時間和計算資源。

採樣統計會有精度的問題,因此Greenplum 也提供了一個參數default_statistics_target,調整採樣的比例。簡單說來,這個值設置得越大,採樣的數量就越多,準確性就越高,但是消耗的時間和資源也越多。

Greenplum 性能優化之路--(三)ANALYZE 1

default_statistics_target.png

直接修改服務器的參數會影響整個集群,通常不建議這樣操作。如果確實有需要,可以嘗試只修改某列的對應參數,如下:

ALTER TABLE {table_name} ALTER COLUMN {col_name} SET STATISTICS {-1|0-1000};

時機

根據上文所述,ANALYZE 會加鎖並且也會消耗系統資源,因此運行命令需要選擇合適的時機盡可能少的運行。根據Greenplum 官網建議,以下3種情況發生後建議運行ANALYZE

批量加載數據後,比如COPY創建索引之後INSERT, UPDATE, and DELETE 大量數據之後

自動化

除了手動運行,ANALYZE 也可以自動化。實際上默認情況下,我們對空表寫入數據後, Greenplum 也會自動幫我們收集統計信息,不過之後在寫入數據,就需要手動操作了。

有2個參數可以用來調整自動化收集的時機,gp_autostats_mode 和gp_autostats_on_change_threshold。 gp_autostats_mode 默認是on_no_stats,也就是如果表還沒有統計信息,這時候寫入數據會導致自動收集,這之後,無論表數據變化多大,都只能手動收集了。如果將gp_autostats_mode 修改為on_change ,就是在數據變化量達到gp_autostats_on_change_threshold 參數配置的量之後,系統就會自動收集統計信息。

分區表

Greenplum 官網對於分區表的ANALYZE 專門進行了講解,其實只要保持默認值,不去修改系統參數optimizer_analyze_root_partition,那麼對於分區表的操作並沒有什麼不同,直接在root 表上進行ANALYZE 即可,系統會自動把所有葉子節點的分區表的統計信息都收集起來。

如果分區表的數目很多,那在root 表上進行ANALYZE 可能會非常耗時,通常的分區表都是帶有時間維度的,歷史的分區表並不會修改,因此單獨ANALYZE 數據發生變化的分區,是更好的實踐。

三、統計信息去了哪裡

pg_class

表的大小是統計信息裡面最直觀,也幾乎是最重要的,這個信息是放在pg_catalog.pg_class 系統表中,reltuples 代表元組數(行數),relpages 代表實際佔用的page 數目(Greenplum中一個page 為32KB)。

需要注意以下3點

1. reltuples 不是準確值,獲取表的準確行數還是需要count。

2. reltuples 和relpages 需要通過ANALYZE 進行收集,對於已有數據的表,系統不會自動更新。

3. reltuples 和relpages 不一定能對齊,比如條數看起來不多的表,實際佔用的page 數目很大,這種一般是由於數據膨脹(bloat)造成,這時候需要vacuum 等操作。

pg_statistic

關於列的統計信息都是存放在pg_catalog.pg_statistic 系統表中。其中表的每一列(如果有統計)都會有一行對應的數據。了解並掌握pg_statistic 的內容,對於深入理解查詢優化非常重要。

列的統計信息內容很豐富,但是目的都是讓優化器估算出,一個查詢條件,能夠過濾多少數據。

以下列舉了pg_statistic 的重要字段:

Greenplum 性能優化之路--(三)ANALYZE 2

對於stakindN 字段中的統計方式,這裡選擇3個最常見的進行說明:

1. STATISTIC_KIND_MCV

高頻值,在一個列中出現最頻繁的值。

高頻值統計在很多場景下都有價值,這裡舉一個數據傾斜的hash join 例子,如下代碼:

/* * ExecHashBuildSkewHash * *Set up for skew optimization if we can identify the most common values *(MCVs) of the outer relation's join key. We make a skew hash bucket *for the hash value of each MCV, up to the number of slots allowed *based on available memory. */static voidExecHashBuildSkewHash(HashJoinTable hashtable, Hash *node, int mcvsToUse){....}

hash join 場景下,我們需要盡可能的把inner table 構建在內存中,但內存資源是有限的,因此我們需要做出一些選擇,什麼內容優先放入內存中。如果外表有高頻值,那我們可以考慮把高頻值對應的內表信息優先放入到內存中,在實踐中,Greenplum 是單獨構建一個skew hash table 與main hash table 並存。

2. STATISTIC_KIND_HISTOGRAM

直方圖,使用等頻直方圖來描述一個列中的數據的分佈。

直方圖主要用於數據分佈不均勻的情況下,對按列過濾後能返回多少數據進行預估。

舉個例子,一個有3種產品的訂單表,商品A 很熱銷,訂單量在90%,商品B 一般,訂單量在9%,商品C 只有1%,則該列的NDV(Number of Distinct Value)值為3,如果一共有1000000條數據,在沒有直方圖統計的情況下,如果查詢商品C 的訂單,優化器會預計要掃描1000000/3≈330000,因此可能選擇全表scan,如果含有直方圖統計,優化器就知道實際上C 商品可能就幾千條數據,因此會選擇走索引。當然這個例子很簡單,實際情況會復雜很多。

3. STATISTIC_KIND_CORRELATION

相關係數,記錄的是當前列未排序的數據分佈和排序後的數據分佈的相關性。

用於估算索引掃描代價的,統計值在-1到1,值越大,表示相關性越高,也就是使用索引掃描代價越低。

舉個例子,初始化如下2張表

create table t_correlation_asc (id int, number int) DISTRIBUTED BY (id);INSERT INTO t_correlation_asc SELECT 1, i FROM generate_series(1, 1000) AS i; create table t_correlation_desc (id int, number int) DISTRIBUTED BY (id);INSERT INTO t_correlation_desc SELECT 1, 1001-i FROM generate_series(1, 1000) AS i;

在查看表對應的統計信息,可以看出在number 列,你按升序寫入1000個數,該列物理存儲的數據實際上就是按升序排序的,反過來降序寫入1000個數,由於順序是相反的,所以相關性是-1

Greenplum 性能優化之路--(三)ANALYZE 3

related.png

四、例子

以下將會構造一個大小表join 的場景,來說明統計信息的收集對於查詢計劃的影響。

1. 初始化表結構和數據:

CREATE TABLE small_table (id int NOT NULL, bar text NOT NULL) DISTRIBUTED BY (id);INSERT INTO small_table SELECT i, 'test:' || i FROM generate_series(1, 10) AS i; CREATE TABLE big_table (id int NOT NULL, bar text NOT NULL) DISTRIBUTED BY (id);INSERT INTO big_table SELECT i, 'test:' || i FROM generate_series(1, 100000) AS i;

pg_class 中對應的數據如下:

Greenplum 性能優化之路--(三)ANALYZE 4

small_table.png

Greenplum 性能優化之路--(三)ANALYZE 5

big_table.png

2. 大小表join

注意為了構造小表廣播的場景,這里關聯鍵需要選擇非分佈鍵。

Greenplum 性能優化之路--(三)ANALYZE 6

說明1.png

3. 給小表插入數據

這裡給小表插入數據後,小表的數據量超過大表

INSERT INTO small_table SELECT i, 'test:' || i FROM generate_series(1, 200000) AS i;

在沒有ANALYZE 的情況下,pg_class 中的數據沒有發生變化,因此查詢計劃也沒有發生變化。

4. 收集統計信息

運行ANALYZE 收集小表的統計信息,如下:

Greenplum 性能優化之路--(三)ANALYZE 7

new_small_table.png

在運行join 語句,查詢計劃發生變化:

Greenplum 性能優化之路--(三)ANALYZE 8

說明2.png

結論:查詢優化器在收到新的統計信息之後,發現是2張數據量差不多的表進行join,因此選擇重分佈而不是小表廣播。

Greenplum 性能優化之路--(三)ANALYZE 9

關注“騰訊雲大數據”公眾號,技術交流、最新活動、服務專享一站Get~