-
Notifications
You must be signed in to change notification settings - Fork 45
/
820-pg_table_bloat.yml
70 lines (65 loc) · 4.2 KB
/
820-pg_table_bloat.yml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
# pg_table_bloat require auxiliary view to work. Disable it or create auxiliary view before use:
# ----------------------------------------------------------------------
# -- Table bloat estimate
# ----------------------------------------------------------------------
# CREATE OR REPLACE VIEW monitor.pg_table_bloat AS
# SELECT CURRENT_CATALOG AS datname, nspname, relname , bs * tblpages AS size,
# CASE WHEN tblpages - est_tblpages_ff > 0 THEN (tblpages - est_tblpages_ff)/tblpages::FLOAT ELSE 0 END AS ratio
# FROM (
# SELECT ceil( reltuples / ( (bs-page_hdr)*fillfactor/(tpl_size*100) ) ) + ceil( toasttuples / 4 ) AS est_tblpages_ff,
# tblpages, fillfactor, bs, tblid, nspname, relname, is_na
# FROM (
# SELECT
# ( 4 + tpl_hdr_size + tpl_data_size + (2 * ma)
# - CASE WHEN tpl_hdr_size % ma = 0 THEN ma ELSE tpl_hdr_size % ma END
# - CASE WHEN ceil(tpl_data_size)::INT % ma = 0 THEN ma ELSE ceil(tpl_data_size)::INT % ma END
# ) AS tpl_size, (heappages + toastpages) AS tblpages, heappages,
# toastpages, reltuples, toasttuples, bs, page_hdr, tblid, nspname, relname, fillfactor, is_na
# FROM (
# SELECT
# tbl.oid AS tblid, ns.nspname , tbl.relname, tbl.reltuples,
# tbl.relpages AS heappages, coalesce(toast.relpages, 0) AS toastpages,
# coalesce(toast.reltuples, 0) AS toasttuples,
# coalesce(substring(array_to_string(tbl.reloptions, ' ') FROM 'fillfactor=([0-9]+)')::smallint, 100) AS fillfactor,
# current_setting('block_size')::numeric AS bs,
# CASE WHEN version()~'mingw32' OR version()~'64-bit|x86_64|ppc64|ia64|amd64' THEN 8 ELSE 4 END AS ma,
# 24 AS page_hdr,
# 23 + CASE WHEN MAX(coalesce(s.null_frac,0)) > 0 THEN ( 7 + count(s.attname) ) / 8 ELSE 0::int END
# + CASE WHEN bool_or(att.attname = 'oid' and att.attnum < 0) THEN 4 ELSE 0 END AS tpl_hdr_size,
# sum( (1-coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 0) ) AS tpl_data_size,
# bool_or(att.atttypid = 'pg_catalog.name'::regtype)
# OR sum(CASE WHEN att.attnum > 0 THEN 1 ELSE 0 END) <> count(s.attname) AS is_na
# FROM pg_attribute AS att
# JOIN pg_class AS tbl ON att.attrelid = tbl.oid
# JOIN pg_namespace AS ns ON ns.oid = tbl.relnamespace
# LEFT JOIN pg_stats AS s ON s.schemaname=ns.nspname AND s.tablename = tbl.relname AND s.inherited=false AND s.attname=att.attname
# LEFT JOIN pg_class AS toast ON tbl.reltoastrelid = toast.oid
# WHERE NOT att.attisdropped AND tbl.relkind = 'r' AND nspname NOT IN ('pg_catalog','information_schema')
# GROUP BY 1,2,3,4,5,6,7,8,9,10
# ) AS s
# ) AS s2
# ) AS s3
# WHERE NOT is_na;
# COMMENT ON VIEW monitor.pg_table_bloat IS 'postgres table bloat estimate';
pg_table_bloat:
name: pg_table_bloat
desc: PostgreSQL table bloat metrics, require auxiliary view pg_table_bloat to work
query: SELECT datname, nspname || '.' || relname AS relname, size, ratio FROM pg_table_bloat ORDER BY size DESC LIMIT 64;
ttl: 300
timeout: 2
min_version: 090400
skip: true
tags: []
metrics:
- datname:
usage: LABEL
description: Database name of this table
- relname:
usage: LABEL
description: Schema qualified name of this table
- size:
usage: GAUGE
description: Total bytes of this table
- ratio:
usage: GAUGE
description: Estimated bloat ratio of this table from 0 to 1