GaussDB单SQL性能慢分析

SQL性能优化：从告警到深度分析
原创 已于 2023-06-16 10:34:36 修改 · 5.5k 阅读 · 41 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#sql #数据库 #GaussDB #云原生 #DBA #数据库开发

于 2023-06-16 10:33:53 首次发布
文章详细描述了处理单SQL性能慢的问题，包括通过告警识别慢SQL、收集统计信息、分析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 left join 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语句是否可以优化，或者索引是否有缺失等。
GaussDB数据库SQL调优：集中式篇
Sebastien23的博客
12-28 1497
本文中涉及到的GaussDB SQL调优策略仅适用于部署环境，不适用于分布式部署环境。
GaussDB SQL执行计划全解析：从节点树到性能优化
feng6693的博客
06-27 493
GaussDB SQL执行计划全解析：从节点树到性能优化一、执行计划核心概念。
【云小课】如何初步定位GaussDB(for openGauss)慢SQL
GaussDB数据库
11-16 841
数据库在日常使用的过程中会产生很多SQL语句，而如果有些SQL语句运行了很长时间还没有结束，这些语句会消耗很多的系统性能。 那么如何查询到有哪些SQL语句影响了系统的运行呢？ 学了本节课程后，您就将会掌握初步定位GaussDB(for openGauss)慢SQL的方法。 操作场景 数据库执行SQL语句长时间无响应。 数据库CPU/内存压力一直很大。 操作步骤 使用DAS或者gsql连接实例。 查询系统中长时间运行的查询语句。 SELECT current_timestamp - quer.
GaussDB之 SQL子查询优化
wowulita123的博客
04-30 958
GaussDB(DWS) SQL子查询优化
高斯数据库性能调优全解析：从CPU飙高到内存优化的实战指南
最新发布
qq_36062554的博客
10-20 676
根据物理内存合理设置。​​根本原因​​：数据库参数设置不合理，特别是enable_memory_limit参数未正确配置。查询pv_session_memory_detail或pv_thread_memory_detail。​​预防为主​​：合理设置内存参数，特别是enable_memory_limit。​​CPU问题分类​​：当前持续高、历史持续高、瞬时冲高、非SQL原因。​​多维度优化​​：业务、数据库、SQL、架构、环境协同优化。​​监控预警​​：建立完善的监控体系，提前发现潜在问题。
GaussDB SQL执行很慢，性能低，有时长时间运行未结束
D2cOneluo的博客
10-13 3637
数据库经过多次删除操作后，索引页面上的索引键将被删除，造成索引膨胀。5、对表定期做vacuum full和analyze，可回收已更新或已删除的数据所占据的磁盘空间。4、分析SQL语句是否使用了不下推的函数，建议更换为支持下推的语法或函数。2、分析查询是否被阻塞，导致语句运行时间过长，可以强制结束有问题的会话。如果没有锁，查找相关业务sql，按照方法一中的判断方法进行处理。如果有锁，则查找出pid字段，使用如下函数，结束任务，释放锁。查询表大小，如果表非常大，而实际只有很少数据，那么应该执行。
gaussdb大表查询性能慢的解决思路
我的程序猿学习之路
08-28 3334
1.经验分享这一套查询性能优化解决方案解决了我们经营分析5G智慧加油站项目中GAUSSDB的绝大部分SQL性能问题，让大内存的表调度慢不再成为每个开发人员头疼的问题。有时候平台上提供的报错信息显示任务超时，一般是脚本中SQL有性能问题，我们就需要分场景一步步去找原因。数据库报错超时并不像其他编程语言有那么清晰的报错原因，很多情况需要我们开发人员细心、有解决经验。有了这一套解决方案的总结并让开发、运维人员一起交流经验，集思广益，持续积累，能降低性能优化门槛，让新人更快上手。2.使用心得。
GaussDB整体性能慢分析
qq_33365731的博客
09-27 890
长时间性能下降通常的场景，通常是指某段时间(小时级)性能抖动，比如: 过去8:00-9:00性能正常，但10:00-11:11性能出现较大的性能抖动，这种场景，可以比较两段时间内WDR报告，排查报告内差异点，可排查Top SQL、Top Wait Events、Load Profile、Cache/IO Stats、Object Stats等。当前数据库内核默认是开启线程池的，由于某些或者某个不优SQL慢，导致业务整体性能差，或者占满整个内核线程池，从而可能会引发更加严重的业务阻塞。
GaussDB数据库SQL诊断--查看全量SQL
Cui_Yuan_666的博客
03-25 1111
GaussDB全量SQL功能支持采集并解析SQL语句，便于用户查看SQL语句的执行情况。支持使用OBS和NAS进行文件存储全量SQL文件，通过系统参数NAS_SUPPORT控制。参数值为false时，表示使用OBS存储。参数值为true，表示使用NAS存储。
【GaussDB】分布式性能分析常用的SQL
laoshu2007的专栏
08-30 1568
--查看连接数 select coorname,usename,application_name,substr(query,1,50) as query,state from pgxc_stat_activity where state='active' and usename='xxx'; select coorname,count() from pgxc_stat_activity wher...
GaussDB分区表查询性能异常分析
qq_33365731的博客
09-27 956
使用分区表进行相关查询业务，SQL性能慢。
【GaussDB】慢SQL诊断
weixin_43346403的博客
04-08 724
2.执行命令查看数据库实例中慢SQL语句执行信息。1.执行命令查看数据库实例中SQL语句执行信息。3.查看当前主节点SQL语句执行信息。4.查看当前备节点SQL语句执行信息。
十八般武艺玩转GaussDB(DWS)性能调优：路径干预
华为云官方博客
01-13 1944
摘要：路径生成是表关联方式确定的主要阶段，本文介绍了几个影响路径生成的要素：cost_param, scan方式，join方式，stream方式，并从原理上分析如何干预路径的生成。 一、cost模型选择 顾名思义，cost_param是控制cost相关的一个参数。在了解cost_param之前，先回顾一下选择率的概念，GaussDB优化器中的选择率是指，当一个表有一个过滤或关联条件时，通过该条件能被选中的行数占总行数的比例，是介于0~1之间的一个实数。选择率在优化器中是一个重要的概念，主要应用于行数和d
GaussDB整体性能慢-视图分析
Gauss松鼠会
07-03 2893
本章节主要描述GaussDB使用性能视图分析如下原因导致的性能慢，找到问题的根因点
GaussDB 慢SQL诊断
m0_58021272的博客
11-28 890
上述示例中，用户输入了SQL执行的起止时间，工具会在数据库WDR报告中在该时间范围内进行搜寻，以发现SQL的精确执行时间和结束时间，之后基于上述规则进行分析；慢SQL提供表和函数两种维度的查询接口，用户从接口中能查询到作业的执行计划，开始、结束执行时间，执行查询的语句，行活动，内核时间，CPU时间，执行时间，解析时间，编译时间，查询重写时间，计划生成时间，网络时间，IO时间，网络开销，锁开销等。情况三：两表join操作，选择了NestLoop方式，且执行代价较大，该场景计算慢，引起的性能慢；
openGauss数据库源码解析——慢SQL检测
scp091的博客
12-06 2065
openGauss数据库源码解析——慢SQL检测 慢SQL检测的定义： 基于历史SQL语句信息进行模型训练，并用训练好的模型进行SQL语句的预测，利用预测结果判断该SQL语句是否是潜在的慢SQL。当发现潜在的慢SQL后，开发者便可以进行针对性优化或者风险评估，以防业务上线后发生问题。 慢SQL检测的功能： 上线业务预检测：上线一批新业务前，使用SQL诊断功能评估此次上线业务的预估执行时长，便于用户参考是否应该修改上线业务。workload分析：能够对现有workload进行分析，将现有workloa
GaussDB整体性能慢分析教程。
高性价比服务器就选：蓝易云
07-17 419
通过以上步骤，你可以分析和优化GaussDB的整体性能慢问题。请注意，具体的优化步骤和方法可能因系统配置、工作负载和具体问题而有所不同。建议参考GaussDB的官方文档和相关性能优化指南，以获得更详细和针对性的优化建议。根据收集到的性能指标，确定系统的瓶颈所在。
openGauss Slow-Query-Diagnosis-慢SQL根因分析使用指导
2301_79720856的博客
04-25 461
【代码】openGauss Slow-Query-Diagnosis-慢SQL根因分析使用指导。
gaussdb 单笔insert INTO，性能不佳
10-14
<think>嗯，用户想解决GaussDB中单笔INSERT INTO性能不佳的问题。用户提供了三个引用资料：引用1提到MERGE INTO可以高效更新导入数据，引用2详细说明了INSERT INTO的三种用法，引用3对比了SELECT INTO和INSERT INTO SELECT的区别。用户特别强调"单笔INSERT INTO性能不佳"，这说明用户可能在使用单条INSERT语句插入数据时遇到了性能瓶颈。 让我仔细分析这个问题。单条INSERT语句性能差通常有几个原因：一是每次插入都需要建立连接和事务开销，二是频繁的磁盘I/O操作，三是索引维护成本高。引用2中提到INSERT INTO有三种形式，但都属于逐条插入。而引用1和引用3其实已经给出了解决方案的核心思路：批量操作。 用户可能是在用第一种或第二种INSERT语法逐条插入数据，这在数据量大时必然很慢。比较有意思的是引用3提到"insert into select可以直接将查询的结果插入"，这其实就是关键的优化方向。另外引用1提到的MERGE INTO虽然主要用于更新，但也侧面说明GaussDB对批量操作有优化。 需要特别注意用户说的"单笔"这个限定词。这说明用户当前可能在循环中执行单条INSERT，这种场景下优化方案很明确：改用批量插入。但用户可能不知道具体怎么实施批量操作，或者担心数据一致性等问题。 从技术实现看，解决方案应该分层次：首先是操作层面的批量改写，其次是事务控制的优化，最后是并发处理的技巧。引用2中第三种写法已经展示了多行写入的语法，这就是最直接的优化手段。另外引用3提到的INSERT INTO SELECT也很实用，特别是当数据源在其他表时。 用户可能还存在一些隐含需求，比如：如何确保批量操作的数据一致性？批量操作失败如何回滚？这些都需要在回答中说明。考虑到用户是技术人员，可以适当提供代码示例，但要注意GaussDB的特殊语法可能和标准SQL有所不同。 值得提醒的是，MERGE INTO在引用1中被描述为"高效的数据导入功能"，但它主要针对UPSERT场景(更新或插入)，和纯插入场景略有不同。不过这个信息可以补充说明，当用户需要合并数据时使用。 最后要强调事务控制的重要性。如果用户原先是每条INSERT一个事务，改成批量后必须注意事务大小控制，避免超大事务导致内存问题。可以建议每5000条提交一次的折中方案。 </think>针对GaussDB单笔`INSERT INTO`性能不佳的问题，结合引用内容和数据库优化原理，提供以下解决方案： --- ### **优化方案** #### 1. **改用批量插入**（核心优化） 单笔逐条插入会产生高频事务开销和网络延迟。需将单条插入改为 **多行批量写入**（引用[2]中第三种语法）： ```sql INSERT INTO table_name (col1, col2, ...) VALUES (value1_1, value1_2, ...), (value2_1, value2_2, ...), -- 一次性插入多行数据 ...; ``` **效果**：减少事务提交次数和日志写入压力，提升吞吐量5-100倍[^2]。 --- #### 2. **使用 `INSERT INTO SELECT` 替代单条插入** 若数据源来自其他表或查询结果，直接使用： ```sql INSERT INTO target_table (col1, col2, ...) SELECT src_col1, src_col2, ... FROM source_table WHERE ...; -- 避免逐条转换插入 ``` **优势**：消除应用层与数据库的交互延迟，数据在引擎内部高效流转[^3]。 --- #### 3. **临时禁用索引和约束（大数据量场景）** 对于超大规模插入： ```sql ALTER TABLE table_name DISABLE TRIGGER ALL; -- 禁用触发器 DROP INDEX index_name; -- 删除非必要索引（需提前备份） -- 执行批量插入操作 ALTER TABLE table_name ENABLE TRIGGER ALL; CREATE INDEX index_name ...; -- 重建索引 ``` **注意**：重建索引耗时需评估，建议在业务低峰期操作。 --- #### 4. **调整事务提交方式** 默认单条`INSERT`自动提交事务，可通过显式事务减少开销： ```sql BEGIN; -- 开启事务 INSERT INTO table ... VALUES (...); INSERT INTO table ... VALUES (...); -- 多个插入操作 COMMIT; -- 统一提交 ``` **适用场景**：无法改造为批量插入时，合并事务降低磁盘I/O压力[^1]。 --- #### 5. **分区表优化** 若目标表为分区表，确保插入操作**直接定位到分区**： ```sql INSERT INTO partition_table PARTITION (partition_name) ... ``` 避免全分区扫描的开销。 --- ### **性能对比示例** | 操作方式 | 10万行耗时 | 资源消耗 | |----------------------|------------|----------| | 单条INSERT循环 | >300s | 高CPU/IO | | 多行批量插入(1000行/批) | ~5s | 中等 | | INSERT INTO SELECT | ~1s | 低 | --- ### **相关问题** 1. 如何评估批量插入的最佳批次大小？ 2. 禁用索引后重建失败如何恢复数据一致性？ 3. GaussDB的`MERGE INTO`在数据更新场景下比`INSERT+UPDATE`快多少[^1]？ 4. 分区表在批量插入时的性能瓶颈有哪些？ > 通过批量操作减少事务开销是核心优化方向。对于超过百万行的数据迁移，建议结合GaussDB的`COPY`命令或ETL工具实现[^1][^3]。
评论 11
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Gauss松鼠会

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值