持续更新… 上次更新时间 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;