Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tbwOK-00ETvS-6t for pgsql-performance@arkaria.postgresql.org; Sun, 26 Jan 2025 06:45:08 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tbwNJ-006COu-IZ for pgsql-performance@arkaria.postgresql.org; Sun, 26 Jan 2025 06:44:05 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tbwNJ-006COh-7I for pgsql-performance@lists.postgresql.org; Sun, 26 Jan 2025 06:44:05 +0000 Received: from mail-yb1-xb2a.google.com ([2607:f8b0:4864:20::b2a]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tbwNF-001Yx3-38 for pgsql-performance@lists.postgresql.org; Sun, 26 Jan 2025 06:44:04 +0000 Received: by mail-yb1-xb2a.google.com with SMTP id 3f1490d57ef6-e53a91756e5so5885103276.1 for ; Sat, 25 Jan 2025 22:44:02 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1737873841; x=1738478641; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=DEfyuN4MuHJVSns4QDSDcodWdKGUNdwMNbtf/B0TExE=; b=NU+XqDVQLP5b4A/lrgT4hr+aXCmI6a40AACDdDyTg2SGF8lDddt/DJPHkFkThQ0x96 FJ765bTTR1MZu0nuvqytX+pT1vFzgxb6WHUwWoC/7JwmJ82fDFkgI6QD1H5HeDs5ZZoi nQOUYjrZBB1rET6gMooQLgv06rrw9WfDmj0Ku//79OEOR/7WJoInEM2UyW01EAP3zpxl nZe1fWoO9EoHDzr8psujoBJhwgvJ6DHMRpfTFpgwxLZK5pA6sun0QBFi5zO3V6jUmP30 O7s4etyxGwVvuSBGKznM0a7t0v+FhVWNUiJP92/TJtsa+SpxD0VXUjTnda44VWrzYP6F Q+Qg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1737873841; x=1738478641; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=DEfyuN4MuHJVSns4QDSDcodWdKGUNdwMNbtf/B0TExE=; b=uEOYccHGwYWhk0S/fsPbQy4l1d+pxtQINCgbkGhLjODb/grSpwoma4sBX6+Pvpvlhn xhxX0wIbTJabxVy78a9XmaqrU+bXRBz/iuwv0VgHD+Nm7htCt9GEkYGzFcBWR60n1oWU KpCX9wYj6+bFdVeF3P4V1ZDjzfQvp+e7/+jb3k1eERkeMeg41KrbdnvNc+x3e3a+xnaU Ir0TC6zFdZ47RMuTVQoD7RAPVcKkZx4Kn4RhF8ixmHqMXxjpF8QX5jEXQX3R93icC+L4 n6zuFxpyG/QAHJ9q9kxgPHwmB8qLyQD53a4ivG5MGN9l1TT8loZXZJbrYyXs/DUjZkFn AHNQ== X-Gm-Message-State: AOJu0Yxo1/Q2JBnyvV+Icqj6LnrCwzxXPJn48pE+EpWE0a/sASzklRzf HJvUSKnpWbUzTG/ma9P2gmW+BnBkqfyaMG9BwGKOOftPAwVfp0OcDQvpAWME+Tzja4pQEkshezG LeC0hGaTwEOLKpwLL0SHuR6WB3YM= X-Gm-Gg: ASbGnctmDbWfUCx3WJ9VxVqyFEzlGIt+K3CoSD7KsCGMJsSz+fIJmzV5mg6YoJ2AGBB ZYkNXcghUoVNl+ZjcOXxXn6xaHCZR3bMpEmU+kju9FROiMInDVNhwrSi28vAMxg== X-Google-Smtp-Source: AGHT+IGRpt5e2qJE9RYt/bS2sYsmb7Ad49jPf46kpgUSSA7N4HltshIlTdkMzQRIVxPF32V+zAmY2or0vKPk6OiRYeE= X-Received: by 2002:a05:690c:80d:b0:6f7:534b:55df with SMTP id 00721157ae682-6f7534b5672mr84036447b3.2.1737873840699; Sat, 25 Jan 2025 22:44:00 -0800 (PST) MIME-Version: 1.0 References: <0E68C6B4-391F-481E-9764-B0D2FE4CF15D@gmail.com> <1C9EF412-CED3-40F6-9081-A9464CC87C4B@gmail.com> In-Reply-To: <1C9EF412-CED3-40F6-9081-A9464CC87C4B@gmail.com> From: Pavel Stehule Date: Sun, 26 Jan 2025 07:43:24 +0100 X-Gm-Features: AWEUYZmlVdf8Kpf-lbVkZgkMsaKW2bxfCkTb4PzcT3-cQGmmWkxDXGtzd1n0a8s Message-ID: Subject: Re: Any risk or overhead considerations for frequently executing queries against catalog tables? To: Frits Hoogland Cc: pgsql-performance@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000f080fd062c96488d" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000f080fd062c96488d Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable so 25. 1. 2025 v 21:01 odes=C3=ADlatel Frits Hoogland 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 c= an > 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 experimen= t. > > 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 wrote: > > Hi > > so 25. 1. 2025 v 18:00 odes=C3=ADlatel Frits Hoogland > 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 i= n >> place updated, with no read consistency provided. >> >> *Frits Hoogland* >> >> >> >> >> On 25 Jan 2025, at 14:32, Pavel Stehule wrote: >> >> Hi >> >> so 25. 1. 2025 v 12:23 odes=C3=ADlatel Frits Hoogland < >> frits.hoogland@gmail.com> napsal: >> >>> For monitoring database behaviour and trying to build an history of >>> activity, if I would create an application that creates a single connec= tion >>> 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 memor= y >>> and when executed serially and do not cause any significant resources t= o 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* >>> >>> >>> >>> >>> >> > --000000000000f080fd062c96488d Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


so 25. 1. 2025 = v=C2=A021:01 odes=C3=ADlatel Frits Hoogland <frits.hoogland@gmail.com> napsal:
I am looking at whether samp= ling key database catalog information per second would have any drawback wh= atsoever.
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 an= d multi xact fields per database.

If the database = client application is too unpredictable to know what SQL it will produce, t= hen having runtime data available at that granularity, so it can be reasona= bly constructed what is going on is very convenient and allows tremendous i= nsight. It would also allow usage of the waitevents to spot any weird behav= ior, such as short-lived peaks. (pg_stat_statements can do that on a busy d= atabase, 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 wou= ld be technically possible without unreasonably affecting the database, a t= hought experiment.

If course the gathered data nee= ds 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 dis= k, but that is a given.

Why would per second be to= o 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.

<= div>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.
=C2=A0

Again, please realise I am happy= and appreciative of the time you take, I am toying with the above describe= d idea.

Frits Hoogland

<= /div>


On 25 Jan 2025, at 19:18, Pavel Ste= hule <pavel= .stehule@gmail.com> wrote:

Hi

so 25. 1. 2025 v=C2=A018:00 odes=C3=ADlatel Frits Hoogl= and <frits= .hoogland@gmail.com> napsal:
Thank you Pavel, that is really useful. I can imag= ine other people thinking about getting fine grained data from postgres mig= ht 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 frequen= cy?

I usually work with minute = sampling and usually it is good enough (statistics are cumulative, so you c= an lose the timestamp, but you never lose data.

On= ly 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
=C2=A0=
If I time= the amount of time that these queries take, it's around 20ms (local co= nnection), so there is a relative long time of all the objects including pg= _database are not actively queried.

I git grepped th= e sourcecode, it seems that there is a rowexclusive lock for pg_database ma= nipulation in case of addition, removal and change of a database in dbcomma= nds.c, but I do think your reasoning is based on the columns datfrozenxid a= nd datminmxid?

There is a lock for updating the fr= ozenxid and mxid for a database in (vacuum.c:LockDatabaseFrozenIds, Exclusi= veLock), but it seems a select should play nice with that?

btw, it's interesting to see that both datfrozenxid and datmin= mxid are in place updated, with no read consistency provided.
Frits Hoogland




On 25 Jan 2025, at 14:32, Pavel Ste= hule <pavel= .stehule@gmail.com> wrote:

Hi

so 25. 1. 2025 v=C2=A012:23 odes=C3=ADlatel Frits Hoogland <frits.hoogland@gmail.com= > napsal:
For monitoring database behaviour and trying to build an history of a= ctivity, if I would create an application that creates a single connection = and execute something like:
select * from pg_stat_activity;
s= elect * 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 o= verhead that should be considered?
My thinking is that the data f= or these catalogs are all in shared memory and when executed serially and d= o 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 theor= etically, there can be some impact to performance.

Regards

Pavel
=C2=A0

Thank= s,

Frits Hoogland






--000000000000f080fd062c96488d--