-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathhh_postgres_analyze
192 lines (171 loc) · 11.3 KB
/
hh_postgres_analyze
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
# Postgres Analyze
[kibana-keeper-logs](https://kibana.prod.monline/app/discover#/?_g=(filters:!(),refreshInterval:(pause:!t,value:0),time:(from:'2022-02-22T20:00:28.254Z',to:'2022-02-22T20:10:39.631Z'))&_a=(columns:!(message),filters:!(('$state':(store:appState),meta:(alias:!n,disabled:!f,index:a00a1fb0-077b-11e8-a53c-d17323260707,key:kubernetes.namespace,negate:!f,params:(query:bcn1),type:phrase),query:(match_phrase:(kubernetes.namespace:bcn1))),('$state':(store:appState),meta:(alias:!n,disabled:!f,index:a00a1fb0-077b-11e8-a53c-d17323260707,key:message,negate:!t,params:(query:'postgres%20parameters%20not%20changed'),type:phrase),query:(match_phrase:(message:'postgres%20parameters%20not%20changed'))),('$state':(store:appState),meta:(alias:!n,disabled:!f,index:a00a1fb0-077b-11e8-a53c-d17323260707,key:message,negate:!t,params:(query:'postgres%20hba%20entries%20not%20changed'),type:phrase),query:(match_phrase:(message:'postgres%20hba%20entries%20not%20changed'))),('$state':(store:appState),meta:(alias:!n,disabled:!f,index:a00a1fb0-077b-11e8-a53c-d17323260707,key:message,negate:!t,params:(query:'already%20master'),type:phrase),query:(match_phrase:(message:'already%20master'))),('$state':(store:appState),meta:(alias:!n,disabled:!f,index:a00a1fb0-077b-11e8-a53c-d17323260707,key:message,negate:!t,params:(query:'our%20db%20requested%20role%20is%20master'),type:phrase),query:(match_phrase:(message:'our%20db%20requested%20role%20is%20master'))),('$state':(store:appState),meta:(alias:!n,disabled:!f,index:a00a1fb0-077b-11e8-a53c-d17323260707,key:message,negate:!t,params:(query:'already%20standby'),type:phrase),query:(match_phrase:(message:'already%20standby'))),('$state':(store:appState),meta:(alias:!n,disabled:!f,index:a00a1fb0-077b-11e8-a53c-d17323260707,key:message,negate:!t,params:(query:'our%20db%20requested%20role%20is%20standby'),type:phrase),query:(match_phrase:(message:'our%20db%20requested%20role%20is%20standby')))),index:a00a1fb0-077b-11e8-a53c-d17323260707,interval:auto,query:(language:kuery,query:'kubernetes.pod.name:%22stolon-keeper-postgres%22'),sort:!(!('@timestamp',desc))))
[kibana-proxy-logs](https://kibana.prod.monline/app/discover#/?_g=(filters:!(),refreshInterval:(pause:!t,value:0),time:(from:'2022-02-22T20:00:28.254Z',to:'2022-02-22T20:10:39.631Z'))&_a=(columns:!(message),filters:!(('$state':(store:appState),meta:(alias:!n,disabled:!f,index:a00a1fb0-077b-11e8-a53c-d17323260707,key:kubernetes.namespace,negate:!f,params:(query:bcn1),type:phrase),query:(match_phrase:(kubernetes.namespace:bcn1))),('$state':(store:appState),meta:(alias:!n,disabled:!f,index:a00a1fb0-077b-11e8-a53c-d17323260707,key:message,negate:!t,params:(query:'postgres%20parameters%20not%20changed'),type:phrase),query:(match_phrase:(message:'postgres%20parameters%20not%20changed'))),('$state':(store:appState),meta:(alias:!n,disabled:!f,index:a00a1fb0-077b-11e8-a53c-d17323260707,key:message,negate:!t,params:(query:'postgres%20hba%20entries%20not%20changed'),type:phrase),query:(match_phrase:(message:'postgres%20hba%20entries%20not%20changed'))),('$state':(store:appState),meta:(alias:!n,disabled:!f,index:a00a1fb0-077b-11e8-a53c-d17323260707,key:message,negate:!t,params:(query:'already%20master'),type:phrase),query:(match_phrase:(message:'already%20master'))),('$state':(store:appState),meta:(alias:!n,disabled:!f,index:a00a1fb0-077b-11e8-a53c-d17323260707,key:message,negate:!t,params:(query:'our%20db%20requested%20role%20is%20master'),type:phrase),query:(match_phrase:(message:'our%20db%20requested%20role%20is%20master'))),('$state':(store:appState),meta:(alias:!n,disabled:!f,index:a00a1fb0-077b-11e8-a53c-d17323260707,key:message,negate:!t,params:(query:'already%20standby'),type:phrase),query:(match_phrase:(message:'already%20standby'))),('$state':(store:appState),meta:(alias:!n,disabled:!f,index:a00a1fb0-077b-11e8-a53c-d17323260707,key:message,negate:!t,params:(query:'our%20db%20requested%20role%20is%20standby'),type:phrase),query:(match_phrase:(message:'our%20db%20requested%20role%20is%20standby'))),('$state':(store:appState),meta:(alias:!n,disabled:!f,index:a00a1fb0-077b-11e8-a53c-d17323260707,key:message,negate:!t,params:(query:'proxying%20to%20master%20address'),type:phrase),query:(match_phrase:(message:'proxying%20to%20master%20address'))),('$state':(store:appState),meta:(alias:!n,disabled:!f,index:a00a1fb0-077b-11e8-a53c-d17323260707,key:message,negate:!t,params:(query:'master%20address'),type:phrase),query:(match_phrase:(message:'master%20address')))),index:a00a1fb0-077b-11e8-a53c-d17323260707,interval:auto,query:(language:kuery,query:'kubernetes.pod.name:%22stolon-proxy-postgres%22'),sort:!(!('@timestamp',desc))))
# Index size
```SQL
SELECT c.relname AS name,
pg_size_pretty(sum(c.relpages::bigint*8192)::bigint) AS size
FROM pg_class c
LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
AND n.nspname !~ '^pg_toast'
AND c.relkind='i'
GROUP BY c.relname
ORDER BY sum(c.relpages) DESC;
```
# Index usage
SELECT relname,idx_scan as index_usage, seq_scan,
CASE idx_scan
WHEN 0 THEN 'Insufficient data'
ELSE (100 * idx_scan / (seq_scan + idx_scan))::text
END percent_of_times_index_used,
n_live_tup rows_in_table
FROM
pg_stat_user_tables
ORDER BY
n_live_tup DESC;
# Bloat
```SQL
SELECT
schemaname, tablename, reltuples::bigint, relpages::bigint, otta,
ROUND(CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS tbloat,
relpages::bigint - otta AS wastedpages,
bs*(sml.relpages-otta)::bigint AS wastedbytes,
pg_size_pretty((bs*(relpages-otta))::bigint) AS wastedsize,
iname, ituples::bigint, ipages::bigint, iotta,
ROUND(CASE WHEN iotta=0 OR ipages=0 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 pg_size_pretty(0::bigint) ELSE pg_size_pretty((bs*(ipages-iotta))::bigint) END AS wastedisize
FROM (
SELECT
schemaname, tablename, cc.reltuples, 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,
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 (
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, (
SELECT
(SELECT current_setting('block_size')::numeric) AS bs,
CASE WHEN substring(v,12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,
CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma
FROM (SELECT version() AS v) AS foo
) AS constants
GROUP BY 1,2,3,4,5
) AS foo
) AS rs
JOIN pg_class cc ON cc.relname = rs.tablename
JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname
LEFT JOIN pg_index i ON indrelid = cc.oid
LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
) AS sml
WHERE sml.relpages - otta > 0 OR ipages - iotta > 10
ORDER BY wastedbytes DESC, wastedibytes DESC;
```
# LOCKS
```SQL
SELECT blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_statement,
blocking_activity.query AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;
```
# Autovacuum
```SQL
WITH table_opts AS (
SELECT
pg_class.oid, relname, nspname, array_to_string(reloptions, '') AS relopts
FROM
pg_class INNER JOIN pg_namespace ns ON relnamespace = ns.oid
), vacuum_settings AS (
SELECT
oid, relname, nspname,
CASE
WHEN relopts LIKE '%autovacuum_vacuum_threshold%'
THEN substring(relopts, '.*autovacuum_vacuum_threshold=([0-9.]+).*')::integer
ELSE current_setting('autovacuum_vacuum_threshold')::integer
END AS autovacuum_vacuum_threshold,
CASE
WHEN relopts LIKE '%autovacuum_vacuum_scale_factor%'
THEN substring(relopts, '.*autovacuum_vacuum_scale_factor=([0-9.]+).*')::real
ELSE current_setting('autovacuum_vacuum_scale_factor')::real
END AS autovacuum_vacuum_scale_factor
FROM
table_opts
)
SELECT
vacuum_settings.nspname AS schema,
vacuum_settings.relname AS table,
to_char(psut.last_vacuum, 'YYYY-MM-DD HH24:MI') AS last_vacuum,
to_char(psut.last_autovacuum, 'YYYY-MM-DD HH24:MI') AS last_autovacuum,
to_char(pg_class.reltuples, '9G999G999G999') AS rowcount,
to_char(psut.n_dead_tup, '9G999G999G999') AS dead_rowcount,
to_char(autovacuum_vacuum_threshold
+ (autovacuum_vacuum_scale_factor::numeric * pg_class.reltuples), '9G999G999G999') AS autovacuum_threshold,
CASE
WHEN autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor::numeric * pg_class.reltuples) < psut.n_dead_tup
THEN 'yes'
END AS expect_autovacuum
FROM
pg_stat_user_tables psut INNER JOIN pg_class ON psut.relid = pg_class.oid
INNER JOIN vacuum_settings ON pg_class.oid = vacuum_settings.oid
ORDER BY 1;
```
# LONG RUNNING QUERIES
```SQL
SELECT
pid,
now() - pg_stat_activity.query_start AS duration,
query AS query
FROM
pg_stat_activity
WHERE
pg_stat_activity.query <> ''::text
AND state <> 'idle'
AND now() - pg_stat_activity.query_start > interval '5 minutes'
ORDER BY
now() - pg_stat_activity.query_start DESC;
```
# SEQ SCAN
```SQL
SELECT relname AS name, seq_scan
FROM pg_stat_user_tables
WHERE seq_scan > 100
ORDER BY seq_scan DESC;
```
# Analyze Transaction
```SQL
tsdb analyze 01GCTTBHE13ZXMP56ERFJD0KBH
```