Content-Type: multipart/alternative;
 boundary=Apple-Mail-7337F8C6-0C6A-480D-8C19-55D3F894A566
Content-Transfer-Encoding: 7bit
From: frits.hoogland@gmail.com
Mime-Version: 1.0 (1.0)
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: <0C899A03-5B27-4380-9EA9-59DE2047FDFD@gmail.com>
References: <62B9D71B-DD8C-45DE-8A15-1C1D716CA645@gmail.com>
Cc: Pavel Stehule <pavel.stehule@gmail.com>,
 pgsql-performance@lists.postgresql.org
In-Reply-To: <62B9D71B-DD8C-45DE-8A15-1C1D716CA645@gmail.com>
To: peter plachta <pplachta@gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/0C899A03-5B27-4380-9EA9-59DE2047FDFD%40gmail.com>
Precedence: bulk


--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 &lt;ppl=
achta@gmail.com&gt; 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 &lt;frits.hoogland@gmail.com&gt; 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 &lt;pavel.stehule@gmail.com&gt; 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&nbsp;18:00 odes=C3=ADlatel Frits Hoogland &lt;<a hre=
f=3D"mailto:frits.hoogland@gmail.com">frits.hoogland@gmail.com</a>&gt; 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>&nbsp;</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 &lt;<a href=3D"mailto:pavel.stehule@gmail.com" target=3D"_blank">pavel.s=
tehule@gmail.com</a>&gt; 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&nbsp;12:23 odes=C3=ADlatel Frits Hoogland &lt;<a href=3D"mailto=
:frits.hoogland@gmail.com" target=3D"_blank">frits.hoogland@gmail.com</a>&gt=
; 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>&nbsp;<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--