public inbox for [email protected]
help / color / mirror / Atom feedFrom: Gilles Darold <[email protected]>
To: Andrey Borodin <[email protected]>
Cc: Tomas Vondra <[email protected]>
Cc: Tomas Vondra <[email protected]>
Cc: Alexander Korotkov <[email protected]>
Cc: Anastasia Lubennikova <[email protected]>
Cc: Daniel Gustafsson <[email protected]>
Cc: Kyotaro Horiguchi <[email protected]>
Cc: pgsql-hackers <[email protected]>
Subject: Re: MultiXact\SLRU buffers configuration
Date: Fri, 11 Dec 2020 18:50:25 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<CAPpHfduT0Kb+8fMt4t0EF5G_3hig+wYgeRadQnNq20F4VxP7LA@mail.gmail.com>
<CAPpHfdsUtSGxRrKn65F_wrE66sNztNY4TpDGDHh2kvAZZDOMqA@mail.gmail.com>
<20201028013651.de5cj2xadgmba5nf@development>
<[email protected]>
<20201028233243.ygm6yqlynkqpzekr@development>
<[email protected]>
<20201029134933.xd4mh2cofuf6tdfz@development>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
Le 10/12/2020 à 15:45, Gilles Darold a écrit :
> Le 08/12/2020 à 18:52, Andrey Borodin a écrit :
>> Hi Gilles!
>>
>> Many thanks for your message!
>>
>>> 8 дек. 2020 г., в 21:05, Gilles Darold<[email protected]> написал(а):
>>>
>>> I know that this report is not really helpful
>> Quite contrary - this benchmarks prove that controllable reproduction exists. I've rebased patches for PG11. Can you please benchmark them (without extending SLRU)?
>>
>> Best regards, Andrey Borodin.
>>
> Hi,
>
>
> Running tests yesterday with the patches has reported log of failures
> with error on INSERT and UPDATE statements:
>
>
> ERROR: lock MultiXactOffsetControlLock is not held
>
>
> After a patch review this morning I think I have found what's going
> wrong. In patch
> v6-0001-Use-shared-lock-in-GetMultiXactIdMembers-for-offs.patch I
> think there is a missing reinitialisation of the lockmode variable to
> LW_NONE inside the retry loop after the call to LWLockRelease() in
> src/backend/access/transam/multixact.c:1392:GetMultiXactIdMembers().
> I've attached a new version of the patch for master that include the
> fix I'm using now with PG11 and with which everything works very well now.
>
>
> I'm running more tests to see the impact on the performances to play
> with multixact_offsets_slru_buffers, multixact_members_slru_buffers
> and multixact_local_cache_entries. I will reports the results later today.
>
Hi,
Sorry for the delay, I have done some further tests to try to reach the
limit without bottlenecks on multixact or shared buffers. The tests was
done on a Microsoft Asure machine with 2TB of RAM and 4 sockets Intel
Xeon Platinum 8280M (128 cpu). PG configuration:
max_connections = 4096
shared_buffers = 64GB
max_prepared_transactions = 2048
work_mem = 256MB
maintenance_work_mem = 2GB
wal_level = minimal
synchronous_commit = off
commit_delay = 1000
commit_siblings = 10
checkpoint_timeout = 1h
max_wal_size = 32GB
checkpoint_completion_target = 0.9
I have tested with several values for the different buffer's variables
starting from:
multixact_offsets_slru_buffers = 64
multixact_members_slru_buffers = 128
multixact_local_cache_entries = 256
to the values with the best performances we achieve with this test to
avoid MultiXactOffsetControlLock or MultiXactMemberControlLock:
multixact_offsets_slru_buffers = 128
multixact_members_slru_buffers = 512
multixact_local_cache_entries = 1024
Also shared_buffers have been increased up to 256GB to avoid
buffer_mapping contention.
Our last best test reports the following wait events:
event_type | event | sum
------------+----------------------------+-----------
Client | ClientRead | 321690211
LWLock | buffer_content | 2970016
IPC | ProcArrayGroupUpdate | 2317388
LWLock | ProcArrayLock | 1445828
LWLock | WALWriteLock | 1187606
LWLock | SubtransControlLock | 972889
Lock | transactionid | 840560
Lock | relation | 587600
Activity | LogicalLauncherMain | 529599
Activity | AutoVacuumMain | 528097
At this stage I don't think we can have better performances by tuning
these buffers at least with PG11.
About performances gain related to the patch for shared lock in
GetMultiXactIdMembers unfortunately I can not see a difference with or
without this patch, it could be related to our particular benchmark. But
clearly the patch on multixact buffers should be committed as this is
really helpfull to be able to tuned PG when multixact bottlenecks are found.
Best regards,
--
Gilles Darold
LzLabs GmbH
https://www.lzlabs.com/
view thread (81+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: MultiXact\SLRU buffers configuration
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox