专门做产品排名的网站,好看的学校网站模板,装修效果图网站推荐,网站页面做文章目录 openGauss学习笔记-144 openGauss 数据库运维-例行维护-慢sql诊断144.1 背景信息144.2 前提条件 openGauss学习笔记-144 openGauss 数据库运维-例行维护-慢sql诊断
144.1 背景信息
在SQL语句执行性能不符合预期时#xff0c;可以查看SQL语句执行信息#xff0c;便… 文章目录 openGauss学习笔记-144 openGauss 数据库运维-例行维护-慢sql诊断144.1 背景信息144.2 前提条件 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.21681808
finish_time | 2020-12-19 16:19:51.22451308
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 (cost0.00..12.50 rows5 width64)| 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.73849108
finish_time | 2020-12-19 16:23:20.77371408
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 (cost1.01..1.02 rows1 width0)| InitPlan 1 (returns $0)| - Seq Scan on pgxc_node (cost0.00..1.01 rows1 width64)| 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.21681808
finish_time | 2020-12-19 16:19:51.22451308
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 (cost0.00..12.50 rows5 width64)| 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.21681808
finish_time | 2022-08-19 16:19:51.22451308
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 (cost0.00..12.50 rows5 width64)| Filter: (name ***::text)
...点赞你的认可是我创作的动力 ⭐️ 收藏你的青睐是我努力的方向 ✏️ 评论你的意见是我进步的财富
