basic pgbench runs with various performance-related patches - Mailing list pgsql-hackers

There was finally some time available on Nate Boley's server, which he
has been kind enough to make highly available for performance testing
throughout this cycle, and I got a chance to run some benchmarks
against a bunch of the perfomance-related patches in the current
CommitFest.  Specifically, I did my usual pgbench tests: 3 runs at
scale factor 100, with various client counts.  I realize that this is
not the only or even most interesting thing to test, but I felt it
would be useful to have this information as a baseline before
proceeding to more complicated testing.  I have another set of tests
running now with a significantly different configuration that will
hopefully provide some useful feedback on some of the things this test
fails to capture, and will post the results of the tests (and the
details of the test configuration) as soon as those results are in.

For the most part, I only tested each patch individually, but in one
case I also tested two patches together (buffreelistlock-reduction-v1
with freelist-ok-v2).  Results are the median of three five-minute
test runs, with one exception: buffreelistlock-reduction-v1 crapped
out during one of the test runs with the following errors, so I've
shown the results for both of the successful runs (though I'm not sure
how relevant the numbers are given the errors, as I expect there is a
bug here somewhere):

log.ws.buffreelistlock-reduction-v1.1.100.300:ERROR:  could not read
block 0 in file "base/20024/11780": read only 0 of 8192 bytes
log.ws.buffreelistlock-reduction-v1.1.100.300:CONTEXT:  automatic
analyze of table "rhaas.public.pgbench_branches"
log.ws.buffreelistlock-reduction-v1.1.100.300:ERROR:  could not read
block 0 in file "base/20024/11780": read only 0 of 8192 bytes
log.ws.buffreelistlock-reduction-v1.1.100.300:CONTEXT:  automatic
analyze of table "rhaas.public.pgbench_tellers"
log.ws.buffreelistlock-reduction-v1.1.100.300:ERROR:  could not read
block 0 in file "base/20024/11780": read only 0 of 8192 bytes
log.ws.buffreelistlock-reduction-v1.1.100.300:CONTEXT:  automatic
analyze of table "rhaas.pg_catalog.pg_database"
log.ws.buffreelistlock-reduction-v1.1.100.300:ERROR:  could not read
block 0 in file "base/20024/11780": read only 0 of 8192 bytes
log.ws.buffreelistlock-reduction-v1.1.100.300:STATEMENT:  vacuum
analyze pgbench_branches
log.ws.buffreelistlock-reduction-v1.1.100.300:ERROR:  could not read
block 0 in file "base/20024/11780": read only 0 of 8192 bytes
log.ws.buffreelistlock-reduction-v1.1.100.300:STATEMENT:  select
count(*) from pgbench_branches

Just for grins, I ran the same set of tests against REL9_1_STABLE, and
the results of those tests are also included below.  It's worth
grinning about: on this test, at 32 clients, 9.2devel (as of commit
4f42b546fd87a80be30c53a0f2c897acb826ad52, on which all of these tests
are based) is 25% faster on permanent tables, 109% faster on unlogged
tables, and 474% faster on a SELECT-only test.

Here's the test configuration:

shared_buffers = 8GB
maintenance_work_mem = 1GB
synchronous_commit = off
checkpoint_segments = 300
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
wal_writer_delay = 20ms

And here are the results.  For everything against master, I've also
included the percentage speedup or slowdown vs. the same test run
against master.  Many of these numbers are likely not statistically
significant, though some clearly are.

