public inbox for [email protected]
help / color / mirror / Atom feedFrom: [email protected]
To: peter plachta <[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 23:42:02 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
--Apple-Mail-7337F8C6-0C6A-480D-8C19-55D3F894A566
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">Thank you, I will look, very interesting!<d=
iv><br></div><div>One thing I found is that because certain statistics are p=
rovided after a query has run, measuring them in a fine grained way shows a p=
eak for something that in reality is taking place over a period of time. &nb=
sp;</div><div><br></div><div>If anyone has a url of where this can be found,=
I would be very grateful!</div><div><br id=3D"lineBreakAtBeginningOfSignatu=
re"><div dir=3D"ltr">Verstuurd vanaf mijn iPhone</div><div dir=3D"ltr"><br><=
blockquote type=3D"cite">Op 25 jan 2025 om 23:08 heeft peter plachta <ppl=
[email protected]> het volgende geschreven:<br><br></blockquote></div><bloc=
kquote type=3D"cite"><div dir=3D"ltr">=EF=BB=BF<meta http-equiv=3D"content-t=
ype" content=3D"text/html; charset=3Dutf-8">DataDog =E2=80=94 which implemen=
ts such metrics for Postgres - has ran into multiple issues doing this type o=
f thing. You 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 iPho=
ne</div><div dir=3D"ltr"><br><blockquote type=3D"cite">On Jan 25, 2025, at 1=
2:01=E2=80=AFPM, Frits Hoogland <[email protected]> wrote:<br><=
br></blockquote></div><blockquote type=3D"cite"><div dir=3D"ltr">=EF=BB=BF<m=
eta http-equiv=3D"content-type" content=3D"text/html; charset=3Dutf-8">I am l=
ooking 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 case, except maybe for pg_database, and I figure that is because of th=
e frozen and multi xact fields per database.</div><div><br></div><div>If the=
database client application is too unpredictable to know what SQL it will p=
roduce, then having runtime data available at that granularity, so it can be=
reasonably constructed what is going on is very convenient and allows treme=
ndous insight. It would also allow usage of the waitevents to spot any weird=
behavior, such as short-lived peaks. (pg_stat_statements can do that on a b=
usy database, for example).</div><div>And if there is no known drawback, if s=
uch a low interval can be organized: why not? I am not saying you are doing i=
t wrong, this is about trying to figure out what are the borders of what wou=
ld be technically possible without unreasonably affecting the database, a th=
ought experiment.</div><div><br></div><div>If course the gathered data needs=
to be organized so that you don't swamp in it, and it shouldn't lead to the=
monitoring data swamping the system, either in memory or on disk, but that i=
s a given.</div><div><br></div><div>Why would per second be too much for loc=
ks? Is there 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 t=
ime you take, I am toying with the above described idea.</div><div><br id=3D=
"lineBreakAtBeginningOfMessage"><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></div></blockquo=
te></div></body></html>=
--Apple-Mail-7337F8C6-0C6A-480D-8C19-55D3F894A566--
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