MogDB/openGauss 常用查詢彙總
概述
在 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 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;