** pgbench, permanent tables, scale factor 100, 300 s **
1 master 686.038059
8 master 4425.744449
16 master 7808.389490
24 master 13276.472813
32 master 11920.691220
80 master 12560.803169
1 REL9_1_STABLE 627.879523 -8.5%
8 REL9_1_STABLE 4188.731855 -5.4%
16 REL9_1_STABLE 7433.309556 -4.8%
24 REL9_1_STABLE 10496.411773 -20.9%
32 REL9_1_STABLE 9547.804833 -19.9%
80 REL9_1_STABLE 7197.655050 -42.7%
1 background-clean-slru-v2 629.518668 -8.2%
8 background-clean-slru-v2 4794.662182 +8.3%
16 background-clean-slru-v2 8062.151120 +3.2%
24 background-clean-slru-v2 13275.834722 -0.0%
32 background-clean-slru-v2 12024.410625 +0.9%
80 background-clean-slru-v2 12113.589954 -3.6%
1 buffreelistlock-reduction-v1 512.828482 -25.2%
8 buffreelistlock-reduction-v1 4765.576805 +7.7%
16 buffreelistlock-reduction-v1 8030.477792 +2.8%
24 buffreelistlock-reduction-v1 13118.481248 -1.2%
32 buffreelistlock-reduction-v1 11895.847998 -0.2%
80 buffreelistlock-reduction-v1 12015.291045 -4.3%
1 buffreelistlock-reduction-v1-freelist-ok-v2 621.960997 -9.3%
8 buffreelistlock-reduction-v1-freelist-ok-v2 4650.200642 +5.1%
16 buffreelistlock-reduction-v1-freelist-ok-v2 7999.167629 +2.4%
24 buffreelistlock-reduction-v1-freelist-ok-v2 13070.123153 -1.6%
32 buffreelistlock-reduction-v1-freelist-ok-v2 11808.986473 -0.9%
80 buffreelistlock-reduction-v1-freelist-ok-v2 12136.960028 -3.4%
1 freelist-ok-v2 629.832419 -8.2%
8 freelist-ok-v2 4800.267011 +8.5%
16 freelist-ok-v2 8018.571815 +2.7%
24 freelist-ok-v2 13122.167158 -1.2%
32 freelist-ok-v2 12004.261737 +0.7%
80 freelist-ok-v2 12188.211067 -3.0%
1 group-commit-2012-01-21 614.425851 -10.4%
8 group-commit-2012-01-21 4705.129896 +6.3%
16 group-commit-2012-01-21 7962.131701 +2.0%
24 group-commit-2012-01-21 13074.939290 -1.5%
32 group-commit-2012-01-21 12458.962510 +4.5%
80 group-commit-2012-01-21 12907.062908 +2.8%
1 removebufmgrfreelist-v1 624.232337 -9.0%
8 removebufmgrfreelist-v1 4787.757828 +8.2%
16 removebufmgrfreelist-v1 7987.562255 +2.3%
24 removebufmgrfreelist-v1 13185.179180 -0.7%
32 removebufmgrfreelist-v1 11988.099057 +0.6%
80 removebufmgrfreelist-v1 11998.675541 -4.5%
1 xloginsert-scale-6 615.631353 -10.3%
8 xloginsert-scale-6 4717.698532 +6.6%
16 xloginsert-scale-6 8118.873611 +4.0%
24 xloginsert-scale-6 14017.789384 +5.6%
32 xloginsert-scale-6 17214.720336 +44.4%
80 xloginsert-scale-6 16803.463204 +33.8%

** pgbench, unlogged tables, scale factor 100, 300 s **
1 master 677.610878
8 master 5028.697280
16 master 8335.044876
24 master 15210.853801
32 master 21479.647280
80 master 21290.549767
1 REL9_1_STABLE 666.931288 -1.6%
8 REL9_1_STABLE 4534.211018 -9.8%
16 REL9_1_STABLE 7844.550171 -5.9%
24 REL9_1_STABLE 11825.330626 -22.3%
32 REL9_1_STABLE 10267.087265 -52.2%
80 REL9_1_STABLE 7376.673339 -65.4%
1 background-clean-slru-v2 671.505881 -0.9%
8 background-clean-slru-v2 5104.108071 +1.5%
16 background-clean-slru-v2 8451.940663 +1.4%
24 background-clean-slru-v2 15527.042960 +2.1%
32 background-clean-slru-v2 21613.149203 +0.6%
80 background-clean-slru-v2 20790.135768 -2.4%
1 buffreelistlock-reduction-v1 675.186982 -0.4%
8 buffreelistlock-reduction-v1 5089.185745 +1.2%
16 buffreelistlock-reduction-v1 8456.887468 +1.5%
24 buffreelistlock-reduction-v1 15539.905486 +2.2%
32 buffreelistlock-reduction-v1 21562.413227 +0.4%
80 buffreelistlock-reduction-v1 21122.885930 -0.8%
1 buffreelistlock-reduction-v1-freelist-ok-v2 667.265247 -1.5%
8 buffreelistlock-reduction-v1-freelist-ok-v2 5085.813672 +1.1%
16 buffreelistlock-reduction-v1-freelist-ok-v2 8320.059951 -0.2%
24 buffreelistlock-reduction-v1-freelist-ok-v2 15685.366152 +3.1%
32 buffreelistlock-reduction-v1-freelist-ok-v2 21565.811574 +0.4%
80 buffreelistlock-reduction-v1-freelist-ok-v2 20945.756221 -1.6%
1 freelist-ok-v2 680.578723 +0.4%
8 freelist-ok-v2 4680.063074 -6.9%
16 freelist-ok-v2 8414.815514 +1.0%
24 freelist-ok-v2 15655.998340 +2.9%
32 freelist-ok-v2 21423.826249 -0.3%
80 freelist-ok-v2 21149.608334 -0.7%
1 group-commit-2012-01-21 666.329625 -1.7%
8 group-commit-2012-01-21 4940.074794 -1.8%
16 group-commit-2012-01-21 8293.787275 -0.5%
24 group-commit-2012-01-21 15370.196487 +1.0%
32 group-commit-2012-01-21 21652.117344 +0.8%
80 group-commit-2012-01-21 21154.700111 -0.6%
1 removebufmgrfreelist-v1 672.889249 -0.7%
8 removebufmgrfreelist-v1 5135.192248 +2.1%
16 removebufmgrfreelist-v1 8487.267114 +1.8%
24 removebufmgrfreelist-v1 15561.649674 +2.3%
32 removebufmgrfreelist-v1 21526.256680 +0.2%
80 removebufmgrfreelist-v1 21439.081729 +0.7%
1 xloginsert-scale-6 663.599217 -2.1%
8 xloginsert-scale-6 4928.240201 -2.0%
16 xloginsert-scale-6 8345.715047 +0.1%
24 xloginsert-scale-6 15314.188610 +0.7%
32 xloginsert-scale-6 21382.161572 -0.5%
80 xloginsert-scale-6 20555.003740 -3.5%

