【文末彩蛋】數據倉庫服務 GaussDB(DWS)單點性能案例集錦

一、數據傾斜

1.1 問題描述

某局點SQL執(zhí)行慢，涉及大表的SQL執(zhí)行不出來結果。

1.2 分析過程

數據傾斜在很多方面都會有體現：

1）gs_ssh –c “df -h”

查看各個數據磁盤的利用率，會有不均衡的現象。正常情況下，利用率最高和利用率最高的磁盤空間相差不大，如果磁盤利用率相差超過了5%就要引起重視。

2）通過等待視圖查看作業(yè)的運行情況，發(fā)現作業(yè)總是等待部分DN，或者個別DN。

Select wait_status, count(*) cnt from pgxc_thread_wait_status where wait_status not like ‘%cmd%’ and wait_status not like ‘%none%’ and wait_status not like ‘%quit%’ group by 1 order by 2 desc;

3）慢語句的explain performance顯示，基表scan的時間和行數各個DN之間不均衡。

基表scan的時間最快的dn耗時5ms，最慢的dn耗時1173ms

數據最多的dn有22831616行，其他dn都是0行，數據有嚴重傾斜。

4）通過傾斜檢查接口可以發(fā)現數據傾斜。

select table_skewness('store_sales');

select table_distribution('public','store_sales');

5）通過資源監(jiān)控發(fā)現，個別節(jié)點的CPU/IO明顯比其他節(jié)點高。

1.3 問題根因

GaussDB當前支持Hash表和復制表兩種分布方式。默認創(chuàng)建的表是Hash分布的，如果不指定分布鍵，則選擇表的第一列作為分布鍵。那么這種情況就可能存在傾斜的。

傾斜造成的負面影響非常大。

首先，SQL的性能會非常差，因為數據只分布在部分DN，那么SQL運行的時候就只有部分DN參與計算，沒有發(fā)揮分布式的優(yōu)勢。

其次，會導致資源傾斜，尤其是磁盤?？赡懿糠执疟P的空間已經接近極限，但是其他磁盤利用率很低。

可能出現部分節(jié)點CPU過高等等問題。

1.4 解決詳情

如何找到傾斜的表：

1）在庫中表個數少于1W的場景，直接使用傾斜視圖查詢當前庫內所有表的數據傾斜情況。

SELECT * FROM pgxc_get_table_skewness ORDER BY totalsize DESC;

2）在庫中表個數非常多（至少大于1W）的場景，因PGXC_GET_TABLE_SKEWNESS涉及全庫查并計算非常全面的傾斜字段，所以可能會花費比較長的時間（小時級），建議參考PGXC_GET_TABLE_SKEWNESS視圖定義，直接使用table_distribution()函數自定義輸出，減少輸出列進行計算優(yōu)化，例如：

SELECT schemaname,tablename,max(dnsize) AS maxsize, min(dnsize) AS minsize FROM pg_catalog.pg_class c INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace INNER JOIN pg_catalog.table_distribution() s ON s.schemaname = n.nspname AND s.tablename = c.relname INNER JOIN pg_catalog.pgxc_class x ON c.oid = x.pcrelid AND x.pclocatortype = 'H' GROUP BY schemaname,tablename;

表的分布鍵的選擇方法：

1）這個列的distinct值比較大，并且沒有明顯的數據傾斜。也可以把多列定義成分布列。

怎么看distinct的大??？

select count(distinct column1) from table;

怎么看數據是不是有傾斜？

select count(*) cnt, column1 from table group by column1 order by cnt limint 100;

2）選用經常做JOIN字段/group by的列，可以減少STREAM運算。

3）不好的實踐：

分布列用默認值（第一列）

分布列用sequence自增生成

分布列用隨機數生成(除非任意列，或者任意兩列的組合做分布鍵都是傾斜的，一般不選用這種方法)。

二、統(tǒng)計信息未收集

2.1 問題描述

略

2.2 分析過程

1. 通過explain verbose/explain performance打印語句的執(zhí)行計劃

2. 執(zhí)行計劃中會有語句未收集統(tǒng)計信息的告警，并且通常E-rows估算非常小。

3. 上述例子中，在打印的執(zhí)行計劃中有Warning提示信息，提示有哪些列在這個執(zhí)行計劃中用到了，但是這些列沒有統(tǒng)計信息。

在CN的pg_log日志中也有會有類似的Warning信息。

同時，E-rows會比實際值小很多。

2.3 問題根因

優(yōu)化器是基于代價的優(yōu)化 (Cost-Based Optimization，簡稱CBO)。在這種優(yōu)化器模型下，數據庫根據表的元組數、字段寬度、NULL記錄比率、distinct值、MCV值、HB值等表的特征值，以及一定的代價計算模型，計算出每一個執(zhí)行步驟的不同執(zhí)行方式的輸出元組數和執(zhí)行代價(cost)，進而選出整體執(zhí)行代價最小/首元組返回代價最小的執(zhí)行方式進行執(zhí)行。

統(tǒng)計信息是優(yōu)化器生成執(zhí)行計劃的基礎，沒有收集統(tǒng)計信息，優(yōu)化器生成的執(zhí)行計劃會非常差，如果統(tǒng)計信息未收集，會導致多種多樣表現形式的性能問題。例如，等值關聯(lián)走NestLoop，大表broadcast，集群CPU持續(xù)增高等等問題。

2.4 解決詳情

周期性地運行ANALYZE，或者在對表的大部分內容做了更改之后馬上執(zhí)行analyze。

三、語句不下推

3.1 問題描述

略

3.2 分析過程

1）通過explain verbose打印語句執(zhí)行計劃

2）上述執(zhí)行計劃中有__REMOTE關鍵字，這就表明當前的語句是不下推執(zhí)行的。

3）不下推語句在pg_log中會打印不下推的原因。上述語句在CN的日志中會找到類似以下的日志：

3.3 問題根因

目前最新版本可以支持絕大多數常用函數的下推。

不下推函數的場景主要出現在自定義函數屬性定義錯誤的場景。

不下推語句的執(zhí)行方式沒有利用分布式的優(yōu)勢，他的執(zhí)行過程相當于把大量的數據和計算過程匯集到一個節(jié)點上去做，因此性能往往非常差。

3.4 解決詳情

審視用戶自定義函數的provolatile屬性是否定義正確。如果定義不正確，要修改對應的屬性，使它能夠下推執(zhí)行。

具體判斷方法可以參考如下說明：

函數相關的所有屬性都在pg_proc這張系統(tǒng)表中可以查到。其中與函數能否下推相關的兩個屬性是provolatile 和 proshippable。

其中provolatile是繼承自PG的字段，他的本質含義是描述函數是IMMUTABLE/STABLE/VOLATILE的。

簡單來講，如果一個函數對于同樣的輸入，一定有相同的輸出，那么這類函數就是IMMUTABLE的，例如絕大部分的字符串處理函數。

如果一個函數的返回結果在一個SQL語句的調用過程中，結果是相同的，那么他就是STABLE的。例如時間相關的處理函數，他的最終顯示結果可能與具體的GUC參數相關（例如控制時間顯示格式的參數），這類函數都是STABLE的。

如果一個函數的返回結果可能隨著每一次的調用而返回不同的結果。例如nextval，random這種函數，每次調用結果都是不可預期的，那么他就是VOLATILE的。

四、not in 和 not exists

4.1 問題描述

客戶的SQL語句執(zhí)行慢，執(zhí)行計劃中有NestLoop

4.2 問題定位

1.首先觀察SQL語句中有not in 語法

2.執(zhí)行計劃中有NestLoop

4.3 問題根因

NestLoop是導致語句性能慢的主要原因。

Hashjoin只能做等值關聯(lián)。NestLoop的條件中有or條件，所以無法用Hashjoin求解。

