Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit488718f

Browse files
Added a metric for multixact members folder size
1 parenta9f7af4 commit488718f

File tree

4 files changed

+258
-5
lines changed

4 files changed

+258
-5
lines changed

‎README.md‎

Lines changed: 17 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -116,11 +116,27 @@ join pg_namespace n on n.oid = c.relnamespace
116116
join pg_attribute aona.attrelid=s.starelidanda.attnum=s.staattnum
117117
wherea.attnum>0and nota.attisdropped;
118118

119-
grantselectonpublic.pg_statistic topg_monitor;
119+
grantselectonpublic.pg_statistic topostgres_ai_mon;
120120
alteruser postgres_ai_monset search_path="$user", public, pg_catalog;
121121
commit;
122122
```
123123

124+
If your database is hosted on AWS:
125+
126+
```sql
127+
create extension if not exists rds_tools;
128+
grant executeon functionrds_tools.pg_ls_multixactdir() to postgres_ai_mon;
129+
```
130+
131+
If you're self-hosting your database:
132+
133+
```sql
134+
grant executeon function pg_stat_file(text) to postgres_ai_mon;
135+
grant executeon function pg_stat_file(text,boolean) to postgres_ai_mon;
136+
grant executeon function pg_ls_dir(text) to postgres_ai_mon;
137+
grant executeon function pg_ls_dir(text,boolean,boolean) to postgres_ai_mon;
138+
```
139+
124140
**One command setup:**
125141

126142
```bash

‎config/grafana/dashboards/Dashboard_1_Node_performance_overview.json‎

