Date: Fri, 13 Sep 2024 04:25:46 +0000
From: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: Kyotaro Horiguchi <horikyota.ntt@gmail.com>,
	pgsql-hackers@lists.postgresql.org
Subject: Re: per backend I/O statistics
Message-ID: <ZuO+yiB//Xyp1PtT@ip-10-97-1-34.eu-west-3.compute.internal>
References: <Zta482TuN7ri1Bis@ip-10-97-1-34.eu-west-3.compute.internal>
 <202409051303.qumelxccgzzn@alvherre.pgsql>
 <Ztm811fM73GFt4z/@ip-10-97-1-34.eu-west-3.compute.internal>
 <ZtsZtaRza9bFFeF8@ip-10-97-1-34.eu-west-3.compute.internal>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <ZtsZtaRza9bFFeF8@ip-10-97-1-34.eu-west-3.compute.internal>
Archived-At: <https://www.postgresql.org/message-id/ZuO%2ByiB//Xyp1PtT%40ip-10-97-1-34.eu-west-3.compute.internal>
Precedence: bulk

Hi,

On Fri, Sep 06, 2024 at 03:03:17PM +0000, Bertrand Drouvot wrote:
> The struct size is 1040 Bytes and that's much more reasonable than the size
> needed for per backend I/O stats in v1 (about 16KB).

One could think that's a high increase of shared memory usage with a high
number of connections (due to the fact that it's implemented as "fixed amount"
stats based on the max number of backends).

So out of curiosity, I did some measurements with:

dynamic_shared_memory_type=sysv
shared_memory_type=sysv
max_connections=20000

On my lab, ipcs -m gives me:

- on master

key        shmid      owner      perms      bytes      nattch     status
0x00113a04 51347487   postgres   600        1149394944 6
0x4bc9f2fa 51347488   postgres   600        4006976    6
0x46790800 51347489   postgres   600        1048576    2

- with v3 applied

key        shmid      owner      perms      bytes      nattch     status
0x00113a04 51347477   postgres   600        1170227200 6
0x08e04b0a 51347478   postgres   600        4006976    6
0x74688c9c 51347479   postgres   600        1048576    2

So, with 20000 max_connections (not advocating that's a reasonable number in 
all the cases), that's a 1170227200 - 1149394944 = 20 MB increase of shared
memory (expected with 20K max_connections and the new struct size of 1040 Bytes)
as compare to master which is 1096 MB.  It means that v3 produces about 2% shared
memory increase with 20000 max_connections.

Out of curiosity with max_connections=100000, then:

- on master:

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status
0x00113a04 52953134   postgres   600        5053915136 6
0x37abf5ce 52953135   postgres   600        20006976   6
0x71c07d5c 52953136   postgres   600        1048576    2

- with v3:

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status
0x00113a04 52953144   postgres   600        5157945344 6
0x7afb24de 52953145   postgres   600        20006976   6
0x2695af58 52953146   postgres   600        1048576    2

So, with 100000 max_connections (not advocating that's a reasonable number in 
all the cases), that's a 5157945344 - 5053915136 = 100 MB increase of shared
memory (expected with 100K max_connections and the new struct size of 1040 Bytes)
as compare to master which is about 4800 MB. It means that v3 produces about
2% shared memory increase with 100000 max_connections.


Then, based on those numbers, I think that the "fixed amount" approach is a good
one as 1/ the amount of shared memory increase is "relatively" small and 2/ most
probably provides performance benefits as compare to the "non fixed" approach.

Regards,

-- 
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com