Re: measuring spinning - Mailing list pgsql-hackers

On Thu, Jan 12, 2012 at 4:00 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
> Please can you repeat the test, focusing on minutes 10-30 of a 30
> minute test run. That removes much of the noise induced during cache
> priming.
>
> My suggested size of database is one that is 80% size of RAM, with
> shared_buffers set to 40% of RAM or 2GB whichever is higher. That
> represents the common case where people know how big their data is and
> purchase RAM accordingly, then set shared_buffers in line with current
> wisdom.

OK, first, let me remember to attach the patch this time, since as
Thom pointed out, I didn't do that before.

Second, those guidelines sound quite different from what I've heard
before, which is 25% of RAM up to a *maximum* of 8GB.  This machine
has 383GB of RAM so 40% of memory would be shared_buffers = 153GB,
which is a lot bigger than I've heard recommended in the past.  So I
could potentially do that test, but I'm a bit surprised by the
proposed configuration.  So far I have been focusing mostly on pgbench
at scale factor 100, because that fits into 8GB of shared_buffers.
Using a larger working set that does not fit in shared_buffers will
expose new bottlenecks, but I've found it's most useful to me to try
to isolate the effect of different bottlenecks, which means running
the simplest workload that demonstrates at least one meaningful
problem.  Anyway, please let me know your thoughts on this.

On x86, we are apparently, getting to the point where things scale
pretty well.  On Nate Boley's 32-core machine (which is still busy
with other work, so I can't do any more tests there right now),
pgbench on  unlogged tables at scale factor 100 scales nearly linearly
all the way out to 32 clients.  On permanent tables, it is
bottlenecked by WALInsertLock and throughput is roughly halved.  On
the other hand, on Itanium, we don't scale even on a read-only
workload.  It is not yet clear to me why, but part of the problem may
be that spinlock contention seems to be much more damaging on Itanium
than it is on x86, and we have way too much of it.

I ran some 30-minute tests and here are the top spinners.  I have not
isolated the first 10 minutes from the remainder of the tests,
although that could possibly be done.  I think I'd need to start the
server, run pgbench for 10 minutes, add a marker line to the log,
restart pgbench for another 20 minutes, and then only process the log
data after the marker point.  Doable, but I haven't done it yet, and
I'm not convinced the results would be much different.  I think
latency would be a more interesting thing to graph against time, but I
haven't gotten there yet either.

SELECT-only test:

lwlock 34: shacq 29192894 exacq 34021 blk 47 spin 6468
lwlock 45: shacq 29701770 exacq 34235 blk 42 spin 7393
lwlock 40: shacq 30353555 exacq 35257 blk 45 spin 7585
lwlock 33: shacq 34585153 exacq 35175 blk 61 spin 9450
lwlock 41: shacq 36603119 exacq 35135 blk 50 spin 10231
lwlock 48: shacq 41095856 exacq 35196 blk 55 spin 10723
lwlock 42: shacq 36566360 exacq 34619 blk 65 spin 11171
lwlock 47: shacq 40809540 exacq 34976 blk 77 spin 12168
lwlock 43: shacq 38972147 exacq 35256 blk 48 spin 12253
lwlock 35: shacq 41278265 exacq 35171 blk 71 spin 13023
lwlock 44: shacq 42915161 exacq 35296 blk 54 spin 13704
lwlock 46: shacq 45259281 exacq 35113 blk 69 spin 14633
lwlock 39: shacq 40758171 exacq 35174 blk 52 spin 15125
lwlock 38: shacq 44954521 exacq 34629 blk 70 spin 17291
lwlock 37: shacq 45250155 exacq 34834 blk 69 spin 17422
lwlock 328934: shacq 205772443 exacq 0 blk 0 spin 372594
lwlock 4: shacq 205775132 exacq 356 blk 162 spin 439076
lwlock 36: shacq 246651001 exacq 34587 blk 392 spin 508843

Unlogged tables:

