<em>Mac</em>Book项目 2009年学校开始实施<em>Mac</em>Book项目,所有师生配备一本<em>Mac</em>Book,并同步更新了校园无线网络。学校每周进行电脑技术更新,每月发送技术支持资料,极大改变了教学及学习方式。因此2011
2021-06-01 09:32:01
史上最全PostgreSQL DBA最常用SQL
2022-10-20 14:03:52
背景
建立檢視, 方便查詢
create schema dba;
create view dba.invalid_index as select indisvalid, indexrelid::regclass, indrelid::regclass, pg_get_indexdef(indexrelid) from pg_index where not indisvalid;
create view dba.ro_conflicts as select datname,pg_stat_get_db_conflict_all(oid) conflict_all,pg_stat_get_db_conflict_bufferpin(oid) conflict_bufferpin,pg_stat_get_db_conflict_lock(oid) conflict_lock,pg_stat_get_db_conflict_snapshot(oid) conflict_snapshot,pg_stat_get_db_conflict_startup_deadlock(oid) conflict_deadlock,pg_stat_get_db_conflict_tablespace(oid) conflict_tbs from pg_database;
create or replace procedure dba.tps() as $$
declare
v1 int8;
v2 int8;
begin
select txid_snapshot_xmax(txid_current_snapshot()) into v1;
commit;
perform pg_sleep(1);
select txid_snapshot_xmax(txid_current_snapshot()) into v2;
commit;
raise notice 'tps: %', v2-v1;
end;
$$ language plpgsql ;
-- 在主節點查詢
create view dba.ro_delay as select application_name,client_addr,client_port,write_lag,replay_lag,sync_state from pg_stat_replication ;
-- 在standby節點執行， 檢查replay比receive的延遲
create view dba.node_delay as select * from pg_size_pretty(pg_wal_lsn_diff(pg_last_wal_receive_lsn(),pg_last_wal_replay_lsn())) as t(delay);
-- 在standby節點執行， 檢查receiver接收wal比上游產生wal的延遲.
create view dba.ro_delay_on_standby as select pg_size_pretty(pg_wal_lsn_diff(latest_end_lsn , received_lsn)) from pg_stat_wal_receiver;
-- 在standby節點執行， 接收wal的速度。
CREATE OR REPLACE PROCEDURE dba.wal_receive_bw()
LANGUAGE plpgsql
AS $procedure$
declare
v1 pg_lsn;
v2 pg_lsn;
begin
select pg_last_wal_receive_lsn() into v1;
commit;
perform pg_sleep(1);
select pg_last_wal_receive_lsn() into v2;
commit;
raise notice 'wal receive bw: %/s', pg_size_pretty(pg_wal_lsn_diff(v2,v1));
end;
$procedure$;
-- 在standby節點執行， replay wal的速度。
CREATE OR REPLACE PROCEDURE dba.wal_replay_bw()
LANGUAGE plpgsql
AS $procedure$
declare
v1 pg_lsn;
v2 pg_lsn;
begin
select pg_last_wal_replay_lsn() into v1;
commit;
perform pg_sleep(1);
select pg_last_wal_replay_lsn() into v2;
commit;
raise notice 'wal replay bw: %/s', pg_size_pretty(pg_wal_lsn_diff(v2,v1));
end;
$procedure$;
create view dba.topsql as select calls,total_time,total_time/calls,query from pg_stat_statements where query !~ 'rds' order by total_time desc limit 5;
create view dba.qps as with
a as (select sum(calls) s from pg_stat_statements),
b as (select sum(calls) s from pg_stat_statements , pg_sleep(1))
select
b.s-a.s -- QPS
from a,b;
create view dba.session_acting_cnt as select count(*) from pg_stat_activity where wait_event is not null and (backend_xid is not null or backend_xmin is not null);
create view dba.sessions as select * from pg_stat_activity where wait_event is not null and (backend_xid is not null or backend_xmin is not null);
create view dba.locks as with
t_wait as
(
select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,
a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,
b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name
from pg_locks a,pg_stat_activity b where a.pid=b.pid and not a.granted
),
t_run as
(
select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,
a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,
b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name
from pg_locks a,pg_stat_activity b where a.pid=b.pid and a.granted
),
t_overlap as
(
select r.* from t_wait w join t_run r on
(
r.locktype is not distinct from w.locktype and
r.database is not distinct from w.database and
r.relation is not distinct from w.relation and
r.page is not distinct from w.page and
r.tuple is not distinct from w.tuple and
r.virtualxid is not distinct from w.virtualxid and
r.transactionid is not distinct from w.transactionid and
r.classid is not distinct from w.classid and
r.objid is not distinct from w.objid and
r.objsubid is not distinct from w.objsubid and
r.pid <> w.pid
)
),
t_unionall as
(
select r.* from t_overlap r
union all
select w.* from t_wait w
)
select locktype,datname,relation::regclass,page,tuple,virtualxid,transactionid::text,classid::regclass,objid,objsubid,
string_agg(
'Pid: '||case when pid is null then 'NULL' else pid::text end||chr(10)||
'Lock_Granted: '||case when granted is null then 'NULL' else granted::text end||' , Mode: '||case when mode is null then 'NULL' else mode::text end||' , FastPath: '||case when fastpath is null then 'NULL' else fastpath::text end||' , VirtualTransaction: '||case when virtualtransaction is null then 'NULL' else virtualtransaction::text end||' , Session_State: '||case when state is null then 'NULL' else state::text end||chr(10)||
'Username: '||case when usename is null then 'NULL' else usename::text end||' , Database: '||case when datname is null then 'NULL' else datname::text end||' , Client_Addr: '||case when client_addr is null then 'NULL' else client_addr::text end||' , Client_Port: '||case when client_port is null then 'NULL' else client_port::text end||' , Application_Name: '||case when application_name is null then 'NULL' else application_name::text end||chr(10)||
'Xact_Start: '||case when xact_start is null then 'NULL' else xact_start::text end||' , Query_Start: '||case when query_start is null then 'NULL' else query_start::text end||' , Xact_Elapse: '||case when (now()-xact_start) is null then 'NULL' else (now()-xact_start)::text end||' , Query_Elapse: '||case when (now()-query_start) is null then 'NULL' else (now()-query_start)::text end||chr(10)||
'SQL (Current SQL in Transaction): '||chr(10)||
case when query is null then 'NULL' else query::text end,
chr(10)||'--------'||chr(10)
order by
( case mode
when 'INVALID' then 0
when 'AccessShareLock' then 1
when 'RowShareLock' then 2
when 'RowExclusiveLock' then 3
when 'ShareUpdateExclusiveLock' then 4
when 'ShareLock' then 5
when 'ShareRowExclusiveLock' then 6
when 'ExclusiveLock' then 7
when 'AccessExclusiveLock' then 8
else 0
end ) desc,
(case when granted then 0 else 1 end)
) as lock_conflict
from t_unionall
group by
locktype,datname,relation,page,tuple,virtualxid,transactionid::text,classid,objid,objsubid ;
create view dba.top10sizetable as
select schemaname,tablename,pg_size_pretty(pg_relation_size((quote_ident(schemaname)||'.'||quote_ident(tablename))::regclass)) from pg_tables order by pg_relation_size((quote_ident(schemaname)||'.'||quote_ident(tablename))::regclass) desc limit 10;
create view dba.top10sizeindex as
select schemaname,tablename,indexname,pg_size_pretty(pg_relation_size((quote_ident(schemaname)||'.'||quote_ident(indexname))::regclass)) from pg_indexes order by pg_relation_size((quote_ident(schemaname)||'.'||quote_ident(indexname))::regclass) desc limit 10;
create view dba.top10sizetableindex as
select schemaname,tablename,pg_size_pretty(pg_total_relation_size((quote_ident(schemaname)||'.'||quote_ident(tablename))::regclass)) from pg_tables order by pg_total_relation_size((quote_ident(schemaname)||'.'||quote_ident(tablename))::regclass) desc limit 10;
create view dba.top10updatetable as
select schemaname,relname,n_tup_upd,n_tup_del,round(n_tup_hot_upd/(case when n_tup_upd=0 then 1.0 else n_tup_upd::numeric end),4) from pg_stat_all_tables order by n_tup_upd+n_tup_del desc limit 10;
create view dba.top10inserttable as
select schemaname,relname,n_tup_ins from pg_stat_all_tables order by n_tup_ins desc limit 10;
create view dba.top10deadtable as
select schemaname,relname,n_dead_tup from pg_stat_all_tables order by n_dead_tup desc limit 10;
create view dba.top10age as
select relnamespace::regnamespace,relname,pg_size_pretty(pg_relation_size(oid)),age(relfrozenxid) from pg_class where relkind='r' and relnamespace<>'pg_catalog'::regnamespace and relnamespace<>'information_schema'::regnamespace order by age(relfrozenxid) desc,pg_relation_size(oid) desc limit 10;
-- 膨脹點查詢
create view dba.oldestxact as
select datname,usename,xact_start,query_start,backend_xid,backend_xmin,
now()-xact_start as old_ts,
txid_current()-least(backend_xid::text::int8,backend_xmin::text::int8) as old_xacts,
query
from pg_stat_activity
where ltrim(lower(query),' ') !~ '^vacuum'
and not (query ~ 'autovacuum' and backend_type <>'client backend')
order by least(backend_xid::text::int8,backend_xmin::text::int8) limit 1;
-- 查詢膨脹空間top 10的表
create view dba.top10bloatsizetable as
SELECT
current_database() AS db, schemaname, tablename, reltuples::bigint AS tups, relpages::bigint AS pages, otta,
ROUND(CASE WHEN otta=0 OR sml.relpages=0 OR sml.relpages=otta THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS tbloat,
CASE WHEN relpages < otta THEN 0 ELSE relpages::bigint - otta END AS wastedpages,
CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END AS wastedbytes,
CASE WHEN relpages < otta THEN '0 bytes'::text ELSE pg_size_pretty((bs*(relpages-otta))::bigint) END AS wastedsize,
iname, ituples::bigint AS itups, ipages::bigint AS ipages, iotta,
ROUND(CASE WHEN iotta=0 OR ipages=0 OR ipages=iotta THEN 0.0 ELSE ipages/iotta::numeric END,1) AS ibloat,
CASE WHEN ipages < iotta THEN 0 ELSE ipages::bigint - iotta END AS wastedipages,
CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes,
CASE WHEN ipages < iotta THEN '0 bytes' ELSE pg_size_pretty((bs*(ipages-iotta))::bigint) END AS wastedisize,
pg_size_pretty(CASE WHEN relpages < otta THEN
CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta::bigint) END
ELSE CASE WHEN ipages < iotta THEN bs*(relpages-otta::bigint)
ELSE bs*(relpages-otta::bigint + ipages-iotta::bigint) END
END) AS totalwastedbytes
FROM (
SELECT
nn.nspname AS schemaname,
cc.relname AS tablename,
COALESCE(cc.reltuples,0) AS reltuples,
COALESCE(cc.relpages,0) AS relpages,
COALESCE(bs,0) AS bs,
COALESCE(CEIL((cc.reltuples*((datahdr+ma-
(CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)),0) AS otta,
COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,
COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols
FROM
pg_class cc
JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname <> 'information_schema'
LEFT JOIN
(
SELECT
ma,bs,foo.nspname,foo.relname,
(datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,
(maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
FROM (
SELECT
ns.nspname, tbl.relname, hdr, ma, bs,
SUM((1-coalesce(null_frac,0))*coalesce(avg_width, 2048)) AS datawidth,
MAX(coalesce(null_frac,0)) AS maxfracsum,
hdr+(
SELECT 1+count(*)/8
FROM pg_stats s2
WHERE null_frac<>0 AND s2.schemaname = ns.nspname AND s2.tablename = tbl.relname
) AS nullhdr
FROM pg_attribute att
JOIN pg_class tbl ON att.attrelid = tbl.oid
JOIN pg_namespace ns ON ns.oid = tbl.relnamespace
LEFT JOIN pg_stats s ON s.schemaname=ns.nspname
AND s.tablename = tbl.relname
AND s.inherited=false
AND s.attname=att.attname,
(
SELECT
(SELECT current_setting('block_size')::numeric) AS bs,
CASE WHEN SUBSTRING(SPLIT_PART(v, ' ', 2) FROM '#"[0-9]+.[0-9]+#"%' for '#')
IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,
CASE WHEN v ~ 'mingw32' OR v ~ '64-bit' THEN 8 ELSE 4 END AS ma
FROM (SELECT version() AS v) AS foo
) AS constants
WHERE att.attnum > 0 AND tbl.relkind='r'
GROUP BY 1,2,3,4,5
) AS foo
) AS rs
ON cc.relname = rs.relname AND nn.nspname = rs.nspname
LEFT JOIN pg_index i ON indrelid = cc.oid
LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
) AS sml order by wastedbytes desc limit 5;
-- 查詢膨脹空間top 10的索引
create view dba.top10bloatsizeindex as
SELECT
current_database() AS db, schemaname, tablename, reltuples::bigint AS tups, relpages::bigint AS pages, otta,
ROUND(CASE WHEN otta=0 OR sml.relpages=0 OR sml.relpages=otta THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS tbloat,
CASE WHEN relpages < otta THEN 0 ELSE relpages::bigint - otta END AS wastedpages,
CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END AS wastedbytes,
CASE WHEN relpages < otta THEN '0 bytes'::text ELSE pg_size_pretty((bs*(relpages-otta))::bigint) END AS wastedsize,
iname, ituples::bigint AS itups, ipages::bigint AS ipages, iotta,
ROUND(CASE WHEN iotta=0 OR ipages=0 OR ipages=iotta THEN 0.0 ELSE ipages/iotta::numeric END,1) AS ibloat,
CASE WHEN ipages < iotta THEN 0 ELSE ipages::bigint - iotta END AS wastedipages,
CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes,
CASE WHEN ipages < iotta THEN '0 bytes' ELSE pg_size_pretty((bs*(ipages-iotta))::bigint) END AS wastedisize,
pg_size_pretty(CASE WHEN relpages < otta THEN
CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta::bigint) END
ELSE CASE WHEN ipages < iotta THEN bs*(relpages-otta::bigint)
ELSE bs*(relpages-otta::bigint + ipages-iotta::bigint) END
END) AS totalwastedbytes
FROM (
SELECT
nn.nspname AS schemaname,
cc.relname AS tablename,
COALESCE(cc.reltuples,0) AS reltuples,
COALESCE(cc.relpages,0) AS relpages,
COALESCE(bs,0) AS bs,
COALESCE(CEIL((cc.reltuples*((datahdr+ma-
(CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)),0) AS otta,
COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,
COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols
FROM
pg_class cc
JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname <> 'information_schema'
LEFT JOIN
(
SELECT
ma,bs,foo.nspname,foo.relname,
(datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,
(maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
FROM (
SELECT
ns.nspname, tbl.relname, hdr, ma, bs,
SUM((1-coalesce(null_frac,0))*coalesce(avg_width, 2048)) AS datawidth,
MAX(coalesce(null_frac,0)) AS maxfracsum,
hdr+(
SELECT 1+count(*)/8
FROM pg_stats s2
WHERE null_frac<>0 AND s2.schemaname = ns.nspname AND s2.tablename = tbl.relname
) AS nullhdr
FROM pg_attribute att
JOIN pg_class tbl ON att.attrelid = tbl.oid
JOIN pg_namespace ns ON ns.oid = tbl.relnamespace
LEFT JOIN pg_stats s ON s.schemaname=ns.nspname
AND s.tablename = tbl.relname
AND s.inherited=false
AND s.attname=att.attname,
(
SELECT
(SELECT current_setting('block_size')::numeric) AS bs,
CASE WHEN SUBSTRING(SPLIT_PART(v, ' ', 2) FROM '#"[0-9]+.[0-9]+#"%' for '#')
IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,
CASE WHEN v ~ 'mingw32' OR v ~ '64-bit' THEN 8 ELSE 4 END AS ma
FROM (SELECT version() AS v) AS foo
) AS constants
WHERE att.attnum > 0 AND tbl.relkind='r'
GROUP BY 1,2,3,4,5
) AS foo
) AS rs
ON cc.relname = rs.relname AND nn.nspname = rs.nspname
LEFT JOIN pg_index i ON indrelid = cc.oid
LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
) AS sml order by wastedibytes desc limit 5;
-- 查詢膨脹比例top 10的表(浪費空間大於10MB的表)
create view dba.top10bloatratiotable as
SELECT
current_database() AS db, schemaname, tablename, reltuples::bigint AS tups, relpages::bigint AS pages, otta,
ROUND(CASE WHEN otta=0 OR sml.relpages=0 OR sml.relpages=otta THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS tbloat,
CASE WHEN relpages < otta THEN 0 ELSE relpages::bigint - otta END AS wastedpages,
CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END AS wastedbytes,
CASE WHEN relpages < otta THEN '0 bytes'::text ELSE pg_size_pretty((bs*(relpages-otta))::bigint) END AS wastedsize,
iname, ituples::bigint AS itups, ipages::bigint AS ipages, iotta,
ROUND(CASE WHEN iotta=0 OR ipages=0 OR ipages=iotta THEN 0.0 ELSE ipages/iotta::numeric END,1) AS ibloat,
CASE WHEN ipages < iotta THEN 0 ELSE ipages::bigint - iotta END AS wastedipages,
CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes,
CASE WHEN ipages < iotta THEN '0 bytes' ELSE pg_size_pretty((bs*(ipages-iotta))::bigint) END AS wastedisize,
pg_size_pretty(CASE WHEN relpages < otta THEN
CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta::bigint) END
ELSE CASE WHEN ipages < iotta THEN bs*(relpages-otta::bigint)
ELSE bs*(relpages-otta::bigint + ipages-iotta::bigint) END
END) AS totalwastedbytes
FROM (
SELECT
nn.nspname AS schemaname,
cc.relname AS tablename,
COALESCE(cc.reltuples,0) AS reltuples,
COALESCE(cc.relpages,0) AS relpages,
COALESCE(bs,0) AS bs,
COALESCE(CEIL((cc.reltuples*((datahdr+ma-
(CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)),0) AS otta,
COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,
COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols
FROM
pg_class cc
JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname <> 'information_schema'
LEFT JOIN
(
SELECT
ma,bs,foo.nspname,foo.relname,
(datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,
(maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
FROM (
SELECT
ns.nspname, tbl.relname, hdr, ma, bs,
SUM((1-coalesce(null_frac,0))*coalesce(avg_width, 2048)) AS datawidth,
MAX(coalesce(null_frac,0)) AS maxfracsum,
hdr+(
SELECT 1+count(*)/8
FROM pg_stats s2
WHERE null_frac<>0 AND s2.schemaname = ns.nspname AND s2.tablename = tbl.relname
) AS nullhdr
FROM pg_attribute att
JOIN pg_class tbl ON att.attrelid = tbl.oid
JOIN pg_namespace ns ON ns.oid = tbl.relnamespace
LEFT JOIN pg_stats s ON s.schemaname=ns.nspname
AND s.tablename = tbl.relname
AND s.inherited=false
AND s.attname=att.attname,
(
SELECT
(SELECT current_setting('block_size')::numeric) AS bs,
CASE WHEN SUBSTRING(SPLIT_PART(v, ' ', 2) FROM '#"[0-9]+.[0-9]+#"%' for '#')
IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,
CASE WHEN v ~ 'mingw32' OR v ~ '64-bit' THEN 8 ELSE 4 END AS ma
FROM (SELECT version() AS v) AS foo
) AS constants
WHERE att.attnum > 0 AND tbl.relkind='r'
GROUP BY 1,2,3,4,5
) AS foo
) AS rs
ON cc.relname = rs.relname AND nn.nspname = rs.nspname
LEFT JOIN pg_index i ON indrelid = cc.oid
LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
) AS sml
where (CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END) >= 10240000
order by tbloat desc,wastedbytes desc limit 5;
-- 查詢膨脹比例top 10的索引(浪費空間大於10MB的索引)
create view dba.top10bloatratioindex as
SELECT
current_database() AS db, schemaname, tablename, reltuples::bigint AS tups, relpages::bigint AS pages, otta,
ROUND(CASE WHEN otta=0 OR sml.relpages=0 OR sml.relpages=otta THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS tbloat,
CASE WHEN relpages < otta THEN 0 ELSE relpages::bigint - otta END AS wastedpages,
CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END AS wastedbytes,
CASE WHEN relpages < otta THEN '0 bytes'::text ELSE pg_size_pretty((bs*(relpages-otta))::bigint) END AS wastedsize,
iname, ituples::bigint AS itups, ipages::bigint AS ipages, iotta,
ROUND(CASE WHEN iotta=0 OR ipages=0 OR ipages=iotta THEN 0.0 ELSE ipages/iotta::numeric END,1) AS ibloat,
CASE WHEN ipages < iotta THEN 0 ELSE ipages::bigint - iotta END AS wastedipages,
CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes,
CASE WHEN ipages < iotta THEN '0 bytes' ELSE pg_size_pretty((bs*(ipages-iotta))::bigint) END AS wastedisize,
pg_size_pretty(CASE WHEN relpages < otta THEN
CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta::bigint) END
ELSE CASE WHEN ipages < iotta THEN bs*(relpages-otta::bigint)
ELSE bs*(relpages-otta::bigint + ipages-iotta::bigint) END
END) AS totalwastedbytes
FROM (
SELECT
nn.nspname AS schemaname,
cc.relname AS tablename,
COALESCE(cc.reltuples,0) AS reltuples,
COALESCE(cc.relpages,0) AS relpages,
COALESCE(bs,0) AS bs,
COALESCE(CEIL((cc.reltuples*((datahdr+ma-
(CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)),0) AS otta,
COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,
COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols
FROM
pg_class cc
JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname <> 'information_schema'
LEFT JOIN
(
SELECT
ma,bs,foo.nspname,foo.relname,
(datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,
(maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
FROM (
SELECT
ns.nspname, tbl.relname, hdr, ma, bs,
SUM((1-coalesce(null_frac,0))*coalesce(avg_width, 2048)) AS datawidth,
MAX(coalesce(null_frac,0)) AS maxfracsum,
hdr+(
SELECT 1+count(*)/8
FROM pg_stats s2
WHERE null_frac<>0 AND s2.schemaname = ns.nspname AND s2.tablename = tbl.relname
) AS nullhdr
FROM pg_attribute att
JOIN pg_class tbl ON att.attrelid = tbl.oid
JOIN pg_namespace ns ON ns.oid = tbl.relnamespace
LEFT JOIN pg_stats s ON s.schemaname=ns.nspname
AND s.tablename = tbl.relname
AND s.inherited=false
AND s.attname=att.attname,
(
SELECT
(SELECT current_setting('block_size')::numeric) AS bs,
CASE WHEN SUBSTRING(SPLIT_PART(v, ' ', 2) FROM '#"[0-9]+.[0-9]+#"%' for '#')
IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,
CASE WHEN v ~ 'mingw32' OR v ~ '64-bit' THEN 8 ELSE 4 END AS ma
FROM (SELECT version() AS v) AS foo
) AS constants
WHERE att.attnum > 0 AND tbl.relkind='r'
GROUP BY 1,2,3,4,5
) AS foo
) AS rs
ON cc.relname = rs.relname AND nn.nspname = rs.nspname
LEFT JOIN pg_index i ON indrelid = cc.oid
LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
) AS sml
where (CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END) >= 10240000
order by ibloat desc,wastedibytes desc limit 5;
create view dba.seqs as select max_value-last_value,* from pg_sequences order by max_value-last_value ;
-- 查詢沒有使用過的大於1MB的索引 top 10 (注意, PK、UK如果只是用於約束, 可能不會被統計計數,但是不能刪掉)
create view dba.top10notusedidx as
select pg_size_pretty(pg_relation_size(indexrelid)),* from pg_stat_all_indexes where pg_relation_size(indexrelid)>=1024000 and (idx_scan=0 or idx_tup_read=0 or idx_tup_fetch=0)
and schemaname not in ('pg_toast','pg_catalog') order by pg_relation_size(indexrelid) desc limit 10;
-- 查詢沒有使用過的大於1MB的表 top 10
create view dba.top10notusedtab as
select pg_size_pretty(pg_relation_size(relid)),* from pg_stat_all_tables
where pg_relation_size(relid)>=1024000 and seq_scan=0 and idx_scan=0 and schemaname not in ('pg_toast','pg_catalog','information_schema') order by pg_relation_size(relid) desc limit 10;
-- 查詢熱表top 10
create view dba.top10hottab as
select pg_size_pretty(pg_relation_size(relid)),* from pg_stat_all_tables where schemaname not in ('pg_toast','pg_catalog','information_schema') order by seq_scan+idx_scan desc, pg_relation_size(relid) desc limit 10;
-- 查詢大於1MB的冷表top 10
create view dba.top10coldtab as
select pg_size_pretty(pg_relation_size(relid)),* from pg_stat_all_tables where schemaname not in ('pg_toast','pg_catalog','information_schema') and pg_relation_size(relid)>=1024000 order by seq_scan+idx_scan,pg_relation_size(relid) desc limit 10;
-- 查詢熱索引top 10
create view dba.top10hotidx as
select pg_size_pretty(pg_relation_size(indexrelid)),* from pg_stat_all_indexes where
schemaname not in ('pg_toast','pg_catalog') order by idx_scan+idx_tup_read+idx_tup_fetch desc, pg_relation_size(indexrelid) desc limit 10;
-- 查詢大於1MB的冷索引top 10(注意, PK、UK如果只是用於約束, 可能不會被統計計數,但是不能刪掉)
create view dba.top10coldidx as
select pg_size_pretty(pg_relation_size(indexrelid)),* from pg_stat_all_indexes where pg_relation_size(indexrelid)>=1024000
and schemaname not in ('pg_toast','pg_catalog') order by idx_scan+idx_tup_read+idx_tup_fetch , pg_relation_size(indexrelid) desc limit 10;
-- freeze風暴預測相關的3個檢視
create view dba.v_freeze as
select
e.*,
a.*
from
(select
current_setting('autovacuum_freeze_max_age')::int as v1, -- 如果表的事務ID年齡大於該值, 即使未開啟autovacuum也會強制觸發FREEZE, 並告警Preventing Transaction ID Wraparound Failures
current_setting('autovacuum_multixact_freeze_max_age')::int as v2, -- 如果表的並行事務ID年齡大於該值, 即使未開啟autovacuum也會強制觸發FREEZE, 並告警Preventing Transaction ID Wraparound Failures
current_setting('vacuum_freeze_min_age')::int as v3, -- 手動或自動垃圾回收時, 如果記錄的事務ID年齡大於該值, 將被FREEZE
current_setting('vacuum_multixact_freeze_min_age')::int as v4, -- 手動或自動垃圾回收時, 如果記錄的並行事務ID年齡大於該值, 將被FREEZE
current_setting('vacuum_freeze_table_age')::int as v5, -- 手動垃圾回收時, 如果表的事務ID年齡大於該值, 將觸發FREEZE. 該引數的上限值為 %95 autovacuum_freeze_max_age
current_setting('vacuum_multixact_freeze_table_age')::int as v6, -- 手動垃圾回收時, 如果表的並行事務ID年齡大於該值, 將觸發FREEZE. 該引數的上限值為 %95 autovacuum_multixact_freeze_max_age
current_setting('autovacuum_vacuum_cost_delay') as v7, -- 自動垃圾回收時, 每輪迴收週期後的一個休息時間, 主要防止垃圾回收太耗資源. -1 表示沿用vacuum_cost_delay的設定
current_setting('autovacuum_vacuum_cost_limit') as v8, -- 自動垃圾回收時, 每輪迴收週期設多大限制, 限制由vacuum_cost_page_hit,vacuum_cost_page_missvacuum_cost_page_dirty引數以及週期內的操作決定. -1 表示沿用vacuum_cost_limit的設定
current_setting('vacuum_cost_delay') as v9, -- 手動垃圾回收時, 每輪迴收週期後的一個休息時間, 主要防止垃圾回收太耗資源.
current_setting('vacuum_cost_limit') as v10, -- 手動垃圾回收時, 每輪迴收週期設多大限制, 限制由vacuum_cost_page_hit,vacuum_cost_page_missvacuum_cost_page_dirty引數以及週期內的操作決定.
current_setting('autovacuum') as autovacuum -- 是否開啟自動垃圾回收
) a,
LATERAL ( -- LATERAL 允許你在這個SUBQUERY中直接參照前面的table, subquery中的column
select
pg_size_pretty(pg_total_relation_size(oid)) sz, -- 表的大小(含TOAST, 索引)
oid::regclass as reloid, -- 表名(物化檢視)
relkind, -- r=表, m=物化檢視
coalesce(
least(
substring(reloptions::text, 'autovacuum_freeze_max_age=(d+)')::int,
substring(reloptions::text, 'autovacuum_freeze_table_age=(d+)')::int
),
a.v1
)
-
age(case when relfrozenxid::text::int<3 then null else relfrozenxid end)
as remain_ages_xid, -- 再產生多少個事務後, 自動垃圾回收會觸發FREEZE, 起因為事務ID
coalesce(
least(
substring(reloptions::text, 'autovacuum_multixact_freeze_max_age=(d+)')::int,
substring(reloptions::text, 'autovacuum_multixact_freeze_table_age=(d+)')::int
),
a.v2
)
-
age(case when relminmxid::text::int<3 then null else relminmxid end)
as remain_ages_mxid, -- 再產生多少個事務後, 自動垃圾回收會觸發FREEZE, 起因為並行事務ID
coalesce(
least(
substring(reloptions::text, 'autovacuum_freeze_min_age=(d+)')::int
),
a.v3
) as xid_lower_to_minage, -- 如果觸發FREEZE, 該表的事務ID年齡會降到多少
coalesce(
least(
substring(reloptions::text, 'autovacuum_multixact_freeze_min_age=(d+)')::int
),
a.v4
) as mxid_lower_to_minage, -- 如果觸發FREEZE, 該表的並行事務ID年齡會降到多少
case
when v5 <= age(case when relfrozenxid::text::int<3 then null else relfrozenxid end) then 'YES'
else 'NOT'
end as vacuum_trigger_freeze1, -- 如果手工執行VACUUM, 是否會觸發FREEZE, 觸發起因(事務ID年齡達到閾值)
case
when v6 <= age(case when relminmxid::text::int<3 then null else relminmxid end) then 'YES'
else 'NOT'
end as vacuum_trigger_freeze2, -- 如果手工執行VACUUM, 是否會觸發FREEZE, 觸發起因(並行事務ID年齡達到閾值)
reloptions -- 表級引數, 優先. 例如是否開啟自動垃圾回收, autovacuum_freeze_max_age, autovacuum_freeze_table_age, autovacuum_multixact_freeze_max_age, autovacuum_multixact_freeze_table_age
from pg_class
where relkind in ('r','m')
) e
order by
least(e.remain_ages_xid , e.remain_ages_mxid), -- 排在越前, 越先觸發自動FREEZE, 即風暴來臨的預測
pg_total_relation_size(reloid) desc -- 同樣剩餘年齡, 表越大, 排越前
;
create view dba.v_freeze_stat as
select
wb, -- 第幾個BATCH, 每個batch代表流逝100萬個事務
cnt, -- 這個batch 有多少表
pg_size_pretty(ssz) as ssz1, -- 這個batch 這些 表+TOAST+索引 有多少容量
pg_size_pretty(ssz) as ssz2, -- 這個batch FREEZE 會導致多少讀IO
pg_size_pretty(ssz*3) as ssz3, -- 這個batch FREEZE 最多可能會導致多少寫IO (通常三份 : 資料檔案, WAL FULL PAGE, WAL)
pg_size_pretty(min_sz) as ssz4, -- 這個batch 最小的表多大
pg_size_pretty(max_sz) as ssz5, -- 這個batch 最大的表多大
pg_size_pretty(avg_sz) as ssz6, -- 這個batch 平均表多大
pg_size_pretty(stddev_sz) as ssz7, -- 這個batch 表大小的方差, 越大, 說明表大小差異化明顯
min_rest_age, -- 這個batch 距離自動FREEZE最低剩餘事務數
max_rest_age, -- 這個batch 距離自動FREEZE最高剩餘事務數
stddev_rest_age, -- 這個batch 距離自動FREEZE剩餘事務數的方差, 越小，說明這個batch觸發freeze將越平緩, 越大, 說明這個batch將有可能在某些點集中觸發freeze (但是可能集中觸發的都是小表)
corr_rest_age_sz, -- 表大小與距離自動freeze剩餘事務數的相關性，相關性越強(值趨向1或-1) stddev_rest_age 與 sz7 說明的問題越有價值
round(100*(ssz/(sum(ssz) over ())), 2)||' %' as ratio -- 這個BATCH的容量佔比，佔比如果非常不均勻，說明有必要調整表級FREEZE引數，讓佔比均勻化
from
(
select a.*, b.* from
(
select
min(least(remain_ages_xid, remain_ages_mxid)) as v_min, -- 整個資料庫中離自動FREEZE的 最小 剩餘事務ID數
max(least(remain_ages_xid, remain_ages_mxid)) as v_max -- 整個資料庫中離自動FREEZE的 最大 剩餘事務ID數
from v_freeze
) as a,
LATERAL ( -- 高階SQL
select
width_bucket(
least(remain_ages_xid, remain_ages_mxid),
a.v_min,
a.v_max,
greatest((a.v_max-a.v_min)/1000000, 1) -- 100萬個事務, 如果要更改統計例如，修改這個值即可
) as wb,
count(*) as cnt,
sum(pg_total_relation_size(reloid)) as ssz,
stddev_samp(pg_total_relation_size(reloid) order by least(remain_ages_xid, remain_ages_mxid)) as stddev_sz,
min(pg_total_relation_size(reloid)) as min_sz,
max(pg_total_relation_size(reloid)) as max_sz,
avg(pg_total_relation_size(reloid)) as avg_sz,
min(least(remain_ages_xid, remain_ages_mxid)) as min_rest_age,
max(least(remain_ages_xid, remain_ages_mxid)) as max_rest_age,
stddev_samp(least(remain_ages_xid, remain_ages_mxid) order by least(remain_ages_xid, remain_ages_mxid)) as stddev_rest_age,
corr(least(remain_ages_xid, remain_ages_mxid), pg_total_relation_size(reloid)) as corr_rest_age_sz
from v_freeze
group by wb
) as b
) t
order by wb;
create view dba.v_freeze_stat_detail as
select
pg_size_pretty(t.ssz) as ssz2, -- 這個batch FREEZE 會導致多少讀IO (表+TOAST+索引)
pg_size_pretty(t.ssz*3) as ssz3, -- 這個batch FREEZE 最多可能會導致多少寫IO (通常三份 : 資料檔案, WAL FULL PAGE, WAL)
pg_size_pretty(t.ssz_sum) as ssz4, -- 所有batch 所有表的總大小 (表+TOAST+索引)
round(100*(t.ssz/t.ssz_sum), 2)||' %' as ratio_batch, -- 這個BATCH的容量佔比，目標是讓所有BATCH佔比儘量一致
round(100*(pg_total_relation_size(t.reloid)/t.ssz), 2)||' %' as ratio_table, -- 這個表佔整個batch的容量佔比，大表儘量錯開freeze
t.*
from
(
select a.*, b.* from
(
select
min(least(remain_ages_xid, remain_ages_mxid)) as v_min, -- 整個資料庫中離自動FREEZE的 最小 剩餘事務ID數
max(least(remain_ages_xid, remain_ages_mxid)) as v_max -- 整個資料庫中離自動FREEZE的 最大 剩餘事務ID數
from v_freeze
) as a,
LATERAL ( -- 高階SQL
select
count(*) over w as cnt, -- 這個batch 有多少表
sum(pg_total_relation_size(reloid)) over () as ssz_sum, -- 所有batch 所有表的總大小 (表+TOAST+索引)
sum(pg_total_relation_size(reloid)) over w as ssz, -- 這個batch 的表大小總和 (表+TOAST+索引)
pg_size_pretty(min(pg_total_relation_size(reloid)) over w) as min_sz, -- 這個batch 最小的表多大
pg_size_pretty(max(pg_total_relation_size(reloid)) over w) as max_sz, -- 這個batch 最大的表多大
pg_size_pretty(avg(pg_total_relation_size(reloid)) over w) as avg_sz, -- 這個batch 平均表多大
pg_size_pretty(stddev_samp(pg_total_relation_size(reloid)) over w) as stddev_sz, -- 這個batch 表大小的方差, 越大, 說明表大小差異化明顯
min(least(remain_ages_xid, remain_ages_mxid)) over w as min_rest_age, -- 這個batch 距離自動FREEZE最低剩餘事務數
max(least(remain_ages_xid, remain_ages_mxid)) over w as max_rest_age, -- 這個batch 距離自動FREEZE最高剩餘事務數
stddev_samp(least(remain_ages_xid, remain_ages_mxid)) over w as stddev_rest_age, -- 這個batch 距離自動FREEZE剩餘事務數的方差, 越小，說明這個batch觸發freeze將越平緩, 越大, 說明這個batch將有可能在某些點集中觸發freeze (但是可能集中觸發的都是小表)
corr(least(remain_ages_xid, remain_ages_mxid), pg_total_relation_size(reloid)) over w as corr_rest_age_sz, -- 表大小與距離自動freeze剩餘事務數的相關性，相關性越強(值趨向1或-1) stddev_rest_age 與 stddev_sz 說明的問題越有價值
t1.*
from
(
select
width_bucket(
least(tt.remain_ages_xid, tt.remain_ages_mxid),
a.v_min,
a.v_max,
greatest((a.v_max-a.v_min)/1000000, 1) -- 100萬個事務, 如果要更改統計例如，修改這個值即可
)
as wb, -- 第幾個BATCH, 每個batch代表流逝100萬個事務
* from v_freeze tt
) as t1
window w as
(
partition by t1.wb
)
) as b
) t
order by
t.wb,
least(t.remain_ages_xid, t.remain_ages_mxid),
pg_total_relation_size(t.reloid) desc
;
create view dba.top20freezebigtable as
select relowner::regrole, relnamespace::regnamespace, relname,
age(relfrozenxid),pg_size_pretty(pg_total_relation_size(oid)) , -- 當前年齡
coalesce(
least(
substring(reloptions::text, 'autovacuum_freeze_max_age=(d+)')::int,
substring(reloptions::text, 'autovacuum_freeze_table_age=(d+)')::int
),
current_setting('autovacuum_freeze_max_age')::int
)
-
age(case when relfrozenxid::text::int<3 then null else relfrozenxid end)
as remain_ages_xid, -- 再產生多少個事務後, 自動垃圾回收會觸發FREEZE, 起因為事務ID
coalesce(
least(
substring(reloptions::text, 'autovacuum_freeze_min_age=(d+)')::int
),
current_setting('vacuum_freeze_min_age')::int
) as xid_lower_to_minage -- 如果觸發FREEZE, 該表的事務ID年齡會降到多少
from pg_class where relkind='r' order by pg_total_relation_size(oid) desc limit 20;
-- 未歸檔wal檔案
create view dba.arch_undone as
select * from pg_ls_archive_statusdir() where name !~ 'done$';
-- 歸檔任務狀態
create view dba.arch_status as
select * from pg_stat_get_archiver();
-- wal空間佔用
create view dba.walsize as
select pg_size_pretty(sum(size)) from pg_ls_waldir();
-- 複製槽狀態(是否有未使用複製槽, 可能導致wal紀錄檔目錄暴漲(不清理))
create view dba.repslots as
select * from pg_replication_slots ;
-- 系統強制保留wal大小
create view dba.wal_keep_size as
with a as (select setting from pg_settings where name='wal_keep_segments') , b as (select setting,unit from pg_settings where name='wal_segment_size') select pg_size_pretty(a.setting::int8*b.setting::int8) from a,b;
-- 系統動態檢查點最大wal保留大小
create view dba.max_wal_size as
select setting||' '||unit from pg_settings where name='max_wal_size';
-- 長事務、prepared statement
create view dba.long_snapshot as
with a as (select min(transaction::Text::int8) m from pg_prepared_xacts ),
b as (select txid_snapshot_xmin(txid_current_snapshot())::text::int8 as m),
c as (select min(least(backend_xid::text::int8,backend_xmin::text::int8)) m from pg_stat_activity ),
d as (select datname,usename,pid,query_start,xact_start,now(),wait_event,query from pg_stat_activity where backend_xid is not null or backend_xmin is not null
order by least(backend_xid::text::int8,backend_xmin::text::int8) limit 1),
e as (select * from pg_prepared_xacts order by transaction::Text::int8 limit 1)
select b.m-least(a.m,c.m),d.*,e.* from a,b,c,d left join e on (1=1);常用查詢
1、查詢唯讀節點延遲
-- 在主節點查詢 select * from dba.ro_delay; -- 在唯讀節點查詢 set lock_timeout='10ms'; set statement_timeout='2s'; select * from dba.node_delay; select * from dba.ro_delay_on_standby;
2、查詢top query, 優化之首
select * from dba.topsql;
3、重置top query統計計數器(通常在高峰期來臨前可以重置,防止結果乾擾)
select pg_stat_statements_reset();
4、查詢 qps , 在psql 終端可以每秒列印一次
select * from dba.qps; watch 1
4.1、查詢tps
call dba.tps();
5、查詢活躍對談數, 如果超過CPU核數, 說明資料庫非常非常繁忙, 需要注意優化
select * from dba.session_acting_cnt;
6、當前活躍對談
select * from dba.sessions;
7、查詢鎖等待, 如果有大量長時間等待, 需要注意業務邏輯是否有問題
select * from dba.locks;
8、查詢佔用空間top 10的表
select * from dba.top10sizetable;
9、查詢佔用空間top 10的索引
select * from dba.top10sizeindex;
10、查詢佔用空間top 10的表(含索引)
select * from dba.top10sizetableindex;
11、查詢膨脹空間top 10的表
select * from dba.top10bloatsizetable;
12、查詢膨脹空間top 10的索引
select * from dba.top10bloatsizeindex;
13、查詢膨脹比例top 10的表
select * from dba.top10bloatratiotable;
14、查詢膨脹比例top 10的索引
select * from dba.top10bloatratioindex;
15、查詢更新和刪除記錄條數top 10的表
select * from dba.top10updatetable;
16、查詢插入記錄條數top 10的表
select * from dba.top10inserttable;
17、查詢髒記錄條數top 10的表
select * from dba.top10deadtable;
18、查詢年齡top 10的表
select * from dba.top10age;
19、查詢當前的最老事務距離當前時間、距離當前事務數, 說明膨脹空間大小, 越大可能導致越多膨脹垃圾.
select * from dba.oldestxact; select * from pg_prepared_xacts;
20、查詢序列的剩餘空間
select * from dba.seqs;
21、PostgreSQL 誰堵塞了誰（鎖等待檢測）- pg_blocking_pids
《PostgreSQL 誰堵塞了誰（鎖等待檢測）- pg_blocking_pids》
22、查詢沒有使用過的大於1MB的索引 top 10 (注意, PK、UK如果只是用於約束, 可能不會被統計計數,但是不能刪掉)
select * from dba.top10notusedidx;
23、查詢沒有使用過的大於1MB的表 top 10
select * from dba.top10notusedtab;
24、查詢熱表top 10
select * from dba.top10hottab;
25、查詢大於1MB的冷表top 10
select * from dba.top10coldtab;
26、查詢熱索引top 10
select * from dba.top10hotidx;
27、查詢大於1MB的冷索引top 10(注意, PK、UK如果只是用於約束, 可能不會被統計計數,但是不能刪掉)
select * from dba.top10coldidx;
28、查詢資料庫freeze風暴預測
select * from dba.v_freeze; select * from dba.v_freeze_stat; select * from dba.v_freeze_stat_detail;
查詢top 20的大表大freeze剩餘年齡。
select * from dba.top20freezebigtable; -- 結合dba.tps, 可以通過remain_ages_xid/dba.tps估算每個表還有多久會發生freeze. call dba.tps();
29、查詢RO節點讀與replay衝突次數， 建議高頻恢復中的ro節點不要跑長sql。
select * from dba.ro_conflicts;
30、DBA在RO 節點人為執行SQL前， 建議設定sql超時， 避免長時間跑 SQL, 導致不必要的replay延遲和 conflict cancel statement
set statement_timeout ='1s'; set lock_timeout='10ms';
31、RO 節點的conflict容忍時間最長設定， 預設為5 min
show max_standby_streaming_delay ; max_standby_streaming_delay ----------------------------- 5min (1 row)
32、清理資料庫stat計數器
df *.*reset*
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+----------------------------------------+--------------------------+---------------------+------
pg_catalog | pg_replication_origin_session_reset | void | | func
pg_catalog | pg_replication_origin_xact_reset | void | | func
pg_catalog | pg_stat_get_bgwriter_stat_reset_time | timestamp with time zone | | func
pg_catalog | pg_stat_get_db_stat_reset_time | timestamp with time zone | oid | func
pg_catalog | pg_stat_reset | void | | func
pg_catalog | pg_stat_reset_shared | void | text | func
pg_catalog | pg_stat_reset_single_function_counters | void | oid | func
pg_catalog | pg_stat_reset_single_table_counters | void | oid | func
public | pg_stat_statements_reset | void | | func
(9 rows)33、在standby節點執行， 檢查當前standby節點接收wal的速度
call dba.wal_receive_bw();
34、在standby節點執行， 檢查當前standby節點replay wal的速度
call dba.wal_replay_bw();
35、wal檔案使用、slot風險檢視。
select * from dba.arch_undone; select * from dba.arch_status; select * from dba.walsize; select * from dba.repslots; select * from dba.wal_keep_size; select * from dba.max_wal_size;
36、長事務、prepared statement
select * from dba.long_snapshot;
37、查詢失效的索引。
select * from dba.invalid_index;
參考
《PostgreSQL 實時健康監控 大屏 - 低頻指標 - 珍藏級》
《PostgreSQL 實時健康監控 大屏 - 高頻指標(伺服器) - 珍藏級》
《PostgreSQL 實時健康監控 大屏 - 高頻指標 - 珍藏級》
《PostgreSQL Freeze 風暴預測續 - 珍藏級SQL》
到此這篇關於PostgreSQL DBA最常用SQL的文章就介紹到這了,更多相關PostgreSQL DBA常用SQL內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！
相關文章
-
黑科技的輕簡出行，告別「電量焦慮症」：Anker氮化鎵超能充系列
综合看Anker超能充系列的性价比很高，并且与不仅和iPhone12/苹果<em>Mac</em>Book很配，而且适合多设备充电需求的日常使用或差旅场景，不管是安卓还是Switch同样也能用得上它，希望这次分享能给准备购入充电器的小伙伴们有所
2021-06-01 09:31:42
-
吳亦凡廠牌首秀，L4WUDU居然忘詞了，Rapper和明星比還是有差距！
除了L4WUDU与吴亦凡已经多次共事，成为了明面上的厂牌成员，吴亦凡还曾带领20XXCLUB全队参加2020年的一场音乐节，这也是20XXCLUB首次全员合照，王嗣尧Turbo、陈彦希Regi、<em>Mac</em> Ova Seas、林渝植等人全部出场。然而让
2021-06-01 09:31:34
-
IPFS、Chia、Bzz和ICP挖礦該怎麼選？哪一個更有優勢？
目前应用IPFS的机构：1 谷歌<em>浏览器</em>支持IPFS分布式协议 2 万维网 (历史档案博物馆)数据库 3 火狐<em>浏览器</em>支持 IPFS分布式协议 4 EOS 等数字货币数据存储 5 美国国会图书馆，历史资料永久保存在 IPFS 6 加
2021-06-01 09:31:24
-
有哪些事是買了雪佛蘭後才知道的？美事偷著樂，開拓者車主隨聊
开拓者的车机是兼容苹果和<em>安卓</em>，虽然我不怎么用，但确实兼顾了我家人的很多需求：副驾的门板还配有解锁开关，有的时候老婆开车，下车的时候偶尔会忘记解锁，我在副驾驶可以自己开门：第二排设计很好，不仅配置了一个很大的
2021-06-01 09:30:48
-
iPhone12在618最新定價，跌價幅度超過1400元，還等iPhone13嗎？
不仅是<em>安卓</em>手机，苹果手机的降价力度也是前所未有了，iPhone12也“跳水价”了，发布价是6799元，如今已经跌至5308元，降价幅度超过1400元，最新定价确认了。iPhone12是苹果首款5G手机，同时也是全球首款5nm芯片的智能机，它
2021-06-01 09:30:45