Re: 60 core performance with 9.3
От | Mark Kirkwood |
---|---|
Тема | Re: 60 core performance with 9.3 |
Дата | |
Msg-id | 53E99E6A.1080305@catalyst.net.nz обсуждение исходный текст |
Ответ на | Re: 60 core performance with 9.3 (Alvaro Herrera <alvherre@2ndquadrant.com>) |
Список | pgsql-performance |
On 01/08/14 09:38, Alvaro Herrera wrote: > Matt Clarkson wrote: > >> The LWLOCK_STATS below suggest that ProcArrayLock might be the main >> source of locking that's causing throughput to take a dive as the client >> count increases beyond the core count. > >> Any thoughts or comments on these results are welcome! > > Do these results change if you use Heikki's patch for CSN-based > snapshots? See > http://www.postgresql.org/message-id/539AD153.9000004@vmware.com for the > patch (but note that you need to apply on top of 89cf2d52030 in the > master branch -- maybe it applies to HEAD the 9.4 branch but I didn't > try). > Hi Alvaro, Applying the CSN patch on top of the rwlock + numa in 9.4 (bit of a patch-fest we have here now) shows modest improvement at highest client number (but appears to hurt performance in the mid range): clients | tps ---------+-------- 6 | 8445 12 | 14548 24 | 20043 48 | 27451 96 | 27718 192 | 23614 384 | 24737 Initial runs were quite disappointing, until we moved the csnlog directory onto the same filesystem that the xlogs are on (PCIe SSD). We could potentially look at locating them on their own separate volume if that make sense. Adding in LWLOCK stats again shows quite a different picture from the previous: 48 clients Lock | Blk | SpinDelay | Blk % | SpinDelay % --------------------+----------+-----------+-----------+------------- WALWriteLock | 25426001 | 1239 | 62.227442 | 14.373550 CLogControlLock | 1793739 | 1376 | 4.389986 | 15.962877 ProcArrayLock | 1007765 | 1305 | 2.466398 | 15.139211 CSNLogControlLock | 609556 | 1722 | 1.491824 | 19.976798 WALInsertLocks 4 | 994170 | 247 | 2.433126 | 2.865429 WALInsertLocks 7 | 983497 | 243 | 2.407005 | 2.819026 WALInsertLocks 5 | 993068 | 239 | 2.430429 | 2.772622 WALInsertLocks 3 | 991446 | 229 | 2.426459 | 2.656613 WALInsertLocks 0 | 964185 | 235 | 2.359741 | 2.726218 WALInsertLocks 1 | 995237 | 221 | 2.435737 | 2.563805 WALInsertLocks 2 | 997593 | 213 | 2.441503 | 2.470998 WALInsertLocks 6 | 978178 | 201 | 2.393987 | 2.331787 BufFreelistLock | 887194 | 206 | 2.171313 | 2.389791 XidGenLock | 327385 | 366 | 0.801240 | 4.245940 CheckpointerCommLock| 104754 | 151 | 0.256374 | 1.751740 WALBufMappingLock | 274226 | 7 | 0.671139 | 0.081206 96 clients Lock | Blk | SpinDelay | Blk % | SpinDelay % --------------------+----------+-----------+-----------+------------- WALWriteLock | 25426001 | 1239 | 62.227442 | 14.373550 WALWriteLock | 30097625 | 9616 | 48.550747 | 19.068393 CLogControlLock | 3193429 | 13490 | 5.151349 | 26.750481 ProcArrayLock | 2007103 | 11754 | 3.237676 | 23.308017 CSNLogControlLock | 1303172 | 5022 | 2.102158 | 9.958556 BufFreelistLock | 1921625 | 1977 | 3.099790 | 3.920363 WALInsertLocks 0 | 2011855 | 681 | 3.245341 | 1.350413 WALInsertLocks 5 | 1829266 | 627 | 2.950805 | 1.243332 WALInsertLocks 7 | 1806966 | 632 | 2.914833 | 1.253247 WALInsertLocks 4 | 1847372 | 591 | 2.980012 | 1.171945 WALInsertLocks 1 | 1948553 | 557 | 3.143228 | 1.104523 WALInsertLocks 6 | 1818717 | 582 | 2.933789 | 1.154098 WALInsertLocks 3 | 1873964 | 552 | 3.022908 | 1.094608 WALInsertLocks 2 | 1912007 | 523 | 3.084276 | 1.037102 XidGenLock | 512521 | 699 | 0.826752 | 1.386107 CheckpointerCommLock| 386853 | 711 | 0.624036 | 1.409903 WALBufMappingLock | 546462 | 65 | 0.881503 | 0.128894 384 clients Lock | Blk | SpinDelay | Blk % | SpinDelay % --------------------+----------+-----------+-----------+------------- WALWriteLock | 25426001 | 1239 | 62.227442 | 14.373550 WALWriteLock | 20703796 | 87265 | 27.749961 | 15.360068 CLogControlLock | 3273136 | 122616 | 4.387089 | 21.582422 ProcArrayLock | 3969918 | 100730 | 5.321008 | 17.730128 CSNLogControlLock | 3191989 | 115068 | 4.278325 | 20.253851 BufFreelistLock | 2014218 | 27952 | 2.699721 | 4.920009 WALInsertLocks 0 | 2750082 | 5438 | 3.686023 | 0.957177 WALInsertLocks 1 | 2584155 | 5312 | 3.463626 | 0.934999 WALInsertLocks 2 | 2477782 | 5497 | 3.321051 | 0.967562 WALInsertLocks 4 | 2375977 | 5441 | 3.184598 | 0.957705 WALInsertLocks 5 | 2349769 | 5458 | 3.149471 | 0.960697 WALInsertLocks 6 | 2329982 | 5367 | 3.122950 | 0.944680 WALInsertLocks 3 | 2415965 | 4771 | 3.238195 | 0.839774 WALInsertLocks 7 | 2316144 | 4930 | 3.104402 | 0.867761 CheckpointerCommLock| 584419 | 10794 | 0.783316 | 1.899921 XidGenLock | 391212 | 6963 | 0.524354 | 1.225602 WALBufMappingLock | 484693 | 83 | 0.649650 | 0.014609 So we're seeing delay coming fairly equally from 5 lwlocks. Thanks again - any other suggestions welcome! Cheers Mark
В списке pgsql-performance по дате отправления:
Предыдущее
От: 楊新波Дата:
Сообщение: how does the planer to estimate row when i use order by and group by
Следующее
От: Marti RaudseppДата:
Сообщение: Re: how does the planer to estimate row when i use order by and group by