PGEast, Hardware Benchmarking, and the PG Performance Farm

Today is the deadline for the special room rate at the hotel hosting this month’s PostgreSQL Conference East 2010.  If you’ve been procrastinating booking a spot at the conference, as of tomorrow that will start costing you.

My talk is on Database Hardware Benchmarking and is scheduled for late afternoon on the first day, Thursday March 25th.  Those who might have seen this talk before, either live at PGCon 2009 or via the video link available there, might be wondering if I’m going to drag out the same slides and talk again.  Not the case; while the general philosophy of the talk (“trust no one, run your own benchmarks”) stays the same, the examples and test mix suggested have been updated to reflect another year worth of hardware advances, PostgreSQL work, and my own research during that time.  The Intel vs. AMD situation in particular has changed quite a bit, requiring a new set of memory benchmarks to really follow what’s going on now.

And PostgreSQL 9.0 fixed a major problem that kept it from normally delivering accurate results on Linux, due to a kernel regression that made much worse an already far too common situation:  it’s easy for a single pgbench client to become the bottleneck when running it, rather than the database itself.  The review I did for multi-threaded pgbench (which can also be multi-process pgbench on systems that don’t support threads) suggested a solid >30% speedup even on systems that didn’t have the bad kernel incompatibility on them.  Subsequent testing suggests it can easily take 8 pgbench processes to get full throughput out of even inexpensive modern processors under recent Linux kernels.  I’ll go over exactly how that ends up playing out on such systems, and how this new feature makes it possible again to use pgbench as the primary way to measure CPU performance running the database.

Recently I’ve also made an updated to the git repo for pgbench-tools that adds working support for PostgreSQL 8.4 and basic 9.0 compatibility, and the next update will include support for the multi-threaded option now that I’ve mapped out how that needs to work.  This is all leading somewhere.  Once we have accurate measurements for PostgreSQL performance that are CPU limited on the server side, something that hasn’t often been the case for over two years now, those again become a useful way to monitor for performance regressions in the PostgreSQL codebase.  The tests included will need to expand for that to cover more eventually, but for now we’ve reached a point where pgbench can be used to find regressions that impact how fast simple SELECT statements execute.  I know that works as expected, because every time I accidentally build PostgreSQL with assertions on that’s caught because I see the average processing rate drop dramatically.

Once I’ve got a couple of systems setup here to test for such regressions, the question becomes how to automate what I’m doing, and then to do the same thing against a wider range of build checkouts.  Ideally, you’d be able to see a graph of average SELECT performance each day, broken down by version, so that when a commit that reduced it was introduced it would immediately be obvious when the performance dropped.  This is the dream goal for building a performance farm similar to the PostgreSQL buildfarm.    The pieces are almost all together now:  my pgbench parts are wrapping up, extensions to the buildfarm to make it speak directly to git are moving along (not a requirement, but nobody working on this project wants to use CVS if we can avoid it), and the main thing missing at this point is someone to put the time in to integrate what I’ve been doing into a buildfarm-like client.

And it looks like we now have a corporate sponsor willing to help with that chunk of work, who I’ll let take credit for when we’re all done, and that’s scheduled to happen this summer.  I fully expect that PostgreSQL 9.1 development, and 9.0 backpatching, is going to happen with an early performance farm in place to guard against performance regressions.  If we can backport the new multi-threaded pgbench to older PostgreSQL versions we might include them in the mix as well.  I already have a backport of the 8.3 pgbench, which has a lot of improvements, I maintain just for testing 8.2 systems.  With pgbench as a fairly standalone contrib module, it’s possible to build a later one different from the rest of the system, so long as it doesn’t expect newer database features to exist too.

If that’s something you’re interested in, my talk at the conference is going to map out the foundations I expect it to be built on.  Regardless, hope you can make it to conference and enjoy the long list of talks being presented there.