GaussDB单SQL性能慢分析

文章目录

问题描述

单SQL性能慢，客户作业对时延要求或者不满足客户预期。

问题现象

• 查看告警，发现慢SQL告警。
• 分析WDR报告有异常SQL。
• 长时间未结束的SQL。
• 用户反馈慢SQL。

告警

• 业务侧相关接口时延、成功率等告警。
• 数据库内核P80/P95相关告警

单SQL性能慢分析

步骤一：确定目标SQL

• 主动发现： 1. 查看告警，发现慢SQL告警。2. 定期巡检WDR报告发现异常SQL，如CPU消耗较多的Top SQL等。3. 长事务告警，发现有长时间未结束的SQL。
• 被动调优：用户、业务反馈慢SQL。

步骤二：收集统计信息、提前排除影响

1. 获取完整的SQL语句和SQL中相关表的结构、索引信息、表大小和索引大小等信息。
2. 获取数据库的参数配置信息，包括work_mem、maintance_work_mem、shared buffers等，比如排序操作或者hash操作语句可能因为work_mem太小而影响执行执行效率。
3. 获取SQL中相关结构的pgstat信息，pgstat相关可以用来分析vacuum和analyze情况、以及表和索引的状态信息，这部分可以通过pg_stat_all_tables， pg_stat_all_indexes， pg_statio_all_tables， pg_statio_all_indexes等视图获取，具体视图分析请参考“单SQL性能慢-视图分析”。
4. 对于有可能写大量日志的慢SQL，需要先确认该环境是否有开启流控（recovery_time_target）操作，为了保证RTO对于突然激增的xlog日志，流控可能会限制xlog同步到备机的速度，导致语句执行变慢，具体排查方法请参考“单SQL性能慢-视图分析”。
5. 收集慢SQL对应时间段的系统资源情况，确认系统资源是否有异常。

步骤三：分析SQL性能瓶颈

• 如果目标SQL长时间未结束。1. 首先确定SQL慢在什么地方，可以通过pg_thread_wait_status或者ASP信息分析该SQL的Top Wait Event信息，具体分析方法参考“单SQL性能慢-视图分析”，其中等待事件的说明请参考•异常等待事件。2. 如该SQL有大量的等锁事件，可以通过ASP中的block sessionid信息找到锁等待关系，并确定等锁的原因。3. 如果语句执行时间超过慢SQL阈值log_min_duration_statement，可以通过Full SQL视图查看计划，具体分析方法请参考“单SQL性能慢-视图分析”。4. 根据找到的慢SQL，跟业务沟通是否能获取完成的业务SQL，尝试复现。
• 单SQL一直慢。- 拿到慢SQL语句首先考虑通过explain获取计划，能快速确定语句的性能瓶颈点，并结合步骤2获取的信息分析具体原因。- 另外可以通过summary_statement和statement_history分析SQL的KPI信息，首先可以通过SQL的时间模型确定具体的耗时阶段，然后结合行活动、语句级别wait event等信息确定SQL的耗时原因，具体分析参考“单SQL性能慢-视图分析”。
• 单SQL偶现慢。- 如果SQL的执行时间超过慢SQL阈值log_min_duration_statement，则通过statement_history查看慢SQL的执行计划、时间模型、行活动、wait event、锁等信息，其中一方面从计划进行分析，其他参考“单SQL性能慢-视图分析”。- 如果慢SQL的top wait event有等事件，可以通过ASP信息查看会话间的锁等待关系。- 如果语句执行时间没超过log_min_duration_statement阈值，第一种可以考虑打开full SQL，set track_stmt_stat_level = ‘L1,L1’，需要注意打开会SQL会记录所有执行的语句（可以会话级别打开），会占用大量的磁盘，用完后要理解关闭；第二种可以考虑调用动态接口，对固定的慢进行跟踪。一般是先通过慢SQL视图分析，通过gs_asp查看慢SQL对应的wait event信息，通过statement_history查看慢SQL信息。

select*from dynamic_func_control('GLOBAL','STMT','TRACK','{"3182919165", "L1"}');-- 抓此SQL的FULLSQL L2 select*from dynamic_func_control('GLOBAL','STMT','UNTRACK','{"3182919165"}');-- 取消抓取 select*from dynamic_func_control('GLOBAL','STMT','LIST','{}');select*from dynamic_func_control('LOCAL','STMT','CLEAN','{}');

单SQL性能慢-视图分析

流控导致慢SQL

常见于批量导数、压测或者批量提交的场景。

1. 在慢SQL出现的时间段内搜索sleep相关字样。
2. 执行如下SQL语句，如果current_sleep_time字段有值说明有流控产生。

SELECT*FROM dbe_perf.global_recovery_status

