Mogdb SQL 通過(guò)plan_node_id來(lái)監(jiān)測(cè)慢sql執(zhí)行步驟

????????????????????????????Oracle可以通過(guò) v$active_session_history中的SQL_PLAN_LINE_ID來(lái)標(biāo)識(shí)采樣來(lái)自于sql執(zhí)行計(jì)劃的哪一步，可以通過(guò)count數(shù)來(lái)說(shuō)明哪一步耗時(shí)久，同理在mogdb中也有此功能。
?????????????????????????????這個(gè)功能主要用于那些耗時(shí)慢sql的排查，即執(zhí)行時(shí)間很長(zhǎng)不能通過(guò)explain analyze分析的語(yǔ)句

????????????????????????????本特性自MogDB 3.0.0版本開(kāi)始引入。

特性簡(jiǎn)介

????????????????????????????采樣線程默認(rèn)1s采樣一次，可通過(guò)guc參數(shù)asp_sample_interval控制，內(nèi)存中最多采樣100000行數(shù)據(jù)，通過(guò)asp_sample_num控制，到達(dá)上限就會(huì)刷新到磁盤上。

客戶價(jià)值

???????????????????????????當(dāng)發(fā)現(xiàn)某條SQL語(yǔ)句存在性能瓶頸，且無(wú)法在采樣視圖中查詢到該SQL每個(gè)算子的執(zhí)行情況時(shí)，可以通過(guò)plan_node_id定位性能問(wèn)題。

???????????????????????????算子：SQL語(yǔ)句執(zhí)行過(guò)程中各個(gè)步驟的具體動(dòng)作（例如：SELECT、SUM、WHERE、Group By、Having、Order By、Limit等）

特性描述

???????????????????????????在dbe_perf.local_active_session和GS_ASP中新增一列plan_node_id來(lái)記錄SQL語(yǔ)句每個(gè)算子操作的執(zhí)行情況。

???????????????????????????現(xiàn)有的監(jiān)控級(jí)別由guc參數(shù)resource_track_level控制，該參數(shù)按照級(jí)別存在三個(gè)值，分別是：
none：不開(kāi)啟資源記錄功能；
query：開(kāi)啟query級(jí)別資源記錄功能；
operator：開(kāi)啟query級(jí)別和算子級(jí)別資源記錄功能，所以只有當(dāng)將resource_track_level設(shè)成operator的時(shí)候才會(huì)對(duì)SQL語(yǔ)句每個(gè)算子操作進(jìn)行采樣。

同樣要開(kāi)啟asp

enable_asp=on
resource_track_level=operator
enable_resource_track=on

???????????????????????????MogDB啟動(dòng)后會(huì)啟動(dòng)一個(gè)后臺(tái)worker采樣線程，為避免浪費(fèi)資源，該采樣線程不會(huì)時(shí)刻采樣，而是每隔一個(gè)采樣周期對(duì)MogDB進(jìn)行采樣，收集MogDB當(dāng)時(shí)的運(yùn)行快照保存到內(nèi)存中，查詢視圖dbe_perf.local_active_session可以查詢到實(shí)時(shí)的采樣信息，該采樣周期由guc參數(shù)asp_sample_interval控制，默認(rèn)采樣周期為1s，MogDB每在內(nèi)存中采樣100000行（由guc參數(shù)asp_sample_num控制）會(huì)將內(nèi)存中的采樣數(shù)據(jù)刷新到GS_ASP表中以供歷史查詢，只有語(yǔ)句執(zhí)行時(shí)間大于采樣時(shí)間，才會(huì)被采樣線程收集到運(yùn)行信息。

使用場(chǎng)景

官方例子

首先在session1中創(chuàng)建表test，并執(zhí)行插入操作：
MogDB=# create table test(c1 int);
CREATE TABLE
MogDB=# insert into test select generate_series(1, 1000000000);

當(dāng)前還沒(méi)執(zhí)行完的sql在pg_stat_activity中查詢
在session2中，從活躍會(huì)話視圖中查詢出該SQL的query_id

MogDB=# select query,query_id from pg_stat_activity where query like ‘insert into test select%’;
query | query_id
-----------------------------------------------------------±----------------
insert into test select generate_series(1, 100000000000); | 562949953421368
(1 row)

