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