磐维数据库慢 SQL 诊断

原创飞天 2024-08-13

2433

磐维数据库介绍

磐维数据库，简称"PanWeiDB"。是中国移动信息技术中心首个基于中国本土开源数据库打造的面向ICT基础设施的自研数据库产品。其产品内核能力基于华为openGauss开源软件，并进一步提升了系统稳定性。磐维数据库具有高性能、高可靠、高安全、高兼容等特点，能够满足复杂多变的业务需求。磐维数据库提供了自动化、流程化的解决方案，实现了一键式数据迁移。这种高效的数据迁移方式不仅提高了迁移数据的效率，也降低了操作难度，为用户带来了极大的便利。

慢 SQL 诊断描述

慢SQL诊断提供诊断慢SQL所需要的必要信息，帮助用户回溯执行时间超过阈值的SQL，诊断SQL性能瓶颈。慢 SQL 诊断分为两部分：实时慢 SQL 和历史慢 SQL。

实时慢 SQL 能根据用户提供的执行时间阈值，输出当前系统中正在执行的，且执行时间超过阈值的作业信息。
历史慢 SQL 能根据用户提供的执行时间阈值，记录所有超过阈值的执行完毕的作业信息。
历史慢 SQL 提供表和文件两种维度的查询接口，用户从接口中能查询到作业的执行计划，开始、结束执行时间，执行查询的语句，行活动，内核时间、CPU时间、执行时间、解析时间、编译时间、查询重写时间、计划生成时间、网络时间、IO 时间等。所有信息都是脱敏的。

应用价值：

实时的慢 SQL 提供给用户管理尚未执行完毕的作业的接口，用户可以手动结束异常的，消耗过多资源的作业。
历史慢 SQL 提供给用户对于慢 SQL 诊断所需的详细信息，用户无需通过复现就能离线诊断特定慢 SQL 的性能问题。表和文件接口方便用户统计慢 SQL 指标，对接第三方平台。

慢 SQL 诊断使用

前提条件

数据库实例运行正常。
查询 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 诊断设置

--设置语句执行跟踪的级别，只跟踪慢SQL，并且记录SQL执行计划
gs_guc reload  -N all -I all -c "track_stmt_stat_level = 'L0,L1'" 
--设置SQL执行时间阈值为5s
gs_guc reload  -N all -I all -c "log_min_duration_statement =5000" 
--追踪语句更详细内容(根据需要设置)
gs_guc reload  -N all -I all -c "track_stmt_parameter = 'on'"

参数说明

1、track_stmt_stat_level：
参数说明：控制语句执行跟踪的级别。
该参数属于 USERSET 类型参数，请参考：配置运行参数->重设参数章节中表 1对应设置方法进行设置，不区分英文字母大小写。
取值范围：字符型
该参数分为两部分，形式为’full sql stat level, slow sql stat level’
full sql stat level 为全量 SQL 跟踪级别，取值范围为 OFF、L0、L1、L2
slow sql stat level 为慢 SQL 的跟踪级别，取值范围为 OFF、L0、L1、L2
【说明】
若全量 SQL 跟踪级别值为非 OFF 时，当前 SQL 跟踪级别值为全量 SQL 和慢 SQL的较高级别（L2 > L1 > L0），级别说明请参见：系统表和系统视图->系统表->STATEMENT_HISTORY 中表 1。
默认值：OFF,L0

2、log_min_duration_statement ：
参数说明：当某条语句的持续时间大于或者等于特定的毫秒数时，log_min_duration_statement 参数用于控制记录每条完成语句的持续时间。
设置 log_min_duration_statement 可以很方便地跟踪需要优化的查询语句。对于使用扩展查询协议的客户端，语法分析、绑定、执行每一步所花时间被独立记录。
该参数属于 SUSET 类型参数，请参考：配置运行参数->重设参数章节中表 1 对应设置方法进行设置。
当此选项与 log_statement 同时使用时，已经被 log_statement 记录的语句文本不会被重复记录。在没有使用 syslog 情况下，推荐使用 log_line_prefix 记录 PID 或会话 ID，方便将当前语句消息连接到最后的持续时间消息。
取值范围：整型，-1 ~ INT_MAX，单位为毫秒。

设置为 250，所有运行时间不短于 250ms 的 SQL 语句都会被记录。
设置为 0，输出所有语句的持续时间。
设置为-1，关闭此功能。
默认值：30min

其他参数：
3、enable_stmt_track
参数说明：控制是否启用 Full /Slow SQL 特性。
在 x86 平台集中式部署下，硬件配置规格为 32 核 CPU/256GB 内存，使用Benchmark SQL 5.0 工具测试性能，开关此参数性能影响约 1.2%。
该参数属于 SIGHUP 类型参数，请参考：配置运行参数->重设参数章节中表 1 对应设置方法进行设置。
取值范围：布尔型

on：表示开启 Full /Slow SQL 捕获。
off：表示关闭 Full /Slow SQL 捕获。
默认值：on

4、track_stmt_details_size
参数说明：设置单语句可以收集的最大的执行事件的大小（byte）。
该参数属于 USERSET 类型参数，请参考：配置运行参数->重设参数章节中表 1对应设置方法进行设置。
取值范围：整型，0 ~ 100000000
默认值：4096

