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

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

> > On 12 Dec 2023, at 18:28, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)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