** pgbench, SELECT-only, scale factor 100, 300 s **
1 master 4474.415026
8 master 33852.480081
16 master 63367.390439
24 master 103869.975640
32 master 218778.460422
80 master 221926.129900
1 REL9_1_STABLE 4377.493967 -2.2%
8 REL9_1_STABLE 27006.472299 -20.2%
16 REL9_1_STABLE 44503.077293 -29.8%
24 REL9_1_STABLE 42646.367806 -58.9%
32 REL9_1_STABLE 38113.938792 -82.6%
80 REL9_1_STABLE 37158.548724 -83.3%
1 background-clean-slru-v2 4448.990827 -0.6%
8 background-clean-slru-v2 32954.904564 -2.7%
16 background-clean-slru-v2 62163.189691 -1.9%
24 background-clean-slru-v2 104054.424938 +0.2%
32 background-clean-slru-v2 219188.777491 +0.2%
80 background-clean-slru-v2 225528.290724 +1.6%
1 buffreelistlock-reduction-v1 ** 4441.150432 4448.333138
8 buffreelistlock-reduction-v1 34063.227940 +0.6%
16 buffreelistlock-reduction-v1 63506.409797 +0.2%
24 buffreelistlock-reduction-v1 104399.970382 +0.5%
32 buffreelistlock-reduction-v1 216559.933170 -1.0%
80 buffreelistlock-reduction-v1 222285.411884 +0.2%
1 buffreelistlock-reduction-v1-freelist-ok-v2 4440.850402 -0.8%
8 buffreelistlock-reduction-v1-freelist-ok-v2 33818.438901 -0.1%
16 buffreelistlock-reduction-v1-freelist-ok-v2 62024.613901 -2.1%
24 buffreelistlock-reduction-v1-freelist-ok-v2 107318.457734 +3.3%
32 buffreelistlock-reduction-v1-freelist-ok-v2 218993.937402 +0.1%
80 buffreelistlock-reduction-v1-freelist-ok-v2 224804.303649 +1.3%
1 freelist-ok-v2 4448.520427 -0.6%
8 freelist-ok-v2 32987.340692 -2.6%
16 freelist-ok-v2 63427.003052 +0.1%
24 freelist-ok-v2 105891.677170 +1.9%
32 freelist-ok-v2 224901.447195 +2.8%
80 freelist-ok-v2 226073.792525 +1.9%
1 group-commit-2012-01-21 4355.726544 -2.7%
8 group-commit-2012-01-21 33000.320589 -2.5%
16 group-commit-2012-01-21 61813.842365 -2.5%
24 group-commit-2012-01-21 104561.991949 +0.7%
32 group-commit-2012-01-21 215981.557010 -1.3%
80 group-commit-2012-01-21 222421.484864 +0.2%
1 removebufmgrfreelist-v1 4465.215178 -0.2%
8 removebufmgrfreelist-v1 34339.075796 +1.4%
16 removebufmgrfreelist-v1 64186.808150 +1.3%
24 removebufmgrfreelist-v1 105002.934233 +1.1%
32 removebufmgrfreelist-v1 220531.094226 +0.8%
80 removebufmgrfreelist-v1 227728.566369 +2.6%
1 xloginsert-scale-6 4347.609435 -2.8%
8 xloginsert-scale-6 33494.005898 -1.1%
16 xloginsert-scale-6 63033.771029 -0.5%
24 xloginsert-scale-6 104033.236840 +0.2%
32 xloginsert-scale-6 221178.054981 +1.1%
80 xloginsert-scale-6 223804.483593 +0.8%

I also went through the logs of all the test runs, looking for errors
or warnings.  Other than the one hard error mentioned above, the only
thing I found was:

WARNING:  corrupted statistics file "pg_stat_tmp/pgstat.stat"

...which happened *a lot*.  Especially on 9.1.  Across all test runs,
here is the total number of occurrences on this message by branch:
     5 background-clean-slru-v2     5 master     9 xloginsert-scale-6    11 freelist-ok-v2    13
group-commit-2012-01-21   15 buffreelistlock-reduction-v1    17 buffreelistlock-reduction-v1-freelist-ok-v2    24
removebufmgrfreelist-v1 1509 REL9_1_STABLE
 

Of the 1509 occurrences of this error messages that occurred on the
REL9_1_STABLE branch, 503 were produced in the 1 client configuration
and 1004 in the 80 client configuration.  I have no explanation for
why those particular numbers of clients should be more problematic
than 8, 16, 24, or 32 - it may be that the system randomly gets into
some kind of a bad state that causes it to spew many copies of this
message, and that just happened to occur on those test runs but not
the others.  I don't know.  But it feels like there's probably a bug
here somewhere.

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