PostgreSQL Server Benchmarks: Part One — Background

27 Feb 2011

We (by which I mean Estately) recently picked up a new database server. Following the recommendations of pretty much the entire PostgreSQL community, I’m starting my performance tuning by thoroughly benchmarking the new machine in a variety of configurations to determine the optimal setup. This is the first in a seven-part series documenting this process.

My goal is to lay out a detailed, methodical plan to discover how best to configure our server. I know that a lot of this ground is well-covered, but doing it for oneself is valuable. The series will be laid out in seven parts:

This post discusses the hardware we have and the questions we want answered. It also goes into detail about how I’ll be answering those questions. For the most part, specifics will be discussed in the results posts, but there are a few things that are worth going over in general before we dive into testing and graphing and stuff.

Background and Notes

Before we get started, I want to plug an excellent book about PostgreSQL performance: PostgreSQL 9.0 High Performance by Gregory Smith. It’s fantastic. If you run PostgreSQL in production, buy it.

Greg is one of the superstars of performance in the PostgreSQL community and much of what follows is based on his advice in the book. Even though it references 9.0 in the title, there’s still a lot of really solid, basic information in there, along the lines of “this is how you set up a database server”. Hell, it’s probably useful even if you don’t use PostgreSQL.

Other resources of interest are Greg’s blog and website, and a talk he gave at PGCon 2009 called Database Hardware Benchmarking. You might also be interested in Josh Berkus’ Performance Whack-a-Mole II. Videos of both are available on the PostgreSQL wiki.

With all that out of the way, let’s dive in.

The Hardware

Some details about the server. It’s a Dell R610, configured thusly:

Two notes about the hardware:

Our current server has much slower CPUs, less memory, and is running 8x SATA(!) 7200 RPM(!!) drives. So, by any measure, this new server should be much, much faster. The original design for the new server called for the slower internal drives to be used for the operating system, the faster internal drives to be used for PostgreSQL’s WAL storage, and the big, fast drives in the external array to be used for the actual cluster data. But as I mention above, we don’t want to take any chances, so it’s time to measure real performance to see what’s what.

Update: Lots of folks have asked why we’re using mechanical drives instead of SSDs. The answer is pretty simple: cost. Our working set is approximately 140GB and growing approximately 500MB per week. We’re working to get that size down, but that’s where we are right now. A future optimization will certainly be SSDs, but now is not the right time.

Benchmark Methodology

As recommended in Greg Smith’s benchmarking talk, I’ll start by testing the memory and CPU to make sure that I’ve got the BIOS dialed in correctly. Dell ships servers with the BIOS set for power savings, so I’m expecting to see a lot of changes here.

I’ll install Ubuntu onto one of the internal 146GB 10k RPM drives. In production these will be a RAID-1 with the OS on them, but for now we’ll be fine in this configuration. I’ll run the memory and CPU benchmarks on the bare system. Once I’ve collected data on the memory and CPUs, I’ll configure the system with the optimal settings for both. Then, I’ll move on to the filesystem and database performance benchmarks. Questions I want to answer here:

Note that when I say “software RAID” I mean Linux Software RAID.

At the same time, I’ll test various filesystem tuning options, like changing ext4’s journaling mode, and disabling atime. In each variation, I’ll test both “raw” filesystem performance and database performance for that configuration. Specifics will be discussed in the results posts.

The Software

We’ve standardized our infrastructure on Ubuntu 10.04 LTS (aka Lucid Lynx), so that will be my test OS. I would have really liked to try FreeBSD as well, since it would allow me to test ufs2 and zfs performance, but the FreeBSD 8.2-RELEASE installer just hangs no matter what I do.

Memory tests will be conducted using memtest86+ v4.20, booted from a USB stick. Greg Smith also recommends in his book running the STREAM memory bandwidth benchmark. He’s built some tooling around this, so I’ll be using his stream-scaling script, booted into the OS.

For CPU tests, I’ll be running sysbench’s CPU benchmarks. They’re kind of silly, but they give a good idea of whether certain BIOS settings make the CPU significantly faster.

Disk and filesystem tests will use three(ish) tools:

Database tests will primarily use the pgbench tool that ships with PostgreSQL. These tests will aim to compare various database tuning parameters. Much, much more detail will be covered in the database results post.

In order to get the most consistent, comparable results, all of the tools used for benchmarking will be built from source. As of this writing, the current version of sysbench is 0.4.12. For bonnie++, I’ll be using the “development” release 1.96. For PostgreSQL, I’ll use 9.0.4.

Caveats

I’m aware that there are some flaws in my methodology.

The biggest one is that the “optimal” configuration according to synthetic benchmarks may not be the optimal configuration in production. My goal here is to get the best raw performance out of the machine as a starting point. I’ll write about application-specific tuning later.

Relatedly, optimizing individual components in order may not be the best method as it may mask certain cases where a “sub-optimal” configuration in one component boosts overall performance.

All that said, I think we can agree that faster is better, and if I can arrive at the fastest possible system for a basic database then I’m in a much better place to start optimizing for my specific application.

It’s also worth noting that our actual production dataset is too large to fit on the OS drives, so I’m not testing that option. Frankly, I don’t really care if the operating system partition is slower than the rest of the storage, since most of the time it’s just sitting there not doing anything.

Final Notes

I want to thank a few people for chatting with me while I was working on this post and providing invaluable technical and editorial advice:

Finally, if I’ve missed something or you have suggestions, please get in touch on Twitter where I’m @bleything, or by email at ben@bleything.net. I’m counting on you, interfriends!

« go back