并发锁冲突导致慢SQL

1. 如果达到慢SQL阈值log_min_duration_statement，则查看statement_history的Top wait event。

SELECT statement_detail_decode(details,'plaintext',true)FROM DBE_PERF.get_global_slow_sql_by_timestamp('start time','end time')WHERE unqiue_sql_id = xxx;

1. 如果慢SQL没有达到SQL阈值log_min_duration_statement，可以直接查看对应时间时间段的dbe_perf.local_active_session/gs_asp信息，查看对应的wait event，如果是实时的SQL慢则参考5。
2. 如果Top Wait event为acquire lock，通过ASP信息分析lock的等锁超时，找到对应的block_sessionid，通过查询以下SQL，查询阻塞该语句的会话。

SELECT*FROM gs_asp WHERE sample_time >'start_time'and sample_time <'end_time'and query like'xxx';

1. 根据block sessionid找到对应的session信息。

SELECT*FROM gs_asp WHERE sample_time >'start_time'and sample_time <'end_time'and query like'xxx'and sessionid = $block sessionid;

1. 实时运行SQL慢，找到对应的block_sessionid，通过查询以下SQL，查询阻塞该语句的会话。

SELECT a.*,b.wait_status, b.wait_event FROM pgxc_stat_activity as a leftjoin pgxc_thread_wait_status as b on a.pid = b.tid and a.sessionid = b. sessionid and a.coorname = b.node_name and b.sessionid = $block_sessionid;

1. 分析找到的会话信息，如果该会话的wait event为wait cmd，说明该会话上的语句已经执行完成，等待客户端发信息。

表膨胀导致大量的死元组

1. 如果达到慢SQL阈值log_min_duration_statement，则查看statement_history/ DBE_PERF.get_global_slow_sql_by_timestamp，如果data_io_time较高或者是n_blocks_fetched- n_blocks_hit加大，说明SQL加载大量的页面导致SQL时延增加。

SELECT*FROM DBE_PERF.get_global_slow_sql_by_timestamp('start time','end time');

1. 如果SQL未达到慢SQL阈值，第一种可以考虑打开full SQL，set track_stmt_stat_level = ‘L1,L1’，需要注意打开会SQL会记录所有执行的语句（可以会话级别打开），会占用大量的磁盘，用完后要理解关闭；第二种可以考虑调用动态接口，对固定的慢进行跟踪。

SELECT*FROM dynamic_func_control('GLOBAL','STMT','TRACK','{"3182919165", "L1"}');-- 抓此SQL的FULLSQL L2 SELECT*FROM dynamic_func_control('GLOBAL','STMT','UNTRACK','{"3182919165"}');-- 取消抓取 SELECT*FROM dynamic_func_control('GLOBAL','STMT','LIST','{}');SELECT*FROM dynamic_func_control('LOCAL','STMT','CLEAN','{}');

1. 通过查看SQL的计划explain(buffers, analyze)查看具体的IO情况，如果语句走索引但是head fetch较大，扫描的行较少，说明需要做大量的可见性判断。
2. 查看慢SQL对应的表的pgstat信息，如果n_dead_tup显示有大量的死元组，或者last_vacuum显示长时间未做vacuum，需要对相关的表做vacuum。

SELECT*FROM pg_stat_all_tables where relname ='xxx';

业务语句不优、计划不优

1. 收集SQL相关的表的结构、索引、表和索引大小等信息。
2. 如果达到慢SQL阈值log_min_duration_statement，则查看statement_history/

DBE_PERF.get_global_slow_sql_by_timestamp，获取SQL的计划。
SELECT*FROM DBE_PERF.get_global_slow_sql_by_timestamp('start time','end time');

1. 如果SQL未达到慢SQL阈值，第一种可以考虑打开full SQL，set track_stmt_stat_level = ‘L1,L1’，需要注意打开会SQL会记录所有执行的语句（可以会话级别打开），会占用大量的磁盘，用完后要理解关闭；第二种可以考虑调用动态接口，对固定的慢进行跟踪，以获取SQL的计划信息。

SELECT*FROM dynamic_func_control('GLOBAL','STMT','TRACK','{"3182919165", "L1"}');-- 抓此SQL的FULLSQL L2 SELECT*FROM dynamic_func_control('GLOBAL','STMT','UNTRACK','{"3182919165"}');-- 取消抓取 SELECT*FROM dynamic_func_control('GLOBAL','STMT','LIST','{}');SELECT*FROM dynamic_func_control('LOCAL','STMT','CLEAN','{}');

1. 或者通过explain查看语句的计划信息。 根据SQL的计划信息和对应的表、索引的信息确认SQL语句是否可以优化，或者索引是否有缺失等。