MogDB/openGauss常用查询汇总

概述

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

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

#!/bin/bash

source ~/.bashrc

thread_sets=`ps -ef |grep -i bin/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);"

查看hang住SQL的堆栈信息

--查sql对应的线程id（lwtid）
select pid,lwtid,state,query from pg_stat_activity a,dbe_perf.thread_wait_status s where a.pid=s.tid and query like '%xxx%';

--查线程的堆栈信息
pstack lwtid

当前活跃慢SQL查询

select datname,usename,application_name,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;

查看1小时内的慢SQL数量，按分钟汇总

select substr(trunc(start_time,'MI'),1,19) start_time,user_name,schema_name,unique_query_id,count(*) execs,round(sum(execution_time)/1e3/count(*),1) et_ms,round(sum(cpu_time)/1e3/count(*),1) cpu_ms,round(sum(data_io_time)/1e3/count(*),1) io_ms,round(sum(n_returned_rows)/count(*),1) rows 
from pg_catalog.statement_history
where (start_time>=now()-1/24 or (finish_time>=now()-1/24 and execution_time>=5*1e6*60)) and is_slow_sql='t'
group by substr(trunc(start_time,'MI'),1,19),user_name,schema_name,unique_query_id
order by 1 desc,6 desc;

六小时内已经执行完的慢SQL

select db_name,user_name,application_name,client_addr,start_time,finish_time,(finish_time - start_time) as run_time,n_soft_parse,n_hard_parse,query,query_plan 
from statement_history 
where (now() - start_time ) < '6 hour' order by (finish_time - start_time) desc ;

统计当前客户端连接会话状态

select client_addr, sum(case when state='active' then  state_count else 0 end) as active,sum(case when state='idle' then  state_count else 0 end) as idle,sum(case when state='idle in transaction' then  state_count else 0 end) as idle_in_transaction
from (select distinct client_addr,state,count(*) over(partition by client_addr,state) as state_count from pg_stat_activity)
group by client_addr order by 2 desc;

查看当前数据库下所有表的注释信息

\dt+

或

select current_database(),nspname as schema ,relname,description
from pg_class c ,pg_namespace n,pg_description d
where c.relnamespace=n.oid 
and c.oid=d.objoid 
and d.objsubid=0 
and nspname not in('pg_catalog','db4ai');

查看当前数据库下所有表字段的注释信息

\d+

或

select current_database(),nspname as schema ,relname,attname as column ,atttypid::regtype as datatype,objsubid,atthasdef as default,description
from pg_class c ,pg_namespace n,pg_attribute a,pg_description d
where c.relnamespace=n.oid 
and a.attrelid=c.oid 
and c.oid=d.objoid 
and a.attnum>0 
and d.objsubid=0 
and nspname not in('pg_catalog','db4ai');

索引有效性及定义

select tablename,indexname,indisvalid,indexdef
from pg_index i,(select schemaname||'.'||tablename tablename,schemaname||'.'||indexname indexname,indexdef from pg_indexes) ie
where i.indrelid::regclass::text=ie.tablename and i.indexrelid::regclass::text=ie.indexname and (indisvalid='f' or lower(indexdef) like '%invalid%');

查看复制槽

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;

锁阻塞详情

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;

查看指定schema下除主键以外的非空约束

select table_schema||'.'||table_name||'.'||column_name as tna 
from information_schema.columns 
where is_nullable='NO' and table_schema in('su1') 
and tna not in(select table_schema||'.'||relname||'.'||column_name as tn 
               from pg_constraint con,pg_class c,pg_attribute a,information_schema.columns col 
               where con.conrelid=c.oid and a.attrelid=c.oid and contype='p' and a.attnum=con.conkey[1] and col.table_name=c.relname and a.attname=col.column_name);

查询一段时间内产生的wal数据量

select pg_size_pretty(sum(size)) as " WAL size"
from pg_ls_waldir() 
where modification > (now() - interval '60 minutes') ; 

密码剩余有效期

select username,pw_creattime,extract(day from remain_dates) remain_days 
from (select pg_get_userbyid(roloid) username ,max(passwordtime) pw_creattime ,max(passwordtime)+(select (case setting when 0 then null else setting end) from pg_settings where name='password_effect_time')::interval - now() as remain_dates 
      from pg_auth_history group by roloid order by 3) foo;

分区表关系查询

select r.relname table_name,p.relname part_table_name,(case p.partstrategy when 'i' then 'interval' when 'r' then 'range' when 'l' then 'list' when 'h' then 'hase' when 'v' then 'value' when 'n' then 'invalid' end) part_type,column_name part_col,r.interval,p.boundaries 
from (select relname,column_name,partstrategy,interval,parttype,parentid 
      from pg_partition r,information_schema.columns c 
      where r.relname=c.table_name and c.ordinal_position::text=r.partkey::text) r,pg_partition p 
where r.parttype='r' and r.parentid=p.parentid and r.relname != p.relname;

分区表定义查询

select pg_get_tabledef(relname::regclass) from pg_partition where parttype='r';