Created
May 22, 2019 15:57
-
-
Save fljdin/8903ec3aca721f02534cd87f2b70ce45 to your computer and use it in GitHub Desktop.
A health check playbook for your Postgres database
https://www.citusdata.com/blog/2019/03/29/health-checks-for-your-postgres-database
https://github.com/heroku/heroku-pg-extras/tree/master/commands
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
sum(heap_blks_read) as heap_read, | |
sum(heap_blks_hit) as heap_hit, | |
sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio | |
FROM | |
pg_statio_user_tables; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT query, | |
calls, | |
total_time, | |
total_time / calls as time_per, | |
stddev_time, | |
rows, | |
rows / calls as rows_per, | |
100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent | |
FROM pg_stat_statements | |
WHERE query not similar to '%pg_%' | |
and calls > 500 | |
--ORDER BY calls | |
--ORDER BY total_time | |
order by time_per | |
--ORDER BY rows_per | |
DESC LIMIT 20; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH constants AS ( | |
SELECT current_setting('block_size')::numeric AS bs, 23 AS hdr, 4 AS ma | |
), bloat_info AS ( | |
SELECT | |
ma,bs,schemaname,tablename, | |
(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 | |
schemaname, tablename, hdr, ma, bs, | |
SUM((1-null_frac)*avg_width) AS datawidth, | |
MAX(null_frac) AS maxfracsum, | |
hdr+( | |
SELECT 1+count(*)/8 | |
FROM pg_stats s2 | |
WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename | |
) AS nullhdr | |
FROM pg_stats s, constants | |
GROUP BY 1,2,3,4,5 | |
) AS foo | |
), table_bloat AS ( | |
SELECT | |
schemaname, tablename, cc.relpages, bs, | |
CEIL((cc.reltuples*((datahdr+ma- | |
(CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)) AS otta | |
FROM bloat_info | |
JOIN pg_class cc ON cc.relname = bloat_info.tablename | |
JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = bloat_info.schemaname AND nn.nspname <> 'information_schema' | |
), index_bloat AS ( | |
SELECT | |
schemaname, tablename, bs, | |
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 bloat_info | |
JOIN pg_class cc ON cc.relname = bloat_info.tablename | |
JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = bloat_info.schemaname AND nn.nspname <> 'information_schema' | |
JOIN pg_index i ON indrelid = cc.oid | |
JOIN pg_class c2 ON c2.oid = i.indexrelid | |
) | |
SELECT | |
type, schemaname, object_name, bloat, pg_size_pretty(raw_waste) as waste | |
FROM | |
(SELECT | |
'table' as type, | |
schemaname, | |
tablename as object_name, | |
ROUND(CASE WHEN otta=0 THEN 0.0 ELSE table_bloat.relpages/otta::numeric END,1) AS bloat, | |
CASE WHEN relpages < otta THEN '0' ELSE (bs*(table_bloat.relpages-otta)::bigint)::bigint END AS raw_waste | |
FROM | |
table_bloat | |
UNION | |
SELECT | |
'index' as type, | |
schemaname, | |
tablename || '::' || iname as object_name, | |
ROUND(CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages/iotta::numeric END,1) AS bloat, | |
CASE WHEN ipages < iotta THEN '0' ELSE (bs*(ipages-iotta))::bigint END AS raw_waste | |
FROM | |
index_bloat) bloat_summary | |
ORDER BY raw_waste DESC, bloat DESC |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
schemaname || '.' || relname AS table, | |
indexrelname AS index, | |
pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size, | |
idx_scan as index_scans | |
FROM pg_stat_user_indexes ui | |
JOIN pg_index i ON ui.indexrelid = i.indexrelid | |
WHERE NOT indisunique AND idx_scan < 50 AND pg_relation_size(relid) > 5 * 8192 | |
ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST, | |
pg_relation_size(i.indexrelid) DESC; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment