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 1tbmLG-00DJFK-KK for pgsql-performance@arkaria.postgresql.org; Sat, 25 Jan 2025 20:01:19 +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 1tbmLE-004WrB-WF for pgsql-performance@arkaria.postgresql.org; Sat, 25 Jan 2025 20:01:17 +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 1tbmLE-004WqH-Kh for pgsql-performance@lists.postgresql.org; Sat, 25 Jan 2025 20:01:16 +0000 Received: from mail-ej1-x62a.google.com ([2a00:1450:4864:20::62a]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tbmLB-001UlC-1i for pgsql-performance@lists.postgresql.org; Sat, 25 Jan 2025 20:01:15 +0000 Received: by mail-ej1-x62a.google.com with SMTP id a640c23a62f3a-ab633d9582aso561887166b.1 for ; Sat, 25 Jan 2025 12:01:13 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1737835273; x=1738440073; darn=lists.postgresql.org; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:from:to:cc:subject:date:message-id:reply-to; bh=okP4Ph3CLyXQu3xsHXmVTTR0+8d8poUl7Upw6oiAeks=; b=X2WZmaP7yUow1bRZUBPIy445WAYikxDgeUWNmCYOUKEhMJjwo+Smb/licWPjmgomJO UDmAHoMcOHFlpXQTmAaVpUSAa8S0bmcz3sQNHn1vVKHes4DjjLCcnI/V4sC1hEUnDp7b 12cuB+LjAJxGSUcmD1/fDsC9KxIKhkSGCBEVI5SjpRYWBif2mmy/H8g2KVbpxTGIwTfX 6c6KF9lKNM+utS1OkkYWgQVnpMQnRbhV7CNDH4EhQCIJ+lf6fGdHbc0OyRHiMoV4ArNc 1dJwkqQgWEDhCMGqb7IvGTAY1Nfox3H4ElZaMXTjKPM/pqXY6h17qQwPW1ChJJdpjl7v FUgg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1737835273; x=1738440073; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=okP4Ph3CLyXQu3xsHXmVTTR0+8d8poUl7Upw6oiAeks=; b=dElXga+cW7ol2g5DAa3apbGckLRKahQkSncCKL4EmfN7un4T+OcvANVEkJfkX93wnH 6F7QXSxkesN9tflKOWvYaHWXpji2ZG+Hv5it7pXFHxfuHdbyO142ixiBBuTIoT1bOGmo 4Jj5ZfpTqKJUUUilvmu307R12/1MTv8yOyIBh0P6LqCbuQt16mFzOorZiKXcdFnJz4T8 jH9ZUMbZCz+pPwCsj84vl5rYwobAg+zKqRo/ug33iSSgWt7hvlhHqmLpmx5w6GkT0pub EK11v2jf0qw5OnISi/JLI5VAeNRO8euTKL9K15ARRpdfxrTrBXnjAtGuzXpEXZ8v78np rXXQ== X-Gm-Message-State: AOJu0YwsP6MVLRftXI8yYiIZewxgynwsApnH0r/3ukdnI+QvKEztxxZ9 f9GdBvzg11K8PxGVerEdwLliOxlse27pSe9WiaNVx0epurCecihR X-Gm-Gg: ASbGncud/lvi+VIthYWiFDv6oUdYoCpCSu7h8VrGIceC2Y5Oc6G1Gr/u4R//CdjjmBU PJDA3FaItAsUCV3ZmNkdDMsSlyJi0wJd3a1xXJIw41voCbIUAWNMVot9WhasKudZ76B/IjmwRmk K8fHM40RRhQp0CWptVi001td9oPyDzRiR1zenAfri61ubIauSRFN0jKt2lI8LptrROzi/ZNP0Ul mziHEYdAUolO9bgJKYYa305TX6JVave91HQgz7HV5Z1KROftyAU9fV3OKH43D2nX0fD12gVnW02 AEsCPJIvEuw1FRxI3BGSeHWz6DwSOQFrLIjndi6b4TuFMfb2Bl3x8FiqfcXfa6mKQ22671u2Ub+ 0zNMxnkYO3VFP9pVY0+XI/ukuHHU2thMVM1Q+VS3a X-Google-Smtp-Source: AGHT+IH0rBE5grlN2rXs30BSXrb0tImssbO/ydc5O1CTdc5x+anfkg2qTe1L/tX5kclf6WqbcvFqAA== X-Received: by 2002:a17:907:7e95:b0:aa6:715a:75b5 with SMTP id a640c23a62f3a-ab38b44d44fmr2931713366b.46.1737835272268; Sat, 25 Jan 2025 12:01:12 -0800 (PST) Received: from smtpclient.apple (2001-1c04-0681-7700-a50d-7275-da0b-78f3.cable.dynamic.v6.ziggo.nl. [2001:1c04:681:7700:a50d:7275:da0b:78f3]) by smtp.gmail.com with ESMTPSA id a640c23a62f3a-ab6760fbd51sm332865766b.151.2025.01.25.12.01.11 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Sat, 25 Jan 2025 12:01:11 -0800 (PST) From: Frits Hoogland Message-Id: <1C9EF412-CED3-40F6-9081-A9464CC87C4B@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_A4D37F44-D985-48BE-992C-97BEECF12E17" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.300.87.4.3\)) Subject: Re: Any risk or overhead considerations for frequently executing queries against catalog tables? Date: Sat, 25 Jan 2025 21:01:00 +0100 In-Reply-To: Cc: pgsql-performance@lists.postgresql.org To: Pavel Stehule References: <0E68C6B4-391F-481E-9764-B0D2FE4CF15D@gmail.com> X-Mailer: Apple Mail (2.3826.300.87.4.3) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_A4D37F44-D985-48BE-992C-97BEECF12E17 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 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 = wrote: >=20 > Hi >=20 > 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? >=20 > 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. >=20 > Only when we try to investigate some special case, then I use second = sampling. When you investigate lock issues, then seconds are too much >=20 > Regards >=20 > Pavel > =20 >> 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. >>=20 >> 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? >>=20 >> 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? >>=20 >> btw, it's interesting to see that both datfrozenxid and datminmxid = are in place updated, with no read consistency provided. >>=20 >> Frits Hoogland >>=20 >>=20 >>=20 >>=20 >>> On 25 Jan 2025, at 14:32, Pavel Stehule > wrote: >>>=20 >>> Hi >>>=20 >>> 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 = 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? >>>=20 >>> The queries to all tables excluding pg_database every 1 sec will = have probably zero impact to performance. >>>=20 >>> 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. >>>=20 >>> Regards >>>=20 >>> Pavel >>> =20 >>>>=20 >>>> Thanks, >>>>=20 >>>> Frits Hoogland >>>>=20 >>>>=20 >>>>=20 >>>>=20 >>=20 --Apple-Mail=_A4D37F44-D985-48BE-992C-97BEECF12E17 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 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 <pavel.stehule@gmail.com> wrote:

Hi

so 25. 1. = 2025 v 18:00 odes=C3=ADlatel Frits Hoogland <frits.hoogland@gmail.com> = 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 <pavel.stehule@gmail.com> = 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 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

Pav= el
 

Thanks,
<= div>
<= b>Frits Hoogland




=


= --Apple-Mail=_A4D37F44-D985-48BE-992C-97BEECF12E17--