lwlock 397680: shacq 27253037 exacq 18 blk 6 spin 7130
lwlock 328724: shacq 28593420 exacq 136 blk 17 spin 8417
lwlock 7: shacq 0 exacq 27803235 blk 713207 spin 10802
lwlock 3: shacq 430 exacq 27801696 blk 519648 spin 11206
lwlock 328942: shacq 56285945 exacq 0 blk 0 spin 39198
lwlock 46: shacq 63099999 exacq 47931 blk 1677 spin 39419
lwlock 45: shacq 63003036 exacq 48607 blk 1669 spin 39541
lwlock 40: shacq 65456415 exacq 48770 blk 1751 spin 44276
lwlock 33: shacq 68438336 exacq 48371 blk 1882 spin 45955
lwlock 35: shacq 68170010 exacq 48193 blk 1869 spin 47024
lwlock 39: shacq 65045489 exacq 47748 blk 1798 spin 47246
lwlock 41: shacq 67985476 exacq 48075 blk 1778 spin 48409
lwlock 37: shacq 69042800 exacq 48529 blk 1854 spin 49465
lwlock 34: shacq 71838042 exacq 48021 blk 1816 spin 51078
lwlock 38: shacq 69249910 exacq 48673 blk 1861 spin 51443
lwlock 36: shacq 84116654 exacq 48646 blk 1832 spin 52778
lwlock 44: shacq 74827084 exacq 48039 blk 1934 spin 54133
lwlock 42: shacq 76580702 exacq 47829 blk 2045 spin 60652
lwlock 48: shacq 95540241 exacq 48460 blk 2295 spin 69160
lwlock 47: shacq 104968331 exacq 48252 blk 2998 spin 101368
lwlock 11: shacq 103820073 exacq 28055301 blk 5097966 spin 138136
lwlock 43: shacq 128230139 exacq 48940 blk 3785 spin 168432
lwlock 4: shacq 264853747 exacq 27802153 blk 19247506 spin 1451265
grand total: shacq 2833461613 exacq 648159963 blk 27379317 spin 2661201

Permanent tables:

lwlock 391776: shacq 5671517 exacq 166 blk 6 spin 1987
lwlock 328712: shacq 7809830 exacq 9903 blk 6070 spin 2090
lwlock 403574: shacq 23866187 exacq 214 blk 18 spin 6064
lwlock 328722: shacq 32127795 exacq 443 blk 118 spin 8578
lwlock 3: shacq 903 exacq 30753692 blk 339559 spin 10098
lwlock 48: shacq 68453969 exacq 86069 blk 2153 spin 29716
lwlock 44: shacq 54326564 exacq 86681 blk 2009 spin 30587
lwlock 45: shacq 53928523 exacq 86577 blk 2019 spin 30991
lwlock 41: shacq 51504788 exacq 86027 blk 1985 spin 31166
lwlock 35: shacq 55746922 exacq 86826 blk 2033 spin 33160
lwlock 39: shacq 54222182 exacq 86429 blk 2005 spin 33313
lwlock 34: shacq 54187983 exacq 86919 blk 2047 spin 33453
lwlock 42: shacq 53684027 exacq 87041 blk 2180 spin 34555
lwlock 47: shacq 57833286 exacq 87531 blk 2236 spin 35677
lwlock 33: shacq 57295122 exacq 86670 blk 2047 spin 35740
lwlock 38: shacq 55993328 exacq 86322 blk 2162 spin 36080
lwlock 40: shacq 60006152 exacq 87487 blk 2362 spin 38188
lwlock 328940: shacq 62700475 exacq 0 blk 0 spin 39076
lwlock 36: shacq 62288934 exacq 87076 blk 2313 spin 39439
lwlock 43: shacq 84290403 exacq 85797 blk 2871 spin 54505
lwlock 46: shacq 90335445 exacq 86210 blk 3252 spin 65658
lwlock 37: shacq 104776289 exacq 86025 blk 3840 spin 101361
lwlock 11: shacq 233629628 exacq 31058220 blk 7662350 spin 244229
lwlock 7: shacq 0 exacq 189140501 blk 45755715 spin 603739
lwlock 4: shacq 297970356 exacq 30754553 blk 21400168 spin 1539598
grand total: shacq 2579309283 exacq 884240878 blk 79245791 spin 3134478

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company