Lines changed: 142 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -18,7 +18,7 @@
1818
"editable":true,
1919
"fiscalYearStartMonth":0,
2020
"graphTooltip":1,
21-
"id":2,
21+
"id":1,
2222
"links": [],
2323
"panels": [
2424
{
@@ -4588,6 +4588,145 @@
45884588
],
45894589
"type":"timeseries"
45904590
},
4591+
{
4592+
"datasource": {
4593+
"type":"datasource",
4594+
"uid":"-- Mixed --"
4595+
},
4596+
"fieldConfig": {
4597+
"defaults": {
4598+
"color": {
4599+
"mode":"palette-classic"
4600+
},
4601+
"custom": {
4602+
"axisBorderShow":false,
4603+
"axisCenteredZero":false,
4604+
"axisColorMode":"text",
4605+
"axisLabel":"",
4606+
"axisPlacement":"auto",
4607+
"axisSoftMin":0,
4608+
"barAlignment":0,
4609+
"barWidthFactor":0.6,
4610+
"drawStyle":"line",
4611+
"fillOpacity":0,
4612+
"gradientMode":"none",
4613+
"hideFrom": {
4614+
"legend":false,
4615+
"tooltip":false,
4616+
"viz":false
4617+
},
4618+
"insertNulls":false,
4619+
"lineInterpolation":"linear",
4620+
"lineWidth":1,
4621+
"pointSize":5,
4622+
"scaleDistribution": {
4623+
"type":"linear"
4624+
},
4625+
"showPoints":"auto",
4626+
"spanNulls":true,
4627+
"stacking": {
4628+
"group":"A",
4629+
"mode":"none"
4630+
},
4631+
"thresholdsStyle": {
4632+
"mode":"off"
4633+
}
4634+
},
4635+
"mappings": [],
4636+
"thresholds": {
4637+
"mode":"absolute",
4638+
"steps": [
4639+
{
4640+
"color":"green"
4641+
},
4642+
{
4643+
"color":"red",
4644+
"value":80
4645+
}
4646+
]
4647+
},
4648+
"unit":"bytes"
4649+
},
4650+
"overrides": [
4651+
{
4652+
"matcher": {
4653+
"id":"byName",
4654+
"options":"Safe threshold"
4655+
},
4656+
"properties": [
4657+
{
4658+
"id":"custom.lineStyle",
4659+
"value": {
4660+
"dash": [
4661+
20,
4662+
10
4663+
],
4664+
"fill":"dash"
4665+
}
4666+
},
4667+
{
4668+
"id":"color",
4669+
"value": {
4670+
"fixedColor":"dark-red",
4671+
"mode":"fixed"
4672+
}
4673+
}
4674+
]
4675+
}
4676+
]
4677+
},
4678+
"gridPos": {
4679+
"h":12,
4680+
"w":24,
4681+
"x":0,
4682+
"y":197
4683+
},
4684+
"id":46,
4685+
"options": {
4686+
"legend": {
4687+
"calcs": [
4688+
"max"
4689+
],
4690+
"displayMode":"table",
4691+
"placement":"bottom",
4692+
"showLegend":true
4693+
},
4694+
"tooltip": {
4695+
"hideZeros":false,
4696+
"mode":"single",
4697+
"sort":"none"
4698+
}
4699+
},
4700+
"pluginVersion":"12.0.2",
4701+
"targets": [
4702+
{
4703+
"datasource": {
4704+
"type":"prometheus",
4705+
"uid":"P7A0D6631BB10B34F"
4706+
},
4707+
"editorMode":"code",
4708+
"expr":"pgwatch_multixact_size_members_bytes{cluster=\"$cluster_name\", node_name=\"$node_name\"}",
4709+
"legendFormat":"members",
4710+
"range":true,
4711+
"refId":"A"
4712+
},
4713+
{
4714+
"datasource": {
4715+
"type":"prometheus",
4716+
"uid":"P7A0D6631BB10B34F"
4717+
},
4718+
"editorMode":"code",
4719+
"expr":"10737418240",
4720+
"hide":false,
4721+
"instant":false,
4722+
"legendFormat":"Safe threshold",
4723+
"range":true,
4724+
"refId":"B"
4725+
}
4726+
],
4727+
"title":"Multixact members folder size",
4728+
"type":"timeseries"
4729+
},
45914730
{
45924731
"fieldConfig": {
45934732
"defaults": {},
@@ -4597,7 +4736,7 @@
45974736
"h":3,
45984737
"w":24,
45994738
"x":0,
4600-
"y":197
4739+
"y":209
46014740
},
46024741
"id":40,
46034742
"options": {
@@ -4683,5 +4822,5 @@
46834822
"timezone":"utc",
46844823
"title":"01. Single node performance overview (high-level)",
46854824
"uid":"f90500a0-a12e-4081-a2f0-07ed96f27915",
4686-
"version":3
4825+
"version":4
46874826
}

‎config/pgwatch-prometheus/metrics.yml‎

Lines changed: 95 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2200,6 +2200,100 @@ metrics:
22002200
gauges:
22012201
-'*'
22022202
statement_timeout_seconds:15
2203+
2204+
multixact_size:
2205+
sqls:
2206+
11:|
2207+
with env as (
2208+
select
2209+
exists (
2210+
select 1
2211+
from pg_proc p
2212+
join pg_namespace n on n.oid = p.pronamespace
2213+
where p.proname = 'pg_ls_multixactdir' and n.nspname = 'rds_tools'
2214+
) as has_rds_fn,
2215+
coalesce(pg_has_role('pg_read_server_files','usage'), false) as has_read_files,
2216+
(select rolsuper from pg_roles where rolname = current_user) as is_super,
2217+
exists (select 1 from pg_proc where proname = 'pg_ls_dir') as has_ls,
2218+
exists (select 1 from pg_proc where proname = 'pg_stat_file') as has_stat
2219+
),
2220+
can_local as (
2221+
select (has_ls and has_stat and (has_read_files or is_super)) as ok from env
2222+
),
2223+
rds_probe_xml as (
2224+
select query_to_xml($q$
2225+
with files as (
2226+
select name, size
2227+
from rds_tools.pg_ls_multixactdir()
2228+
),
2229+
members as (
2230+
select sum(size)::bigint as sz from files where name like 'pg_multixact/members%'
2231+
),
2232+
offsets as (
2233+
select sum(size)::bigint as sz from files where name like 'pg_multixact/offsets%'
2234+
),
2235+
has_rows as (
2236+
select exists(select 1 from files where name like 'pg_multixact/%') as any_rows
2237+
)
2238+
select
2239+
case when (select any_rows from has_rows) then coalesce((select sz from members), 0) end as members_bytes,
2240+
case when (select any_rows from has_rows) then coalesce((select sz from offsets), 0) end as offsets_bytes,
2241+
case when (select any_rows from has_rows) then 0 else 1 end as status_code
2242+
$q$, true, true, '') as x
2243+
where (select has_rds_fn from env)
2244+
),
2245+
local_probe_xml as (
2246+
select query_to_xml($q$
2247+
with dirs as (
2248+
select
2249+
(pg_stat_file('pg_multixact/members', true)).isdir as has_members,
2250+
(pg_stat_file('pg_multixact/offsets', true)).isdir as has_offsets
2251+
),
2252+
flags as (
2253+
select ((select has_members from dirs) or (select has_offsets from dirs)) as has_any
2254+
),
2255+
members as (
2256+
select sum((pg_stat_file(format('pg_multixact/members/%s', d), true)).size)::bigint as sz
2257+
from pg_ls_dir('pg_multixact/members') as d(d)
2258+
where (select has_members from dirs)
2259+
),
2260+
offsets as (
2261+
select sum((pg_stat_file(format('pg_multixact/offsets/%s', d), true)).size)::bigint as sz
2262+
from pg_ls_dir('pg_multixact/offsets') as d(d)
2263+
where (select has_offsets from dirs)
2264+
)
2265+
select
2266+
case when (select has_any from flags) then coalesce((select sz from members), 0) end as members_bytes,
2267+
case when (select has_any from flags) then coalesce((select sz from offsets), 0) end as offsets_bytes,
2268+
case when (select has_any from flags) then 0 else 1 end as status_code
2269+
$q$, true, true, '') as x
2270+
where not (select has_rds_fn from env) and (select ok from can_local)
2271+
),
2272+
picked as (
2273+
select * from rds_probe_xml
2274+
union all
2275+
select * from local_probe_xml
2276+
limit 1
2277+
),
2278+
parsed as (
2279+
select
2280+
(xpath('//members_bytes/text()', x))[1]::text::bigint as members_bytes,
2281+
(xpath('//offsets_bytes/text()', x))[1]::text::bigint as offsets_bytes,
2282+
(xpath('//status_code/text()', x))[1]::text::int as status_code
2283+
from picked
2284+
)
2285+
select * from parsed
2286+
union all
2287+
select
2288+
null::bigint as members_bytes,
2289+
null::bigint as offsets_bytes,
2290+
2::int as status_code
2291+
where not exists (select 1 from parsed);
2292+
gauges:
2293+
-members_bytes
2294+
-offsets_bytes
2295+
-status_code
2296+
statement_timeout_seconds:15
22032297

22042298
presets:
22052299
full:
@@ -2244,6 +2338,7 @@ presets:
22442338
stats_reset:3600
22452339
archive_lag:15
22462340
pg_vacuum_progress:30
2341+
multixact_size:300
22472342
pg_index_pilot:
22482343
metrics:
22492344
pg_index_pilot:30

‎config/target-db/init.sql‎

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -44,6 +44,9 @@ GRANT SELECT ON pg_stat_database TO monitor;
4444
GRANTSELECTON pg_stat_user_tables TO monitor;
4545
-- Grant pg_monitor role to monitor user for enhanced monitoring capabilities
4646
GRANT pg_monitor TO monitor;
47-
47+
GRANT EXECUTEON FUNCTION pg_stat_file(text) TO monitor;
48+
GRANT EXECUTEON FUNCTION pg_stat_file(text,boolean) TO monitor;
49+
GRANT EXECUTEON FUNCTION pg_ls_dir(text) TO monitor;
50+
GRANT EXECUTEON FUNCTION pg_ls_dir(text,boolean,boolean) TO monitor;
4851
-- Set search path for the monitor user
4952
ALTERUSER monitorSET search_path="$user", public, pg_catalog;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp