Unique SQL特性原理與應用

1??什么是Unique SQL

用戶執行SQL語句時，每一個SQL語句文本都會進入解析器（Parser），生成“解析樹”（parse tree）。遍歷解析樹中各個結點，忽略其中的常數值，以一定的算法結合樹中的各結點，計算出來一個整數值，用來唯一標識這一類SQL，這個整數值被稱為Unique SQL ID，Unique SQL ID相同的SQL語句屬于同一個“Unique SQL”。

例如，用戶先后輸入如下兩條SQL語句：

select * from t1 where id = 1;

select * from t1 where id = 2;

這兩條SQL語句除了過濾條件的常數值不同，其他地方都相同，由此生成的解析樹的拓撲結構完全相同，故Unique SQL ID也相同。因此兩條語句屬于如下同一個Unique SQL：

select * from t1 where id = ?;

GaussDB內核會對所有上面形式的SQL語句匯總統計信息，通過視圖呈現給用戶。通過這種方式，可以排除一些無關的常量值的干擾，獲得某一類SQL語句的統計數據，為性能分析和問題定位提供數值依據。

注意，對于Unique SQL ID的計算，只會排除常數值，而不會排除其他的差異。例如，SQL語句“select * from t2 where id = 1;”?與上面的SQL不屬于同一個Unique SQL，不同用戶，從不同的CN節點執行的相同的SQL語句也不屬于同一個Unique SQL。

2??Unique SQL如何統計

收到SQL請求后，GaussDB內核首先算出其Unique SQL ID。如果該Unique SQL ID已存在，則直接更新相關的統計信息。如果不存在，首先創建一個Unique SQL，然后再更新統計信息，如下圖所示：

Unique SQL的統計信息包括執行次數，響應時間，Cache/IO數量，行活動和時間分布等信息，可以通過如下兩個視圖查詢：

·???????????gs_instr_unique_sql

·???????????pgxc_instr_unique_sql

前者顯示當前CN（Coordinator Node）節點（執行當前SQL命令的節點）上的Unique SQL信息，后者顯示系統中所有CN節點上的Unique SQL信息。兩個視圖的格式相同，均由下表中的字段組成：

字段名稱

字段類型

解釋

node_name

name

Unique SQL來源CN的名稱

node_id

integer

Unique SQL來源CN的ID(等同于pgxc_node表中的node_id)

user_name

name

執行SQL語句的用戶名稱

user_id

oid

執行SQL語句的用戶OID

unique_sql_id

bigint

歸一化的Unique SQL ID

query

text

歸一化的Unique SQL字符串

n_calls

bigint

調用次數

min_elapse_time

bigint

SQL在內核內的最小運行時間（單位：微秒）

max_elapse_time

bigint

SQL在內核內的最大運行時間（單位：微秒）

total_elapse_time

bigint

SQL在內核內的總運行時間（單位：微秒）

n_returned_rows

bigint

SELECT返回的結果集行數

n_tuples_fetched

bigint

隨機掃描行數

n_tuples_returned

bigint

順序掃描行數

n_tuples_inserted

bigint

插入行數

n_tuples_updated

bigint

更新行數

n_tuples_deleted

bigint

刪除行數

n_blocks_fetched

bigint

buffer的塊訪問次數

n_blocks_hit

bigint

buffer的塊命中次數

n_soft_parse

bigint

軟解析次數, n_soft_parse + n_bard_parse可能大于n_calls,?因為子查詢未計入n_calls

n_hard_parse

bigint

硬解析次數, n_soft_parse + n_bard_parse可能大于n_calls,?因為子查詢未計入n_calls

db_time

bigint

有效的DB時間花費，多線程將累加（單位：微秒）

cpu_time

bigint

CPU時間（單位：微秒）

execution_time

bigint

執行器內執行時間（單位：微秒）

parse_time

bigint

SQL解析時間（單位：微秒）

plan_time

bigint

SQL生成計劃時間（單位：微秒）

rewrite_time

bigint

SQL重寫時間（單位：微秒）

pl_execution_time

bigint

plpgsql上的執行時間（單位：微秒）

pl_compilation_time

bigint

plpgsql上的編譯時間（單位：微秒）

net_send_time

bigint

網絡上的時間花費（單位：微秒）

data_io_time

bigint

IO上的時間花費(單位：微秒)

3??如何使用Unique SQL

使用Unique SQL功能需要打開以下變量開關：

·???????????enable_resource_check（默認為on）

·???????????track_counts（默認為on，影響行活動和Cache/IO相關字段）

此外還需要將instr_unique_sql_count設為正整數。該變量默認為0，且不能在gsql會話中修改，需要通過SIGHUP的方式設置，例如：

gs_guc reload -Z coordinator -D /path/to/coordinator1/ -c "instr_unique_sql_count=20" > /dev/null

instr_unique_sql_count參數決定了系統收集的unique sql的數量。當收集的unique數量達到這個數后，新的sql不再被收集。如果將該數值改大，原有的unique sql信息保留，同時開始收集新的unique sql。如果將該數值改小，則會清空當前CN節點所有已收集的unique sql信息，然后開始收集新的unique sql。

設置好上述變量后，Unique sql統計視圖可以像普通視圖一樣查詢，例如：

postgres=# select node_name,query,n_calls from pgxc_instr_unique_sql;

node_name?? |?????????????????????????? query??????????????????????????? | n_calls

--------------+------------------------------------------------------------+---------

coordinator2 | select node_name,query,n_calls from pgxc_instr_unique_sql; |?????? 0

