public inbox for [email protected]
help / color / mirror / Atom feedFrom: Tomas Vondra <[email protected]>
To: Andrey Borodin <[email protected]>
To: 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: Tue, 10 Nov 2020 01:13:22 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<CAPpHfduekT=BrM3rLqvo4ajzKYmDk+6aCoYJrNHhK0Je+v2z=Q@mail.gmail.com>
<[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]>
Hi,
After the issue reported in [1] got fixed, I've restarted the multi-xact
stress test, hoping to reproduce the issue. But so far no luck :-(
I've started slightly different tests on two machines - on one machine
I've done this:
a) init.sql
create table t (a int);
insert into t select i from generate_series(1,100000000) s(i);
alter table t add primary key (a);
b) select.sql
SELECT * FROM t
WHERE a = (1+mod(abs(hashint4(extract(epoch from now())::int)),
100000000)) FOR KEY SHARE;
c) pgbench -n -c 32 -j 8 -f select.sql -T $((24*3600)) test
The idea is to have large table and many clients hitting a small random
subset of the rows. A sample of wait events from ~24h run looks like this:
e_type | e_name | sum
----------+----------------------+----------
LWLock | BufferContent | 13913863
| | 7194679
LWLock | WALWrite | 1710507
Activity | LogicalLauncherMain | 726599
Activity | AutoVacuumMain | 726127
Activity | WalWriterMain | 725183
Activity | CheckpointerMain | 604694
Client | ClientRead | 599900
IO | WALSync | 502904
Activity | BgWriterMain | 378110
Activity | BgWriterHibernate | 348464
IO | WALWrite | 129412
LWLock | ProcArray | 6633
LWLock | WALInsert | 5714
IO | SLRUWrite | 2580
IPC | ProcArrayGroupUpdate | 2216
LWLock | XactSLRU | 2196
Timeout | VacuumDelay | 1078
IPC | XactGroupUpdate | 737
LWLock | LockManager | 503
LWLock | WALBufMapping | 295
LWLock | MultiXactMemberSLRU | 267
IO | DataFileWrite | 68
LWLock | BufferIO | 59
IO | DataFileRead | 27
IO | DataFileFlush | 14
LWLock | MultiXactGen | 7
LWLock | BufferMapping | 1
So, nothing particularly interesting - there certainly are not many wait
events related to SLRU.
On the other machine I did this:
a) init.sql
create table t (a int primary key);
insert into t select i from generate_series(1,1000) s(i);
b) select.sql
select * from t for key share;
c) pgbench -n -c 32 -j 8 -f select.sql -T $((24*3600)) test
and the wait events (24h run too) look like this:
e_type | e_name | sum
-----------+-----------------------+----------
LWLock | BufferContent | 20804925
| | 2575369
Activity | LogicalLauncherMain | 745780
Activity | AutoVacuumMain | 745292
Activity | WalWriterMain | 740507
Activity | CheckpointerMain | 737691
Activity | BgWriterHibernate | 731123
LWLock | WALWrite | 570107
IO | WALSync | 452603
Client | ClientRead | 151438
BufferPin | BufferPin | 23466
LWLock | WALInsert | 21631
IO | WALWrite | 19050
LWLock | ProcArray | 15082
Activity | BgWriterMain | 14655
IPC | ProcArrayGroupUpdate | 7772
LWLock | WALBufMapping | 3555
IO | SLRUWrite | 1951
LWLock | MultiXactGen | 1661
LWLock | MultiXactMemberSLRU | 359
LWLock | MultiXactOffsetSLRU | 242
LWLock | XactSLRU | 141
IPC | XactGroupUpdate | 104
LWLock | LockManager | 28
IO | DataFileRead | 4
IO | ControlFileSyncUpdate | 1
Timeout | VacuumDelay | 1
IO | WALInitWrite | 1
Also nothing particularly interesting - few SLRU wait events.
So unfortunately this does not really reproduce the SLRU locking issues
you're observing - clearly, there has to be something else triggering
it. Perhaps this workload is too simplistic, or maybe we need to run
different queries. Or maybe the hw needs to be somewhat different (more
CPUs? different storage?)
[1]
https://www.postgresql.org/message-id/20201104013205.icogbi773przyny5@development
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
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]
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