導致出現這個現象的原因是由not in的語義決定的(具體可以參考外網關于not in 和 not exists的介紹)。

4.4 解決詳情

大多數場景下，客戶需要的結果集其實是可以通過not exists獲得的，因此上述語句可以通過修改將not in 修改為not exists。

五、未分區(qū)剪枝

5.1 問題描述

三條sql查詢慢，查詢的分區(qū)表總共185億條數據，查詢條件中沒有涉及分區(qū)鍵

select passtime from 表 where passtime<'2020-02-19 15:28:14' and passtime>'2020-02-18 15:28:37' order by passtime desc limit 10; select max(passtime) from 表 where passtime<'2020-02-19 15:28:14' and passtime>'2020-02-18 15:28:37';

列存表，分區(qū)鍵為createtime,哈希分布鍵為motorvehicleid

5.2 分析過程

1.和客戶確認部分業(yè)務慢，慢的業(yè)務中都涉及到了同一張表tb_motor_vehicle

2.和客戶收集幾個典型的慢sql，分別打印執(zhí)行計劃

從執(zhí)行計劃中可以看出來，兩條sql的耗時都集中在Partitioned CStore Scan on public.tb_motor_vehicle列存表的分區(qū)掃描上

3.和客戶確認，該表的分區(qū)鍵為createtime，而涉及到的sql中無任何createtime的篩選和過濾條件，基本可以確認是由于慢sql的計劃沒有走分區(qū)剪枝，導致了全表掃描，對于185億條數據量的表，全表掃描性能會很差。

4.通過在篩選條件中增加分區(qū)鍵過濾條件，優(yōu)化后的sql和執(zhí)行計劃如下：

SELECT passtime FROM tb_motor_vehicle WHERE createtime > '2020-02-19 00:00:00' AND createtime < '2020-02-20 00:00:00' AND passtime > '2020-02-19 00:00:00' AND passtime < '2020-02-20 00:00:00' ORDER BY passtime DESC LIMIT 10000;

性能從十幾分鐘，優(yōu)化到了12秒左右，性能有明顯提升

5.3 問題根因

慢sql過濾條件中未涉及分區(qū)字段，導致執(zhí)行計劃未分區(qū)剪枝，走了全表掃描，性能嚴重裂化

5.4 解決詳情

在慢sql的過濾條件中增加分區(qū)篩選條件，避免走全表掃描

六、行數估算過小，走了nestloop

6.1 問題描述

查詢語句執(zhí)行慢，卡住無法返回結果

sql特點是2-3張表left join，然后通過select查詢結果，執(zhí)行計劃如下：

6.2 分析過程

1.排查當前的IO，內存，CPU使用情況，沒有發(fā)現資源占用高的情況

2.查看慢sql的線程等待狀態(tài)

select * from pg_thread_wait_status where query_id=’149181737656737395’;

根據線程等待狀態(tài)，并沒有出現都在等待某個DN的情況，初步排除中間結果集偏斜到了同一個DN的情況。

3.到相應的實例節(jié)點上，打印等待狀態(tài)為none的線程堆棧信息如下：

gstack 14104

通過反復打印堆棧信息，發(fā)現堆棧在變化，并沒有hang死，所以初步判斷該問題未性能慢的問題，堆棧中有VecNestLoopRuntime，以及結合執(zhí)行計劃，初步判斷是由于統(tǒng)計信息不準，優(yōu)化器評估結果集較少，計劃走了nestloop導致性能下降。

4.對表執(zhí)行analyze后性能并沒有太大改善

5.對sql增加hint關閉索引，讓優(yōu)化器強行走hashjoin，發(fā)現hint功能沒有生效，原因是hint無法改變子查詢中的計劃

6.通過set enable_indexscan = off;執(zhí)行計劃被改變，走了Hash Left Join，慢sql在3秒左右跑出結果，滿足客戶需求。

6.3 問題根因

優(yōu)化器在選擇執(zhí)行計劃時，對結果集評估較小，導致計劃走了nestloop，性能下降

