Re: [HACKERS] lwlock contention with SSI

[Robert Haas]

On Tue, Oct 7, 2014 at 2:40 PM, Kevin Grittner kgri...@ymail.com wrote:
 Robert Haas robertmh...@gmail.com wrote:
 About a month ago, I told Kevin Grittner in an off-list conversation
 that I'd work on providing him with some statistics about lwlock
 contention under SSI.  I then ran a benchmark on a 16-core,
 64-hardware thread IBM server, testing read-only pgbench performance
 at scale factor 300 with 1, 8, and 32 clients (and an equal number of
 client threads).

 I hate to say this when I know how much work benchmarking is, but I
 don't think any benchmark of serializable transactions has very
 much value unless you set any transactions which don't write to
 READ ONLY.  I guess it shows how a naive conversion by someone who
 doesn't read the docs or chooses to ignore the advice on how to get
 good performance will perform, but how interesting is that?

 It might be worth getting TPS numbers from the worst-looking test
 from this run, but with the read-only run done after changing
 default_transaction_read_only = on.  Some shops using serializable
 transactions set that in the postgresql.conf file, and require that
 any transaction which will be modifying data override it.

Well, we could do that.  But I'm not sure it's very realistic.  The
pgbench workload is either 100% write or 100% read, but most real
work-loads are mixed; say, 95% read, 5% write.  If the client software
has to be responsible for flipping default_transaction_read_only for
every write transaction, or just doing BEGIN TRANSACTION READ WRITE
and COMMIT around each otherwise-single-statement write transaction,
that's a whole bunch of extra server round trips and complexity that
most people are not going to want to bother with.  We can tell them
that they have to do it anyway, of course.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] lwlock contention with SSI

[Dan Ports]

On Tue, Apr 09, 2013 at 07:49:51PM -0400, Robert Haas wrote:
 These locks are all SSI-related and they're all really hot.  Lock 28
 is SerializableXactHashLock and lock 29 is
 SerializableFinishedListLock; both are acquired an order of magnitude
 more often than any non-SSI lock, and cause two orders of magnitude
 more blocking than any other lock whatsoever.  Lock 30 is
 SerializablePredicateLockListLock, which has no exclusive lock
 acquisitions at all on this test, but the shared acquisitions result
 in significant spinlock contention.

This matches what I saw when I looked into this a while ago. I even
started sketching out some plans of how we might deal with it, but
unfortunately I never had much time to work on it, and that seems
unlikely to change any time soon. :-\

As it is, pretty much any operation involving SSI requires acquiring
SerializableXactHashLock (usually exclusive), except for checking
whether a read or write indicates a conflict. That includes starting
and ending a transaction.

Two things make this hard to fix:
 - SSI is about checking for rw-conflicts, which are inherently about
   *pairs* of transactions. This makes it hard to do fine-grained
   locking, because a lot of operations involve looking at or modifying
   the conflict list of more than one transaction.
 - SerializableXactHashLock protects many things. Besides the 
   SERIALIZABLEXACT structures themselves, there's also the free lists
   for SERIALIZABLEXACTs and RWConflicts, the SerializableXidHash
   table, the latest SxactCommitSeqno and SxactGlobalXmin, etc.

I'm trying to swap back in my notes about how to address this. It is
bound to be a substantial project, however.

Dan

-- 
Dan R. K. PortsUW CSEhttp://drkp.net/


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] lwlock contention with SSI

[Robert Haas]

About a month ago, I told Kevin Grittner in an off-list conversation
that I'd work on providing him with some statistics about lwlock
contention under SSI.  I then ran a benchmark on a 16-core,
64-hardware thread IBM server, testing read-only pgbench performance
at scale factor 300 with 1, 8, and 32 clients (and an equal number of
client threads).  The following non-default configuration settings
were used:

shared_buffers = 8GB
maintenance_work_mem = 1GB
synchronous_commit = off
checkpoint_segments = 300
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
log_line_prefix = '%t [%p] '
default_transaction_isolation = 'serializable'
max_pred_locks_per_transaction = 1000

After running the test, I dropped the ball for a month.  But, picking
it back up again, here are the results.  I've taken the data that is
produced by LWLOCK_STATS, and I've filtered it down by consolidating
entries for the same lock across all PIDs that show up in the log
file.  Then, I've omitted all entries where blk == 0 and spindelay ==
0, because those locks were never contended, so they're boring; and
also because including them makes the results too long to make sense
of.  The results for the remaining locks are attached as three files,
based on the number of clients.  The really revealing entries, IMHO,
are these results from the 32-client test:

lwlock 28: shacq 86952211 exacq 257812441 blk 35212093 spindelay 40811
lwlock 29: shacq 0 exacq 87516792 blk 31177203 spindelay 10038
lwlock 30: shacq 227960353 exacq 0 blk 0 spindelay 10711

These locks are all SSI-related and they're all really hot.  Lock 28
is SerializableXactHashLock and lock 29 is
SerializableFinishedListLock; both are acquired an order of magnitude
more often than any non-SSI lock, and cause two orders of magnitude
more blocking than any other lock whatsoever.  Lock 30 is
SerializablePredicateLockListLock, which has no exclusive lock
acquisitions at all on this test, but the shared acquisitions result
in significant spinlock contention.  This latter problem could
probably be ameliorated with a reader/writer lock (a primitive we
don't currently have in Postgres, but you could build one up using
lwlocks), but it's unlikely to make much difference without doing
something about SerializableXactHashLock and
SerializableFinishedListLock first.

Once you get past these big three, there's also a ton of blocking on
the PredicateLockMgrLocks, which seem only ever to be acquired in
exclusive mode, but it's not nearly as bad due to the 16-way
partitioning.

[Obligatory disclaimer: This has nothing to do with 9.3 and is not
intended to distract attention therefrom.]

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
lwlock 0: shacq 0 exacq 1716121 blk 49 spindelay 0
lwlock 44: shacq 8834024 exacq 106905 blk 1 spindelay 0
lwlock 46: shacq 1360967 exacq 107745 blk 1 spindelay 0
lwlock 0: shacq 0 exacq 1724700 blk 31636 spindelay 9
lwlock 4: shacq 48837488 exacq 129 blk 2 spindelay 0
lwlock 28: shacq 59034372 exacq 205443978 blk 12204797 spindelay 1022
lwlock 29: shacq 0 exacq 59096784 blk 4817845 spindelay 66
lwlock 30: shacq 196048118 exacq 0 blk 0 spindelay 318
lwlock 33: shacq 9590676 exacq 107818 blk 1409 spindelay 3
lwlock 34: shacq 9318919 exacq 107241 blk 1342 spindelay 0
lwlock 35: shacq 8092746 exacq 106793 blk 1235 spindelay 0
lwlock 36: shacq 9864538 exacq 108249 blk 1499 spindelay 0
lwlock 37: shacq 57488133 exacq 106358 blk 5323 spindelay 58
lwlock 38: shacq 9704085 exacq 108118 blk 1449 spindelay 0
lwlock 39: shacq 10340396 exacq 107910 blk 1524 spindelay 2
lwlock 40: shacq 8163154 exacq 108542 blk 1299 spindelay 3
lwlock 41: shacq 8324565 exacq 107888 blk 1269 spindelay 2
lwlock 42: shacq 9752315 exacq 108333 blk 1432 spindelay 0
lwlock 43: shacq 9066090 exacq 107348 blk 1429 spindelay 0
lwlock 44: shacq 59938209 exacq 107609 blk 6492 spindelay 64
lwlock 45: shacq 7628029 exacq 106633 blk 1117 spindelay 4
lwlock 46: shacq 9211130 exacq 108452 blk 1312 spindelay 1
lwlock 47: shacq 9851329 exacq 107981 blk 1448 spindelay 1
lwlock 48: shacq 8129228 exacq 107463 blk 1262 spindelay 0
lwlock 49: shacq 0 exacq 90 blk 2 spindelay 0
lwlock 50: shacq 0 exacq 288 blk 13 spindelay 2
lwlock 51: shacq 0 exacq 138 blk 2 spindelay 0
lwlock 53: shacq 0 exacq 126 blk 3 spindelay 0
lwlock 59: shacq 0 exacq 1080 blk 14 spindelay 0
lwlock 61: shacq 0 exacq 192 blk 4 spindelay 0
lwlock 63: shacq 0 exacq 480 blk 10 spindelay 0
lwlock 65: shacq 0 exacq 13472461 blk 58249 spindelay 2
lwlock 66: shacq 0 exacq 13554792 blk 59318 spindelay 1
lwlock 67: shacq 0 exacq 13347513 blk 57087 spindelay 1
lwlock 68: shacq 0 exacq 13458882 blk 60748 spindelay 0
lwlock 69: shacq 0 exacq 13457110 blk 60654 spindelay 2
lwlock 70: shacq 0 exacq 13592353 blk 61859 spindelay 1
lwlock 71: shacq 0 exacq 13546043 blk 61450 spindelay 2
lwlock 72: shacq 0 exacq 13375547 blk 59844 spindelay 1
lwlock 73: shacq 0 exacq 13492622 blk 61400 spindelay 0
lwlock 74: shacq 0