Re: per backend I/O statistics

From: Bertrand Drouvot <bertranddrouvot(dot)pg(at)gmail(dot)com>
To: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
Cc: Kyotaro Horiguchi <horikyota(dot)ntt(at)gmail(dot)com>, pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Re: per backend I/O statistics
Date: 2024-09-13 04:25:46
Message-ID: ZuO+yiB//Xyp1PtT@ip-10-97-1-34.eu-west-3.compute.internal
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Hunaid Sohail 2024-09-13 04:49:55 Re: Psql meta-command conninfo+
Previous Message Richard Guo 2024-09-13 04:04:01 Re: Why don't we consider explicit Incremental Sort?