Sysbench OLTP Benchmark

The Sysbench OLTP application benchmark runs on top of a MySQL database running the InnoDB storage engine. The job of the storage engine is to manage the interface from the on-disk database to the applications reading and writing data to and from the storage engine. The storage engine in turn manages IO threads and logs, and it keeps an in-memory cache to minimize disk access. The chart below gives a simplistic overview of the engine.

Since the InnoDB engine keeps an in-memory cache called the buffer pool, performance will be directly affected by the ratio of the working set size to the size of the buffer pool. In other words, if the buffer pool is large enough to hold the working set or the working set is small enough to fit in the buffer pool, most operations will never be IO bound. However, if the database is too large to fit into memory, then IO performance will dictate transactional response times and throughput. We are characterizing the performance of the drive in this situation where the database does not fit in memory, which leads to increased IO traffic from the InnoDB storage engine.

Multiple threads (the number depends on database configuration for high performance applications and varies from 32 to 128) read data in a random fashion from the backing store with a block size of 16Kbytes. These reads are related to database queries requesting data from the backing store. As reads are processed, they are automatically cached in the buffer pool. As the buffer pool fills up, InnoDB uses a LRU (Least Recently Used) policy to evict older pages to make room for newer data.

Database writes are first directed to the transaction log and to the buffer pool. The transaction log is a sequentially written ring buffer that is updated for every write transaction. Depending on the MySQL configuration, this update can lead to an immediate on-disk write, or it can persist temporarily in RAM as a buffer that is eventually flushed to disk by the file system. The size of this log buffer varies, but is usually around 256MB. Recommended settings for ACID compliance require that the log writes hit the disk on every database commit. These writes are 4K in length.

The log only does "physiological" writes where the delta between the previous data and the new data is written. For the original data, InnoDB writes to the buffer pool in RAM, which has to be drained asynchronously as well. The process of writing out the data back to the file system is configurable and occurs in the background. The writes are also 16K. The writes are actually double-writes where the engine first writes the data into an intermediary location called the double-write buffer. The engine then copies that data into its final location in the file system. This is necessary to avoid the "torn page" problem where a 16KB database page is partially written to disk due to a power loss or other catastrophic event.

Origins of the Sysbench Benchmark

We brought the Sysbench test setup into our lab after several conversations with Micron about how they simulate real-world MySQL application environments for SSD testing and measurement. They developed a test methodology around Sysbench after finding that synthetic storage benchmarks rarely provided a complete view of how a drive will behave when under a particular application workload. The Sysbench test allows Micron to simulate an environment that most closely represents a standard MySQL database workload, which are prevalent in applications such as Facebook, Craigslist and Booking.com

We worked particularly closely with Moussa Ba, who helped co-author this article. Moussa is a software engineer on Micron’s PCIe development team where his work includes application and system software optimization for high performance IO devices.

Sysbench OLTP Benchmark

Sysbench is a system performance benchmark that includes an OnLine Transaction Processing (OLTP) test profile. The OLTP test is not an approximation of an OLTP test, but is rather a true database-backed benchmark that conducts transactional queries to an instance of MySQL in a CentOS environment.

The first step in setting up the benchmark is to create the database itself, which is done by specifying the number of tables in the database as well the number of rows per table. In our test, we defined 100 tables with 10 million rows each, which resulted in a database of 1 billion entries. This database was 260GB.

Sysbench has two operating modes: default mode which reads and writes to the database and a readonly mode. The default R/W mode will execute the following query types: 5 SELECT queries, 2 UPDATE queries, 1 DELETE query and 1 INSERT. Looking at IO counts, the observed read/write ratio is about 75% reads and 25% writes.

Sysbench Testing Environment

Storage solutions are tested with the Sysbench OLTP benchmark in the StorageReview Enterprise Test Lab utilizing stand-alone servers. We utilize off-the-shelf channel ThinkServer RD630s from Lenovo to show both realistic performance and a solid price/performance ratio, changing only the storage adapter or network interface to connect our RD630 to different storage products. The ThinkServer RD630 has proven itself to offer great compatibility with third-party devices, making it an excellent go-to platform for this diverse testing environment. The RD630 also leverages Intel's powerful Sandy Bridge Xeon architecture, which gives us the compute power to properly stress a wide range of storage offerings and maximize their performance potential. Another advantage of the RD630 in our test lab compared to other current-generation servers on the market is that the primary on-board storage is tied to an industry-standard LSI RAID add-on card with long-established driver support, and it can also easily be swapped out if necessary. 

For testing individual SSDs without the influence of RAID adapter cache, we equipped each server with LSI 9207-8i HBAs to test single SAS or SATA SSDs, while PCIe Application Accelerators are connected natively. To test hard drive or SSD arrays, the stock LSI 9260-8i RAID card is used to leverage the I/O capabilities of multiple drives in different RAID types. Depending on the networked storage product under evaluation, we turn to a variety of network HBAs depending on the interface required. For 10GbE, we utilize Lenovo-qualified Intel X520-T2 adapters, while for Twinax SPF+ equipment we use Mellanox ConnectX-3 EN HBAs. For 8 or 16Gb Fibre Channel-attached storage, we use QLogic 2600-series HBAs.

