TPC-like Database Benchmarking Tools

 

This post describes five popular tools for benchmarking relational databases: HammerDB, Quest Benchmark Factory, SwingBench, pgbench, and Oracle RAT. 

Previously, I described TPC and TPC-like benchmarks for relational databases.  Recall a benchmark is "like" a TPC benchmark if it follows some of the rules but not all of them.  The tools described in this post follow some of the TPC's rules so we call them "TPC-like".  Publishing results is generally not allowed by commercial DBMS vendors, and use of the benchmark trademark is also restricted.

Ok then, let's look at the most commonly used benchmarking tools for relational databases.

HammerDB is my #1 choice for benchmarking relational databases.  HammerDB supports every major DBMS on Linux and Windows including bare metal, VM, and cloud.  This means you can use one tool for all environments.  You can even compare performance across platforms or DBMS, such as comparing Oracle on Linux to SQL on Windows.  It is also highly configurable and has a user-friendly GUI.  The downside is it only has two older benchmarks based on TPC-C and TPC-H, which HammerDB calls TPROC-C and TPROC-H.  Adding a TPC-E-like and a TPC-DS-like benchmark have been mentioned, but that could be years out.  HammerDB is free open source software written by Steve Shaw.  It is available through the TPC's GitHub site or you can get a binary installer from https://www.hammerdb.com/download.html.

HammerDB 4.0 Dashboard

Benchmark Factory is a cost-based tool developed by the database geniuses at Quest (Quest was acquired by Dell and renamed, but then Dell decided to rename Dell Software Group back to Quest).  I have used Quest Benchmark Factory for TPC-C, E, and H like testing and found it to be a bit overwhelming at first simply because it is so configurable.  Tech support was responsive and helpful.  None of the other benchmarking tools in this post have paid support teams, but they do have public forums which are pretty helpful.  For me there were three real problems:  Benchmark Factory cannot be installed on Linux, it does not support TPC-DS, and does not support PostgreSQL so I needed additional software like HammerDB to finish my work.  On the plus side, it has excellent graphical charting of benchmark performance.

SwingBench is a great benchmarking tool for Oracle, but it only supports Oracle.  There's no way to compare the performance of multiple DBMS and decide which offers the best price/performance ratio.  SwingBench is great if you want to assess the impact of a system change to your Oracle database, or measure the performance gains by moving your Oracle database to a new server or to swap out the storage layer.  The last time I used SwingBench it offered 13 different tests, and the most popular was the Sales Order Entry (SOE) which loosely resembles the TPC-C benchmark.  Also at that time it was the only available tool with a TPC-DS-like benchmark.  SwingBench has excellent graphing of performance metrics which you can watch while the benchmark runs.  SwingBench is written by Dominic Giles and is available at no cost, but it is not open source (the code is compiled) if that matters.  I met Dominic at Oracle HQ and we talked briefly about SwingBench.  He works for Oracle but SwingBench is not Oracle software: he wrote it for his own reasons and eventually the coders at Oracle started using it for change impact analysis.

SwingBench 2.6 Dashboard

pgbench is a stripped-down implementation of the defunct TPC-B benchmark for PostgreSQL only.  Recall TPC-B was replaced by TPC-C several decades ago because it in no way represents a modern workload.  The point of TPC-B was to show system scalability, but pgbench pretty much lets you do whatever you want.  pgbench went unchanged for nearly a decade, but was recently enhanced to support PostgreSQL 10, 11, 12, and 13, and to add support for Declarative Partitioning on table ACCOUNTS, and a few minor enhancements as noted in the user documentation.  The only good thing about pgbench is it's free.  But so is HammerDB and that's a lot better tool.

Oracle RAT stands for Real Application Testing, and it's an extra cost option for Oracle only.  RAT allows you to capture your application's workload on the database server, transfer that workload to another server, and replay it.  It does not support industry standard benchmarks like TPC-C, but in reality the most accurate way to assess how a system change will impact your application is to actually test your application.  RAT is the only tool listed here capable of doing so.  Due to cost and complexity it is generally not used by smaller organizations: it's not the size of the database that matters, it's the size of the DBA team because using RAT takes a lot of time away from monitoring and tuning databases.

The table below tries to summarize the benchmarking tools,

 

