Initial 9.2 pgbench write results

Last year at this time, I was investigating things like ext3 vs xfs, how
well Linux's dirty_bytes parameter worked, and how effective a couple of
patches were on throughput & latency. The only patch that ended up
applied for 9.1 was for fsync compaction. That was measurably better in
terms of eliminating backend syncs altogether, and it also pulled up
average TPS a bit on the database scales I picked out to test it on.
That rambling group of test sets is available at
http://highperfpostgres.com/pgbench-results/index.htm

For the first round of 9.2 testing under a write-heavy load, I started
with 9.0 via the yum.postgresql.org packages for SL6, upgraded to 9.1
from there, and then used a source code build of 9.2 HEAD as of Feb 11
(58a9596ed4a509467e1781b433ff9c65a4e5b5ce). Attached is an Excel
spreadsheet showing the major figures, along with a CSV formatted copy
of that data too. Results that are ready so far are available at
http://highperfpostgres.com/results-write-9.2-cf4/index.htm

Most of that is good; here's the best and worst parts of the news in
compact form:

scale=500, db is 46% of RAM
Version Avg TPS
9.0 1961
9.1 2255
9.2 2525

scale=1000, db is 94% of RAM; clients=4
Version TPS
9.0 535
9.1 491 (-8.4% relative to 9.0)
9.2 338 (-31.2% relative to 9.1)

There's usually a tipping point with pgbench results, where the
characteristics change quite a bit as the database exceeds total RAM
size. You can see the background writer statistics change quite a bit
around there too. Last year the sharpest part of that transition
happened when exceeding total RAM; now it's happening just below that.

This test set takes about 26 hours to run in the stripped down form I'm
comparing, which doesn't even bother trying larger than RAM scales like
2000 or 3000 that might also be helpful. Most of the runtime time is
spent on the larger scale database tests, which unfortunately are the
interesting ones this year. I'm torn at this point between chasing down
where this regression came from, moving forward with testing the new
patches proposed for this CF, and seeing if this regression also holds
with SSD storage. Obvious big commit candidates to bisect this over are
the bgwriter/checkpointer split (Nov 1) and the group commit changes
(Jan 30). Now I get to pay for not having set this up to run
automatically each week since earlier in the 9.2 development cycle.

If someone else wants to try and replicate the bad part of this, best
guess for how is using the same minimal postgresql.conf changes I have
here, and picking your database scale so that the test database just
barely fits into RAM. pgbench gives rough 16MB of data per unit of
scale, and scale=1000 is 15GB; percentages above are relative to the
16GB of RAM in my server. Client count should be small, number of
physical cores is probably a good starter point (that's 4 in my system,
I didn't test below that). At higher client counts, the general
scalability improvements in 9.2 negate some of this downside.

= Server config =

The main change to the 8 hyperthreaded core test server (Intel i7-870)
for this year is bumping it from 8GB to 16GB of RAM, which effectively
doubles the scale I can reach before things slow dramatically. It's
also been updated to run Scientific Linux 6.0, giving a slightly later
kernel. That kernel does have different defaults for
dirty_background_ratio and dirty_ratio, they're 10% and 20% now
(compared to 5%/10% in last year's tests).

Drive set for tests I'm publishing so far is basically the same: 4-port
Areca card with 256MB battery-backed cache, 3 disk RAID0 for the
database, single disk for the WAL, all cheap 7200 RPM drives. The OS is
a separate drive, not connected to the caching controller. That's also
where the pgbench latency data is writing to. Idea is that this will be
similar to having around 10 drives in a production server, where you'll
also be using RAID1 for redundancy. I have some numbers brewing for
this system running with an Intel 320 series SSD, too, but they're not
ready yet.

= Test setup =

pgbench-tools has been upgraded to break down its graphs per test set
now, and there's even a configuration option to use client-side
Javascript to put that into a tab-like interface available. Thanks to
Ben Bleything for that one.

Minimal changes were made to the postgresql.conf. shared_buffers=2GB,
checkpoint_segments=64, and I left wal_buffers at its default so that
9.1 got credit for that going up. See
http://highperfpostgres.com/results-write-9.2-cf4/541/pg_settings.txt
for a full list of changes, drive mount options, and important kernel
settings. Much of that data wasn't collected in last year's
pgbench-tools runs.

= Results commentary =

For the most part the 9.2 results are quite good. The increase at high
client counts is solid, as expected from all the lock refactoring this
release has gotten. The smaller than RAM results that particularly
benefited from the 9.1 changes, particularly the scale=500 ones, leaped
as much in 9.2 as they did in 9.1. scale=500 and clients=96 is up 58%
from 9.0 to 9.2 so far.

The problems are all around the higher scales. scale=4000 (58GB) was
detuned an average of 1.7% in 9.1, which seemed a fair trade for how
much the fsync compaction helped with worse case behavior. It drops
another 7.2% on average in 9.2 so far though. The really bad one is
scale=1000 (15GB, so barely fitting in RAM now; very different from
scale=1000 last year). With this new kernel/more RAM/etc., I'm seeing
an average of a 7% TPS drop for the 9.1 changes. The drop from 9.1 to
9.2 is another 26%.

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com