-
Notifications
You must be signed in to change notification settings - Fork 42
/
700-pg_table.yml
282 lines (273 loc) · 23.1 KB
/
700-pg_table.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
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
##
# SYNOPSIS
# pg_table.pg_table_10_12_*
#
# DESCRIPTION
# PostgreSQL table metrics (9.4-12)
#
# OPTIONS
# Tags []
# TTL 10
# Priority 0
# Timeout 1s
# Fatal false
# Version 90400 ~ 130000
# Source 700-pg_table.yml
#
# METRICS
# datname (LABEL)
# Database name of this table
# relname (LABEL)
# Relation name of this table
# relid (GAUGE)
# Relation oid of this table
# kind (GAUGE)
# Relation kind r/table/114,m/mview/109,t/toast/116,p/partitioned/112
# pages (GAUGE)
# Size of the on-disk representation of this table in pages
# tuples (COUNTER)
# All txid before this have been frozen on this table
# frozenxid (COUNTER)
# All txid before this have been frozen on this table
# age (GAUGE)
# Age of this table in vacuum cycles
# ncols (GAUGE)
# Number of columns in the table
# seq_scan (COUNTER)
# Number of sequential scans initiated on this table
# seq_tup_read (COUNTER)
# Number of live rows fetched by sequential scans
# idx_scan (COUNTER)
# Number of index scans initiated on this table
# idx_tup_fetch (COUNTER)
# Number of live rows fetched by index scans
# tbl_scan (COUNTER)
# Number of scans initiated on this table
# tup_read (COUNTER)
# Number of live rows fetched by scans
# n_tup_ins (COUNTER)
# Number of rows inserted
# n_tup_upd (COUNTER)
# Number of rows updated (includes HOT updated rows)
# n_tup_del (COUNTER)
# Number of rows deleted
# n_tup_mod (COUNTER)
# Number of rows modified (insert + update + delete)
# n_tup_hot_upd (COUNTER)
# Number of rows HOT updated (i.e with no separate index update required)
# n_live_tup (GAUGE)
# Estimated number of live rows
# n_dead_tup (GAUGE)
# Estimated number of dead rows
# n_mod_since_analyze (GAUGE)
# Estimated number of rows modified since this table was last analyzed
# last_vacuum (DISCARD)
# Last time at which this table was manually vacuumed (not counting VACUUM FULL)
# last_autovacuum (DISCARD)
# Last time at which this table was vacuumed by the autovacuum daemon
# last_analyze (DISCARD)
# Last time at which this table was manually analyzed
# last_autoanalyze (DISCARD)
# Last time at which this table was analyzed by the autovacuum daemon
# vacuum_count (COUNTER)
# Number of times this table has been manually vacuumed (not counting VACUUM FULL)
# autovacuum_count (COUNTER)
# Number of times this table has been vacuumed by the autovacuum daemon
# analyze_count (COUNTER)
# Number of times this table has been manually analyzed
# autoanalyze_count (COUNTER)
# Number of times this table has been analyzed by the autovacuum daemon
# heap_blks_read (COUNTER)
# Number of disk blocks read from this table
# heap_blks_hit (COUNTER)
# Number of buffer hits in this table
# idx_blks_read (COUNTER)
# Number of disk blocks read from all indexes on this table
# idx_blks_hit (COUNTER)
# Number of buffer hits in all indexes on this table
# toast_blks_read (DISCARD)
# Number of disk blocks read from this table's TOAST table (if any)
# toast_blks_hit (DISCARD)
# Number of buffer hits in this table's TOAST table (if any)
# tidx_blks_read (DISCARD)
# Number of disk blocks read from this table's TOAST table indexes (if any)
# tidx_blks_hit (DISCARD)
# Number of buffer hits in this table's TOAST table indexes (if any)
#
pg_table_16:
name: pg_table
desc: PostgreSQL table metrics (v13 with n_ins_since_vacuum)
query: |
SELECT CURRENT_CATALOG AS datname, relnamespace::RegNamespace || '.' || c.relname AS relname, c.oid AS relid, ascii(relkind) AS kind,
relpages AS pages, reltuples AS tuples, relfrozenxid AS frozenxid, age(relfrozenxid) AS age, relnatts AS ncols,
seq_scan,seq_tup_read,idx_scan,idx_tup_fetch,seq_scan + idx_scan AS tbl_scan, seq_tup_read + idx_tup_fetch AS tup_read,
n_tup_ins,n_tup_upd,n_tup_del,(n_tup_ins + n_tup_upd + n_tup_del) AS n_tup_mod,n_tup_hot_upd,n_tup_newpage_upd,n_live_tup,n_dead_tup,
n_mod_since_analyze,n_ins_since_vacuum,last_vacuum,last_autovacuum,last_analyze,last_autoanalyze,last_seq_scan,
vacuum_count,autovacuum_count,analyze_count,autoanalyze_count,
heap_blks_read,heap_blks_hit,idx_blks_read,idx_blks_hit,toast_blks_read,toast_blks_hit,tidx_blks_read,tidx_blks_hit
FROM pg_class c, LATERAL (SELECT * FROM pg_stat_user_tables WHERE relid = c.oid LIMIT 1) psut, LATERAL (SELECT * FROM pg_statio_user_tables WHERE relid = c.oid LIMIT 1) psutio
WHERE relnamespace = ANY (SELECT oid FROM pg_namespace WHERE nspname !~ '^pg_' AND nspname !~ '^_' AND nspname !~ '^timescaledb' AND nspname !~ '^citus' AND nspname !~ '^columnar' AND nspname NOT IN ('pg_catalog','information_schema','pg_toast','repack','monitor')) AND relkind = ANY (ARRAY ['r', 'm', 't', 'p'])
ORDER BY relpages DESC LIMIT 256;
ttl: 10
timeout: 1
min_version: 160000
metrics:
- datname: { usage: LABEL ,description: Database name of this table }
- relname: { usage: LABEL ,description: Relation name of this table }
- relid: { usage: GAUGE ,description: Relation oid of this table }
- kind: { usage: GAUGE ,description: Relation kind r/table/114,m/mview/109,t/toast/116,p/partitioned/112 }
- pages: { usage: GAUGE ,description: Size of the on-disk representation of this table in pages }
- tuples: { usage: COUNTER ,description: All txid before this have been frozen on this table }
- frozenxid: { usage: COUNTER ,description: All txid before this have been frozen on this table }
- age: { usage: GAUGE ,description: Age of this table in vacuum cycles }
- ncols: { usage: GAUGE ,description: Number of columns in the table }
- seq_scan: { usage: COUNTER ,default: 0 ,description: Number of sequential scans initiated on this table }
- seq_tup_read: { usage: COUNTER ,default: 0 ,description: Number of live rows fetched by sequential scans }
- idx_scan: { usage: COUNTER ,default: 0 ,description: Number of index scans initiated on this table }
- idx_tup_fetch: { usage: COUNTER ,default: 0 ,description: Number of live rows fetched by index scans }
- tbl_scan: { usage: COUNTER ,default: 0 ,description: Number of scans initiated on this table }
- tup_read: { usage: COUNTER ,default: 0 ,description: Number of live rows fetched by scans }
- n_tup_ins: { usage: COUNTER ,default: 0 ,description: Number of rows inserted }
- n_tup_upd: { usage: COUNTER ,default: 0 ,description: Number of rows updated (includes HOT updated rows) }
- n_tup_del: { usage: COUNTER ,default: 0 ,description: Number of rows deleted }
- n_tup_mod: { usage: COUNTER ,default: 0 ,description: Number of rows modified (insert + update + delete) }
- n_tup_hot_upd: { usage: COUNTER ,default: 0 ,description: Number of rows HOT updated (i.e with no separate index update required) }
- n_tup_newpage_upd: { usage: COUNTER ,default: 0 ,description: Number of rows updated where the successor version goes onto a new heap page }
- n_live_tup: { usage: GAUGE ,description: Estimated number of live rows }
- n_dead_tup: { usage: GAUGE ,description: Estimated number of dead rows }
- n_mod_since_analyze: { usage: GAUGE ,description: Estimated number of rows modified since this table was last analyzed }
- n_ins_since_vacuum: { usage: GAUGE ,description: Estimated number of rows inserted since this table was last vacuumed }
- last_vacuum: { usage: DISCARD ,description: Last time at which this table was manually vacuumed (not counting VACUUM FULL) }
- last_autovacuum: { usage: DISCARD ,description: Last time at which this table was vacuumed by the autovacuum daemon }
- last_analyze: { usage: DISCARD ,description: Last time at which this table was manually analyzed }
- last_autoanalyze: { usage: DISCARD ,description: Last time at which this table was analyzed by the autovacuum daemon }
- last_seq_scan: { usage: DISCARD ,description: The timestamp of the last seq scan on this table }
- vacuum_count: { usage: COUNTER ,default: 0 ,description: Number of times this table has been manually vacuumed (not counting VACUUM FULL) }
- autovacuum_count: { usage: COUNTER ,default: 0 ,description: Number of times this table has been vacuumed by the autovacuum daemon }
- analyze_count: { usage: COUNTER ,default: 0 ,description: Number of times this table has been manually analyzed }
- autoanalyze_count: { usage: COUNTER ,default: 0 ,description: Number of times this table has been analyzed by the autovacuum daemon }
- heap_blks_read: { usage: COUNTER ,default: 0 ,description: Number of disk blocks read from this table }
- heap_blks_hit: { usage: COUNTER ,default: 0 ,description: Number of buffer hits in this table }
- idx_blks_read: { usage: COUNTER ,default: 0 ,description: Number of disk blocks read from all indexes on this table }
- idx_blks_hit: { usage: COUNTER ,default: 0 ,description: Number of buffer hits in all indexes on this table }
- toast_blks_read: { usage: DISCARD ,default: 0 ,description: Number of disk blocks read from this table's TOAST table (if any) }
- toast_blks_hit: { usage: DISCARD ,default: 0 ,description: Number of buffer hits in this table's TOAST table (if any) }
- tidx_blks_read: { usage: DISCARD ,default: 0 ,description: Number of disk blocks read from this table's TOAST table indexes (if any) }
- tidx_blks_hit: { usage: DISCARD ,default: 0 ,description: Number of buffer hits in this table's TOAST table indexes (if any) }
pg_table_13:
name: pg_table
desc: PostgreSQL table metrics (v13 with n_ins_since_vacuum)
query: |
SELECT CURRENT_CATALOG AS datname, relnamespace::RegNamespace || '.' || c.relname AS relname, c.oid AS relid, ascii(relkind) AS kind,
relpages AS pages, reltuples AS tuples, relfrozenxid AS frozenxid, age(relfrozenxid) AS age, relnatts AS ncols,
seq_scan,seq_tup_read,idx_scan,idx_tup_fetch,seq_scan + idx_scan AS tbl_scan, seq_tup_read + idx_tup_fetch AS tup_read,
n_tup_ins,n_tup_upd,n_tup_del,(n_tup_ins + n_tup_upd + n_tup_del) AS n_tup_mod,n_tup_hot_upd,n_live_tup,n_dead_tup,
n_mod_since_analyze,n_ins_since_vacuum,last_vacuum,last_autovacuum,last_analyze,last_autoanalyze,
vacuum_count,autovacuum_count,analyze_count,autoanalyze_count,
heap_blks_read,heap_blks_hit,idx_blks_read,idx_blks_hit,toast_blks_read,toast_blks_hit,tidx_blks_read,tidx_blks_hit
FROM pg_class c, LATERAL (SELECT * FROM pg_stat_user_tables WHERE relid = c.oid LIMIT 1) psut, LATERAL (SELECT * FROM pg_statio_user_tables WHERE relid = c.oid LIMIT 1) psutio
WHERE relnamespace = ANY (SELECT oid FROM pg_namespace WHERE nspname !~ '^pg_' AND nspname !~ '^_' AND nspname !~ '^timescaledb' AND nspname !~ '^citus' AND nspname !~ '^columnar' AND nspname NOT IN ('pg_catalog','information_schema','pg_toast','repack','monitor')) AND relkind = ANY (ARRAY ['r', 'm', 't', 'p'])
ORDER BY relpages DESC LIMIT 256;
ttl: 10
timeout: 1
min_version: 130000
max_version: 160000
metrics:
- datname: { usage: LABEL ,description: Database name of this table }
- relname: { usage: LABEL ,description: Relation name of this table }
- relid: { usage: GAUGE ,description: Relation oid of this table }
- kind: { usage: GAUGE ,description: Relation kind r/table/114,m/mview/109,t/toast/116,p/partitioned/112 }
- pages: { usage: GAUGE ,description: Size of the on-disk representation of this table in pages }
- tuples: { usage: COUNTER ,description: All txid before this have been frozen on this table }
- frozenxid: { usage: COUNTER ,description: All txid before this have been frozen on this table }
- age: { usage: GAUGE ,description: Age of this table in vacuum cycles }
- ncols: { usage: GAUGE ,description: Number of columns in the table }
- seq_scan: { usage: COUNTER ,default: 0 ,description: Number of sequential scans initiated on this table }
- seq_tup_read: { usage: COUNTER ,default: 0 ,description: Number of live rows fetched by sequential scans }
- idx_scan: { usage: COUNTER ,default: 0 ,description: Number of index scans initiated on this table }
- idx_tup_fetch: { usage: COUNTER ,default: 0 ,description: Number of live rows fetched by index scans }
- tbl_scan: { usage: COUNTER ,default: 0 ,description: Number of scans initiated on this table }
- tup_read: { usage: COUNTER ,default: 0 ,description: Number of live rows fetched by scans }
- n_tup_ins: { usage: COUNTER ,default: 0 ,description: Number of rows inserted }
- n_tup_upd: { usage: COUNTER ,default: 0 ,description: Number of rows updated (includes HOT updated rows) }
- n_tup_del: { usage: COUNTER ,default: 0 ,description: Number of rows deleted }
- n_tup_mod: { usage: COUNTER ,default: 0 ,description: Number of rows modified (insert + update + delete) }
- n_tup_hot_upd: { usage: COUNTER ,default: 0 ,description: Number of rows HOT updated (i.e with no separate index update required) }
- n_live_tup: { usage: GAUGE ,description: Estimated number of live rows }
- n_dead_tup: { usage: GAUGE ,description: Estimated number of dead rows }
- n_mod_since_analyze: { usage: GAUGE ,description: Estimated number of rows modified since this table was last analyzed }
- n_ins_since_vacuum: { usage: GAUGE ,description: Estimated number of rows inserted since this table was last vacuumed }
- last_vacuum: { usage: DISCARD ,description: Last time at which this table was manually vacuumed (not counting VACUUM FULL) }
- last_autovacuum: { usage: DISCARD ,description: Last time at which this table was vacuumed by the autovacuum daemon }
- last_analyze: { usage: DISCARD ,description: Last time at which this table was manually analyzed }
- last_autoanalyze: { usage: DISCARD ,description: Last time at which this table was analyzed by the autovacuum daemon }
- vacuum_count: { usage: COUNTER ,default: 0 ,description: Number of times this table has been manually vacuumed (not counting VACUUM FULL) }
- autovacuum_count: { usage: COUNTER ,default: 0 ,description: Number of times this table has been vacuumed by the autovacuum daemon }
- analyze_count: { usage: COUNTER ,default: 0 ,description: Number of times this table has been manually analyzed }
- autoanalyze_count: { usage: COUNTER ,default: 0 ,description: Number of times this table has been analyzed by the autovacuum daemon }
- heap_blks_read: { usage: COUNTER ,default: 0 ,description: Number of disk blocks read from this table }
- heap_blks_hit: { usage: COUNTER ,default: 0 ,description: Number of buffer hits in this table }
- idx_blks_read: { usage: COUNTER ,default: 0 ,description: Number of disk blocks read from all indexes on this table }
- idx_blks_hit: { usage: COUNTER ,default: 0 ,description: Number of buffer hits in all indexes on this table }
- toast_blks_read: { usage: DISCARD ,default: 0 ,description: Number of disk blocks read from this table's TOAST table (if any) }
- toast_blks_hit: { usage: DISCARD ,default: 0 ,description: Number of buffer hits in this table's TOAST table (if any) }
- tidx_blks_read: { usage: DISCARD ,default: 0 ,description: Number of disk blocks read from this table's TOAST table indexes (if any) }
- tidx_blks_hit: { usage: DISCARD ,default: 0 ,description: Number of buffer hits in this table's TOAST table indexes (if any) }
pg_table_10_12:
name: pg_table
desc: PostgreSQL table metrics (9.4-12)
query: |
SELECT CURRENT_CATALOG AS datname, relnamespace::RegNamespace || '.' || c.relname AS relname, oid AS relid, ascii(relkind) AS kind,
relpages AS pages, reltuples AS tuples, relfrozenxid AS frozenxid, age(relfrozenxid) AS age, relnatts AS ncols,
seq_scan,seq_tup_read,idx_scan,idx_tup_fetch,seq_scan + idx_scan AS tbl_scan, seq_tup_read + idx_tup_fetch AS tup_read,
n_tup_ins,n_tup_upd,n_tup_del,(n_tup_ins + n_tup_upd + n_tup_del) AS n_tup_mod,n_tup_hot_upd,n_live_tup,n_dead_tup,
n_mod_since_analyze,last_vacuum,last_autovacuum,last_analyze,last_autoanalyze,
vacuum_count,autovacuum_count,analyze_count,autoanalyze_count,
heap_blks_read,heap_blks_hit,idx_blks_read,idx_blks_hit,toast_blks_read,toast_blks_hit,tidx_blks_read,tidx_blks_hit
FROM pg_class c, LATERAL (SELECT * FROM pg_stat_user_tables WHERE relid = c.oid LIMIT 1) psut, LATERAL (SELECT * FROM pg_statio_user_tables WHERE relid = c.oid LIMIT 1) psutio
WHERE relnamespace = ANY (SELECT oid FROM pg_namespace WHERE nspname !~ '^pg_' AND nspname !~ '^_' AND nspname !~ '^timescaledb' AND nspname !~ '^citus' AND nspname !~ '^columnar' AND nspname NOT IN ('pg_catalog','information_schema','pg_toast','repack','monitor')) AND relkind = ANY (ARRAY ['r', 'm', 't', 'p'])
ORDER BY relpages DESC LIMIT 256;
ttl: 10
timeout: 1
min_version: 090400
max_version: 130000
metrics:
- datname: { usage: LABEL ,description: Database name of this table }
- relname: { usage: LABEL ,description: Relation name of this table }
- relid: { usage: GAUGE ,description: Relation oid of this table }
- kind: { usage: GAUGE ,description: Relation kind r/table/114,m/mview/109,t/toast/116,p/partitioned/112 }
- pages: { usage: GAUGE ,description: Size of the on-disk representation of this table in pages }
- tuples: { usage: COUNTER ,description: All txid before this have been frozen on this table }
- frozenxid: { usage: COUNTER ,description: All txid before this have been frozen on this table }
- age: { usage: GAUGE ,description: Age of this table in vacuum cycles }
- ncols: { usage: GAUGE ,description: Number of columns in the table }
- seq_scan: { usage: COUNTER ,default: 0 ,description: Number of sequential scans initiated on this table }
- seq_tup_read: { usage: COUNTER ,default: 0 ,description: Number of live rows fetched by sequential scans }
- idx_scan: { usage: COUNTER ,default: 0 ,description: Number of index scans initiated on this table }
- idx_tup_fetch: { usage: COUNTER ,default: 0 ,description: Number of live rows fetched by index scans }
- tbl_scan: { usage: COUNTER ,default: 0 ,description: Number of scans initiated on this table }
- tup_read: { usage: COUNTER ,default: 0 ,description: Number of live rows fetched by scans }
- n_tup_ins: { usage: COUNTER ,default: 0 ,description: Number of rows inserted }
- n_tup_upd: { usage: COUNTER ,default: 0 ,description: Number of rows updated (includes HOT updated rows) }
- n_tup_del: { usage: COUNTER ,default: 0 ,description: Number of rows deleted }
- n_tup_mod: { usage: COUNTER ,default: 0 ,description: Number of rows modified (insert + update + delete) }
- n_tup_hot_upd: { usage: COUNTER ,default: 0 ,description: Number of rows HOT updated (i.e with no separate index update required) }
- n_live_tup: { usage: GAUGE ,description: Estimated number of live rows }
- n_dead_tup: { usage: GAUGE ,description: Estimated number of dead rows }
- n_mod_since_analyze: { usage: GAUGE ,description: Estimated number of rows modified since this table was last analyzed }
- last_vacuum: { usage: DISCARD ,description: Last time at which this table was manually vacuumed (not counting VACUUM FULL) }
- last_autovacuum: { usage: DISCARD ,description: Last time at which this table was vacuumed by the autovacuum daemon }
- last_analyze: { usage: DISCARD ,description: Last time at which this table was manually analyzed }
- last_autoanalyze: { usage: DISCARD ,description: Last time at which this table was analyzed by the autovacuum daemon }
- vacuum_count: { usage: COUNTER ,default: 0 ,description: Number of times this table has been manually vacuumed (not counting VACUUM FULL) }
- autovacuum_count: { usage: COUNTER ,default: 0 ,description: Number of times this table has been vacuumed by the autovacuum daemon }
- analyze_count: { usage: COUNTER ,default: 0 ,description: Number of times this table has been manually analyzed }
- autoanalyze_count: { usage: COUNTER ,default: 0 ,description: Number of times this table has been analyzed by the autovacuum daemon }
- heap_blks_read: { usage: COUNTER ,default: 0 ,description: Number of disk blocks read from this table }
- heap_blks_hit: { usage: COUNTER ,default: 0 ,description: Number of buffer hits in this table }
- idx_blks_read: { usage: COUNTER ,default: 0 ,description: Number of disk blocks read from all indexes on this table }
- idx_blks_hit: { usage: COUNTER ,default: 0 ,description: Number of buffer hits in all indexes on this table }
- toast_blks_read: { usage: DISCARD ,default: 0 ,description: Number of disk blocks read from this table's TOAST table (if any) }
- toast_blks_hit: { usage: DISCARD ,default: 0 ,description: Number of buffer hits in this table's TOAST table (if any) }
- tidx_blks_read: { usage: DISCARD ,default: 0 ,description: Number of disk blocks read from this table's TOAST table indexes (if any) }
- tidx_blks_hit: { usage: DISCARD ,default: 0 ,description: Number of buffer hits in this table's TOAST table indexes (if any) }