【GreenPlum 运维】

最新推荐文章于 2024-03-29 11:10:00 发布

kinifine

最新推荐文章于 2024-03-29 11:10:00 发布

阅读量315

点赞数

分类专栏： GreenPlum 文章标签：运维数据库 postgresql

本文链接：https://blog.csdn.net/wfslife/article/details/128467039

版权

GreenPlum 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

持续更新… 上次更新时间 2022-12-28 12:53

文章目录

- 一、数据库查询慢语句监控

一、数据库查询慢语句监控

1.1 简介

设置调度任务执行匿名函数过程, 实时查询系统表 pg_stat_activity, 记录每个查询的耗时等情况, 并写入结果表

1.2 结果表建表语句

drop table public.ads_system_gpmoitor_querylog;
create table public.ads_system_gpmoitor_querylog(
	id text null,
	pid int4 null,
	usename name null,
	application_name text null,
	client_addr inet null,
	state_change timestamptz null,
	log_time timestamptz null,
	query text null,
	waiting_reason text null
)
distributed randomly;

comment on table public.ads_system_gpmoitor_querylog is '慢查询监控结果表';
comment on column public.ads_system_gpmoitor_querylog.id is '主键';
comment on column public.ads_system_gpmoitor_querylog.pid is '进程号';
comment on column public.ads_system_gpmoitor_querylog.usename is '用户';
comment on column public.ads_system_gpmoitor_querylog.application_name is '客户端';
comment on column public.ads_system_gpmoitor_querylog.client_addr is '客户端ip';
comment on column public.ads_system_gpmoitor_querylog.state_change is '查询开始(状态变为active)';
comment on column public.ads_system_gpmoitor_querylog.log_time is '查询结束(最后记录时间)';
comment on column public.ads_system_gpmoitor_querylog.query is '查询语句';
comment on column public.ads_system_gpmoitor_querylog.waiting_reason is '锁等待原因';

1.3 调度

频率 : 建议每30s调度执行一次
过程 : 采用匿名函数的方式执行

set search_path= public;
do $$
	-- 记录 >30s 的 query, 保留7天数据
	begin

	drop table if exists temp_ads_system_gpmoitor_query;
	create temp table temp_ads_system_gpmoitor_query as 
	select
		substring(md5(pid::varchar||state_change),9,16) id,
		pid,
		usename,
		application_name,
		client_addr,
		state_change,
		current_timestamp log_time,
		query,
		waiting_reason
	from pg_stat_activity
	where state ='active' and now() - query_start > interval '30 s';

	delete from ads_system_gpmoitor_querylog 
	where id in (select id from temp_ads_system_gpmoitor_query where id is not null) or (state_change < (current_date) -7);

	insert into ads_system_gpmoitor_querylog(
		id,pid,usename,application_name,client_addr,state_change,log_time,query,waiting_reason)
	select id,pid,usename,application_name,client_addr,state_change,log_time,query,waiting_reason
	from temp_ads_system_gpmoitor_query;

end;

$$;
commit;

1.4 查询结果

select
	pid "进程号",
	usename "用户",
	application_name "客户端",
	client_addr "客户端ip",
	state_change "查询开始(状态变为active)",
	log_time "查询结束(最后记录时间)",
	query "查询语句",
	waiting_reason "锁等待原因",
	extract(epoch FROM (log_time- state_change))::int "总耗时(s)"
from  public.ads_system_gpmoitor_querylog 
where state_change >= current_date
order by "总耗时(s)" desc;