5、track_stmt_retention_time
参数说明：组合参数，控制全量/慢 SQL 记录的保留时间。以 60 秒为周期读取该参数，并执行清理超过保留时间的记录，仅 sysadmin 用户可以访问。
该参数属于 SIGHUP 类型参数，请参考：配置运行参数->重设参数章节中表 1 对应设置方法进行设置。
取值范围：字符型
该参数分为两部分，形式为’full sql retention time, slow sql retention time’
full sql retention time 为全量 SQL 的保留时间，取值范围为 0 ~ 86400s
slow sql retention time 为慢 SQL 的保留时间，取值范围为 0 ~ 604800s
默认值：3600,604800

6、track_stmt_parameter
参数说明：追踪语句更详细内容。
当开启后，在 statement_history 中记录的执行语句不再进行归一化操作，可以显示完整的 SQL 语句信息，这有助于数据库管理员 (DBA) 进行问题定位。对于简单查询，将显示完整语句信息；对于 PBE (Prepared Bound Execute) 语句，除了显示完整语句信息外，还会追加每个变量的数值信息，格式为“query string；parameters:$1=value1,$2=value2,…”。
该参数属于 SIGHUP 类型参数，可以通过 gs_guc reload 命令进行设置。
取值范围：布尔型，on 表示开启显示完整 SQL 语句信息的功能，off 表示关闭此功能。默认值为 off 。

慢 SQL 查询

select * from dbe_perf.statement_history;

慢 SQL 模拟示例

session1：调用存储过程向tab1表中插入大量记录

testdb=# CREATE OR REPLACE PROCEDURE insert_task()
testdb-# AS
testdb$# BEGIN
testdb$# FOR I IN 1..100000000 loop
testdb$# INSERT INTO tab1 VALUES(I);
testdb$# END LOOP;
testdb$# END;
testdb$# /
CREATE PROCEDURE
testdb=# call  insert_task();
此处发生等待

session2：同时truncate tab1表。


testdb=# truncate table tab1;
此处发生等待

session3：查询慢sql


postgres=# select * from dbe_perf.statement_history where db_name='testdb' and query like '%tab1%' ;
-[ RECORD 1 ]--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------
db_name              | testdb
schema_name          | testns
origin_node          | 0
user_name            | omm
application_name     | gsql
client_addr          | 
client_port          | -1
unique_query_id      | 3545397025
debug_query_id       | 3659174697242281
query                | truncate table tab1;
start_time           | 2024-08-13 23:55:13.427868+08
finish_time          | 2024-08-14 00:10:51.569744+08
slow_sql_threshold   | 5000000
transaction_id       | 73659
thread_id            | 140538297317120
session_id           | 140538297317120
n_soft_parse         | 0
n_hard_parse         | 0
query_plan           | 
n_returned_rows      | 0
n_tuples_fetched     | 12
n_tuples_returned    | 6
n_tuples_inserted    | 0
n_tuples_updated     | 1
n_tuples_deleted     | 0
n_blocks_fetched     | 37
n_blocks_hit         | 37
db_time              | 938141884
cpu_time             | 53880
execution_time       | 0
parse_time           | 34
plan_time            | 0
rewrite_time         | 0
pl_execution_time    | 0
pl_compilation_time  | 0
data_io_time         | 0
net_send_info        | {"time":64, "n_calls":4, "size":2082}
net_recv_info        | {"time":5543809, "n_calls":1, "size":26}
net_stream_send_info | {"time":0, "n_calls":0, "size":0}
net_stream_recv_info | {"time":0, "n_calls":0, "size":0}
lock_count           | 35
lock_time            | 938108705
lock_wait_count      | 1
lock_wait_time       | 938102903
lock_max_count       | 6
lwlock_count         | 0
lwlock_wait_count    | 0
lwlock_time          | 0
lwlock_wait_time     | 0
details              | \x8a00000002418000000005000000011300427566486173685461626c6553656172636800280000000000000001100057414c42756666657241636365737300000000000000000005090049646c6557616974007550ea3700000000040e00776169742077616c2073796e
63007321000000000000040b00666c7573682064617461000a00000000000000
is_slow_sql          | 1
trace_id             | 
advise               | 

postgres=#

STATEMENT_HISTORY表说明：
获得当前节点的执行语句的信息。查询系统表必须具有 sysadmin 权限。只可在系统库中查询到结果，用户库中无法查询。
对于此系统表查询有如下约束：

必须在 postgres 库内查询，其它库中不存数据。
此系统表受 track_stmt_stat_level 控制，默认为“OFF,L0”，第一部分控制 Full SQL，第二部分控制 Slow SQL，具体字段记录级别见下表。考虑性能影响，更改该参数的值时建议通过 set 方式设置，使该参数仅对当前会话生效。
对于 Slow SQL，当 track_stmt_stat_level 的值为非 OFF 时，且 SQL 执行时间超过 log_min_duration_statement，会记录为慢 SQL。
对于存储过程内的 SQL 语句(parent_unique_sql_id 非 0)，n_soft_parse、n_hard_parse、n_returned_rows 需要设置参数instr_unique_sql_track_type 为 all 才能记录，n_tuples_fetched、n_tuples_returned、n_tuples_inserted、n_tuples_updated、
n_tuples_deleted、n_blocks_fetched、n_blocks_hit、db_time、cpu_time、execution_time、parse_time、plan_time、rewrite_time、
pl_execution_time、pl_compilation_time、data_io_time、net_send_info、net_recv_info、net_stream_send_info、net_stream_recv_info、lock_wait_count、lock_wait_time、lwlock_count、lwlock_wait_count、lwlock_ti、lwlock_wait_time、details、trace_id、advise 列不支持，记录的值没有实际意义。

STATEMENT_HISTORY表字段：