在session2中，根據(jù)該query_id從活躍作業(yè)管理視圖中查詢出該語(yǔ)句的帶plan_node_id的執(zhí)行計(jì)劃（該語(yǔ)句執(zhí)行cost需要大于guc值resource_track_cost才會(huì)被記錄到該視圖中，該guc參數(shù)默認(rèn)值為100000，session級(jí)別可更新，所以為了方便測(cè)試，可在測(cè)試中將該值改成10）

Set resource_track_cost=10;

MogDB=# select query_plan from dbe_perf.statement_complex_runtime where queryid = 562949953421368;
query_plan

Coordinator Name: datanode1 +
1 | Insert on test (cost=0.00…17.51 rows=1000 width=8) +
2 | -> Subquery Scan on “SELECT” (cost=0.00…17.51 rows=1000 width=8) +
3 | -> Result (cost=0.00…5.01 rows=1000 width=0) +
(1 row)

在session2中，根據(jù)query_id從采樣視圖dbe_perf.local_active_session中查詢出該語(yǔ)句的采樣情況，結(jié)合上面查詢的執(zhí)行計(jì)劃做性能分析。
MogDB=# select plan_node_id, count(plan_node_id) from dbe_perf.local_active_session where query_id = 562949953421368 group by plan_node_id;
plan_node_id | count
--------------±------
3 | 12
1 | 366
2 | 2
(3 rows)

在session2中執(zhí)行，當(dāng)內(nèi)存數(shù)據(jù)到達(dá)上限值（由guc參數(shù)asp_sample_num控制）的時(shí)候，則會(huì)將現(xiàn)有內(nèi)存的采樣數(shù)據(jù)刷新到gs_asp表中，刷盤后查詢gs_asp表也會(huì)查到該語(yǔ)句的算子采樣的數(shù)據(jù)。
MogDB=# select plan_node_id, count(plan_node_id) from gs_asp where query_id = 562949953421368 group by plan_node_id;
plan_node_id | count
--------------±------
3 | 19
1 | 582
2 | 3

當(dāng)發(fā)現(xiàn)insert into test select generate_series(1, 1000000000)存在性能瓶頸，通過(guò)以上的步驟定位發(fā)現(xiàn)，insert操作在整個(gè)SQL語(yǔ)句執(zhí)行過(guò)程中被采樣的數(shù)值最高（ plan_node_id =1 ，count=366），可以對(duì)其進(jìn)行優(yōu)化。
相關(guān)表
GS_ASP、LOCAL_ACTIVE_SESSION

實(shí)際應(yīng)用例子：

select count(*)
from test2 t2, test3 t3
where t2.id = t3.id;

在postgres庫(kù)中查詢

select state,a.query,a.query_id,a.unique_sql_id,a.*
from pg_stat_activity a where a.state=‘a(chǎn)ctive’ and usename=‘tpcc_user’;

unique_sql_id=‘3007706519’
query_id=‘1407374883554139’

此表中unique_sql_id 同dbe_perf中的unique_query_id

查詢?cè)趦?nèi)存中的執(zhí)行計(jì)劃

enable_asp=on
resource_track_level=operator
enable_resource_track=on

select query_plan ,a.*
from dbe_perf.statement_complex_runtime a
where queryid=‘1407374883554139’;

如果執(zhí)行完，同樣可查歷史

select query_plan,sch.*
from dbe_perf.statement_complex_history sch
where sch.queryid=‘1407374883554873’;

查詢哪步慢
select las.plan_node_id ,count(las.plan_node_id)
from dbe_perf.local_active_session las
where las.unique_query_id =‘3007706519’
group by las.plan_node_id
order by count(*) desc;

select las.query_id,las.unique_query_id,las.*
from dbe_perf.local_active_session las
where las.unique_query_id =‘3007706519’
order by sample_time desc;

如果查詢歷史執(zhí)行計(jì)劃，必須打開(kāi)如下參數(shù)

必須在postgres庫(kù)內(nèi)查詢，其它庫(kù)中不存數(shù)據(jù)。
此系統(tǒng)表受track_stmt_stat_level控制，默認(rèn)為“OFF,L0”，第一部分控制Full SQL，第二部分控制Slow SQL，具體字段記錄級(jí)別見(jiàn)下表。
對(duì)于Slow SQL，當(dāng)track_stmt_stat_level的值為非OFF時(shí)，且SQL執(zhí)行時(shí)間超過(guò)log_min_duration_statement，會(huì)記錄為慢SQL。

