background
Create a view for easy query
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 () 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 on the 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 ;
-- existstandbyNode execution， examinereplayComparereceiveDelay
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);
-- existstandbyNode execution， examinereceivertake overwalCompare上游产生walDelay.
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;
-- existstandbyNode execution， take overwalSpeed。
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$;
-- existstandbyNode execution， replay walSpeed。
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 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 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
-- 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 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 as with
t_wait as
(
select ,,,,,,,,
,,,,,,,
,,b.xact_start,b.query_start,,,b.client_addr,b.client_port,b.application_name
from pg_locks a,pg_stat_activity b where = and not
),
t_run as
(
select ,,,,,,,,
,,,,,,,
,,b.xact_start,b.query_start,,,b.client_addr,b.client_port,b.application_name
from pg_locks a,pg_stat_activity b where = and
),
t_overlap as
(
select r.* from t_wait w join t_run r on
(
is not distinct from and
is not distinct from and
is not distinct from and
is not distinct from and
is not distinct from and
is not distinct from and
is not distinct from and
is not distinct from and
is not distinct from and
is not distinct from and
<>
)
),
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;
-- Expansion point query
create view 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;
-- Query expansion spacetop 10Table 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 =0 OR =otta THEN 0.0 ELSE /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*(-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
AS schemaname,
AS tablename,
COALESCE(,0) AS reltuples,
COALESCE(,0) AS relpages,
COALESCE(bs,0) AS bs,
COALESCE(CEIL((*((datahdr+ma-
(CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)),0) AS otta,
COALESCE(,'?') AS iname, COALESCE(,0) AS ituples, COALESCE(,0) AS ipages,
COALESCE(CEIL((*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols
FROM
pg_class cc
JOIN pg_namespace nn ON = AND <> 'information_schema'
LEFT JOIN
(
SELECT
ma,bs,,,
(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
, , 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 = AND =
) AS nullhdr
FROM pg_attribute att
JOIN pg_class tbl ON =
JOIN pg_namespace ns ON =
LEFT JOIN pg_stats s ON =
AND =
AND =false
AND =,
(
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 > 0 AND ='r'
GROUP BY 1,2,3,4,5
) AS foo
) AS rs
ON = AND =
LEFT JOIN pg_index i ON indrelid =
LEFT JOIN pg_class c2 ON =
) AS sml order by wastedbytes desc limit 5;
-- Query expansion spacetop 10Index 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 =0 OR =otta THEN 0.0 ELSE /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*(-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
AS schemaname,
AS tablename,
COALESCE(,0) AS reltuples,
COALESCE(,0) AS relpages,
COALESCE(bs,0) AS bs,
COALESCE(CEIL((*((datahdr+ma-
(CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)),0) AS otta,
COALESCE(,'?') AS iname, COALESCE(,0) AS ituples, COALESCE(,0) AS ipages,
COALESCE(CEIL((*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols
FROM
pg_class cc
JOIN pg_namespace nn ON = AND <> 'information_schema'
LEFT JOIN
(
SELECT
ma,bs,,,
(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
, , 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 = AND =
) AS nullhdr
FROM pg_attribute att
JOIN pg_class tbl ON =
JOIN pg_namespace ns ON =
LEFT JOIN pg_stats s ON =
AND =
AND =false
AND =,
(
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 > 0 AND ='r'
GROUP BY 1,2,3,4,5
) AS foo
) AS rs
ON = AND =
LEFT JOIN pg_index i ON indrelid =
LEFT JOIN pg_class c2 ON =
) AS sml order by wastedibytes desc limit 5;
-- 查询膨胀Compare例top 10Table of(More waste of space than10MBTable 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 =0 OR =otta THEN 0.0 ELSE /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*(-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
AS schemaname,
AS tablename,
COALESCE(,0) AS reltuples,
COALESCE(,0) AS relpages,
COALESCE(bs,0) AS bs,
COALESCE(CEIL((*((datahdr+ma-
(CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)),0) AS otta,
COALESCE(,'?') AS iname, COALESCE(,0) AS ituples, COALESCE(,0) AS ipages,
COALESCE(CEIL((*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols
FROM
pg_class cc
JOIN pg_namespace nn ON = AND <> 'information_schema'
LEFT JOIN
(
SELECT
ma,bs,,,
(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
, , 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 = AND =
) AS nullhdr
FROM pg_attribute att
JOIN pg_class tbl ON =
JOIN pg_namespace ns ON =
LEFT JOIN pg_stats s ON =
AND =
AND =false
AND =,
(
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 > 0 AND ='r'
GROUP BY 1,2,3,4,5
) AS foo
) AS rs
ON = AND =
LEFT JOIN pg_index i ON indrelid =
LEFT JOIN pg_class c2 ON =
) AS sml
where (CASE WHEN relpages < otta THEN 0 ELSE bs*(-otta)::bigint END) >= 10240000
order by tbloat desc,wastedbytes desc limit 5;
-- 查询膨胀Compare例top 10Index of(More waste of space than10MBIndex 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 =0 OR =otta THEN 0.0 ELSE /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*(-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
AS schemaname,
AS tablename,
COALESCE(,0) AS reltuples,
COALESCE(,0) AS relpages,
COALESCE(bs,0) AS bs,
COALESCE(CEIL((*((datahdr+ma-
(CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)),0) AS otta,
COALESCE(,'?') AS iname, COALESCE(,0) AS ituples, COALESCE(,0) AS ipages,
COALESCE(CEIL((*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols
FROM
pg_class cc
JOIN pg_namespace nn ON = AND <> 'information_schema'
LEFT JOIN
(
SELECT
ma,bs,,,
(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
, , 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 = AND =
) AS nullhdr
FROM pg_attribute att
JOIN pg_class tbl ON =
JOIN pg_namespace ns ON =
LEFT JOIN pg_stats s ON =
AND =
AND =false
AND =,
(
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 > 0 AND ='r'
GROUP BY 1,2,3,4,5
) AS foo
) AS rs
ON = AND =
LEFT JOIN pg_index i ON indrelid =
LEFT JOIN pg_class c2 ON =
) 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 as select max_value-last_value,* from pg_sequences order by max_value-last_value ;
-- The query that has not been used is greater than1MBIndex of top 10 (Notice, PK、UKIf only for constraints, Probably not counted,But it cannot be deleted)
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 that has not been used is greater than1MBTable 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 hot tabletop 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 than1MBCold watchtop 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 indextop 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 than1MBCold indextop 10(Notice, PK、UKIf only for constraints, Probably not counted,But it cannot be deleted)
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;
-- freezeStorm prediction related3Views
create view dba.v_freeze as
select
e.*,
a.*
from
(select
current_setting('autovacuum_freeze_max_age')::int as v1, -- If the table's transactionIDAge is greater than this value, Even if it is not turned onautovacuumIt will also be forced to triggerFREEZE, And alertPreventing Transaction ID Wraparound Failures
current_setting('autovacuum_multixact_freeze_max_age')::int as v2, -- If the table's parallel transactionIDAge is greater than this value, Even if it is not turned onautovacuumIt will also be forced to triggerFREEZE, And alertPreventing Transaction ID Wraparound Failures
current_setting('vacuum_freeze_min_age')::int as v3, -- When manually or automatically garbage collection, If the transaction is loggedIDAge is greater than this value, Will beFREEZE
current_setting('vacuum_multixact_freeze_min_age')::int as v4, -- When manually or automatically garbage collection, If the logged parallel transactionIDAge is greater than this value, Will beFREEZE
current_setting('vacuum_freeze_table_age')::int as v5, -- During manual garbage collection, If the table's transactionIDAge is greater than this value, Will triggerFREEZE. 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's parallel transactionIDAge is greater than this value, Will triggerFREEZE. The upper limit of this parameter is %95 autovacuum_multixact_freeze_max_age
current_setting('autovacuum_vacuum_cost_delay') as v7, -- When automatic garbage collection, A rest time after each recycling cycle, Mainly prevent garbage recycling from excessive resource consumption. -1 Indicates usevacuum_cost_delaySettings
current_setting('autovacuum_vacuum_cost_limit') as v8, -- When automatic garbage collection, How much limit is required for each recycling cycle, Limited byvacuum_cost_page_hit,vacuum_cost_page_missvacuum_cost_page_dirtyParameters and operation decisions within the cycle. -1 Indicates usevacuum_cost_limitSettings
current_setting('vacuum_cost_delay') as v9, -- During manual garbage collection, A rest time after each recycling cycle, Mainly prevent garbage recycling from excessive resource consumption.
current_setting('vacuum_cost_limit') as v10, -- During manual garbage collection, How much limit is required for each recycling cycle, Limited byvacuum_cost_page_hit,vacuum_cost_page_missvacuum_cost_page_dirtyParameters and operation decisions within the cycle.
current_setting('autovacuum') as autovacuum -- Whether to enable automatic garbage collection
) a,
LATERAL ( -- LATERAL 允许你existthisSUBQUERYDirectly quote the previous one intable, subqueryIn-housecolumn
select
pg_size_pretty(pg_total_relation_size(oid)) sz, -- Table size(IncludeTOAST, index)
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, -- After how many more transactions are generated, Automatic garbage collection will triggerFREEZE, Became a businessID
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, -- After how many more transactions are generated, Automatic garbage collection will triggerFREEZE, Because of concurrent transactionsID
coalesce(
least(
substring(reloptions::text, 'autovacuum_freeze_min_age=(\d+)')::int
),
a.v3
) as xid_lower_to_minage, -- If triggeredFREEZE, 该surfaceofTransactionsIDHow much age will be reduced
coalesce(
least(
substring(reloptions::text, 'autovacuum_multixact_freeze_min_age=(\d+)')::int
),
a.v4
) as mxid_lower_to_minage, -- If triggeredFREEZE, 该surfaceof并行TransactionsIDHow much age will be reduced
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 manuallyVACUUM, Will it triggerFREEZE, The trigger cause(TransactionsIDAge 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 manuallyVACUUM, Will it triggerFREEZE, The trigger cause(并行TransactionsIDAge reaches threshold)
reloptions -- surface级parameter, priority. 例如Whether to enable 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), -- 排exist越前, The first triggers the automaticFREEZE, That is, the prediction of the coming storm
pg_total_relation_size(reloid) desc -- The same remaining age, surfaceThe bigger, Echiae
;
create view dba.v_freeze_stat as
select
wb, -- What is theBATCH, Eachbatch代surface流逝100万个Transactions
cnt, -- thisbatch 有多少surface
pg_size_pretty(ssz) as ssz1, -- thisbatch These surface+TOAST+index How much capacity is there
pg_size_pretty(ssz) as ssz2, -- thisbatch FREEZE How many readings will be causedIO
pg_size_pretty(ssz*3) as ssz3, -- thisbatch FREEZE How many writings may be caused at mostIO (Usually three servings : Data File, WAL FULL PAGE, WAL)
pg_size_pretty(min_sz) as ssz4, -- thisbatch MinimumTable of多大
pg_size_pretty(max_sz) as ssz5, -- thisbatch maximumTable of多大
pg_size_pretty(avg_sz) as ssz6, -- thisbatch 平均surface多大
pg_size_pretty(stddev_sz) as ssz7, -- thisbatch surfacesizeof方差, The bigger, 说明surfacesize差异化明显
min_rest_age, -- thisbatch Distance automaticFREEZE最低剩余Transactionsnumber
max_rest_age, -- thisbatch Distance automaticFREEZE最高剩余Transactionsnumber
stddev_rest_age, -- thisbatch Distance automaticFREEZE剩余Transactionsnumberof方差, The smaller the，说明thisbatchtriggerfreezeWill be more smooth, The bigger, 说明thisbatch将有可能exist某些点集中triggerfreeze (但是可能集中triggerof都是小surface)
corr_rest_age_sz, -- surfacesizeandDistance automaticfreeze剩余Transactionsnumberof相关性，The stronger the correlation(Value Trend1or-1) stddev_rest_age and sz7 The more valuable the problem explained
round(100*(ssz/(sum(ssz) over ())), 2)||' %' as ratio -- thisBATCHof容量占Compare，占Compare如果非常不均匀，说明有必要调整surface级FREEZEparameter，让占Compare均匀化
from
(
select a.*, b.* from
(
select
min(least(remain_ages_xid, remain_ages_mxid)) as v_min, -- Automatically in the entire databaseFREEZEof Minimum 剩余TransactionsIDnumber
max(least(remain_ages_xid, remain_ages_mxid)) as v_max -- Automatically in the entire databaseFREEZEof maximum 剩余TransactionsIDnumber
from v_freeze
) as a,
LATERAL ( -- advancedSQL
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万个Transactions, If you want to change the statistics, for example，修改this值即可
) 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() as ssz2, -- thisbatch FREEZE How many readings will be causedIO (surface+TOAST+index)
pg_size_pretty(*3) as ssz3, -- thisbatch FREEZE How many writings may be caused at mostIO (Usually three servings : Data File, WAL FULL PAGE, WAL)
pg_size_pretty(t.ssz_sum) as ssz4, -- allbatch allsurfaceof总size (surface+TOAST+index)
round(100*(/t.ssz_sum), 2)||' %' as ratio_batch, -- thisBATCHof容量占Compare，目标是让allBATCH占Compare尽量一致
round(100*(pg_total_relation_size()/), 2)||' %' as ratio_table, -- thissurface占整个batchof容量占Compare，大surface尽量错开freeze
t.*
from
(
select a.*, b.* from
(
select
min(least(remain_ages_xid, remain_ages_mxid)) as v_min, -- Automatically in the entire databaseFREEZEof Minimum 剩余TransactionsIDnumber
max(least(remain_ages_xid, remain_ages_mxid)) as v_max -- Automatically in the entire databaseFREEZEof maximum 剩余TransactionsIDnumber
from v_freeze
) as a,
LATERAL ( -- advancedSQL
select
count(*) over w as cnt, -- thisbatch 有多少surface
sum(pg_total_relation_size(reloid)) over () as ssz_sum, -- allbatch allsurfaceof总size (surface+TOAST+index)
sum(pg_total_relation_size(reloid)) over w as ssz, -- thisbatch Table ofsize总和 (surface+TOAST+index)
pg_size_pretty(min(pg_total_relation_size(reloid)) over w) as min_sz, -- thisbatch MinimumTable of多大
pg_size_pretty(max(pg_total_relation_size(reloid)) over w) as max_sz, -- thisbatch maximumTable of多大
pg_size_pretty(avg(pg_total_relation_size(reloid)) over w) as avg_sz, -- thisbatch 平均surface多大
pg_size_pretty(stddev_samp(pg_total_relation_size(reloid)) over w) as stddev_sz, -- thisbatch surfacesizeof方差, The bigger, 说明surfacesize差异化明显
min(least(remain_ages_xid, remain_ages_mxid)) over w as min_rest_age, -- thisbatch Distance automaticFREEZE最低剩余Transactionsnumber
max(least(remain_ages_xid, remain_ages_mxid)) over w as max_rest_age, -- thisbatch Distance automaticFREEZE最高剩余Transactionsnumber
stddev_samp(least(remain_ages_xid, remain_ages_mxid)) over w as stddev_rest_age, -- thisbatch Distance automaticFREEZE剩余Transactionsnumberof方差, The smaller the，说明thisbatchtriggerfreezeWill be more smooth, The bigger, 说明thisbatch将有可能exist某些点集中triggerfreeze (但是可能集中triggerof都是小surface)
corr(least(remain_ages_xid, remain_ages_mxid), pg_total_relation_size(reloid)) over w as corr_rest_age_sz, -- surfacesizeandDistance automaticfreeze剩余Transactionsnumberof相关性，The stronger the correlation(Value Trend1or-1) stddev_rest_age and stddev_sz The more valuable the problem explained
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万个Transactions, If you want to change the statistics, for example，修改this值即可
)
as wb, -- What is theBATCH, Eachbatch代surface流逝100万个Transactions
* from v_freeze tt
) as t1
window w as
(
partition by
)
) as b
) t
order by
,
least(t.remain_ages_xid, t.remain_ages_mxid),
pg_total_relation_size() 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, -- After how many more transactions are generated, Automatic garbage collection will triggerFREEZE, Became a businessID
coalesce(
least(
substring(reloptions::text, 'autovacuum_freeze_min_age=(\d+)')::int
),
current_setting('vacuum_freeze_min_age')::int
) as xid_lower_to_minage -- If triggeredFREEZE, 该surfaceofTransactionsIDHow much age will be reduced
from pg_class where relkind='r' order by pg_total_relation_size(oid) desc limit 20;
-- Not archivedwaldocument
create view dba.arch_undone as
select * from pg_ls_archive_statusdir() where name !~ 'done$';
-- Archive task status
create view dba.arch_status as
select * from pg_stat_get_archiver();
-- walSpace occupancy
create view as
select pg_size_pretty(sum(size)) from pg_ls_waldir();
-- Copy slot status(Is there any unused copy slots, May causewalLog catalogue surges(Don't clean up))
create view as
select * from pg_replication_slots ;
-- System mandatory retentionwalsize
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(::int8*::int8) from a,b;
-- 系统动态examine点maximumwal保留size
create view dba.max_wal_size as
select setting||' '||unit from pg_settings where name='max_wal_size';
-- 长Transactions、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 -least(,),d.*,e.* from a,b,c,d left join e on (1=1);Common queries
1. Query read-only node delay
-- Query on the master node select * from dba.ro_delay; -- Query on read-only node set lock_timeout='10ms'; set statement_timeout='2s'; select * from dba.node_delay; select * from dba.ro_delay_on_standby;
2. Query top query, the first optimization
select * from ;
3. Reset the top query statistics counter (usually before the peak period comes, preventing the result from being interfered with)
select pg_stat_statements_reset();
4. Query qps, you can print once per second in the psql terminal
select * from ; \watch 1
4.1. Query tps
call ();
5. Query the number of active sessions. If the number of CPU cores exceeds the number of CPU cores, it means that the database is very busy and you need to pay attention to optimization.
select * from dba.session_acting_cnt;
6. Current active session
select * from ;
7. Query lock waiting. If there is a lot of waiting for a long time, you need to pay attention to whether there is any problem with the business logic.
select * from ;
8. Query the table with top 10 space occupied
select * from dba.top10sizetable;
9. Query the index of top 10 in space
select * from dba.top10sizeindex;
10. Query the table that occupies 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 top 10 of expansion space
select * from dba.top10bloatsizeindex;
13. Query the table of expansion ratio top 10
select * from dba.top10bloatratiotable;
14. Query the index of the expansion ratio top 10
select * from dba.top10bloatratioindex;
15. Query update and delete tables with top 10 records
select * from dba.top10updatetable;
16. Query the table with the top 10 records
select * from dba.top10inserttable;
17. Query the table with top 10 dirty records
select * from dba.top10deadtable;
18. Query the table with age top 10
select * from dba.top10age;
19. Query the current oldest transaction distance from the current time and number of transactions to indicate the size of the expansion space. The larger the current transaction, the more bloated garbage it may cause.
select * from ; select * from pg_prepared_xacts;
20. Remaining space in the query sequence
select * from ;
21. PostgreSQL who blocks whom (lock is waiting for detection) - pg_blocking_pids
"PostgreSQL Who blocked whom (locks waiting for detection) - pg_blocking_pids"
22. Query the index of more than 1MB that has not been used before (note that if PK and UK are only used for constraints, they may not be counted by statistics, but cannot be deleted)
select * from dba.top10notusedidx;
23. Query tables larger than 1MB that have not been used top 10
select * from dba.top10notusedtab;
24. Query the hot table top 10
select * from dba.top10hottab;
25. Query the top 10 cold tables larger than 1MB
select * from dba.top10coldtab;
26. Query hot index top 10
select * from dba.top10hotidx;
27. Query the top 10 of cold indexes greater than 1MB (note that if PK and UK are only used for constraints, they may not be counted by statistics, but cannot be deleted)
select * from dba.top10coldidx;
28. Query the database freeze storm prediction
select * from dba.v_freeze; select * from dba.v_freeze_stat; select * from dba.v_freeze_stat_detail;
Query the top 20 big freeze remaining age.
select * from dba.top20freezebigtable; -- Combined, Can be passedremain_ages_xid/Estimate how long will each table happen?freeze. call ();
29. When querying the number of times the RO node reads and replay conflicts, it is recommended that RO nodes in high-frequency recovery do not run long SQL.
select * from dba.ro_conflicts;
30. Before the DBA performs SQL artificially on the RO node, it is recommended to set SQL timeout to avoid long-term SQL running, resulting in unnecessary replay delay and conflict cancel statement
set statement_timeout ='1s'; set lock_timeout='10ms';
31. The maximum tolerance time setting of the conflict of RO node is 5 min.
show max_standby_streaming_delay ; max_standby_streaming_delay ----------------------------- 5min (1 row)
32. Clean 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 on standby node to check the speed at which standby node receives wal
call dba.wal_receive_bw();
34. Execute on standby node and check the speed of the current standby node replay wal
call dba.wal_replay_bw();
35. Use wal file and slot risk viewing.
select * from dba.arch_undone; select * from dba.arch_status; select * from ; select * from ; select * from dba.wal_keep_size; select * from dba.max_wal_size;
36. Long business, prepared statement
select * from dba.long_snapshot;
37. Query the invalid index.
select * from dba.invalid_index;
refer to
"PostgreSQL Real-time Health Monitoring Large Screen - Low Frequency Indicators - Collection Level"
"PostgreSQL Real-time Health Monitoring Large Screen - High-frequency Indicators (Server) - Collection Level"
"PostgreSQL Real-time Health Monitoring Large Screen - High-frequency Indicators - Collection Level"
"PostgreSQL Freeze Storm Prediction Continued - Collection Level SQL"
This is all about this article about PostgreSQL DBA's most commonly used SQL. For more related PostgreSQL DBA's commonly used SQL content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!