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 exacq 13471909 blk 61793 spindelay 0 lwlock 75: shacq 0 exacq 13593579 blk 62315 spindelay 3 lwlock 76: shacq 0 exacq 13558873 blk 61616 spindelay 3 lwlock 77: shacq 0 exacq 13403202 blk 60852 spindelay 2 lwlock 78: shacq 0 exacq 13476093 blk 61286 spindelay 1 lwlock 79: shacq 0 exacq 13686866 blk 63847 spindelay 0 lwlock 80: shacq 0 exacq 13488704 blk 63236 spindelay 1 lwlock 359: shacq 0 exacq 6 blk 3 spindelay 0 lwlock 365: shacq 0 exacq 7 blk 5 spindelay 0 lwlock 375: shacq 0 exacq 2 blk 1 spindelay 0 lwlock 383: shacq 0 exacq 12 blk 9 spindelay 0 lwlock 385: shacq 0 exacq 6 blk 4 spindelay 0 lwlock 387: shacq 0 exacq 3 blk 1 spindelay 0 lwlock 391: shacq 0 exacq 3 blk 1 spindelay 0 lwlock 397: shacq 0 exacq 4 blk 2 spindelay 0 lwlock 399: shacq 0 exacq 3 blk 1 spindelay 0 lwlock 401: shacq 0 exacq 3 blk 1 spindelay 0 lwlock 403: shacq 0 exacq 3 blk 1 spindelay 0 lwlock 405: shacq 0 exacq 3 blk 1 spindelay 0 lwlock 411: shacq 0 exacq 4 blk 1 spindelay 0 lwlock 413: shacq 0 exacq 6 blk 3 spindelay 0 lwlock 416: shacq 16238720 exacq 0 blk 0 spindelay 17 lwlock 417: shacq 0 exacq 5 blk 2 spindelay 0 lwlock 418: shacq 15979650 exacq 0 blk 0 spindelay 4 lwlock 419: shacq 0 exacq 3 blk 1 spindelay 0 lwlock 421: shacq 0 exacq 4 blk 1 spindelay 0 lwlock 423: shacq 0 exacq 5 blk 2 spindelay 0 lwlock 425: shacq 0 exacq 3 blk 1 spindelay 0 lwlock 427: shacq 0 exacq 4 blk 1 spindelay 0 lwlock 431: shacq 0 exacq 5 blk 2 spindelay 0 lwlock 433: shacq 0 exacq 3 blk 1 spindelay 0 lwlock 436: shacq 16476438 exacq 0 blk 0 spindelay 15 lwlock 438: shacq 16268694 exacq 0 blk 0 spindelay 13 lwlock 440: shacq 16180011 exacq 0 blk 0 spindelay 19 lwlock 442: shacq 15922327 exacq 0 blk 0 spindelay 10 lwlock 474: shacq 168824 exacq 0 blk 0 spindelay 1 lwlock 747: shacq 0 exacq 4 blk 1 spindelay 0 lwlock 1121: shacq 0 exacq 4 blk 1 spindelay 0 lwlock 2235: shacq 0 exacq 4 blk 1 spindelay 0 lwlock 2717: shacq 0 exacq 4 blk 1 spindelay 0 lwlock 68581: shacq 0 exacq 4 blk 1 spindelay 0 lwlock 629477: shacq 0 exacq 4 blk 1 spindelay 0 lwlock 755221: shacq 0 exacq 4 blk 1 spindelay 0 lwlock 1013853: shacq 0 exacq 4 blk 1 spindelay 0 lwlock 1041309: shacq 0 exacq 4 blk 1 spindelay 0
lwlock 0: shacq 0 exacq 1724601 blk 73853 spindelay 72 lwlock 4: shacq 56805055 exacq 279 blk 5 spindelay 0 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 lwlock 33: shacq 11151133 exacq 107817 blk 3702 spindelay 66 lwlock 34: shacq 10851857 exacq 107241 blk 3616 spindelay 98 lwlock 35: shacq 9414008 exacq 106791 blk 2938 spindelay 49 lwlock 36: shacq 11477672 exacq 108252 blk 3842 spindelay 72 lwlock 37: shacq 66865355 exacq 106360 blk 12448 spindelay 2340 lwlock 38: shacq 11291780 exacq 108117 blk 3746 spindelay 66 lwlock 39: shacq 12013313 exacq 107911 blk 3908 spindelay 82 lwlock 40: shacq 9509043 exacq 108542 blk 3144 spindelay 58 lwlock 41: shacq 9680058 exacq 107887 blk 3289 spindelay 50 lwlock 42: shacq 11344425 exacq 108337 blk 3904 spindelay 74 lwlock 43: shacq 10539456 exacq 107410 blk 3361 spindelay 82 lwlock 44: shacq 69720023 exacq 107606 blk 14571 spindelay 2710 lwlock 45: shacq 8869345 exacq 106631 blk 3137 spindelay 60 lwlock 46: shacq 10719854 exacq 108455 blk 3587 spindelay 60 lwlock 47: shacq 11450519 exacq 107980 blk 3785 spindelay 66 lwlock 48: shacq 9456344 exacq 107463 blk 3174 spindelay 77 lwlock 49: shacq 0 exacq 240 blk 1 spindelay 0 lwlock 50: shacq 0 exacq 864 blk 62 spindelay 0 lwlock 51: shacq 0 exacq 426 blk 8 spindelay 0 lwlock 53: shacq 0 exacq 414 blk 8 spindelay 0 lwlock 59: shacq 0 exacq 2010 blk 82 spindelay 0 lwlock 61: shacq 0 exacq 486 blk 36 spindelay 0 lwlock 63: shacq 0 exacq 1080 blk 24 spindelay 0 lwlock 64: shacq 0 exacq 324 blk 17 spindelay 0 lwlock 65: shacq 0 exacq 17978651 blk 160169 spindelay 92 lwlock 66: shacq 0 exacq 18084265 blk 162134 spindelay 75 lwlock 67: shacq 0 exacq 17828059 blk 156807 spindelay 56 lwlock 68: shacq 0 exacq 17968317 blk 162885 spindelay 70 lwlock 69: shacq 0 exacq 17983307 blk 163612 spindelay 61 lwlock 70: shacq 0 exacq 18144490 blk 167287 spindelay 78 lwlock 71: shacq 0 exacq 18093834 blk 166059 spindelay 81 lwlock 72: shacq 0 exacq 17844905 blk 162880 spindelay 61 lwlock 73: shacq 0 exacq 17987408 blk 165251 spindelay 79 lwlock 74: shacq 0 exacq 17980412 blk 164199 spindelay 68 lwlock 75: shacq 0 exacq 18160007 blk 167390 spindelay 68 lwlock 76: shacq 0 exacq 18090913 blk 166982 spindelay 87 lwlock 77: shacq 0 exacq 17881885 blk 162294 spindelay 75 lwlock 78: shacq 0 exacq 17989507 blk 165583 spindelay 64 lwlock 79: shacq 0 exacq 18251907 blk 170429 spindelay 75 lwlock 80: shacq 0 exacq 18015516 blk 169966 spindelay 56 lwlock 170: shacq 783 exacq 0 blk 0 spindelay 2 lwlock 359: shacq 0 exacq 4 blk 1 spindelay 0 lwlock 363: shacq 0 exacq 6 blk 3 spindelay 0 lwlock 369: shacq 0 exacq 3 blk 2 spindelay 0 lwlock 371: shacq 0 exacq 7 blk 5 spindelay 0 lwlock 379: shacq 0 exacq 3 blk 2 spindelay 0 lwlock 381: shacq 0 exacq 4 blk 2 spindelay 0 lwlock 391: shacq 0 exacq 3 blk 2 spindelay 0 lwlock 437: shacq 0 exacq 3 blk 1 spindelay 0 lwlock 438: shacq 18931906 exacq 0 blk 0 spindelay 702 lwlock 440: shacq 18628563 exacq 0 blk 0 spindelay 731 lwlock 443: shacq 0 exacq 6 blk 3 spindelay 0 lwlock 445: shacq 0 exacq 3 blk 1 spindelay 0 lwlock 447: shacq 0 exacq 6 blk 3 spindelay 0 lwlock 453: shacq 0 exacq 4 blk 1 spindelay 0 lwlock 455: shacq 0 exacq 4 blk 1 spindelay 0 lwlock 457: shacq 0 exacq 3 blk 1 spindelay 0 lwlock 465: shacq 0 exacq 5 blk 2 spindelay 0 lwlock 467: shacq 0 exacq 6 blk 3 spindelay 0 lwlock 469: shacq 0 exacq 4 blk 1 spindelay 0 lwlock 470: shacq 19092989 exacq 0 blk 0 spindelay 847 lwlock 472: shacq 18788054 exacq 0 blk 0 spindelay 801 lwlock 473: shacq 0 exacq 4 blk 1 spindelay 0 lwlock 475: shacq 0 exacq 4 blk 1 spindelay 0 lwlock 477: shacq 0 exacq 4 blk 1 spindelay 0 lwlock 479: shacq 0 exacq 4 blk 1 spindelay 0 lwlock 485: shacq 0 exacq 5 blk 1 spindelay 0 lwlock 491: shacq 0 exacq 4 blk 1 spindelay 0 lwlock 492: shacq 18780871 exacq 0 blk 0 spindelay 829 lwlock 493: shacq 0 exacq 4 blk 1 spindelay 0 lwlock 494: shacq 18544176 exacq 0 blk 0 spindelay 494 lwlock 643: shacq 0 exacq 4 blk 1 spindelay 0 lwlock 976: shacq 65840 exacq 0 blk 0 spindelay 1 lwlock 1570: shacq 65763 exacq 0 blk 0 spindelay 1 lwlock 1640: shacq 131304 exacq 0 blk 0 spindelay 1 lwlock 1805: shacq 0 exacq 4 blk 1 spindelay 0 lwlock 3500: shacq 67211 exacq 0 blk 0 spindelay 1 lwlock 4288: shacq 65998 exacq 0 blk 0 spindelay 1 lwlock 4302: shacq 65529 exacq 0 blk 0 spindelay 1 lwlock 4751: shacq 0 exacq 4 blk 1 spindelay 0 lwlock 48319: shacq 0 exacq 4 blk 1 spindelay 0 lwlock 64627: shacq 0 exacq 4 blk 1 spindelay 0 lwlock 70387: shacq 0 exacq 4 blk 1 spindelay 0 lwlock 87685: shacq 0 exacq 4 blk 1 spindelay 0 lwlock 90361: shacq 0 exacq 4 blk 1 spindelay 0 lwlock 113391: shacq 0 exacq 4 blk 1 spindelay 0 lwlock 166923: shacq 0 exacq 4 blk 1 spindelay 0 lwlock 331375: shacq 0 exacq 4 blk 1 spindelay 0 lwlock 939179: shacq 0 exacq 4 blk 1 spindelay 0 lwlock 953585: shacq 0 exacq 4 blk 1 spindelay 0 lwlock 1085611: shacq 0 exacq 4 blk 1 spindelay 0 lwlock 1132653: shacq 0 exacq 4 blk 1 spindelay 0
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