(1 row)

系統函數reset_instr_unique_sql可以清理unique sql信息，該函數有3個參數，含義如下：

1.???scope：如果為"GLOBAL"，則清除所有CN節點上的數據；如果為"LOCAL"，只清空當前CN上的數據。

2.???type：如果為“ALL”，則清除所有數據；如果為"BY_USERID"，只清除指定用戶的unique SQL；如果為"BY_CNID"，只清除指定CN的unique SQL。

3.???value：如果type=“ALL”，該參數無意義；如果type="BY_USERID"，該參數為指定用戶的ID，如果type="BY_CNID"，該參數為指定CN的ID。

例如：

postgres=# select reset_instr_unique_sql('global','all',0);

reset_instr_unique_sql

------------------------

t

(1 row)

此外，如果數據庫進程重啟，也會導致之前收集的unique SQL信息被清空。

4??用Unique SQL輔助定位問題

unique sql視圖提供了豐富的信息，用戶可以根據需要選取對自己有幫助的信息使用。本節針對客戶在生產環境中遇到的實際情況，舉例說明幾種該視圖的使用方法，可供性能優化參考。

4.1??查詢異常的行活動導致的磁盤爭用

異常的行活動可能引起磁盤爭用，導致業務運行緩慢。通過查看掃描的行數、返回的函數、更改的行數等指標的波動情況，可以發現異常的行活動，幫助定位原因。

postgres=# select sum(n_returned_rows) n_returned_rows, sum(n_tuples_fetched) n_tuples_fetched,

sum(n_tuples_returned) n_tuples_returned, sum(n_tuples_inserted) n_tuples_inserted,

sum(n_tuples_updated) n_tuples_updated, sum(n_tuples_deleted) n_tuples_deleted from pgxc_instr_unique_sql;

n_returned_rows | n_tuples_fetched | n_tuples_returned | n_tuples_inserted | n_tuples_updated | n_tuples_deleted

-----------------+------------------+-------------------+-------------------+------------------+------------------

234 |??????????????? 0 |???????????????? 0 |??????? ?????????0 |??????????????? 0 |??????????????? 0

(1 row)

4.2??查詢Top SQL對資源的占用情況

可以基于執行時間、CPU時間、掃描行數、物理讀/邏輯讀等指標，對unique SQL視圖中的SQL語句進行排序，找出占用資源最多的那些SQL語句，有針對性地其分析對性能的影響和原因，幫助查找和定位問題。例如,

·???????????按SQL執行時間順序或倒序排序：

SELECT user_name, unique_sql_id, query, total_elapse_time FROM pgxc_instr_unique_sql ORDER BY total_elapse_time ASC?或?DESC;

·???????????按SQL執行占用CPU時間進行順序或倒序排序：

SELECT user_name, unique_sql_id, query, cpu_time FROM pgxc_instr_unique_sql ORDER BY cpu_time ASC?或?DESC;

·???????????按SQL順序掃描行數順序或倒序排序：

SELECT user_name, unique_sql_id, query, n_tuples_returned FROM pgxc_instr_unique_sql ORDER BY n_tuples_returned ASC?或?DESC;

·???????????按SQL總掃描行進行順序或倒序排序：

SELECT user_name, unique_sql_id, query, n_tuples_fetched + n_tuples_returned FROM pgxc_instr_unique_sql ORDER BY n_tuples_fetched + n_tuples_returned ASC?或?DESC;

·???????????按SQL執行執行器時間進行順序或倒序排序：

SELECT user_name, unique_sql_id, query, execution_time FROM pgxc_instr_unique_sql ORDER BY execution_time ASC?或?DESC;

·???????????按SQL執行物理讀次數進行順序或倒序排序：

SELECT user_name, unique_sql_id, query, n_blocks_fetched FROM pgxc_instr_unique_sql ORDER BY n_blocks_fetched ASC?或?DESC;

·???????????按SQL執行邏輯讀次數進行順序或倒序排序：

SELECT user_name, unique_sql_id, query, n_blocks_hit FROM pgxc_instr_unique_sql ORDER BY n_blocks_hit ASC?或?DESC;

4.3??查詢邏輯讀/物理讀數量

邏輯讀/物理讀過多可能導致SQL語句占用較多的CPU時間。通過查詢unique SQL視圖可以得到sql語句邏輯/物理讀數據塊的數量，輔助判斷響應過慢的原因：

·???????????查詢物理讀塊數量：

SELECT n_blocks_fetched FROM pgxc_instr_unique_sql;

·???????????查詢邏輯讀塊數量：

SELECT n_blocks_hit FROM pgxc_instr_unique_sql;

4.4??診斷內存配額不足導致性能低下

如果數據庫緩沖區設置得太小，會導致每個SQL語句執行的結果不能被緩存，當前SQL執行完畢如果有其他SQL執行就會把內存中上一個或上幾個SQL緩存的執行結果擠出去，下一輪如果當前這個SQL再次執行時候又需要從磁盤進行物理IO讀取數據，而不能直接從緩存中獲取數據，進而導致SQL執行性能較差。

緩沖區配額是否足夠大，可以通過命中率來判斷。緩沖區命中率=n_blocks_hit/n_blocks_fetched，可以通過查詢unique SQL來診斷是否存在內存配額不足的問題：

SELECT (n_blocks_hit/ n_blocks_fetched) AS hit_ratio from pgxc_instr_unique_sql;

EI企業智能 數據倉庫服務 GaussDB(DWS) Gauss AP 應用性能調優

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

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