Let’s talk about database application benchmarking. This is a skill set which, in my opinion, is one of the major differentiators between a journeyman-level DBA and a true master of the trade. In this article, I'll be giving you a brief introduction to TPC benchmarks and, in future articles, I’ll be telling you how to extract specific tidbits of very valuable information from the published benchmark results. But let’s get started with an overview.
Back in 2006, I was asked to assist several co-authors in writing a book for Rampant Press called Database Benchmarking: A Practical Approach for Oracle and SQL Server. I felt pretty well-equipped to add to the discussion. I’d already done a lot of database benchmarking in my many years as a database administrator at several different employers, all F100-sized enterprises. And one of the things I’d learned over the years was that conducting a meaningful database benchmark is very hard work.
For starters, there was the time invested. In most cases, designing and constructing the benchmark application required weeks of preparation for a whole team of people and, after the conclusion of the benchmark, required a few additional weeks of time to analyze and interpret the results. On top of that, any sort of failure in the benchmark usually required a complete reset, resulting in one step forward then two steps back, and a couple of weeks of lost time.
Good Benchmarks Require Time and Planning
Typically, because the enterprise was on the cusp of huge investment in some major new hardware and database vendor investment, the effort to find the best mix of hardware and database platform was worth the time and expense. It just made good sense, spending some tens of thousands of dollars for a solid benchmark could save the enterprise a large multiple in heightened performance and productivity, not to mention the peace of mind in knowing that the configuration we planned was a strong solution.
Our own benchmarks, it is worth noting, were precisely honed to test our own custom applications. But just because we were testing our own applications and database designs didn’t mean that we didn’t do a lot of research first. So before we would design and conduct our own benchmarks, we spent a lot of time pouring over the database performance benchmarks that were designed and audited by the lauded Transaction Processing Counsel (www.tpc.org), now known as the Transaction Performance Processing Counsel.
Which TPC Benchmark to Use?
You’re doing yourself a major disservice if you don’t read the latest TPC benchmarks for your favorite database platform and hardware vendor. Why? There’s a wide variety of extremely useful information to be gleaned from the TPC benchmark reports. And some of that information comes in unexpected forms too.
I typically start at http://tpc.org/information/results.asp where you can find the latest benchmark test results by hardware vendor and by database platform. There you’ll find the three main types of TPC benchmarks: TPC-C, -E, and -H. Both TPC-C and TPC-E are intended to benchmark a transaction processing workload. While the TPC-C benchmark consists of a rather simplistic database design and workload of five SQL statements, the TPC-E benchmark is a much more diverse set of SQL statements over a much larger and more elaborate database. Oracle and IBM DB2 still use TPC-C for benchmarking. Microsoft favors the more modern TPC-E benchmark. TPC-H, on the other hand, benchmarks for a much more business intelligence workload heavy with ad hoc queries.
So it’s simple where to start researching TPC benchmarks. When researching business intelligence applications, simply use TPC-H for your favorite database platform and hardware vendors - available on the TPC results webpage. When researching OLTP and transaction-oriented workloads, start with TPC-C for Oracle and IBM DB2 or TPC-E for Microsoft SQL Server.
In the next article, we’ll start to drill down into the specifics of reading the reports and how to find specific, hidden gems within the reports that you can use in your own database environment.