【GaussDB】分布式性能分析常用的SQL

2024-08-31 14:36

本文主要是介绍【GaussDB】分布式性能分析常用的SQL,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

--查看连接数
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 where usename='cbsprd' group by coorname;
select coorname,client_addr,count(
) from pgxc_stat_activity where state='active' and usename='cbsprd';

--杀会话
execute direct on all 'select pg_terminate_session(pid,sessionid) from pg_stat_activity where usename='xxx';
execute direct on all 'select * from statement_history whee unique_query_id='1178397073' and query is not null;';

--清理statement
select reset_unique_sql('GLOBAL','ALL',0);

--查询TOP SQL
select n_calls,unique_sql_id,substr(query,1,50) as query,total_elapse_time/n_calls/1000 as avg_time,total_elapse_time/1000 as runtime from dbe_perf.statement where user_name='cbsprd' and n_calls>10 and avg_time>5 order by runtime desc;
select n_calls,unique_sql_id,substr(query,1,50) as query,total_elapse_time/n_calls/1000 as avg_time,total_elapse_time/1000 as runtime from dbe_perf.statement where user_name='cbsprd' and n_calls>10 and query like '%kapb% order by runtime desc;
select sum(total_elapse_time)/1000 as total_time from be_perf.statement where user_name='cbsprd' ;

--查询等待事件
select wait_status,wait_event,count() from pg_thread_wait_status group by 1,2 order by 3 desc;
select wait_status,event,count(
) from gs_asp where sample_time >'2024-06-26 17:20:00' and sample_time<'2024-06-26 17:50:00' and databaseid=685433 group by 1,2 order by 3 desc;
select wait_status,event,count(*) from dbe_perf.local_active session where sample_time >'2024-06-26 17:20:00' and sample_time<'2024-06-26 17:50:00' and databaseid=685433 group by 1,2 order by 3 desc;
select * from dbe_perf.local_active session where sample_time >'2024-06-26 17:20:00' and sample_time<'2024-06-26 17:50:00' and databaseid=685433 and unique_query_id;=3935832574;

--查询慢SQL
select unique_query_id,substr(query,1,100) as query,count(*) from be_perf.get_global_full_sql_by_timestamp('2024-06-12 10:30:00','2024-06-12 16:30:00') where user_name='cbsprd' group by 1,2 order by 3 desc;

--查询单条慢SQL记录
\x
select *,dbe_perf.get_global_full_sql_by_timestamp('2024-06-12 10:30:00','2024-06-12 16:30:00') where unique_query_id=xxxxx and query is not null;
select * from dbe_perf.statement_history where unique_query_id=xxxxx and query is not null;
select * from dbe_perf.statement where unique_sql_id=xxxxxx;

--查询列的统计信息
select * from pg_stats where tablename='kapb_jioyrz' and attname='jiluztai';

--SQLPATCH
select * from dbe_sql_util.drop_sql_patch('patch1');
select * from dbe_sql_util.create_hint_sql_patch('809780568','809780568','use_cplan');
select * from dbe_sql_util.create_hint_sql_patch('patch4','37772378082','set(enable_for_fqs_on)');
select * from dbe_sql_util.create_hint_sql_patch('3203049046','3203049046','set(enable_stream_operator off)');
select * from dbe_sql_util.create_hint_sql_patch('3203049046','3203049046','set(enable_stream_operator off)');
select * from dbe_sql_util.create_hint_sql_patch('1580322454','1580322454','set(query_dop 8) set(enable_seqscan on)');
select * from dbe_sql_util.create_hint_sql_patch('1580322453','1580322453','set(query_dop 8) set(enable_seqscan on) set(try_vector_engine_strategy force');
select * from dbe_sql_util.create_hint_sql_patch('1580322452','1580322452','no_expand(@"sel$2")');
select * from gs_sql_patch;

--汇总数据库消耗时间
select sum(total_elapse_time)/1000 as total_time from be_perf.statement where user_name='cbsprd' ;

--查看锁
select *,pg_catalog.statement_detail_decode(details,'plaintext',true) from statement_history where unique_query_id=3177795013 order by db_time desc;
select * from pg_catalog.statement_detail_decode(detail,'plaintext',true);--detail='\x5e......'十六进制码

--生成火焰图
perf record -e cpu-clock -g -p 946111 -- sleep 60

--抓full sql
select * from dynamic_func_control('LOCAL','STMT','TRACK','{"1178397073","L2"}');
select * from dynamic_func_control('LOCAL','STMT','TRACK','{"1178397073"}');

--内存消耗
select * from pg_total_memory_detail;

select contextname,sum(totalsize)/1024/1024 totalsize,sum(freesize)/1024/1024 freesize,count() sum from pv_thread_memory_context group by contextname order by sum desc limit 10;
select contextname,sum(totalsize)/1024/1024 totalsize,sum(freesize)/1024/1024 freesize,count(
) sum from pv_session_memory_context group by contextname order by sum desc limit 20;

--耗时统计
select a.ljhaoshi/1000/60/60,a.* from cbsprd.ksys_plrenw a where a.pljypich='hx_dayend_all_20240626_9999' order by a.dqjioayrq asc;

--内存不足问题
gs_guc set -Z coordinator -Z datanode -N all -I all -c "enable_global_plancache=on"
gs_guc set -Z coordinator -Z datanode -N all -I all -c "enable_stream_pbe=off"

这篇关于【GaussDB】分布式性能分析常用的SQL的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1124176

相关文章

浅谈mysql的not exists走不走索引

《浅谈mysql的notexists走不走索引》在MySQL中,​NOTEXISTS子句是否使用索引取决于子查询中关联字段是否建立了合适的索引,下面就来介绍一下mysql的notexists走不走索... 在mysql中,​NOT EXISTS子句是否使用索引取决于子查询中关联字段是否建立了合适的索引。以下

Linux系统性能检测命令详解

《Linux系统性能检测命令详解》本文介绍了Linux系统常用的监控命令(如top、vmstat、iostat、htop等)及其参数功能,涵盖进程状态、内存使用、磁盘I/O、系统负载等多维度资源监控,... 目录toppsuptimevmstatIOStatiotopslabtophtopdstatnmon

Android kotlin中 Channel 和 Flow 的区别和选择使用场景分析

《Androidkotlin中Channel和Flow的区别和选择使用场景分析》Kotlin协程中,Flow是冷数据流,按需触发,适合响应式数据处理;Channel是热数据流,持续发送,支持... 目录一、基本概念界定FlowChannel二、核心特性对比数据生产触发条件生产与消费的关系背压处理机制生命周期

Jenkins分布式集群配置方式

《Jenkins分布式集群配置方式》:本文主要介绍Jenkins分布式集群配置方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录1.安装jenkins2.配置集群总结Jenkins是一个开源项目,它提供了一个容易使用的持续集成系统,并且提供了大量的plugin满

Java通过驱动包(jar包)连接MySQL数据库的步骤总结及验证方式

《Java通过驱动包(jar包)连接MySQL数据库的步骤总结及验证方式》本文详细介绍如何使用Java通过JDBC连接MySQL数据库,包括下载驱动、配置Eclipse环境、检测数据库连接等关键步骤,... 目录一、下载驱动包二、放jar包三、检测数据库连接JavaJava 如何使用 JDBC 连接 mys

SQL中如何添加数据(常见方法及示例)

《SQL中如何添加数据(常见方法及示例)》SQL全称为StructuredQueryLanguage,是一种用于管理关系数据库的标准编程语言,下面给大家介绍SQL中如何添加数据,感兴趣的朋友一起看看吧... 目录在mysql中,有多种方法可以添加数据。以下是一些常见的方法及其示例。1. 使用INSERT I

Qt使用QSqlDatabase连接MySQL实现增删改查功能

《Qt使用QSqlDatabase连接MySQL实现增删改查功能》这篇文章主要为大家详细介绍了Qt如何使用QSqlDatabase连接MySQL实现增删改查功能,文中的示例代码讲解详细,感兴趣的小伙伴... 目录一、创建数据表二、连接mysql数据库三、封装成一个完整的轻量级 ORM 风格类3.1 表结构

Spring Boot中WebSocket常用使用方法详解

《SpringBoot中WebSocket常用使用方法详解》本文从WebSocket的基础概念出发,详细介绍了SpringBoot集成WebSocket的步骤,并重点讲解了常用的使用方法,包括简单消... 目录一、WebSocket基础概念1.1 什么是WebSocket1.2 WebSocket与HTTP

MySQL 中的 CAST 函数详解及常见用法

《MySQL中的CAST函数详解及常见用法》CAST函数是MySQL中用于数据类型转换的重要函数,它允许你将一个值从一种数据类型转换为另一种数据类型,本文给大家介绍MySQL中的CAST... 目录mysql 中的 CAST 函数详解一、基本语法二、支持的数据类型三、常见用法示例1. 字符串转数字2. 数字

Mysql实现范围分区表(新增、删除、重组、查看)

《Mysql实现范围分区表(新增、删除、重组、查看)》MySQL分区表的四种类型(范围、哈希、列表、键值),主要介绍了范围分区的创建、查询、添加、删除及重组织操作,具有一定的参考价值,感兴趣的可以了解... 目录一、mysql分区表分类二、范围分区(Range Partitioning1、新建分区表:2、分