Sysbench Benchmark Environment

Lenovo ThinkServer RD630 - SATA/SAS Testing Platform

  • 2594-ABU Topseller Model
  • Dual Intel E5-2650 CPUs (2.0GHz, 8-cores, 20MB Cache)
  • 128GB RAM (8GB x 16 DDR3, 64GB per CPU)
  • 100GB Micron RealSSD P400e SSD (via LSI 9207-8i) Boot Drive
  • 960GB Micron M500 (via 9207-8i) Pre-built Database Storage
  • CentOS 6.3 64-bit
  • Percona XtraDB 5.5.30-rel30.1
    • Database Tables: 100
    • Database Size: 10,000,000
    • Database Threads: 32
    • RAM Buffer: 24GB

The main goal with this platform is highlighting how enterprise storage performs in an actual enterprise environment and workload, instead of relying on synthetic or pseudo-synthetic workloads. Synthetic workload generators are great at showing how well storage devices perform with a continuous synthetic I/O pattern, but they don't take into consideration any of the other outside variables that illuminate how devices actually work in production environments. Synthetic workload generators have the benefit of showing a clean I/O pattern time and time again, but they won't ever replicate a true production environment. Introducing application performance on top of storage products begins to show how well the storage interacts with its drivers, the local operating system, the application being tested, the network stack, the networking switching and external servers. These are variables that a synthetic workload generator simply can't take into account, and they are also an order of magnitude more resource- and infrastructure-intensive in terms of the equipment required to execute this particular benchmark.

Overall Sysbench Performance Results

We test a wide range of storage solutions with the Sysbench OLTP benchmark that meet the minimum requirements of the testing environment. To qualify for testing, the storage device must have a usable capacity exceeding 260GB and be geared towards operating under stressful enterprise conditions. This includes new PCIe Application Accelerators, single SAS or SATA enterprise SSDs or consumer SSDs utilized in the enterprise market, as well as large HDD arrays that are locally or network attached. Listed below are the steady-state 32-thread TPS and latency figures captured from all devices evaluated to date in this test. In product reviews, we dive into greater detail and put competing products head-to-head; our main list shows the stratification of different storage solutions.

PCIe Application Accelerator /  Multi-SSD/HDD RAID Sysbench Performance Results 

Device 32-Thread Average TPS Average Response Time 99th Percentile Latency
Huawei ES3000 2.4TB
MLC PCIe SSD x 1
2734.69  11.7  19.84
Huawei ES3000 1.2TB
MLC PCIe SSD x 1
2615.12  12.23  21.80
Fusion ioDrive2 Duo 2.4TB
MLC PCIe SSD x 1
2521.06  12.69  23.92
Micron P320h 700GB
SLC PCIe SSD x 1
2443.56  13.09  22.45
Micron P420m 1.4TB
MLC PCIe SSD x 1
2361.29  13.55  25.84
Fusion ioDrive2 1.2TB
MLC PCIe SSD x 1
2354.06  13.59  29.35
Virident FlashMAX II 2.2TB
MLC PCIe SSD x 1
2278.11  14.04  26.04
LSI Nytro WarpDrive 800GB
MLC PCIe SSD x 1
1977.67  16.18  39.94
LSI Nytro WarpDrive 400GB
MLC PCIe SSD x 1
1903.14  16.81  39.30


Individual SAS / SATA SSD Results

Device 32-Thread Average TPS Average Response Time 99th Percentile Latency
Intel S3700 800GB
eMLC SATA x 1
1488.71  21.49  40
Toshiba PX02SM 400GB
eMLC SAS (6Gb/s) x 1
1487.03  21.52  62.02
Smart Optimus 400GB
eMLC SAS x 1
1477.1  21.66  52.69
OCZ Talos 2 C 480GB
MLC SAS x 1
1438.7  22.24  47.32
OCZ Talos 2 R 400GB
eMLC SAS x 1
1421.15  22.51  45.06
SanDisk Extreme 480GB
MLC SATA x 1
1303.48  24.55  53.56
STEC s842 800GB
eMLC SAS x 1
1293.56  24.74  67.20
Intel S3500 480GB
eMLC SATA x 1
1241.59  25.77  54.27
Seagate 600 Pro 400GB
eMLC SATA x 1
1198.2  26.7  62.69
Hitachi SSD400S.B 400GB
SLC SAS x 1
1191.47  26.85  47.87
OCZ Vector 512GB
MLC SATA x 1
1130.03  28.32  62.67
SanDisk Extreme II 480GB
MLC SATA x 1
981.34  32.61  102.58
Hitachi SSD400M
eMLC SAS x 1
878.9  36.41  68.03
Toshiba eSSD
SLC SAS x 1
758.41  42.19  140.59
Micron M500 480GB
MLC SATA x 1
668.6  47.86  461.80