Re: Scaling shared buffer eviction

On 2014-09-25 16:50:44 +0200, Andres Freund wrote:
> On 2014-09-25 10:44:40 -0400, Robert Haas wrote:
> > On Thu, Sep 25, 2014 at 10:42 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> > > On Thu, Sep 25, 2014 at 10:24 AM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> > >> On 2014-09-25 10:22:47 -0400, Robert Haas wrote:
> > >>> On Thu, Sep 25, 2014 at 10:14 AM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> > >>> > That leads me to wonder: Have you measured different, lower, number of
> > >>> > buffer mapping locks? 128 locks is, if we'd as we should align them
> > >>> > properly, 8KB of memory. Common L1 cache sizes are around 32k...
> > >>>
> > >>> Amit has some results upthread showing 64 being good, but not as good
> > >>> as 128. I haven't verified that myself, but have no reason to doubt
> > >>> it.
> > >>
> > >> How about you push the spinlock change and I crosscheck the partition
> > >> number on a multi socket x86 machine? Seems worthwile to make sure that
> > >> it doesn't cause problems on x86. I seriously doubt it'll, but ...
> > >
> > > OK.
> >
> > Another thought is that we should test what impact your atomics-based
> > lwlocks have on this.
>
> Yes, I'd planned to test that as well. I think that it will noticeably
> reduce the need to increase the number of partitions for workloads that
> fit into shared_buffers. But it won't do much about exclusive
> acquirations of the buffer mapping locks. So I think there's independent
> benefit of increasing the number.

Here we go.

Postgres was configured with.
-c shared_buffers=8GB \
-c log_line_prefix="[%m %p] " \
-c log_min_messages=debug1 \
-p 5440 \
-c checkpoint_segments=600
-c max_connections=200

Each individual measurement (#TPS) is the result of a
pgbench -h /tmp/ -p 5440 postgres -n -M prepared -c $clients -j $clients -S -T 10
run.

Master is as of ef8863844bb0b0dab7b92c5f278302a42b4bf05a.

First, a scale 200 run. That fits entirely into shared_buffers:

#scale #client #partitions #TPS
200 1 16 8353.547724 8145.296655 8263.295459
200 16 16 171014.763118 193971.091518 133992.128348
200 32 16 259119.988034 234619.421322 201879.618322
200 64 16 178909.038670 179425.091562 181391.354613
200 96 16 141402.895201 138392.705402 137216.416951
200 128 16 125643.089677 124465.288860 122527.209125

(other runs here stricken, they were contorted due some concurrent
activity. But nothing interesting).

So, there's quite some variation in here. Not very surprising given the
short runtimes, but still.

Looking at a profile nearly all the contention is around
GetSnapshotData(). That might hide the interesting scalability effects
of the partition number. So I next tried my rwlock-contention branch.

#scale #client #partitions #TPS
200 1 1 8540.390223 8285.628397 8497.022656
200 16 1 136875.484896 164302.769380 172053.413980
200 32 1 308624.650724 240502.019046 260825.231470
200 64 1 453004.188676 406226.943046 406973.325822
200 96 1 442608.459701 450185.431848 445549.710907
200 128 1 487138.077973 496233.594356 457877.992783

200 1 16 9477.217454 8181.098317 8457.276961
200 16 16 154224.573476 170238.637315 182941.035416
200 32 16 302230.215403 285124.708236 265917.729628
200 64 16 405151.647136 443473.797835 456072.782722
200 96 16 443360.377281 457164.981119 474049.685940
200 128 16 490616.257063 458273.380238 466429.948417

200 1 64 8410.981874 11554.708966 8359.294710
200 16 64 139378.312883 168398.919590 166184.744944
200 32 64 288657.701012 283588.901083 302241.706222
200 64 64 424838.919754 416926.779367 436848.292520
200 96 64 462352.017671 446384.114441 483332.592663
200 128 64 471578.594596 488862.395621 466692.726385

200 1 128 8350.274549 8140.699687 8305.975703
200 16 128 144553.966808 154711.927715 202437.837908
200 32 128 290193.349170 213242.292597 261016.779185
200 64 128 413792.389493 431267.716855 456587.450294
200 96 128 490459.212833 456375.442210 496430.996055
200 128 128 470067.179360 464513.801884 483485.000502

Not much there either.

So, on to the next scale, 1000. That doesn't fit into s_b anymore.

master:
#scale #client #partitions #TPS
1000 1 1 7378.370717 7110.988121 7164.977746
1000 16 1 66439.037413 85151.814130 85047.296626
1000 32 1 71505.487093 75687.291060 69803.895496
1000 64 1 42148.071099 41934.631603 43253.528849
1000 96 1 33760.812746 33969.800564 33598.640121
1000 128 1 30382.414165 30047.284982 30144.576494

1000 1 16 7228.883843 9479.793813 7217.657145
1000 16 16 105203.710528 112375.187471 110919.986283
1000 32 16 146294.286762 145391.938025 144620.709764
1000 64 16 134411.772164 134536.943367 136196.793573
1000 96 16 107626.878208 105289.783922 96480.468107
1000 128 16 92597.909379 86128.040557 92417.727720

1000 1 64 7130.392436 12801.641683 7019.999330
1000 16 64 120180.196384 125319.373819 126137.930478
1000 32 64 181876.697461 190578.106760 189412.973015
1000 64 64 216233.590299 222561.774501 225802.194056
1000 96 64 171928.358031 165922.395721 168283.712990
1000 128 64 139303.139631 137564.877450 141534.449640

1000 1 128 8215.702354 7209.520152 7026.888706
1000 16 128 116196.740200 123018.284948 127045.761518
1000 32 128 183391.488566 185428.757458 185732.926794
1000 64 128 218547.133675 218096.002473 208679.436158
1000 96 128 155209.830821 156327.200412 157542.582637
1000 128 128 131127.769076 132084.933955 124706.336737

rwlock:
#scale #client #partitions #TPS
1000 1 1 7377.270393 7494.260136 7207.898866
1000 16 1 79289.755569 88032.480145 86810.772569
1000 32 1 83006.336151 88961.964680 88508.832253
1000 64 1 44135.036648 46582.727314 45119.421278
1000 96 1 35036.174438 35687.025568 35469.127697
1000 128 1 30597.870830 30782.335225 30342.454439

1000 1 16 7114.602838 7265.863826 7205.225737
1000 16 16 128507.292054 131868.678603 124507.097065
1000 32 16 212779.122153 185666.608338 210714.373254
1000 64 16 239776.079534 239923.393293 242476.922423
1000 96 16 169240.934839 166021.430680 169187.643644
1000 128 16 136601.409985 139340.961857 141731.068752

1000 1 64 13271.722885 11348.028311 12531.188689
1000 16 64 129074.053482 125334.720264 125140.499619
1000 32 64 198405.463848 196605.923684 198354.818005
1000 64 64 250463.474112 249543.622897 251517.159399
1000 96 64 251715.751133 254168.028451 251502.783058
1000 128 64 243596.368933 234671.592026 239123.259642

1000 1 128 7376.371403 7301.077478 7240.526379
1000 16 128 127992.070372 133537.637394 123382.418747
1000 32 128 185807.703422 194303.674428 184919.586634
1000 64 128 270233.496350 271576.483715 262281.662510
1000 96 128 266023.529574 272484.352878 271921.597420
1000 128 128 260004.301457 266710.469926 263713.245868

Based on this I think we can fairly conclude that increasing the number
of partitions is quite the win on larger x86 machines too. Independent
of the rwlock patch, although it moves the contention points to some
degree.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services