Re: reducing the overhead of frequent table locks - now, with WIP patch

On 06/03/2011 03:17 PM, Robert Haas wrote:
[...]
> 
> As you can see, this works out to a bit more than a 4% improvement on
> this two-core box.  I also got access (thanks to Nate Boley) to a
> 24-core box and ran the same test with scale factor 100 and
> shared_buffers=8GB.  Here are the results of alternating runs without
> and with the patch on that machine:
> 
> tps = 36291.996228 (including connections establishing)
> tps = 129242.054578 (including connections establishing)
> tps = 36704.393055 (including connections establishing)
> tps = 128998.648106 (including connections establishing)
> tps = 36531.208898 (including connections establishing)
> tps = 131341.367344 (including connections establishing)
> 
> That's an improvement of about ~3.5x.  According to the vmstat output,
> when running without the patch, the CPU state was about 40% idle.
> With the patch, it dropped down to around 6%.

nice - but lets see on real hardware...

Testing this on a brand new E7-4850 4 Socket/10cores+HT Box - so 80
hardware threads:

first some numbers with -HEAD(-T 120, runtimes at lower -c counts have
fairly high variation in the results, first number is the number of
connections/threads):


-j1:    tps = 7928.965493 (including connections establishing)
-j8:    tps = 53610.572347 (including connections establishing)
-j16:    tps = 80835.446118 (including connections establishing)
-j32:    tps = 75666.731883 (including connections establishing)
-j40:    tps = 74628.568388 (including connections establishing)
-j64.    tps = 68268.081973 (including connections establishing)
-c80    tps = 66704.216166 (including connections establishing)

postgresql is completely lock limited in this test anything beyond
around -j10 is basically not able to push the box to more than 80% IDLE(!)


and now with the patch applied:

-j1:    tps = 7783.295587 (including connections establishing)    
-j8:    tps = 44361.661947 (including connections establishing)
-j16:    tps = 92270.464541 (including connections establishing)
-j24:    tps = 108259.524782 (including connections establishing)
-j32:    tps = 183337.422612 (including connections establishing)
-j40    tps = 209616.052430 (including connections establishing)
-j48:    tps = 229621.292382 (including connections establishing)
-j56:    tps = 218690.391603 (including connections establishing)
-j64:    tps = 188028.348501 (including connections establishing)
-j80.    tps = 118814.741609 (including connections establishing)


so much better - but I still think there is some headroom left still,
although pgbench itself is a CPU hog in those benchmark with eating up
to 10 cores in the worst case scenario - will retest with sysbench which
in the past showed more reasonable CPU usage for me.



and a profile(patched code) for the -j48(aka fastest) case:

731535   11.8408  postgres                 s_lock
291878    4.7244  postgres                 LWLockAcquire
242373    3.9231  postgres                 AllocSetAlloc
239083    3.8698  postgres                 LWLockRelease
202341    3.2751  postgres                 SearchCatCache
190055    3.0763  postgres                 hash_search_with_hash_value
187148    3.0292  postgres                 base_yyparse
173265    2.8045  postgres                 GetSnapshotData
75700     1.2253  postgres                 core_yylex
74974     1.2135  postgres                 MemoryContextAllocZeroAligned
61404     0.9939  postgres                 _bt_compare
57529     0.9312  postgres                 MemoryContextAlloc


and one for the -j80 case(also patched).


485798   48.9667  postgres                 s_lock
60327     6.0808  postgres                 LWLockAcquire
57049     5.7503  postgres                 LWLockRelease
18357     1.8503  postgres                 hash_search_with_hash_value
17033     1.7169  postgres                 GetSnapshotData
14763     1.4881  postgres                 base_yyparse
14460     1.4575  postgres                 SearchCatCache
13975     1.4086  postgres                 AllocSetAlloc
6416      0.6467  postgres                 PinBuffer
5024      0.5064  postgres                 SIGetDataEntries
4704      0.4741  postgres                 core_yylex
4625      0.4662  postgres                 _bt_compare



Stefan