配置參數(shù)：
track_stmt_stat_level=‘L2,L0’
log_min_duration_statement=1000ms

執(zhí)行完可以查到相應(yīng)的信息
select a.unique_query_id,a.query,a.query_plan,a.debug_query_id ,a.*
from dbe_perf.statement_history a
–where a.query like ‘%hashjoin%’;
where a.unique_query_id =‘3007706519’;

select a.unique_query_id,a.query,a.query_plan,a.debug_query_id ,a.*
from pg_catalog.statement_history a
–where a.query like ‘%hashjoin%’;
where a.unique_query_id =‘3007706519’;

debug_query_id =‘1125899906854472’;

select *
from pg_settings
where name like ‘%track_stmt_stat_level%’
or name like ‘%log_min_duration_statement%’
or name like ‘%asp_sample_interval%’
or name like ‘%resource_track_level%’;

執(zhí)行完可以查到相應(yīng)的信息

select query,unique_query_id,query_plan,a.*
from DBE_PERF.get_global_slow_sql_by_timestamp(‘2020-12-01 09:25:22’, ‘2023-12-31 23:54:41’) a
where a.unique_query_id =‘3007706519’;

select query,unique_query_id,query_plan,a.*
from DBE_PERF.get_global_full_sql_by_timestamp(‘2020-12-01 09:25:22’, ‘2023-12-31 23:54:41’) a
where a.unique_query_id =‘3007706519’;

select * from pg_settings where name like ‘%asp%’;

DBE_PERF.get_global_full_sql_by_timestamp() 這些系統(tǒng)存儲(chǔ)過(guò)程可以在相應(yīng)模式下查看基表

查詢執(zhí)行不完的sql的query_id

根據(jù)query_id查詢?cè)趦?nèi)存中的執(zhí)行計(jì)劃

根據(jù)plan_node_id統(tǒng)計(jì)在哪些步驟采樣次數(shù)多

采樣次數(shù)多的證明執(zhí)行時(shí)間長(zhǎng)
注意：在sql執(zhí)行完畢后，才會(huì)將執(zhí)行計(jì)劃等信息寫(xiě)入到
dbe_perf.statement_history
pg_catalog.statement_history
這時(shí)這些歷史表的執(zhí)行計(jì)劃才可以查詢，在內(nèi)存中的按上文查找

算子采樣實(shí)現(xiàn)原理

1. 新增一個(gè)pgstat_report_plan_node_id的輕量函數(shù)對(duì)session狀態(tài)監(jiān)控結(jié)構(gòu)體PgBackendStatus中的plan_node_id賦值
2. 每個(gè)執(zhí)行器算子的執(zhí)行都會(huì)分Init（初始化），Exec（執(zhí)行），End（結(jié)束收尾）三個(gè)階段，真正的執(zhí)行階段是在Exec中，在每個(gè)算子的Exec函數(shù)中調(diào)用上述pgstat_report_plan_node_id函數(shù)，將本算子的plan_node_id賦值到session的狀態(tài)監(jiān)控結(jié)構(gòu)體中
3. 采樣框架定期采樣從session的狀態(tài)監(jiān)控結(jié)構(gòu)體數(shù)組中獲取每個(gè)session的狀態(tài)，將值賦值到全局采樣數(shù)組中以便查詢

采樣線程的工作函數(shù)為SubAspWorker，該工作線程會(huì)每隔固定時(shí)間，默認(rèn)1s(間隔時(shí)間可通過(guò)guc參數(shù)asp_sample_interval配置，最小為1s，最大為10s)的時(shí)間去遍歷BackendStatusArray數(shù)組，并且將內(nèi)容寫(xiě)到全局?jǐn)?shù)組active_sess_hist_arrary中，以及檢查是否需要進(jìn)行刷盤操作，當(dāng)內(nèi)存中數(shù)量達(dá)到上限值默認(rèn)10w(10-10w可通過(guò)asp_sample_num配置)行的時(shí)候，就會(huì)觸發(fā)刷出機(jī)制，持久化到gs_asp表中。