GaussDB(DWS) 單點性能案例集錦

1.1?數據傾斜

1.1.1?問題描述

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

1.1.2?分析過程

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

1.???????gs_ssh –c “df -h”

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

2.???????通過等待視圖查看作業的運行情況，發現作業總是等待部分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.???????通過傾斜檢查接口可以發現數據傾斜。

select table_skewness('store_sales');

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

5.???????通過資源監控發現，個別節點的CPU/IO明顯比其他節點高。

1.1.3?問題根因

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

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

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

其次，會導致資源傾斜，尤其是磁盤。可能部分磁盤的空間已經接近極限，但是其他磁盤利用率很低。

可能出現部分節點CPU過高等等問題。

1.1.4?解決詳情

如何找到傾斜的表：

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

1

SELECT?*?FROM?pgxc_get_table_skewness?ORDER?BY?totalsize?DESC;

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

1

2

3

4

5

6

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自增生成

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

1.2?統計信息未收集

1.2.1?問題描述

略

1.2.2?分析過程

1.???????通過explain verbose/explain performance打印語句的執行計劃

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

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

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

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

1.2.3?問題根因

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

統計信息是優化器生成執行計劃的基礎，沒有收集統計信息，優化器生成的執行計劃會非常差，如果統計信息未收集，會導致多種多樣表現形式的性能問題。例如，等值關聯走NestLoop，大表broadcast，集群CPU持續增高等等問題。

1.2.4?解決詳情

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

1.3?語句不下推

1.3.1?問題描述

略

1.3.2?分析過程

1.???????通過explain verbose打印語句執行計劃

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

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

1.3.3?問題根因

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

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

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

1.3.4?解決詳情

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

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

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

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

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

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

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

1.4 not in?和?not exists

1.4.1?問題描述

客戶的SQL語句執行慢，執行計劃中有NestLoop

1.4.2?問題定位

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

2.???????執行計劃中有NestLoop

1.4.3?問題根因

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

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

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

1.4.4?解決詳情

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

1.5?未分區剪枝

1.5.1?問題描述

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

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';

列存表，分區鍵為createtime,哈希分布鍵為motorvehicleid

1.5.2?分析過程

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

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

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

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

4.???????通過在篩選條件中增加分區鍵過濾條件，優化后的sql和執行計劃如下：

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;

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

1.5.3?問題根因

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

1.5.4?解決詳情

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

1.6?行數估算過小，走了nestloop

1.6.1?問題描述

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

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

1.6.2?分析過程

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

2.???????查看慢sql的線程等待狀態

select * from pg_thread_wait_status where query_id=’149181737656737395’;

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

3.???????到相應的實例節點上，打印等待狀態為none的線程堆棧信息如下：

gstack 14104

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

4.???????對表執行analyze后性能并沒有太大改善

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

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

1.6.3?問題根因

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

1.6.4?解決詳情

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

1.7?表數據膨脹，未清理臟數據

1.7.1?問題描述

數據庫性能時快時慢問題

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

1.7.2?分析過程

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

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

cat /proc/cpuinfo |grep MHz

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

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

select * from pg_stat_activity where state !=’idle’ and usename !=’omm’;

4.???????根據執行計劃，發現在部分DN上耗時較高，查詢表的傾斜情況，并未發現有傾斜的情況

select table_skewness(‘ioc_dm.m_ss_index_event’);

5.???????檢查內存相關參數，設置不合理，需要優化

單節點總內存大小為256G

max_process_memory為12G，設置過小

shared_buffers為32M，設置過小

work_mem：CN：64M 、DN：64M

max_active_statements: -1（不限制并發數）

設置方式如下：

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.???????進一步分析掃描慢的原因，發現表數據膨脹嚴重，對其中一張8G大小的表，總數據量5萬條，做完vacuum full后大小減小為5.6M

1.7.3?問題根因

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

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

1.7.4?解決詳情

1.???????對業務涉及到的常用的大表，執行vacuum full操作，清理臟數據；

2.???????設置GUC內存參數

1.8 “in?常量”優化

1.8.1?問題描述

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

1.8.2?分析過程

1.???????打印語句的執行計劃：

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

1.8.3?問題根因

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

1.8.4?解決詳情

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

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

1.9?相關子查詢1

1.9.1?問題描述

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

1.9.2?分析過程

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

1.9.3?問題根因

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

1.9.4?解決詳情

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

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

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

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

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

1.11?相關子查詢2

1.11.1?問題描述

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

1.11.2?分析過程

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

1.11.3?問題根因

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

1.11.4?解決詳情

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

1.12?單表點查性能差

1.12.1?問題描述

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

1.12.2?分析過程

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

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

1.12.3?問題根因

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

1.12.4?解決詳情

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

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

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

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

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

1.13 NestLoop+indexscan的適用場景

1.13.1?問題描述

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

1.13.2?分析過程

1.???????原始的執行計劃：

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

1.13.3?問題根因

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

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

1.13.4?解決詳情

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

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

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

修改后的執行計劃如下：

EI企業智能 Gauss AP SQL 數據倉庫服務 GaussDB(DWS)

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

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