public inbox for [email protected]help / color / mirror / Atom feed
Any risk or overhead considerations for frequently executing queries against catalog tables? 9+ messages / 5 participants [nested] [flat]
* Any risk or overhead considerations for frequently executing queries against catalog tables? @ 2025-01-25 11:23 Frits Hoogland <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: Frits Hoogland @ 2025-01-25 11:23 UTC (permalink / raw) To: [email protected] For monitoring database behaviour and trying to build an history of activity, if I would create an application that creates a single connection and execute something like: select * from pg_stat_activity; select * from pg_stat_database; select * from pg_stat_bgwriter; select * from pg_stat_wal; select * from pg_settings; select * from pg_database; For which the query is prepared, and execute that every 1 second, would there be any realistic danger or overhead that should be considered? My thinking is that the data for these catalogs are all in shared memory and when executed serially and do not cause any significant resources to be taken? Thanks, Frits Hoogland ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Any risk or overhead considerations for frequently executing queries against catalog tables? @ 2025-01-25 13:32 Pavel Stehule <[email protected]> parent: Frits Hoogland <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: Pavel Stehule @ 2025-01-25 13:32 UTC (permalink / raw) To: Frits Hoogland <[email protected]>; +Cc: [email protected] Hi so 25. 1. 2025 v 12:23 odesílatel Frits Hoogland <[email protected]> napsal: > For monitoring database behaviour and trying to build an history of > activity, if I would create an application that creates a single connection > and execute something like: > select * from pg_stat_activity; > select * from pg_stat_database; > select * from pg_stat_bgwriter; > select * from pg_stat_wal; > select * from pg_settings; > select * from pg_database; > For which the query is prepared, and execute that every 1 second, would > there be any realistic danger or overhead that should be considered? > My thinking is that the data for these catalogs are all in shared memory > and when executed serially and do not cause any significant resources to be > taken? > The queries to all tables excluding pg_database every 1 sec will have probably zero impact to performance. I am not sure about pg_database - it is a very important table, and your query can block operations that need exclusive lock to this table. So theoretically, there can be some impact to performance. Regards Pavel > > Thanks, > > *Frits Hoogland* > > > > > ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Any risk or overhead considerations for frequently executing queries against catalog tables? @ 2025-01-25 16:59 Frits Hoogland <[email protected]> parent: Pavel Stehule <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: Frits Hoogland @ 2025-01-25 16:59 UTC (permalink / raw) To: Pavel Stehule <[email protected]>; +Cc: [email protected] Thank you Pavel, that is really useful. I can imagine other people thinking about getting fine grained data from postgres might wonder the same as I do about this. And really from a computer's perspective I would say that once a second isn't really a high frequency? 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 objects including pg_database are not actively queried. I git 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 datfrozenxid and datminmxid? There is a lock for updating the frozenxid and mxid for a database in (vacuum.c:LockDatabaseFrozenIds, ExclusiveLock), but it seems a select should play nice with that? btw, it's interesting to see that both datfrozenxid and datminmxid are in place updated, with no read consistency provided. Frits Hoogland > On 25 Jan 2025, at 14:32, Pavel Stehule <[email protected]> wrote: > > Hi > > so 25. 1. 2025 v 12:23 odesílatel Frits Hoogland <[email protected] <mailto:[email protected]>> napsal: >> For monitoring database behaviour and trying to build an history of activity, if I would create an application that creates a single connection and execute something like: >> select * from pg_stat_activity; >> select * from pg_stat_database; >> select * from pg_stat_bgwriter; >> select * from pg_stat_wal; >> select * from pg_settings; >> select * from pg_database; >> For which the query is prepared, and execute that every 1 second, would there be any realistic danger or overhead that should be considered? >> My thinking is that the data for these catalogs are all in shared memory and when executed serially and do not cause any significant resources to be taken? > > The queries to all tables excluding pg_database every 1 sec will have probably zero impact to performance. > > I am not sure about pg_database - it is a very important table, and your query can block operations that need exclusive lock to this table. So theoretically, there can be some impact to performance. > > Regards > > Pavel > >> >> Thanks, >> >> Frits Hoogland >> >> >> >> ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Any risk or overhead considerations for frequently executing queries against catalog tables? @ 2025-01-25 18:18 Pavel Stehule <[email protected]> parent: Frits Hoogland <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: Pavel Stehule @ 2025-01-25 18:18 UTC (permalink / raw) To: Frits Hoogland <[email protected]>; +Cc: [email protected] Hi so 25. 1. 2025 v 18:00 odesílatel Frits Hoogland <[email protected]> napsal: > Thank you Pavel, that is really useful. I can imagine other people > thinking about getting fine grained data from postgres might wonder the > same as I do about this. > And really from a computer's perspective I would say that once a second > isn't really a high frequency? > I usually work with minute sampling and usually it is good enough (statistics are cumulative, so you can lose the timestamp, but you never lose data. Only when we try to investigate some special case, then I use second sampling. When you investigate lock issues, then seconds are too much Regards Pavel > 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 objects > including pg_database are not actively queried. > > I git 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 datfrozenxid and datminmxid? > > There is a lock for updating the frozenxid and mxid for a database in > (vacuum.c:LockDatabaseFrozenIds, ExclusiveLock), but it seems a select > should play nice with that? > > btw, it's interesting to see that both datfrozenxid and datminmxid are in > place updated, with no read consistency provided. > > *Frits Hoogland* > > > > > On 25 Jan 2025, at 14:32, Pavel Stehule <[email protected]> wrote: > > Hi > > so 25. 1. 2025 v 12:23 odesílatel Frits Hoogland <[email protected]> > napsal: > >> For monitoring database behaviour and trying to build an history of >> activity, if I would create an application that creates a single connection >> and execute something like: >> select * from pg_stat_activity; >> select * from pg_stat_database; >> select * from pg_stat_bgwriter; >> select * from pg_stat_wal; >> select * from pg_settings; >> select * from pg_database; >> For which the query is prepared, and execute that every 1 second, would >> there be any realistic danger or overhead that should be considered? >> My thinking is that the data for these catalogs are all in shared memory >> and when executed serially and do not cause any significant resources to be >> taken? >> > > The queries to all tables excluding pg_database every 1 sec will have > probably zero impact to performance. > > I am not sure about pg_database - it is a very important table, and your > query can block operations that need exclusive lock to this table. So > theoretically, there can be some impact to performance. > > Regards > > Pavel > > >> >> Thanks, >> >> *Frits Hoogland* >> >> >> >> >> > ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Any risk or overhead considerations for frequently executing queries against catalog tables? @ 2025-01-25 20:01 Frits Hoogland <[email protected]> parent: Pavel Stehule <[email protected]> 0 siblings, 2 replies; 9+ messages in thread From: Frits Hoogland @ 2025-01-25 20:01 UTC (permalink / raw) To: Pavel Stehule <[email protected]>; +Cc: [email protected] I am looking at whether sampling key database catalog information per second would have any drawback whatsoever. I think you're saying that you think isn't the case, except maybe for pg_database, and I figure that is because of the frozen and multi xact fields per database. If the database client application is too unpredictable to know what SQL it will produce, then having runtime data available at that granularity, so it can be reasonably constructed what is going on is very convenient and allows tremendous 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 busy database, for example). And if there is no known drawback, if such a low interval can be organized: why not? I am not saying you are doing it wrong, this is about trying to figure out what are the borders of what would be technically possible without unreasonably affecting the database, a thought experiment. 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 is a given. Why would per second be too much for locks? Is there overhead to select from pg_locks, or pg_blocking_pids()? Again, please realise I am happy and appreciative of the time you take, I am toying with the above described idea. Frits Hoogland > On 25 Jan 2025, at 19:18, Pavel Stehule <[email protected]> wrote: > > Hi > > so 25. 1. 2025 v 18:00 odesílatel Frits Hoogland <[email protected] <mailto:[email protected]>> napsal: >> Thank you Pavel, that is really useful. I can imagine other people thinking about getting fine grained data from postgres might wonder the same as I do about this. >> And really from a computer's perspective I would say that once a second isn't really a high frequency? > > I usually work with minute sampling and usually it is good enough (statistics are cumulative, so you can lose the timestamp, but you never lose data. > > Only when we try to investigate some special case, then I use second sampling. When you investigate lock issues, then seconds are too much > > Regards > > Pavel > >> 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 objects including pg_database are not actively queried. >> >> I git 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 datfrozenxid and datminmxid? >> >> There is a lock for updating the frozenxid and mxid for a database in (vacuum.c:LockDatabaseFrozenIds, ExclusiveLock), but it seems a select should play nice with that? >> >> btw, it's interesting to see that both datfrozenxid and datminmxid are in place updated, with no read consistency provided. >> >> Frits Hoogland >> >> >> >> >>> On 25 Jan 2025, at 14:32, Pavel Stehule <[email protected] <mailto:[email protected]>> wrote: >>> >>> Hi >>> >>> so 25. 1. 2025 v 12:23 odesílatel Frits Hoogland <[email protected] <mailto:[email protected]>> napsal: >>>> For monitoring database behaviour and trying to build an history of activity, if I would create an application that creates a single connection and execute something like: >>>> select * from pg_stat_activity; >>>> select * from pg_stat_database; >>>> select * from pg_stat_bgwriter; >>>> select * from pg_stat_wal; >>>> select * from pg_settings; >>>> select * from pg_database; >>>> For which the query is prepared, and execute that every 1 second, would there be any realistic danger or overhead that should be considered? >>>> My thinking is that the data for these catalogs are all in shared memory and when executed serially and do not cause any significant resources to be taken? >>> >>> The queries to all tables excluding pg_database every 1 sec will have probably zero impact to performance. >>> >>> I am not sure about pg_database - it is a very important table, and your query can block operations that need exclusive lock to this table. So theoretically, there can be some impact to performance. >>> >>> Regards >>> >>> Pavel >>> >>>> >>>> Thanks, >>>> >>>> Frits Hoogland >>>> >>>> >>>> >>>> >> ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Any risk or overhead considerations for frequently executing queries against catalog tables? @ 2025-01-25 22:08 peter plachta <[email protected]> parent: Frits Hoogland <[email protected]> 1 sibling, 1 reply; 9+ messages in thread From: peter plachta @ 2025-01-25 22:08 UTC (permalink / raw) To: Frits Hoogland <[email protected]>; +Cc: Pavel Stehule <[email protected]>; [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-- ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Any risk or overhead considerations for frequently executing queries against catalog tables? @ 2025-01-25 22:42 [email protected] parent: peter plachta <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: [email protected] @ 2025-01-25 22:42 UTC (permalink / raw) To: peter plachta <[email protected]>; +Cc: Pavel Stehule <[email protected]>; [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-- ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Any risk or overhead considerations for frequently executing queries against catalog tables? @ 2025-01-26 06:43 Pavel Stehule <[email protected]> parent: Frits Hoogland <[email protected]> 1 sibling, 0 replies; 9+ messages in thread From: Pavel Stehule @ 2025-01-26 06:43 UTC (permalink / raw) To: Frits Hoogland <[email protected]>; +Cc: [email protected] so 25. 1. 2025 v 21:01 odesílatel Frits Hoogland <[email protected]> napsal: > I am looking at whether sampling key database catalog information per > second would have any drawback whatsoever. > I think you're saying that you think isn't the case, except maybe for > pg_database, and I figure that is because of the frozen and multi xact > fields per database. > > If the database client application is too unpredictable to know what SQL > it will produce, then having runtime data available at that granularity, so > it can be reasonably constructed what is going on is very convenient and > allows tremendous 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 busy database, for example). > And if there is no known drawback, if such a low interval can be > organized: why not? I am not saying you are doing it wrong, this is about > trying to figure out what are the borders of what would be technically > possible without unreasonably affecting the database, a thought experiment. > > 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 is a given. > > Why would per second be too much for locks? Is there overhead to select > from pg_locks, or pg_blocking_pids()? > when you have a query about 10ms, then the lock 50ms is important overhead. Surely queries to pg_locks are queries like any other - there is a lot of overhead with planner, executor, and locking. Fortunately, it is almost CPU related. > > Again, please realise I am happy and appreciative of the time you take, I > am toying with the above described idea. > > *Frits Hoogland* > > > > > On 25 Jan 2025, at 19:18, Pavel Stehule <[email protected]> wrote: > > Hi > > so 25. 1. 2025 v 18:00 odesílatel Frits Hoogland <[email protected]> > napsal: > >> Thank you Pavel, that is really useful. I can imagine other people >> thinking about getting fine grained data from postgres might wonder the >> same as I do about this. >> And really from a computer's perspective I would say that once a second >> isn't really a high frequency? >> > > I usually work with minute sampling and usually it is good enough > (statistics are cumulative, so you can lose the timestamp, but you never > lose data. > > Only when we try to investigate some special case, then I use second > sampling. When you investigate lock issues, then seconds are too much > > Regards > > Pavel > > >> 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 objects >> including pg_database are not actively queried. >> >> I git 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 datfrozenxid and datminmxid? >> >> There is a lock for updating the frozenxid and mxid for a database in >> (vacuum.c:LockDatabaseFrozenIds, ExclusiveLock), but it seems a select >> should play nice with that? >> >> btw, it's interesting to see that both datfrozenxid and datminmxid are in >> place updated, with no read consistency provided. >> >> *Frits Hoogland* >> >> >> >> >> On 25 Jan 2025, at 14:32, Pavel Stehule <[email protected]> wrote: >> >> Hi >> >> so 25. 1. 2025 v 12:23 odesílatel Frits Hoogland < >> [email protected]> napsal: >> >>> For monitoring database behaviour and trying to build an history of >>> activity, if I would create an application that creates a single connection >>> and execute something like: >>> select * from pg_stat_activity; >>> select * from pg_stat_database; >>> select * from pg_stat_bgwriter; >>> select * from pg_stat_wal; >>> select * from pg_settings; >>> select * from pg_database; >>> For which the query is prepared, and execute that every 1 second, would >>> there be any realistic danger or overhead that should be considered? >>> My thinking is that the data for these catalogs are all in shared memory >>> and when executed serially and do not cause any significant resources to be >>> taken? >>> >> >> The queries to all tables excluding pg_database every 1 sec will have >> probably zero impact to performance. >> >> I am not sure about pg_database - it is a very important table, and your >> query can block operations that need exclusive lock to this table. So >> theoretically, there can be some impact to performance. >> >> Regards >> >> Pavel >> >> >>> >>> Thanks, >>> >>> *Frits Hoogland* >>> >>> >>> >>> >>> >> > ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Any risk or overhead considerations for frequently executing queries against catalog tables? @ 2025-01-26 17:56 Jeremy Schneider <[email protected]> parent: [email protected] 0 siblings, 0 replies; 9+ messages in thread From: Jeremy Schneider @ 2025-01-26 17:56 UTC (permalink / raw) To: [email protected]; +Cc: peter plachta <[email protected]>; Pavel Stehule <[email protected]>; [email protected] > On Jan 25, 2025, at 2:42 PM, [email protected] wrote: > > One thing I found is that because certain statistics are provided after a query has run, measuring them in a fine grained way shows a peak for something that in reality is taking place over a period of time. +1 I have also encountered this. I think most stats (pg_stat_statements, block hit/read counts, object access counts, etc) are all updated at query completion time. I think it’s related to the architecture of the statistics collector. The main thing I’ve seen change in real time is fields in pg_stat_activity (like wait events). As a result, I’m not sure how useful per-second stats on the other views are. Mainly I think it will just tell you which second the query completed, and that’s easy to get from logging (db or app tier). stats from the other views tend to be most useful when normalized over minutes or hours. I wrote out a Postgres observability wishlist two weeks ago and much of it would probably best be added to pg_stat_activity [1]. I wonder if we might eventually want the underlying pg_stat_activity function to have an argument to exclude pulling data that’s less frequently accessed, similar to how the pg_stat_statements function can exclude query text to be more efficient. -Jeremy 1: https://ardentperf.com/2025/01/08/postgres-per-connection-statistics/ ^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2025-01-26 17:56 UTC | newest] Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-01-25 11:23 Any risk or overhead considerations for frequently executing queries against catalog tables? Frits Hoogland <[email protected]> 2025-01-25 13:32 ` Pavel Stehule <[email protected]> 2025-01-25 16:59 ` Frits Hoogland <[email protected]> 2025-01-25 18:18 ` Pavel Stehule <[email protected]> 2025-01-25 20:01 ` Frits Hoogland <[email protected]> 2025-01-25 22:08 ` peter plachta <[email protected]> 2025-01-25 22:42 ` [email protected] 2025-01-26 17:56 ` Jeremy Schneider <[email protected]> 2025-01-26 06:43 ` Pavel Stehule <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox