Re: reducing the overhead of frequent table locks - now, with WIP patch
От | Robert Haas |
---|---|
Тема | Re: reducing the overhead of frequent table locks - now, with WIP patch |
Дата | |
Msg-id | BANLkTi=7OmZEawHc9JOw=ePD_kK2muxiNQ@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: reducing the overhead of frequent table locks - now, with WIP patch (Robert Haas <robertmhaas@gmail.com>) |
Ответы |
Re: reducing the overhead of frequent table locks - now,
with WIP patch
(Robert Haas <robertmhaas@gmail.com>)
|
Список | pgsql-hackers |
On Sun, Jun 5, 2011 at 5:46 PM, Robert Haas <robertmhaas@gmail.com> wrote: > Could you compile with LWLOCK_STATS, rerun these tests, total up the > "blk" numbers by LWLockId, and post the results? (Actually, totalling > up the shacq and exacq numbers would be useful as well, if you > wouldn't mind.) I did this on the loaner 24-core box from Nate Boley and got the following results. This is just the LWLocks that had blk>0. lwlock 0: shacq 0 exacq 200625 blk 24044 lwlock 4: shacq 80101430 exacq 196 blk 28 lwlock 33: shacq 8333673 exacq 11977 blk 864 lwlock 34: shacq 7092293 exacq 11890 blk 803 lwlock 35: shacq 7893875 exacq 11909 blk 848 lwlock 36: shacq 7567514 exacq 11912 blk 830 lwlock 37: shacq 7427774 exacq 11930 blk 745 lwlock 38: shacq 7120108 exacq 11989 blk 853 lwlock 39: shacq 7584952 exacq 11982 blk 782 lwlock 40: shacq 7949867 exacq 12056 blk 821 lwlock 41: shacq 6612240 exacq 11929 blk 746 lwlock 42: shacq 47512112 exacq 11844 blk 4503 lwlock 43: shacq 7943511 exacq 11871 blk 878 lwlock 44: shacq 7534558 exacq 12033 blk 800 lwlock 45: shacq 7128256 exacq 12045 blk 856 lwlock 46: shacq 7575339 exacq 12015 blk 818 lwlock 47: shacq 6745173 exacq 12094 blk 806 lwlock 48: shacq 8410348 exacq 12104 blk 977 lwlock 49: shacq 0 exacq 5007594 blk 172533 lwlock 50: shacq 0 exacq 5011704 blk 172282 lwlock 51: shacq 0 exacq 5003356 blk 172802 lwlock 52: shacq 0 exacq 5009020 blk 174648 lwlock 53: shacq 0 exacq 5010808 blk 172080 lwlock 54: shacq 0 exacq 5004908 blk 169934 lwlock 55: shacq 0 exacq 5009324 blk 170281 lwlock 56: shacq 0 exacq 5005904 blk 171001 lwlock 57: shacq 0 exacq 5006984 blk 169942 lwlock 58: shacq 0 exacq 5000346 blk 170001 lwlock 59: shacq 0 exacq 5004884 blk 170484 lwlock 60: shacq 0 exacq 5006304 blk 171325 lwlock 61: shacq 0 exacq 5008421 blk 170866 lwlock 62: shacq 0 exacq 5008162 blk 170868 lwlock 63: shacq 0 exacq 5002238 blk 170291 lwlock 64: shacq 0 exacq 5005348 blk 169764 lwlock 307: shacq 0 exacq 2 blk 1 lwlock 315: shacq 0 exacq 3 blk 2 lwlock 337: shacq 0 exacq 4 blk 3 lwlock 345: shacq 0 exacq 2 blk 1 lwlock 349: shacq 0 exacq 2 blk 1 lwlock 231251: shacq 0 exacq 2 blk 1 lwlock 253831: shacq 0 exacq 2 blk 1 So basically, even with the patch, at 24 cores the lock manager locks are still under tremendous pressure. But note that there's a big difference between what's happening here and what's happening without the patch. Here's without the patch: lwlock 0: shacq 0 exacq 191613 blk 17591 lwlock 4: shacq 21543085 exacq 102 blk 20 lwlock 33: shacq 2237938 exacq 11976 blk 463 lwlock 34: shacq 1907344 exacq 11890 blk 458 lwlock 35: shacq 2125308 exacq 11908 blk 442 lwlock 36: shacq 2038220 exacq 11912 blk 430 lwlock 37: shacq 1998059 exacq 11927 blk 449 lwlock 38: shacq 1916179 exacq 11953 blk 409 lwlock 39: shacq 2042173 exacq 12019 blk 479 lwlock 40: shacq 2140002 exacq 12056 blk 448 lwlock 41: shacq 1776772 exacq 11928 blk 392 lwlock 42: shacq 12777368 exacq 11842 blk 2451 lwlock 43: shacq 2132240 exacq 11869 blk 478 lwlock 44: shacq 2026845 exacq 12031 blk 446 lwlock 45: shacq 1918618 exacq 12045 blk 449 lwlock 46: shacq 2038437 exacq 12011 blk 472 lwlock 47: shacq 1814660 exacq 12089 blk 401 lwlock 48: shacq 2261208 exacq 12105 blk 478 lwlock 49: shacq 0 exacq 1347524 blk 17020 lwlock 50: shacq 0 exacq 1350678 blk 16888 lwlock 51: shacq 0 exacq 1346260 blk 16744 lwlock 52: shacq 0 exacq 1348432 blk 16864 lwlock 53: shacq 0 exacq 22216779 blk 4914363 lwlock 54: shacq 0 exacq 22217309 blk 4525381 lwlock 55: shacq 0 exacq 1348406 blk 13438 lwlock 56: shacq 0 exacq 1345996 blk 13299 lwlock 57: shacq 0 exacq 1347890 blk 13654 lwlock 58: shacq 0 exacq 1343486 blk 13349 lwlock 59: shacq 0 exacq 1346198 blk 13471 lwlock 60: shacq 0 exacq 1346236 blk 13532 lwlock 61: shacq 0 exacq 1343688 blk 13547 lwlock 62: shacq 0 exacq 1350068 blk 13614 lwlock 63: shacq 0 exacq 1345302 blk 13420 lwlock 64: shacq 0 exacq 1348858 blk 13635 lwlock 321: shacq 0 exacq 2 blk 1 lwlock 329: shacq 0 exacq 4 blk 3 lwlock 337: shacq 0 exacq 6 blk 4 lwlock 347: shacq 0 exacq 5 blk 4 lwlock 357: shacq 0 exacq 3 blk 2 lwlock 363: shacq 0 exacq 3 blk 2 lwlock 369: shacq 0 exacq 4 blk 3 lwlock 379: shacq 0 exacq 2 blk 1 lwlock 383: shacq 0 exacq 2 blk 1 lwlock 445: shacq 0 exacq 2 blk 1 lwlock 449: shacq 0 exacq 2 blk 1 lwlock 451: shacq 0 exacq 2 blk 1 lwlock 1023: shacq 0 exacq 2 blk 1 lwlock 11401: shacq 0 exacq 2 blk 1 lwlock 115591: shacq 0 exacq 2 blk 1 lwlock 117177: shacq 0 exacq 2 blk 1 lwlock 362839: shacq 0 exacq 2 blk 1 In the unpatched case, two lock manager locks are getting beaten to death, and the others all about equally contended. By eliminating the portion of the lock manager contention that pertains specifically to the two heavily trafficked locks, system throughput improves by about 3.5x - and, not surprisingly, traffic on the lock manager locks increases by approximately the same multiple. Those locks now become the contention bottleneck, with about 12x the blocking they had pre-patch. I'm definitely interested in investigating what to do about that, but I don't think it's this patch's problem to fix all of our lock manager bottlenecks. Another thing to note is that pre-patch, the two really badly contented LWLocks were blocking about 22% of the time; post-patch, all of the lock manager locks are blocking about 3.4% of the time. That's certainly not great, but it's progress. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
В списке pgsql-hackers по дате отправления:
Следующее
От: Jeff DavisДата:
Сообщение: Re: Assert failure when rechecking an exclusion constraint