Re: SLRU optimization - configurable buffer pool and partitioning the SLRU lock

On Wed, Dec 13, 2023 at 5:49 PM Andrey M. Borodin <x4mmm@yandex-team.ru> wrote:

> > On 12 Dec 2023, at 18:28, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
> >
> > Andrey, do you have any stress tests or anything else that you used to
> > gain confidence in this code?
>
> We are using only first two steps of the patchset, these steps do not touch locking stuff.
>
> We’ve got some confidence after Yura Sokolov’s benchmarks [0]. Thanks!
>

I have run this test [1], instead of comparing against the master I
have compared the effect of (patch-1 = (0001+0002)slur buffer bank) vs
(patch-2 = (0001+0002+0003) slur buffer bank + bank-wise lock), and
here is the result of the benchmark-1 and benchmark-2.  I have noticed
a very good improvement with the addition of patch 0003.

Machine information:
Architecture:          x86_64
CPU op-mode(s):        32-bit, 64-bit
Byte Order:            Little Endian
CPU(s):                128
On-line CPU(s) list:   0-127

configurations:

max_wal_size=20GB
shared_buffers=20GB
checkpoint_timeout=40min
max_connections=700
maintenance_work_mem=1GB

subtrans_buffers=$variable
multixact_offsets_buffers=$variable
multixact_members_buffers=$variable

benchmark-1
version  | subtrans | multixact   | tps
              | buffers   | offs/memb | func+ballast
-----------+--------------+--------------+------
patch-1 | 64           | 64/128        |  87 + 58
patch-2 | 64           | 64/128         | 128 +83
patch-1 | 1024        | 512/1024    |  96 + 64
patch-2 | 1024        | 512/1024    | 163+108

benchmark-2

version  | subtrans | multixact   | tps
              | buffers   | offs/memb | func
-----------+--------------+--------------+------
patch-1 | 64           | 64/128        |  10
patch-2 | 64           | 64/128         | 12
patch-1 | 1024        | 512/1024    | 44
patch-2 | 1024        | 512/1024    | 72


[1] https://www.postgresql.org/message-id/flat/e46cdea96979545b2d8a13b451d8b1ce61dc7238.camel%40postgrespro.ru#0ed2cad11470825d464093fe6b8ef6a3


--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com