Re: 60 core performance with 9.3 - Mailing list 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