1

mogdb/opengauss常用查询汇总 -利来国际app

概述

在mogdb/opengauss日常运维过程中,会经常通过sql来获取想要查看的信息,这些sql可以作为监控指标、巡检指标,也可以临时查询使用。

通过系统线程id查对应的query

#!/bin/bash
source ~/.bashrc
thread_sets=`ps -ef |grep -i gaussdb |grep -v grep|awk -f ' ' '{print $2}'|xargs top -n 1 -bhp |grep -i ' worker'|awk -f ' ' '{print $1}'|tr "\n" ","|sed -e 's/,$/\n/'`
gsql -p 26000 postgres -c "select  pid,lwtid,state,query from pg_stat_activity a,dbe_perf.thread_wait_status s where a.pid=s.tid and lwtid in($thread_sets);"

查看复制槽

select slot_name,coalesce(plugin,'_') as plugin,slot_type,datoid,coalesce(database,'_') as database,
       (case active when 't' then 1 else 0 end)as active,coalesce(xmin,'_') as xmin,dummy_standby,
       pg_xlog_location_diff(case when pg_is_in_recovery() then restart_lsn else pg_current_xlog_location() end , restart_lsn)  as delay_lsn
from pg_replication_slots;

查看主备延迟

--主库
select client_addr,sync_state,pg_xlog_location_diff(pg_current_xlog_location(),receiver_replay_location) from pg_stat_replication;
--备库
select now() as now, 
       coalesce(pg_last_xact_replay_timestamp(), now()) replay,
       extract(epoch from (now() - coalesce(pg_last_xact_replay_timestamp(), now()))) as diff;

慢sql查询

select datname,usename,client_addr,pid,query_start::text,extract(epoch from (now() - query_start)) as query_runtime,xact_start::text,extract(epoch from(now() - xact_start)) as xact_runtime,state,query 
from pg_stat_activity 
where state not in('idle') and query_start is not null;

锁阻塞详情

with tl as (select usename,granted,locktag,query_start,query 
            from pg_locks l,pg_stat_activity a 
            where l.pid=a.pid and locktag in(select locktag from pg_locks where granted='f')) 
select ts.usename locker_user,ts.query_start locker_query_start,ts.granted locker_granted,ts.query locker_query,tt.query locked_query,tt.query_start locked_query_start,tt.granted locked_granted,tt.usename locked_user,extract(epoch from now() - tt.query_start) as locked_times
from (select * from tl where granted='t') as ts,(select * from tl where granted='f') tt 
where ts.locktag=tt.locktag 
order by 1;

锁阻塞源统计

with tl as (select usename,granted,locktag,query_start,query 
            from pg_locks l,pg_stat_activity a 
            where l.pid=a.pid and locktag in(select locktag from pg_locks where granted='f')) 
select usename,query_start,granted,query,count(query) count 
from tl 
where granted='t' 
group by usename,query_start,granted,query 
order by 5 desc;

数据表大小排序

select current_catalog as datname,nsp.nspname,rel.relname,
             pg_total_relation_size(rel.oid)       as bytes,
             pg_relation_size(rel.oid)             as relsize,
             pg_indexes_size(rel.oid)              as indexsize,
             pg_total_relation_size(reltoastrelid) as toastsize
from pg_namespace nsp join pg_class rel on nsp.oid = rel.relnamespace
where nspname not in ('pg_catalog', 'information_schema','snapshot') and rel.relkind = 'r'
order by 4 desc limit 100; 

索引大小排序

select current_catalog as datname,schemaname schema_name,relname table_name,indexrelname index_name,pg_table_size(indexrelid) as index_size 
from pg_stat_user_indexes 
where schemaname not in('pg_catalog', 'information_schema','snapshot')
order by 4 desc limit 100;

表膨胀率排序

select current_catalog as datname,schemaname,relname,n_live_tup,n_dead_tup,round((n_dead_tup::numeric/(case (n_dead_tup n_live_tup) when 0 then 1 else (n_dead_tup n_live_tup) end ) *100),2) as dead_rate
from pg_stat_user_tables
where (n_live_tup   n_dead_tup) > 10000
order by 5 desc limit 100;

session按状态分类所占用内存大小

select state,sum(totalsize)::bigint as totalsize
from gs_session_memory_detail m,pg_stat_activity a 
where substring_inner(sessid,position('.' in sessid)  1)=a.sessionid and usename<>'mondb' and pid != pg_backend_pid() 
group by state order by sum(totalsize) desc;

查看session中query占用内存大小

select sessionid, coalesce(application_name,'')as application_name,
       coalesce(client_addr::text,'') as client_addr,sum(usedsize)::bigint as usedsize, 
       sum(totalsize)::bigint as totalsize,query 
from gs_session_memory_detail s,pg_stat_activity a 
where substring_inner(sessid,position('.' in sessid)  1)=a.sessionid 
group by sessionid,query,application_name,client_addr 
order by sum(totalsize) desc limit 10;
最后修改时间:2021-11-29 15:32:57
「喜欢文章,快来给作者赞赏墨值吧」
【利来手机国际的版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:[email protected]进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论