public inbox for [email protected]
help / color / mirror / Atom feedFrom: peter plachta <[email protected]>
To: Frits Hoogland <[email protected]>
Cc: Pavel Stehule <[email protected]>
Cc: [email protected]
Subject: Re: Any risk or overhead considerations for frequently executing queries against catalog tables?
Date: Sat, 25 Jan 2025 14:08:29 -0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
--Apple-Mail-30F8AE05-2BB8-44DC-BD5B-E8C17F40CF05
Content-Type: text/html;
charset=utf-8
Content-Transfer-Encoding: quoted-printable
<html><head><meta http-equiv=3D"content-type" content=3D"text/html; charset=3D=
utf-8"></head><body dir=3D"auto">DataDog =E2=80=94 which implements such met=
rics for Postgres - has ran into multiple issues doing this type of thing. Y=
ou may be able to search their bugs / repo to see what they were. I just can=
=E2=80=99t remember them off hand, it=E2=80=99s been a while.<div><br id=3D"=
lineBreakAtBeginningOfSignature"><div dir=3D"ltr">Sent from my iPhone</div><=
div dir=3D"ltr"><br><blockquote type=3D"cite">On Jan 25, 2025, at 12:01=E2=80=
=AFPM, Frits Hoogland <[email protected]> wrote:<br><br></block=
quote></div><blockquote type=3D"cite"><div dir=3D"ltr">=EF=BB=BF<meta http-e=
quiv=3D"content-type" content=3D"text/html; charset=3Dutf-8">I am looking at=
whether sampling key database catalog information per second would have any=
drawback whatsoever.<div>I think you're saying that you think isn't the cas=
e, except maybe for pg_database, and I figure that is because of the frozen a=
nd multi xact fields per database.</div><div><br></div><div>If the database c=
lient application is too unpredictable to know what SQL it will produce, the=
n having runtime data available at that granularity, so it can be reasonably=
constructed what is going on is very convenient and allows tremendous insig=
ht. It would also allow usage of the waitevents to spot any weird behavior, s=
uch as short-lived peaks. (pg_stat_statements can do that on a busy database=
, for example).</div><div>And if there is no known drawback, if such a low i=
nterval can be organized: why not? I am not saying you are doing it wrong, t=
his is about trying to figure out what are the borders of what would be tech=
nically possible without unreasonably affecting the database, a thought expe=
riment.</div><div><br></div><div>If course the gathered data needs to be org=
anized so that you don't swamp in it, and it shouldn't lead to the monitorin=
g data swamping the system, either in memory or on disk, but that is a given=
.</div><div><br></div><div>Why would per second be too much for locks? Is th=
ere overhead to select from pg_locks, or pg_blocking_pids()?</div><div><br><=
/div><div>Again, please realise I am happy and appreciative of the time you t=
ake, I am toying with the above described idea.</div><div><br id=3D"lineBrea=
kAtBeginningOfMessage"><div>
<div dir=3D"auto" style=3D"caret-color: rgb(0, 0, 0); color: rgb(0, 0, 0); l=
etter-spacing: normal; text-align: start; text-indent: 0px; text-transform: n=
one; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px;=
text-decoration: none; word-wrap: break-word; -webkit-nbsp-mode: space; lin=
e-break: after-white-space;"><div><b>Frits Hoogland</b></div><div><br></div>=
</div><br class=3D"Apple-interchange-newline"><br class=3D"Apple-interchange=
-newline">
</div>
<div><br><blockquote type=3D"cite"><div>On 25 Jan 2025, at 19:18, Pavel Steh=
ule <[email protected]> wrote:</div><br class=3D"Apple-interchan=
ge-newline"><div><div dir=3D"ltr"><div dir=3D"ltr"><div>Hi<br></div><br><div=
class=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail=
_attr">so 25. 1. 2025 v 18:00 odes=C3=ADlatel Frits Hoogland <<a hre=
f=3D"mailto:[email protected]">[email protected]</a>> napsa=
l:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.=
8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div>Thank you P=
avel, that is really useful. I can imagine other people thinking about getti=
ng fine grained data from postgres might wonder the same as I do about this.=
<div>And really from a computer's perspective I would say that once a second=
isn't really a high frequency?</div></div></blockquote><div><br></div><div>=
I usually work with minute sampling and usually it is good enough (statistic=
s are cumulative, so you can lose the timestamp, but you never lose data.</d=
iv><div><br></div><div>Only when we try to investigate some special case, th=
en I use second sampling. When you investigate lock issues, then seconds are=
too much<br></div><div><br></div><div>Regards</div><div><br></div><div>Pave=
l<br></div><div> </div><blockquote class=3D"gmail_quote" style=3D"margi=
n:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"=
><div><div>If I time the amount of time that these queries take, it's around=
20ms (local connection), so there is a relative long time of all the object=
s including pg_database are not actively queried.<br><div><br></div><div>I g=
it grepped the sourcecode, it seems that there is a rowexclusive lock for pg=
_database manipulation in case of addition, removal and change of a database=
in dbcommands.c, but I do think your reasoning is based on the columns datf=
rozenxid and datminmxid?</div><div><br></div><div>There is a lock for updati=
ng the frozenxid and mxid for a database in (vacuum.c:LockDatabaseFrozenIds,=
ExclusiveLock), but it seems a select should play nice with that?</div><div=
><br></div><div>btw, it's interesting to see that both datfrozenxid and datm=
inmxid are in place updated, with no read consistency provided.</div><div><b=
r id=3D"m_540779283335872413lineBreakAtBeginningOfMessage"><div>
<div dir=3D"auto" style=3D"letter-spacing: normal; text-align: start; text-i=
ndent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; te=
xt-decoration: none;"><div><b>Frits Hoogland</b></div><div><br></div></div><=
br><br>
</div>
<div><br><blockquote type=3D"cite"><div>On 25 Jan 2025, at 14:32, Pavel Steh=
ule <<a href=3D"mailto:[email protected]" target=3D"_blank">pavel.s=
[email protected]</a>> wrote:</div><br><div><div dir=3D"ltr"><div>Hi<br></=
div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">so 2=
5. 1. 2025 v 12:23 odes=C3=ADlatel Frits Hoogland <<a href=3D"mailto=
:[email protected]" target=3D"_blank">[email protected]</a>>=
; napsal:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px=
0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div>For=
monitoring database behaviour and trying to build an history of activity, i=
f I would create an application that creates a single connection and execute=
something like:<div>select * from pg_stat_activity;</div><div>select * from=
pg_stat_database;</div><div>select * from pg_stat_bgwriter;</div><div>selec=
t * from pg_stat_wal;</div><div>select * from pg_settings;</div><div>select *=
from pg_database;</div><div>For which the query is prepared, and execute th=
at every 1 second, would there be any realistic danger or overhead that shou=
ld be considered?</div><div>My thinking is that the data for these catalogs a=
re all in shared memory and when executed serially and do not cause any sign=
ificant resources to be taken?</div></div></blockquote><div><br></div><div>T=
he queries to all tables excluding pg_database every 1 sec will have probabl=
y zero impact to performance.</div><div><br></div><div>I am not sure about p=
g_database - it is a very important table, and your query can block operatio=
ns that need exclusive lock to this table. So theoretically, there can be so=
me impact to performance.</div><div><br></div><div>Regards</div><div><br></d=
iv><div>Pavel<br></div><div> <br></div><blockquote class=3D"gmail_quote=
" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);p=
adding-left:1ex"><div><div><br></div><div>Thanks,</div><div><br></div><div><=
div>
<div dir=3D"auto" style=3D"letter-spacing:normal;text-align:start;text-inden=
t:0px;text-transform:none;white-space:normal;word-spacing:0px;text-decoratio=
n:none"><div><b>Frits Hoogland</b></div><div><br></div></div><br><br>
</div>
<br></div></div></blockquote></div></div>
</div></blockquote></div><br></div></div></div></blockquote></div></div></di=
v>
</div></blockquote></div><br></div></div></blockquote></div></body></html>=
--Apple-Mail-30F8AE05-2BB8-44DC-BD5B-E8C17F40CF05--
view thread (9+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Any risk or overhead considerations for frequently executing queries against catalog tables?
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox