Create relevant views according to the data dictionary to facilitate query
create schema dba;
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 ;
-- Query in master node
create view dba.ro_delay as select application_name,client_addr,client_port,write_lag,replay_lag,sync_state from pg_stat_replication ;
-- stay standby Node execution, check replay than receive Delay of
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);
-- stay standby Node execution, receive wal Speed.
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$;
-- stay standby Node execution, replay wal Speed.
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
order by least(backend_xid::text::int8,backend_xmin::text::int8) limit 1;
-- Query expansion space top 10 Table of
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;
-- Query expansion space top 10 Index of
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;
-- Query expansion ratio top 10 Table of(Waste space greater than 10 MB Table of)
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;
-- Query expansion ratio top 10 Index of(Waste space greater than 10 MB Index of)
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 ;
-- The query has not used more than 1 MB Index of top 10 (be careful, PK,UK If only for constraints, May not be counted,But you can't delete it)
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;
-- The query has not used more than 1 MB Table of 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;
-- Query heat table 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;
-- Query greater than 1 MB Cold meter 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;
-- Query hot index 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;
-- Query greater than 1 MB Cold index of top 10(be careful, PK,UK If only for constraints, May not be counted,But you can't delete it)
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 views related to Storm Prediction
create view dba.v_freeze as
select
e.*,
a.*
from
(select
current_setting('autovacuum_freeze_max_age')::int as v1, -- If the table's transactions ID Age greater than this value, Even if it is not turned on autovacuum It will also be triggered forcibly FREEZE, And alarm Preventing Transaction ID Wraparound Failures
current_setting('autovacuum_multixact_freeze_max_age')::int as v2, -- If the table has parallel transactions ID Age greater than this value, Even if it is not turned on autovacuum It will also be triggered forcibly FREEZE, And alarm Preventing Transaction ID Wraparound Failures
current_setting('vacuum_freeze_min_age')::int as v3, -- Manual or automatic garbage collection, If logged transactions ID Age greater than this value, Will be FREEZE
current_setting('vacuum_multixact_freeze_min_age')::int as v4, -- Manual or automatic garbage collection, If parallel transactions are logged ID Age greater than this value, Will be FREEZE
current_setting('vacuum_freeze_table_age')::int as v5, -- During manual garbage collection, If the table's transactions ID Age greater than this value, Will trigger FREEZE. The upper limit of this parameter is %95 autovacuum_freeze_max_age
current_setting('vacuum_multixact_freeze_table_age')::int as v6, -- During manual garbage collection, If the table has parallel transactions ID Age greater than this value, Will trigger FREEZE. The upper limit of this parameter is %95 autovacuum_multixact_freeze_max_age
current_setting('autovacuum_vacuum_cost_delay') as v7, -- Automatic garbage collection, One rest time after each recovery cycle, It mainly prevents waste recycling from consuming too much resources. -1 Indicates use vacuum_cost_delay Settings for
current_setting('autovacuum_vacuum_cost_limit') as v8, -- Automatic garbage collection, What are the limits for each recovery cycle, Restricted by vacuum_cost_page_hit,vacuum_cost_page_missvacuum_cost_page_dirty Parameters and operation decisions in the cycle. -1 Indicates use vacuum_cost_limit Settings for
current_setting('vacuum_cost_delay') as v9, -- During manual garbage collection, One rest time after each recovery cycle, It mainly prevents waste recycling from consuming too much resources.
current_setting('vacuum_cost_limit') as v10, -- During manual garbage collection, What are the limits for each recovery cycle, Restricted by vacuum_cost_page_hit,vacuum_cost_page_missvacuum_cost_page_dirty Parameters and operation decisions in the cycle.
current_setting('autovacuum') as autovacuum -- Turn on automatic garbage collection
) a,
LATERAL ( -- LATERAL Allow you to be here SUBQUERY Directly reference the previous table, subquery Medium column
select
pg_size_pretty(pg_total_relation_size(oid)) sz, -- Table size(contain TOAST, Indexes)
oid::regclass as reloid, -- Table name(Materialized view)
relkind, -- r=surface, m=Materialized view
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, -- How many more transactions are generated, Automatic garbage collection is triggered FREEZE, Cause transaction 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, -- How many more transactions are generated, Automatic garbage collection is triggered FREEZE, Because of concurrent transactions ID
coalesce(
least(
substring(reloptions::text, 'autovacuum_freeze_min_age=(\d+)')::int
),
a.v3
) as xid_lower_to_minage, -- If triggered FREEZE, Transactions for this table ID How old will it fall
coalesce(
least(
substring(reloptions::text, 'autovacuum_multixact_freeze_min_age=(\d+)')::int
),
a.v4
) as mxid_lower_to_minage, -- If triggered FREEZE, Parallel transactions for this table ID How old will it fall
case
when v5 <= age(case when relfrozenxid::text::int<3 then null else relfrozenxid end) then 'YES'
else 'NOT'
end as vacuum_trigger_freeze1, -- If executed manually VACUUM, Will it trigger FREEZE, Trigger cause(affair ID Age reaches threshold)
case
when v6 <= age(case when relminmxid::text::int<3 then null else relminmxid end) then 'YES'
else 'NOT'
end as vacuum_trigger_freeze2, -- If executed manually VACUUM, Will it trigger FREEZE, Trigger cause(Parallel transaction ID Age reaches threshold)
reloptions -- Table level parameters, first. For example, whether to turn on automatic garbage collection, 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), -- Ahead of Vietnam, The earlier the trigger, the more automatic FREEZE, That is, the prediction of the coming storm
pg_total_relation_size(reloid) desc -- Same remaining age, The larger the table, Row ahead
;
create view dba.v_freeze_stat as
select
wb, -- Number BATCH, each batch Represents 1 million transactions
cnt, -- this batch How many watches are there
pg_size_pretty(ssz) as ssz1, -- this batch These tables+TOAST+How much capacity does the index have
pg_size_pretty(ssz) as ssz2, -- this batch FREEZE How many reads will it cause IO
pg_size_pretty(ssz*3) as ssz3, -- this batch FREEZE How many writes are possible IO (Usually three copies : data file, WAL FULL PAGE, WAL)
pg_size_pretty(min_sz) as ssz4, -- this batch How big is the smallest table
pg_size_pretty(max_sz) as ssz5, -- this batch How big is the largest table
pg_size_pretty(avg_sz) as ssz6, -- this batch How big is the average table
pg_size_pretty(stddev_sz) as ssz7, -- this batch Variance of table size, The bigger, It shows that the table size is significantly different
min_rest_age, -- this batch Distance automatic FREEZE Minimum number of transactions remaining
max_rest_age, -- this batch Distance automatic FREEZE Maximum number of remaining transactions
stddev_rest_age, -- this batch Distance automatic FREEZE Variance of the number of remaining transactions, The smaller, the better batch trigger freeze The smoother it will be, The bigger, Explain this batch It will be possible to trigger at some point set freeze (However, small tables may be triggered centrally)
corr_rest_age_sz, -- Table size and distance Auto freeze The correlation of the number of remaining transactions, the stronger the correlation(Value tends to 1 or-1) stddev_rest_age And sz7 The more valuable the problem is
round(100*(ssz/(sum(ssz) over ())), 2)||' %' as ratio -- this BATCH If the ratio is very uneven, it indicates that it is necessary to adjust the meter level FREEZE Parameters to homogenize the proportion
from
(
select a.*, b.* from
(
select
min(least(remain_ages_xid, remain_ages_mxid)) as v_min, -- Automatic in the whole database FREEZE Minimum remaining transactions ID number
max(least(remain_ages_xid, remain_ages_mxid)) as v_max -- Automatic in the whole database FREEZE Maximum remaining transactions ID number
from v_freeze
) as a,
LATERAL ( -- senior 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 10000 transactions, If you want to change statistics, for example, modify this value
) 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, -- this batch FREEZE How many reads will it cause IO (surface+TOAST+Indexes)
pg_size_pretty(t.ssz*3) as ssz3, -- this batch FREEZE How many writes are possible IO (Usually three copies : data file, WAL FULL PAGE, WAL)
pg_size_pretty(t.ssz_sum) as ssz4, -- All batch Total size of all tables (surface+TOAST+Indexes)
round(100*(t.ssz/t.ssz_sum), 2)||' %' as ratio_batch, -- this BATCH The goal is to make all BATCH The proportion shall be consistent as far as possible
round(100*(pg_total_relation_size(t.reloid)/t.ssz), 2)||' %' as ratio_table, -- This watch takes up the whole batch The capacity proportion of large meters shall be staggered as far as possible freeze
t.*
from
(
select a.*, b.* from
(
select
min(least(remain_ages_xid, remain_ages_mxid)) as v_min, -- Automatic in the whole database FREEZE Minimum remaining transactions ID number
max(least(remain_ages_xid, remain_ages_mxid)) as v_max -- Automatic in the whole database FREEZE Maximum remaining transactions ID number
from v_freeze
) as a,
LATERAL ( -- senior SQL
select
count(*) over w as cnt, -- this batch How many watches are there
sum(pg_total_relation_size(reloid)) over () as ssz_sum, -- All batch Total size of all tables (surface+TOAST+Indexes)
sum(pg_total_relation_size(reloid)) over w as ssz, -- this batch Total table size (surface+TOAST+Indexes)
pg_size_pretty(min(pg_total_relation_size(reloid)) over w) as min_sz, -- this batch How big is the smallest table
pg_size_pretty(max(pg_total_relation_size(reloid)) over w) as max_sz, -- this batch How big is the largest table
pg_size_pretty(avg(pg_total_relation_size(reloid)) over w) as avg_sz, -- this batch How big is the average table
pg_size_pretty(stddev_samp(pg_total_relation_size(reloid)) over w) as stddev_sz, -- this batch Variance of table size, The bigger, It shows that the table size is significantly different
min(least(remain_ages_xid, remain_ages_mxid)) over w as min_rest_age, -- this batch Distance automatic FREEZE Minimum number of transactions remaining
max(least(remain_ages_xid, remain_ages_mxid)) over w as max_rest_age, -- this batch Distance automatic FREEZE Maximum number of remaining transactions
stddev_samp(least(remain_ages_xid, remain_ages_mxid)) over w as stddev_rest_age, -- this batch Distance automatic FREEZE Variance of the number of remaining transactions, The smaller, the better batch trigger freeze The smoother it will be, The bigger, Explain this batch It will be possible to trigger at some point set freeze (However, small tables may be triggered centrally)
corr(least(remain_ages_xid, remain_ages_mxid), pg_total_relation_size(reloid)) over w as corr_rest_age_sz, -- Table size and distance Auto freeze The correlation of the number of remaining transactions, the stronger the correlation(Value tends to 1 or-1) stddev_rest_age And stddev_sz The more valuable the problem is
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 10000 transactions, If you want to change statistics, for example, modify this value
)
as wb, -- Number BATCH, each batch Represents 1 million transactions
* 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)) , -- Current age
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, -- How many more transactions are generated, Automatic garbage collection is triggered FREEZE, Cause transaction ID
coalesce(
least(
substring(reloptions::text, 'autovacuum_freeze_min_age=(\d+)')::int
),
current_setting('vacuum_freeze_min_age')::int
) as xid_lower_to_minage -- If triggered FREEZE, Transactions for this table ID How old will it fall
from pg_class where relkind='r' order by pg_total_relation_size(oid) desc limit 20;
Common query
1. Query read-only node latency
– query in the master node
select * from dba.ro_delay;
– query in read-only node
2. Query top query, the first optimization
select * from dba.topsql;
3. Reset the top query statistics counter (it can usually be reset before the peak period to prevent interference with the results)
select pg_stat_statements_reset();
4. Query qps and print once per second at psql terminal
select * from dba.qps;
\watch 1
4.1. Query tps
call dba.tps();
- 5. Query the number of active sessions. If it exceeds the number of CPU cores, the database is very busy and needs to be optimized
select * from dba.session_acting_cnt;
6. Current active session
select * from dba.sessions;
7. Query lock waiting. If there is a lot of waiting for a long time, you need to pay attention to whether there is a problem with the business logic
select * from dba.locks;
8. Query the table occupying space top 10
select * from dba.top10sizetable;
9. Query the index of the occupied space top 10
select * from dba.top10sizeindex;
10. Query the table occupying space top 10 (including index)
select * from dba.top10sizetableindex;
11. Query the table of expansion space top 10
select * from dba.top10bloatsizetable;
12. Query the index of the expansion space top 10
select * from dba.top10bloatsizeindex;
13. Query the table of expansion ratio top 10
select * from dba.top10bloatratiotable;
14. Query the index of expansion ratio top 10
select * from dba.top10bloatratioindex;
15. Query, update and delete the table with the number of records top 10
select * from dba.top10updatetable;
16. Query the table with top 10 records inserted
select * from dba.top10inserttable;
17. Query the table of top 10 dirty records
select * from dba.top10deadtable;
18. Query the table of age top 10
select * from dba.top10age;
19. Query the distance from the current oldest transaction to the current time and the number of transactions to the current, indicating that the larger the expansion space, the more expansion garbage may be caused
select * from dba.oldestxact; select * from pg_prepared_xacts;
20. Remaining space of query sequence
select * from dba.seqs;
21. Who is blocked by PostgreSQL (lock waiting for detection) - pg_blocking_pids
PostgreSQL who is blocking who (lock waiting for detection)
22. Query the unused index top 10 greater than 1MB (note that if PK and UK are only used for constraints, they may not be counted, but they cannot be deleted)
select * from dba.top10notusedidx;
23. Query unused tables larger than 1MB top 10
select * from dba.top10notusedtab;
24. Query heat meter top 10
select * from dba.top10hottab;
25. Query the top 10 of a cold meter larger than 1MB
select * from dba.top10coldtab;
26. Query the hot index top 10
select * from dba.top10hotidx;
27. Query the cold index top 10 greater than 1MB (note that PK and UK may not be counted but cannot be deleted if they are only used for constraints)
select * from dba.top10coldidx;
28. Query the database freeze storm forecast
select * from dba.v_freeze; select * from dba.v_freeze_stat; select * from dba.v_freeze_stat_detail;
Query the remaining age of the large free table of top 20.
select * from dba.top20freezebigtable;
– combined with DBA TPS, via remain_ages_xid/dba.tps estimates how long each table will be free
call dba.tps();
29. Query the number of conflicts between ro node read and replay. It is recommended that ro nodes in high-frequency recovery do not run long sql.
select * from dba.ro_conflicts;
30. Before the RO node executes SQL manually, the DBA recommends setting the SQL timeout to avoid running SQL for a long time, resulting in unnecessary replay delay and conflict cancel statement
set statement_timeout ='1s'; set lock_timeout='10ms';
31. The maximum conflict tolerance time of RO node is set to 5 min by default
show max_standby_streaming_delay ; max_standby_streaming_delay ----------------------------- 5min (1 row)
32. Clean up the database stat counter
\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. Execute at the standby node to check the speed at which the current standby node receives wal
call dba.wal_receive_bw();
34. Execute at the standby node to check the replay wal speed of the current standby node
call dba.wal_replay_bw();