6.4 解決詳情

通過set set enable_indexscan = off;關閉索引功能，讓優(yōu)化器生成的執(zhí)行計劃不走nestloop，而走Hashjoin

七、表數據膨脹，未清理臟數據

7.1 問題描述

數據庫性能時快時慢問題

GaussDB 數據庫性能時快時慢問題，原先幾秒鐘的sql，目前20幾秒出來，導致前臺IOC頁面數據加載超時，無法對用戶提供圖表顯示

7.2 分析過程

1. raid卡緩存策略未開啟、CPU開啟了節(jié)能模式，查詢并未開啟

/opt/MegaRAID/MegaCli/MegaCli64 -LDinfo -Lall –aAll |grep 'Write Cache'（root用戶） cat /proc/cpuinfo |grep MHz

2.和客戶確認是部分業(yè)務慢，可以提供部分慢sql，打印執(zhí)行計劃，耗時主要在index scan上，懷疑是IO爭搶導致，通過監(jiān)控IO，發(fā)現并沒有IO資源使用瓶頸。

3.查詢當前活躍sql，發(fā)現有大量的create index語句，需要和客戶確認該業(yè)務是否合理

select * from pg_stat_activity where state !=’idle’ and usename !=’omm’;4.根據執(zhí)行計劃，發(fā)現在部分DN上耗時較高，查詢表的傾斜情況，并未發(fā)現有傾斜的情況

select table_skewness(‘ioc_dm.m_ss_index_event’);5.檢查內存相關參數，設置不合理，需要優(yōu)化

單節(jié)點總內存大小為256G

max_process_memory為12G，設置過小

shared_buffers為32M，設置過小

work_mem：CN：64M 、DN：64M

max_active_statements: -1（不限制并發(fā)數）

設置方式如下：

gs_guc set -Z coordinator -Z datanode -N all -I all -c "max_process_memory=25GB" gs_guc set -Z coordinator -Z datanode -N all -I all -c "shared_buffers=8GB" gs_guc set -Z coordinator -Z datanode -N all -I all -c "work_mem=128MB"

6.進一步分析掃描慢的原因，發(fā)現表數據膨脹嚴重，對其中一張8G大小的表，總數據量5萬條，做完vacuum full后大小減小為5.6M

7.3 問題根因

1.大量表頻繁增刪改，未及時清理，導致臟數據過多，表數據膨脹，查詢慢

2.交付時，內存參數設置不合理

7.4 解決詳情

1.對業(yè)務涉及到的常用的大表，執(zhí)行vacuum full操作，清理臟數據；

2.設置GUC內存參數

八、“in 常量”優(yōu)化

8.1 問題描述

簡單的大表過濾的SQL語句中有一個“in 常量”的過濾條件，常量的個數非常多(約有2000多個)，基表數據量比較大，SQL語句執(zhí)行不出來。

8.2 分析過程

1.打印語句的執(zhí)行計劃：

2.執(zhí)行計劃中，in條件還是作為普通的過濾條件存在。這種場景下，最優(yōu)的執(zhí)行計劃應該是將“in 常量”轉化為join操作性能更好。

8.3 問題根因

執(zhí)行計劃中，in條件還是作為普通的過濾條件存在。這種場景下，最優(yōu)的執(zhí)行計劃應該是將“in 常量”轉化為join操作性能更好。

8.4 解決詳情

qrw_inlist2join_optmode可以控制把“in 常量”轉join的行為。默認是cost_base的。如果優(yōu)化器估算不準，可能會出現需要轉化的場景沒有做轉化，導致性能較差。

這種情況下可以通過設置qrw_inlist2join_optmode為rule_base來規(guī)避解決。

九、相關子查詢1

9.1 問題描述

用戶的SQL性能差，執(zhí)行計劃中有SubPlan的關鍵字

9.2 分析過程

執(zhí)行計劃中有SubPlan，這類語句的性能往往比較差。

