Skip site navigation (1) Skip section navigation (2)

Initial 9.2 pgbench write results

From: Greg Smith <greg(at)2ndQuadrant(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Initial 9.2 pgbench write results
Date: 2012-02-14 18:45:52
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
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

For the first round of 9.2 testing under a write-heavy load, I started 
with 9.0 via the 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

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 
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

Attachment: pgbench-9.2-cf4.csv
Description: text/csv (2.9 KB)
Attachment: pgbench-9.2-cf4.xls
Description: application/ (18.5 KB)


pgsql-hackers by date

Next:From: Greg SmithDate: 2012-02-14 20:25:08
Subject: Re: Initial 9.2 pgbench write results
Previous:From: Bruce MomjianDate: 2012-02-14 16:11:20
Subject: Re: pg_test_fsync performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group