sqlskills-logo-2015-white.png

A SQL Server Hardware Tidbit a Day – Day 21

For Day 21 of this series, I want to talk a little bit about the TPC-E OLTP benchmark.

The TPC Benchmark E (TPC-E) is an OLTP performance benchmark that was introduced in February 2007. TPC-E is a not a replacement for the older TPC-C benchmark, but rather is a completely new OLTP benchmark. It is an OLTP, database-centric workload that is meant to reduce the cost and complexity of running the benchmark compared to the older TPC-C benchmark. It simulates the OLTP workload of a brokerage firm that interacts with customers using synchronous transactions and with a financial market using asynchronous transactions.

The business model of the brokerage firm is organized by Customers, Accounts, and Securities. The data model for TPC-E is significantly more complex, but more realistic than TPC-C, with 33 tables and many different data types. The data model for the TPC-E database does enforce referential integrity, unlike the older TPC-C data model.

The TPC-E database is populated with pseudo-real data, including customer names from the year 2000 U.S. Census, and company listings from the NYSE and NASDAQ. Having realistic data introduces data skew, and makes the data compressible. Unlike TPC-C, the storage media for TPC-E must be fault tolerant (which means no RAID 0 arrays). Overall, the TPC-E benchmark is designed to have reduced I/O requirements compared to the old TPC-C benchmark, which makes it both less expensive and more realistic since the sponsoring hardware vendors will not feel as much pressure to equip their test systems with disproportionately large disk subsystems in order to get the best test results. The TPC-E benchmark is also more CPU intensive than the old TPC-C benchmark. It is essentially CPU-bound, as long as you have adequate I/O capacity to drive the workload.

The TPC-E implementation is broken down into a Driver and a System Under Test (SUT), separated by a mandatory network. The Driver represents the various client devices that would use an N-tier client-server system, abstracted into a load generation system. The SUT has multiple Application servers (Tier A) that communicate with the database server and its associated storage subsystem (Tier B). TPC provides a transaction harness component that runs in Tier A, while the test sponsor provides the other components in the SUT.

The performance metric for TPC-E is transactions per second, tpsE. The actual tpsE score represents the average number of Trade Result transactions executed within one second. To be fully compliant with the TPC-E standard, all references to tpsE results must include the tpsE rate, the associated price per tpsE, and the availability date of the priced configuration.

It seems interesting that, as of early 2013, Microsoft is the only database vendor that has any submitted TPC-E results, even though the TPC-E benchmark has been available for over six years. Whatever the reasons why the other database vendors haven’t allowed any TPC-E results to be submitted by the hardware vendors, there are certainly many results posted for SQL Server, which makes it a very useful benchmark when assessing SQL Server hardware.

The most recent posted TPC-E result is for an IBM System x3850 X5 Server with a 5457.20 tpsE score for an eight-socket system. This system has eight, ten-core Intel Xeon E7-8870 processors that have a total of 160 logical cores for the system. It also has 4TB of RAM and 236 SSDs in its I/O subsystem, using a 22TB initial database size for the test. Looking at the Executive Summary, you can see that it is running SQL Server 2012 Enterprise Edition on top of Windows Server 2012 Standard Edition. This is basically the biggest system (in terms of RAM) that you can build under the current Windows Server 2012 license limits for memory.

It is using RAID 5 for the data files, RAID 10 for the log file and RAID 1 for tempdb, with (236) 200GB 3Gbps SAS SSDs (model # 81Y9956), and (2) 600GB 6Gbps 10K SAS drives. The data files are spread across eleven, twenty-drive RAID 5 arrays (using 200GB 3Gbps SSDs), while the log file is on one, sixteen-drive RAID 10 array (using 200GB 3Gbps SSDs), and tempdb is on one, two-drive RAID 1 array, using 600GB 10K SAS drives). That shows you that tempdb is probably not hit very hard during the TPC-E benchmark!

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.