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
|
;;; Guix Data Service -- Information about Guix over time
;;; Copyright © 2020 Christopher Baines <mail@cbaines.net>
;;;
;;; This program is free software: you can redistribute it and/or
;;; modify it under the terms of the GNU Affero General Public License
;;; as published by the Free Software Foundation, either version 3 of
;;; the License, or (at your option) any later version.
;;;
;;; This program is distributed in the hope that it will be useful,
;;; but WITHOUT ANY WARRANTY; without even the implied warranty of
;;; MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
;;; Affero General Public License for more details.
;;;
;;; You should have received a copy of the GNU Affero General Public
;;; License along with this program. If not, see
;;; <http://www.gnu.org/licenses/>.
(define-module (guix-data-service metrics)
#:use-module (ice-9 match)
#:use-module (squee)
#:export (fetch-high-level-table-size-metrics
fetch-pg-stat-user-tables-metrics))
(define (fetch-high-level-table-size-metrics conn)
;; Adapted from https://wiki.postgresql.org/wiki/Disk_Usage
(define query
"
WITH RECURSIVE pg_inherit(inhrelid, inhparent) AS (
SELECT inhrelid, inhparent
FROM pg_inherits
UNION
SELECT child.inhrelid, parent.inhparent
FROM pg_inherit child, pg_inherits parent
WHERE child.inhparent = parent.inhrelid
), pg_inherit_short AS (
SELECT *
FROM pg_inherit
WHERE inhparent NOT IN (SELECT inhrelid FROM pg_inherit)
)
SELECT table_name,
row_estimate,
table_bytes,
index_bytes,
toast_bytes
FROM (
SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes
FROM (
SELECT c.oid,
nspname AS table_schema,
relname AS table_name,
SUM(c.reltuples) OVER (partition BY parent) AS row_estimate,
SUM(pg_total_relation_size(c.oid)) OVER (partition BY parent) AS total_bytes,
SUM(pg_indexes_size(c.oid)) OVER (partition BY parent) AS index_bytes,
SUM(pg_total_relation_size(reltoastrelid)) OVER (partition BY parent) AS toast_bytes,
parent
FROM (
SELECT pg_class.oid,
reltuples,
relname,
relnamespace,
pg_class.reltoastrelid,
COALESCE(inhparent, pg_class.oid) parent
FROM pg_class
LEFT JOIN pg_inherit_short ON inhrelid = oid
WHERE relkind IN ('r', 'p')
) c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
) a
WHERE oid = parent
AND table_schema = 'guix_data_service'
) a;")
(map (match-lambda
((name row-estimate table-bytes index-bytes toast-bytes)
(list name
(or (string->number row-estimate) 0)
(or (string->number table-bytes) 0)
(or (string->number index-bytes) 0)
(or (string->number toast-bytes) 0))))
(exec-query conn query)))
(define (fetch-pg-stat-user-tables-metrics conn)
(define query
"
SELECT relname, seq_scan, seq_tup_read,
COALESCE(idx_scan, 0), COALESCE(idx_tup_fetch, 0),
n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd,
n_live_tup, n_dead_tup, n_mod_since_analyze,
COALESCE(extract(epoch from last_vacuum), 0),
COALESCE(extract(epoch from last_autovacuum), 0),
COALESCE(extract(epoch from last_analyze), 0),
COALESCE(extract(epoch from last_autoanalyze), 0),
vacuum_count, autovacuum_count, analyze_count, autoanalyze_count
FROM pg_stat_user_tables")
(map (match-lambda
((relname seq-scan seq-tup-read
idx-scan idx-tup-fetch
n-tup-ins n-tup-upd n-tup-del 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)
`((name . ,relname)
(seq-scan . ,seq-scan)
(seq-tup-read . ,seq-tup-read)
(idx-scan . ,idx-scan)
(idx-tup-fetch . ,idx-tup-fetch)
(n-tup-ins . ,n-tup-ins)
(n-tup-upd . ,n-tup-upd)
(n-tup-del . ,n-tup-del)
(n-tup-hot-upd . ,n-tup-hot-upd)
(n-live-tup . ,n-live-tup)
(n-dead-tup . ,n-dead-tup)
(n-mod-since-analyze . ,n-mod-since-analyze)
(last-vacuum . ,last-vacuum)
(last-autovacuum . ,last-autovacuum)
(last-analyze . ,last-analyze)
(last-autoanalyze . ,last-autoanalyze)
(vacuum-count . ,vacuum-count)
(autovacuum-count . ,autovacuum-count)
(analyze-count . ,analyze-count)
(autoanalyze-count . ,autoanalyze-count))))
(exec-query conn query)))
|