HammerDB

BM Factory

SwingBench

pgbench

Oracle RAT

Free

YES

 

YES

YES

 

Open Source

YES

 

 

YES

 

TPC-C-Like

YES

YES

YES

 

 

TPC-E-Like

 

YES

YES

 

 

TPC-DS-Like

 

 

YES

 

 

TPC-H-Like

YES

YES

YES

 

 

Your Workload

YES*

YES*

 

 

YES

Linux

YES

 

YES

YES

YES

Windows

YES

YES

YES

YES

YES

Oracle

YES

YES

YES

 

YES

SQL Server

YES

YES

 

 

 

MySQL

YES

YES

 

 

 

PostgreSQL

YES

 

 

YES

 

GUI

YES

YES

YES

 

YES

CLI

YES

 

YES

YES

YES

Figure 1:  Benchmarking Tools Feature Matrix

Notes:

  • "Your Workload" means the tool can monitor your application database and capture a workload for replay instead of using a TPC-like benchmark.  HammerDB has a trace collector for Oracle only.  Benchmark Factory has Capture/Replay for Oracle only.
  • Some of the tools were not listed as open source even though they include some open modules.  For example: SwingBench provides queries in plain text, but the program's kernel is compiled.

A major gripe against all of the above mentioned tools is they don't enforce a primary rule common to all TPC benchmarks: higher performance must be achieved by scaling the system.  These tools simply let you crank one knob or another to achieve max performance at minimal scale.  All TPC benchmarks require you to scale users and data proportionally in the spirit of "system scalability testing", and they limit your score based on scale.  Not one of the tools enforces this primary rule. 

The simplest example of that problem is pgbench which is based on the TPC-B benchmark.  The primary rule of TPC-B is score cannot exceed data scale.  The whole point of TPC-B is to pick a target score and then scale your system to it.  If you want to show a score of 100 transactions per second then you need to load a data scale of 100.  Conversely, if you load data scale 100 then you are only allowed to report scores less than or equal to 100 transactions per second.  Instead, the tool lets you load an incredibly small scale of data and perform all data processing in memory to achieve falsely high scores. 

All of the tools mentioned in this post suffer from this same issue.  But, if you have read my other blog post "Ignoring Rules When Benchmarking Databases" then you are probably already aware of this and taking advantage of it to inflate your test scores.

Speaking of my other posts related to database benchmarking, I have a whole series of posts dedicated to HammerDB.  Check them out.

Comments

Post a Comment

Popular posts from this blog

Using DBGen to Generate TPC-H Test Data

Image
In this post I describe the Data Base Generator (DBGen) utility in terms of what it is, how to install it, and how to use it.   This post assumes a Linux operating system.   There are different build procedures for DBGen on Windows, but the concepts carry over. DBGen is used to create a TPC-H schema (i.e., it provides the CREATE TABLE statements) and to generate the data.   DBGen is used in official TPC-H benchmarking, but it can also be used in informal TPC-H-like benchmarking.   The schema and data is compatible with most 3 rd party benchmarking tools like HammerDB, so even if you plan to run a TPC-H-like test using HammerDB you may still use DBGen to create the schema and data. The  CREATE TABLE   statements are ANSI standard SQL and can be run in any DBMS without editing.   There are no SQL statements for indexes or foreign key constraints provided by DBGen since those types of objects are not required.   The data is created as delimited...
Read more

Oracle 21c Caching Solutions

This blog post reviews all of the caching solutions available in Oracle Database 21c.  I dive into several of the caching solutions, but I do try to keep things relatively short-and-sweet here.  I have other blogs dedicated to each caching solution. I don't mean the Oracle SGA or PGA.   I mean optional features you can enable to enhance performance. The content of this blog post is applicable to Oracle 12g, 18c, 19c, and 21c.   Some of the caching solutions were available in Oracle 11g, but I did not regression test my examples back that far. There have been no new caching solutions in Oracle 12g R2 through 21c.   One solution in particular, the In-Memory Column Store, has received some major enhancements.   I briefly describe it here, but the deep dive is in another blog post. Summary of Oracle Caching Solutions Oracle 21c includes six caching features as listed in my table below.   I've listed them in order from newest on top to oldest at t...
Read more