9.3 問題根因

執(zhí)行計劃中有SubPlan的語句往往性能比較差，這是因為，引用SubPlan結果的算子可能需要反復的調用獲取這個SubPlan的值，即SubPlan以下的結果要重復執(zhí)行很多次。

9.4 解決詳情

這類問題通常通過改寫SQL來規(guī)避。往往這種場景的SQL語句的改寫是比較困難，而且很容易出現改寫后的結果不一致問題。

由于我們在比較高的版本上已經支持了很多場景想的SubPlan的自動轉化為join操作，因此一種比較方便的思路是打印他在高版本下的執(zhí)行計劃(explain verbose)，然后根據explain verbose 演繹出來改寫后的SQL語句。

以上述為例，他在高版本的執(zhí)行計劃如下：

那么根據上述信息，SQL語句可以改寫為：

為了確認改寫后的語句與原來的語句是等價的，可以再次打印改寫后的執(zhí)行計劃，對比：

十、相關子查詢2

10.1 問題描述

UPDATE場景下出現了SubPlan導致語句執(zhí)行性能差

10.2 分析過程

上述執(zhí)行計劃中有SubPlan，這類語句的性能往往比較差。

10.3 問題根因

執(zhí)行計劃中有SubPlan的語句往往性能比較差，原因與1.9章節(jié)案例類似。

10.4 解決詳情

上述問題可以通過特定的改寫方法來解決：

十一、單表點查性能差

11.1 問題描述

單表查詢的場景下，客戶預期1s以內返回結果，實際執(zhí)行耗時超過10s

11.2 分析過程

1. 通過抓取問題SQL的執(zhí)行信息，發(fā)現大部分的耗時都在“CStore Scan”

2. 分析出問題的場景：基表是一張十億級別的表，每晚有批量增量數據入庫，同時會有少量的數據清洗的工作。白天會有高并發(fā)的查詢操作，查詢不涉及表關聯(lián)，并且返回結果都不大。

11.3 問題根因

這種場景屬于行列存表選擇錯誤導致的問題。這種場景應該使用行存表+btree索引。

11.4 解決詳情

調整表定義，表修改為行存表。同時建立btree索引，索引建立的原則：

基于充分分析客戶SQL的背景下去建立索引。

索引要建立的剛剛好，不要有冗余

建立組合索引時候，要把過濾性比較好的列往前放

盡可能多的過濾條件都用到索引

十二、NestLoop+indexscan的適用場景

12.1 問題描述

某客戶反饋兩個表的關聯(lián)要去秒級返回，其中大表有2.7T，小表有100GB左右，查詢結果一般都不大，過濾條件中有過濾性比價好的條件。

12.2 分析過程

1. 原始的執(zhí)行計劃：

2. 可以看到兩個表關聯(lián)走了HashJoin，主要的耗時在基表掃描和HashJoin操作上。

12.3 問題根因

主要的耗時點是在Hashjoin 和基表掃描上，這種情況下可以考用NestLoop+indexScan的計劃。

這種計劃會把join條件下推到基表掃描上，然后利用基表的索引，提前把數據過濾掉。

12.4 解決詳情

由于NestLoop+indexScan的計劃有一些約束：

1. Join的時候不能有stream（不能通過stream來傳遞join條件的下推）

2. 大表上要有合適的索引。

修改后的執(zhí)行計劃如下：

文末彩蛋：

華為云3月開年采購季正在進行中，采購季中數倉GaussDB(DWS)

包月包年都將有重大優(yōu)惠，對企業(yè)用戶尤為友好！

PS：關注數倉GaussDB(DWS)公眾號，get最新最全的產品資訊和數倉黑科技，更有超多活動，福利不停歇！歡迎訪問數倉GaussDB(DWS)開發(fā)者論壇，產品特性隨時交流，求助問題還有專家在線實時答疑哦~掃描下方二維碼關注我哦↓↓↓

SQL 數據倉庫服務 GaussDB(DWS)

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

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