openGauss學習筆記-144 openGauss 數據庫運維-例行維護-慢sql診斷

144.1 背景信息

在SQL語句執行性能不符合預期時，可以查看SQL語句執行信息，便于事后分析SQL語句執行時的行為，從而診斷SQL語句執行出現的相關問題。

144.2 前提條件

• 數據庫實例運行正常。
• 查詢SQL語句信息，需要合理設置GUC參數track_stmt_stat_level。track_stmt_stat_level參數控制語句執行跟蹤的級別，第一部分控制全量SQL，第二部分控制慢SQL。對于慢SQL，當track_stmt_stat_level的值為非OFF時，且SQL執行時間超過log_min_duration_statement，會記錄為慢SQL。默認值為"OFF,L0"，建議設置為"L0,L0"。
• 只能用系統管理員和監控管理員權限進行操作。

執行命令查看數據庫實例中SQL語句執行信息
select * from dbe_perf.get_global_full_sql_by_timestamp(start_timestamp, end_timestamp); 
例如：
select * from DBE_PERF.get_global_full_sql_by_timestamp('2020-12-01 09:25:22', '2020-12-31 23:54:41');
-[ RECORD 1 ]--------+---------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------
node_name            | dn_6001_6002_6003
db_name              | postgres
schema_name          | "$user",public
origin_node          | 1938253334
user_name            | user_dj
application_name     | gsql
client_addr          |
client_port          | -1
unique_query_id      | 3671179229
debug_query_id       | 72339069014839210
query                | select name, setting from pg_settings where name in (?)
start_time           | 2020-12-19 16:19:51.216818+08
finish_time          | 2020-12-19 16:19:51.224513+08
slow_sql_threshold   | 1800000000
transaction_id       | 0
thread_id            | 139884662093568
session_id           | 139884662093568
n_soft_parse         | 0
n_hard_parse         | 1
query_plan           | Datanode Name: dn_6001_6002_6003| Function Scan on pg_show_all_settings a  (cost=0.00..12.50 rows=5 width=64)|   Filter: (name = '***'::text)
...執行命令查看數據庫實例中慢SQL語句執行信息
select * from dbe_perf.get_global_slow_sql_by_timestamp(start_timestamp, end_timestamp);
例如：
select * from DBE_PERF.get_global_slow_sql_by_timestamp('2020-12-01 09:25:22', '2020-12-31 23:54:41');
-[ RECORD 1 ]--------+---------------------------------------------------------------------------------------------------
node_name            | dn_6001_6002_6003
db_name              | postgres
schema_name          | "$user",public
origin_node          | 1938253334
user_name            | user_dj
application_name     | gsql
client_addr          |
client_port          | -1
unique_query_id      | 2165004317
debug_query_id       | 72339069014839319
query                | select * from DBE_PERF.get_global_slow_sql_by_timestamp(?, ?);
start_time           | 2020-12-19 16:23:20.738491+08
finish_time          | 2020-12-19 16:23:20.773714+08
slow_sql_threshold   | 10000
transaction_id       | 0
thread_id            | 139884662093568
session_id           | 139884662093568
n_soft_parse         | 10
n_hard_parse         | 8
query_plan           | Datanode Name: dn_6001_6002_6003| Result  (cost=1.01..1.02 rows=1 width=0)|   InitPlan 1 (returns $0)|     ->  Seq Scan on pgxc_node  (cost=0.00..1.01 rows=1 width=64)|           Filter: (nodeis_active AND ((node_type = '***'::"char") OR (node_type = '***'::"char")))
...查看當前主節點SQL語句執行信息
select * from statement_history;
例如：
select * from statement_history;
-[ RECORD 1 ]--------+---------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------
db_name              | postgres
schema_name          | "$user",public
origin_node          | 1938253334
user_name            | user_dj
application_name     | gsql
client_addr          |
client_port          | -1
unique_query_id      | 3671179229
debug_query_id       | 72339069014839210
query                | select name, setting from pg_settings where name in (?)
start_time           | 2020-12-19 16:19:51.216818+08
finish_time          | 2020-12-19 16:19:51.224513+08
slow_sql_threshold   | 1800000000
transaction_id       | 0
thread_id            | 139884662093568
session_id           | 139884662093568
n_soft_parse         | 0
n_hard_parse         | 1
query_plan           | Datanode Name: dn_6001_6002_6003| Function Scan on pg_show_all_settings a  (cost=0.00..12.50 rows=5 width=64)|   Filter: (name = '***'::text)
...查看當前備節點SQL語句執行信息
select * from dbe_perf.standby_statement_history(is_only_slow, start_timestamp, end_timestamp);
例如：
select * from dbe_perf.standby_statement_history(true, '2022-08-01 09:25:22', '2022-08-31 23:54:41');
db_name              | postgres
schema_name          | "$user",public
origin_node          | 0
user_name            | user_dj
application_name     | gsql
client_addr          |
client_port          | -1
unique_query_id      | 1660376009
debug_query_id       | 281474976710740
query                | select name, setting from pg_settings where name in (?)
start_time           | 2022-08-19 16:19:51.216818+08
finish_time          | 2022-08-19 16:19:51.224513+08
slow_sql_threshold   | 1800000000
transaction_id       | 0
thread_id            | 140058747205376
session_id           | 140058747205376
n_soft_parse         | 0
n_hard_parse         | 1
query_plan           | Datanode Name: sgnode| Function Scan on pg_show_all_settings a  (cost=0.00..12.50 rows=5 width=64)|   Filter: (name = '***'::text)
...

👍 點贊，你的認可是我創作的動力！

?? 收藏，你的青睞是我努力的方向！

?? 評論，你的意見是我進步的財富！