Re: Postgres benchmark?

On Wed, 2 Jul 2008, David Siebert wrote:

> What I would like to try just for my own amusment is to build a small
> test box. It will not be a server class machine. I am thinking of using
> an AMD X2 and to start a SATA hard drive.

What I did when wanting to run similar experiments was get a moderately
expensive RAID controller (Areca ARC-1210, about $300, there are other
options) and step up to 3 drives--DB, WAL, and OS (two more cheap ATA
drives will set you back another $120).  That's just enough to get you
benchmark results that translate fairly well to server land.  If you don't
don't have a controller with a decent write cache on it, there are all
kinds of write-heavy tests that you can't get results that mean anything
useful on.

> Then I would like to test different file systems, then different
> operating systems, different amounts of ram, 32 vs 64 bit, and software
> raids.

Different operating systems is the hard one here.  My own tests trying to
compare Linux and Solaris on the same hardware gave very different
results, and it's a lot of work to get a fair comparison between two
platforms like that.  A lot of that is not being able to use the same
filesystem in the same way; UFS/ZFS are tuned very differently from ext3
for example.

When you run database benchmarks, the usual setup is to note what ratio
there is between the database and the amount of RAM, because varying the
RAM itself is kind of boring.  You end up with a curve like
http://www.westnet.com/~gsmith/content/postgresql/pgbench-scaling.htm
regardless, how much RAM you have just shifts exactly where the inflection
points are in an unsurprising way.

32/64, file systems, and software RAID are a bit more useful.  A lot of
the filesystem/RAID ground has been explored at these two links:

http://www.commandprompt.com/blogs/joshua_drake/2008/04/is_that_performance_i_smell_ext2_vs_ext3_on_50_spindles_testing_for_postgresql/

http://merlinmoncure.blogspot.com/2007/08/following-are-results-of-our-testing-of.html

> I doubt that would ever publish my results. The flame war that would
> happen would take all the fun out of it for me. I am sure that someone
> would say that since I wasn't using a server machine that my results
> where invalid, others would say that I made errors in tuning for the
> different operating systems or that X would show benefits if I was using
> a real server machine.

What you should do is send out your suggested test plan before you run the
tests and get feedback such that you're more likely to get accurate
results.  There's a lot of useful tests that could be done in this area
that are not too hard to design, but the actual follow-through takes a
long time.  If you wanted to do some of that work, you should be able to
get enough help doing that to end up with worthwhile results in the end.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD