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 1tbkkl-00D7Go-6p for pgsql-performance@arkaria.postgresql.org; Sat, 25 Jan 2025 18:19:31 +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 1tbkkj-00487T-Sb for pgsql-performance@arkaria.postgresql.org; Sat, 25 Jan 2025 18:19:29 +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 1tbkkj-00487J-GX for pgsql-performance@lists.postgresql.org; Sat, 25 Jan 2025 18:19:29 +0000 Received: from mail-yb1-xb32.google.com ([2607:f8b0:4864:20::b32]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tbkkg-001U08-2d for pgsql-performance@lists.postgresql.org; Sat, 25 Jan 2025 18:19:29 +0000 Received: by mail-yb1-xb32.google.com with SMTP id 3f1490d57ef6-e398484b60bso4864371276.1 for ; Sat, 25 Jan 2025 10:19:26 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1737829165; x=1738433965; 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=h12PXQJHza7XiN2jQ+9Ti4umHThG1H7ftlRTZd8PcmE=; b=BMPAirqN8xIUwvspcSmL2WQjOVNzUrxp68u1RF8LnyR3K/Z2EBZgvALjJto2UIPxv+ T6S+mtFoS/RrpF/2axYyYZM5AU4UFjWgjaAma771K/D9eQeHch/oiw4zaUriETb7MEJS Hw9qURPoPcHIsSZrQFOHemKb032lpeHfD+0zcuCR0FXGQQEvFU1tovM/0Wt2+yHt6LJG PKM3dGzFKhuKAyJeYjAhqOety1ZUyhtLBMWJHTZg4U5eUQoDgA028GCtaFyZlYKsEccT oTq4GGzRymfHvuJ7CtK46bdIOMlf90TELM4iRwq6l5PkNgEd+cCpwii6zjMwL8VKAR2/ GUiQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1737829165; x=1738433965; 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=h12PXQJHza7XiN2jQ+9Ti4umHThG1H7ftlRTZd8PcmE=; b=fTOz8Owlj/55sbBZNdaAJjNXrGfLpLOvJoAq+hwhExuhCXJwpNkFsxPsJh8f0o2fMi L3blZ5hcM/EA0i3SEDRXHxr5GJk3Irdu8S5OM8+K0PoLwKblrGBxYwp5H/d4OkM59wgJ M3ZLmDM4o97i0+3AY4h2JqMB8Vp3Xs5dluWpQR9oOyuz2Vzy8B6tavs5fAxY1bvw40U2 GphMEMvacwkmkQcOLyal2TY9QpOy611fX7JPosOPmEi+ipPy4HgGnIbe38vEAuNvyvmb OJR0cSq0CRge650lwNiYnryJtLvESmlGQgyGscCn4fPnvHGDRUEjfWk0aISdCSH2k2lp lJzQ== X-Gm-Message-State: AOJu0YyxtOnQITYq3FUHUsGcYmg3rErUf6CmLgDR9M6E4BzBMw2YVD4B kyHUB/MPbWq8LQwRJpykfVkg94I0TkMSXe8Wk9zEbMfQiR8wwAasXAlAELP4UeeXxHAd4i/WWKr LL43ihDyoc/qHtjRd6Agx2FMlMP4= X-Gm-Gg: ASbGnctMDZr3OgwNUJ1ls69mZO1LHIIANofIQHrZBgcTS315YLN+JqGpNY1aIR7M3ox 5QpjDxBbGvhw1ZgdogJsKTq0qwEZPiNwSMd3NzApn74661r+iJjoFJ2P1/yntDQ== X-Google-Smtp-Source: AGHT+IFTo3YHvXU/isSHsW261LoMBf2OiRC5nxiQA72W5XJASrW2EZsOdLcw76UfsE8Y74CQlplE2j+BOKaQvEqbUPc= X-Received: by 2002:a05:690c:6b87:b0:6ea:916f:e897 with SMTP id 00721157ae682-6f6eb92ef46mr265125757b3.29.1737829165346; Sat, 25 Jan 2025 10:19:25 -0800 (PST) MIME-Version: 1.0 References: <0E68C6B4-391F-481E-9764-B0D2FE4CF15D@gmail.com> In-Reply-To: <0E68C6B4-391F-481E-9764-B0D2FE4CF15D@gmail.com> From: Pavel Stehule Date: Sat, 25 Jan 2025 19:18:49 +0100 X-Gm-Features: AWEUYZkF2WRzErH1CZ035mnBnFT7UoBDF82BcCeeIjAmR6Jx8KwnvFK3swYNsNM 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="00000000000014b7f7062c8be2d6" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000014b7f7062c8be2d6 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 in > 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 > napsal: > >> For monitoring database behaviour and trying to build an history of >> activity, if I would create an application that creates a single connect= ion >> 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* >> >> >> >> >> > --00000000000014b7f7062c8be2d6 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi

so 25. 1= . 2025 v=C2=A018:00 odes=C3=ADlatel Frits Hoogland <frits.hoogland@gmail.com> napsal:
<= blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-l= eft:1px solid rgb(204,204,204);padding-left:1ex">
Thank you Pavel, that= is really useful. I can imagine other people thinking about getting fine g= rained 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 is= n't really a high frequency?

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

Only when we try to investigate some special cas= e, then I use second sampling. When you investigate lock issues, then secon= ds are too much

Regards

Pavel
=C2=A0
If I time the amount of time that these queries take, = it's around 20ms (local connection), so there is a relative long time o= f all the objects including pg_database are not actively queried.
<= br>
I git grepped the sourcecode, it seems that there is a rowexc= lusive lock for pg_database manipulation in case of addition, removal and c= hange 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 ni= ce with that?

btw, it's interesting to see tha= t both datfrozenxid and datminmxid are in place updated, with no read consi= stency provided.

Frits Hoogland

<= /div>


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





--00000000000014b7f7062c8be2d6--