The top 5 reasons to run your own database benchmarks

Some opinions claim that “Benchmarks are meaningless”, “benchmarks are irrelevant” or “benchmarks are nothing like your real applications”However for others “Benchmarks matter,” as they “account for the processing architecture and speed, memory, storage subsystems and the database engine.”

So who is right? Could you really be better off not being better informed? This post addresses some of the opinions around database benchmarking and gives the top 5 reasons why industry standard benchmarking is important and should be an essential foundation of your database engineering strategy.

Note that the main developer of HammerDB is Intel employee (#IAMINTEL) however HammerDB is a personal open source project and any opinions are specific to the context of HammerDB as an independent personal project and are not related to Intel in any way.

1. Repeatability

The first known usage of the term benchmark stems from surveying in the early 1800’s  to mark the border between North and South Carolina. in this context a bench is a mark in typically a stone structure to place a level staff to make sure that accurate measurements can be made from the same position at some point in time in the future.  This initial usage of the term gives us  insight into the importance of benchmarks today that can be summed in a single word  “Repeatability”.   Just like the original context of the term benchmark the single most important and overriding factor of any database benchmark is that if you run the same test on exactly the same configuration, (allowing for a small margin or error) you get the same result.  If you change something and re-run the same test the difference in the results can be attributed to the changes you made.  Consequently over time you have a reference point “set in stone” from which future changes can be measured by running the same test.  That is why we run a workload designed exactly for this purpose as it gives us a “benchmark”.

2. Scalability

Benchmarks are nothing like your real applications, and that’s actually good thing because unlike your application a good benchmark application like HammerDB has been designed to scale. “Scalability” is a product of both the benchmarking application itself (See the post on HammerDB Architecture to see how it scales implementing multiple virtual users as threads) as well as the benchmarking workload (The TPC benchmarks that HammerDB uses have been designed specifically for this purpose and proven over decades to scale). With a proven application and workload that delivers repeatability, you can then determine the capabilities of the components that you want to assess as noted in the introduction such as processing architecture and speed, memory, storage subsystems and the database engine.

For example the following chart (with the actual data removed for the reasons described further in this post) shows the HammerDB TPC-C workload run against the same database on different processing architectures (grouped by colour) and speed with the most recent at the top. As the chart shows because we know that both HammerDB and the implementation of the TPC-C workload scales then we can determine that with this particular database engine both the software and hardware scales as well.

If you only test your own application (and if you have more than one application which one will you use for benchmarking?) can you be sure that it scales?  if not how will you determine the optimal deployment platform for those applications (if you don’t know  whether your application  scales in the first place?) The answer is to run a proven application and workload to provide those quantifiable and repeatable results. With a chart such as the one above you can then compare results from your own application tests to determine whether they scale as well.

3. Security

So if you have compared the results of your own application and it is repeatable and scalable then why not use that for all of your benchmarking going forward? A key reason is  “Security” of the data that you are going to use to test for your benchmarks.  When running HammerDB you can generate and insert data “on the fly”  or generate flat files and upload them for bulk inserting. In all cases this is random data so presents no security challenges.  When testing your application however this is a very real consideration.  For example when considering moving a database application into the cloud are you going to copy your actual production data into multiple cloud environments for testing?  If not are you going to “clean” your data so it does not present a security risk? A benchmarking scenario of testing maintenance jobs is one of the highest risk approaches  around. If you are going to restore your production backups onto the new server how can you be sure that this data is safe in transit and completely deleted when the test is complete.  Either way there will be a much higher level of security due diligence and hence cost required when using production data.  Even if you are confident that your data is secure then  you then need a way to accurately simulate your application. HammerDB offers a way to do this for Oracle by the replaying of trace files and there is an excellent series on doing this by House of Brick. However be aware that even such an approach as capturing tracefiles or using similar tools can reveal the data in your application.

4. Portability

Even if you are not particularly concerned about the security of your data for your test strategy, then another disadvantage of only testing your own application is that it is likely to lack “Portability”.  This means that if you wish to compare your application running against a different database or operating system then there may be a considerable effort to port the application to multiple new environments just for testing and after establishing functionality there may be considerable tuning effort as well.  This is why for example HammerDB offers all functionality equally on both Windows and Linux and was therefore the first benchmarking tool to natively support SQL Server on Linux. HammerDB has been tuned, optimized and tested against all of the databases it supports for high performance to enable you to explore the boundaries of these databases before committing to a full porting strategy for your application.  in addition to restrictions on the portability of your own application this also applies to a number of available database benchmarking applications as well. HammerDB provides performance metrics that can be used to compare databases however many benchmarking tools are severely limited being available only on one operating system or supporting only one database. This lack of portability restricts the comparability of your available options at the outset making a non-portable benchmark set is of limited use.

5. Shareability

The final and arguably most important reason for running standard benchmarks is one of “Shareability”. There is a significant advantage in running the same repeatable, scalable,  secure and portable workload as everyone else with numerous examples shared on the HammerDB website.  For example if you want to run SQL Server on AWS there is already an example published by AWS here (Note how the graphs resemble the same scalability as previously described in this article). This is especially important as the number of official published benchmarks has reduced over time as shown in the graph from this presentation on TPC relevance.  The TPC workloads are more popular than ever before however the way are published and the information shared has changed dramatically in the era of cloud and social media.

The other side of shareability is the license clause known as the “Dewitt Clause” in some proprietary databases. There are many similar posts and articles on this clause which this post will not repeat apart from stating that database performance information may be less available for some databases compared to others as the license prevents the publication of performance data.  It is arguable that in the modern era whether these clauses continue to provide the benefit intended. Nevertheless the restriction of available data means running your own standard benchmarks to gather your own data for private is an essential process for users of these databases.


So are standard database benchmarks meaningless?  Clearly if the team running the benchmarks lacks the sufficient skillset to both correctly run and interpret the workloads then this could be true. Otherwise having a known workload that exercises all of the essential features of a relational database and runs on as varied an environment as possible is invaluable whether you are working in research or engineering and maybe even don’t have your own applications to test or are choosing the optimal hardware and software configurations for a complex production support environment.   Either way HammerDB is designed from the outset to help you be better informed and to share this performance information as widely as possible.

HammerDB Concepts and Architecture

HammerDB is open source and all of the source code is available at the sourceforge Git development site here or the github mirror here  This Git repository is updated with every release.  In fact all of this source code is also included in readable form with each and every release.  However if you have downloaded the source code and are looking to add features or make modifications such as adding new databases you may be wondering once you have the source code where to start. This guide to HammerDB concepts and architectures is aimed  at helping you understand how HammerDB is built and how it can be extended and modified.

Programming Languages

When you download the source code for HammerDB you can see that the programming language it is written in is called TCL .  TCL is a dynamic or interpreted language where the code is compiled into bytecode at runtime.  It is important to understand at the outset that HammerDB is written in TCL because of the unique threading capabilities that TCL brings. This article Threads Done Right… With Tcl gives an excellent overview of these capabilities and it should be clear that to build a scalable benchmarking tool this thread performance and scalability is key.  In contrast for example Python has the Global Interpreter Lock or GIL – “the global interpreter lock, or GIL, is a mutex that prevents multiple native threads from executing Python bytecodes at once. This lock is necessary mainly because CPython’s memory management is not thread-safe“. Therefore only TCL provides the foundation for building a tool such as HammerDB although Python can be loaded in the threads if desired.  If you are not familiar with TCL you can also see that it is closely associated with a graphical user interface toolkit called TK.  For HammerDB if you run the command-line version you are using TCL only, if you are running the graphical environment you are using TCL and TK. (Note that for the features used HammerDB will only run with TCL/TK 8.6 or above).  So if you have downloaded the source code or the pre-packaged version and look for example in files under the src directory you will see a number of files with a .tcl extension that you can read – these are identical in both the source code and the pre-packaged version.   It is of note that we have not discussed the operating system yet, that is because the source code and the code included with the pre-packaged versions is identical whether it is running on Linux or Windows on x86-64 architecture (or any other platform).  That is a slight exaggeration as there are some architectural differences (such as command-line console interaction) however these can be managed with a statement such as follows:

if {[string match windows $::tcl_platform(platform)]} {

Otherwise the code is the same on all platforms.  Clearly however there is a difference between platforms as you can tell from the download page and when you download the pre-packaged version you have and additional bin and lib directories that are (mostly) unique to a particular platform. These bin and lib directories are not included with the source code.  TCL was designed as a language to be closely tied with C/C++ and at this lower level there is the compiled TCL (tclsh) or TCL/TK (wish) interpreter and supporting libraries.  You can download and compile TCL/TK 8.6 or above yourself and replace the existing interpreter to be able to run the HammerDB interface from the source code by adding the bin and lib directories.  However HammerDB will not be able to connect to a database yet and the librarycheck command included with the CLI gives an indication why.

HammerDB CLI v3.1
Copyright (C) 2003-2018 Steve Shaw
Type "help" for a list of commands
The xml is well-formed, applying configuration
Checking database library for Oracle
Success ... loaded library Oratcl for Oracle
Checking database library for MSSQLServer
Success ... loaded library tclodbc for MSSQLServer
Checking database library for Db2
Success ... loaded library db2tcl for Db2
Checking database library for MySQL
Success ... loaded library mysqltcl for MySQL
Checking database library for PostgreSQL
Success ... loaded library Pgtcl for PostgreSQL
Checking database library for Redis
Success ... loaded library redis for Redis


Database Extensions

Also written in C HammerDB includes the pre-compiled extensions to the native interfaces for the databases that HammerDB supports as well as other supporting packages for functions such as graphing. Some of these packages have been modified from the original open source packages to ensure the correct functionality for HammerDB. All extensions have been compiled with gcc on Linux and MSVC on Windows for the x86-64 platform and require the presence of the compatible database library at runtime. For commercial databases this third-party library is the only non-open source component of the stack.

Database Extension
Oracle/TimesTen Oratcl 4.6
MS SQL Server/Linux and Windows Debian patch version of TclODBC 2.5.1 updated to 2.5.2
IBM Db2 updated version of db2tclBelinksy to add bind variable support as version 2.0.0
MariaDB/MySQL/Amazon Aurora MySQLTcl version 3.052
PostgreSQL/Greenplum/Amazon Redshift PgTcl Pgtclng version 2.1.1
Redis In-built pure TCL client
Trafodion SQL on Hadoop (Deprecated in HammerDB v3.0 ) TDBC included with TCL

Other Extensions

Functionality Extension
Clearlooks GUI Theme for Linux (but can be used on Windows)
Expect Console functionality for command-line functionality on Linux
tWAPI TCL Windows API used for command-line and CPU agent functionality on Windows
tkblt BLT graphical package extension for metrics for both Linux and Windows

Pure Tcl Modules

In addition to compiled extensions there are also a number of TCL modules located in the modules directory, these extensions are similar to the packages in the lib directory however the main distinction is that almost all of these modules are written in TCL rather than compiled extensions written in C.  These modules may also call additional packages for example as shown below the ReadLine module calls either Expect or tWAPI depending on the platform.

package provide TclReadLine 1.2
#TclReadLine2 modified to use Expect on Linux and Twapi on Windows
if {[string match windows $::tcl_platform(platform)]} {
package require twapi
package require twapi_input
} else {
package require Expect

Loading Extensions and Modules

The key command for loading extensions and modules is “package require”, for example the following extract at the top of the Oracle driver script shows the loading of the compiled Oratcl package as well as the common functions module for the TPC-C workload.

set library Oratcl ;# Oracle OCI Library
if [catch {package require $library} message] { error "Failed to load $library - $message" }
if [catch {::tcl::tm::path add modules} ] { error "Failed to find modules directory" }
if [catch {package require tpcccommon} ] { error "Failed to load tpcc common functions" } else { namespace import tpcccommon::* }

It should be clear that HammerDB is easy to extend by adding a new package into the lib (compiled) or modules (Pure TCL) directories and an example of how this is done is shown with adding Python support. The include directory contains the platform specific C header files for this purpose.


The agent directory contains the agent code to be run on the system under test to gather the CPU utilisation information. On Linux this runs the mpstat command on Windows mpstat is provided by HammerDB.


The images directory contains TCL files with the images used by HammerDB encoded in base64 format.


The configuration for HammerDB is defined by a number of XML files in the config directory.  HammerDB is modular meaning that this configuration determines the files and directories to load from the src directory at runtime.  At the top level is the database.xml that determines the other database configurations to load. Note that the commands field in this file only determines which words to highlight in the graphical text editor rather than defining the commands themselves.

Starting HammerDB

When you start HammerDB GUI on Linux the hammerdb file is a bash script file that calls the Linux compiled wish (window shell) executable that then runs the TCL commands incorporated within that shell file. On Windows there is an external batch file that calls the Windows compiled wish and then runs TCL commands within the hammerdb file.  For the command line it is the TCL shell that is run rather than wish.  The hammerdb file acts as a loader that loads the modules, the icon images and the generic source, the XML based configuration is then loaded followed by the database specific components dependent on this configuration.  The GUI or command line interface is then started.  As virtual users are started the database specific packages and workload specific modules are loaded into the virtual user threads. The following diagram illustrates the process. Note that as the diagram illustrates most of the code is shared between both command-line and GUI implementations and what and how the virtual users run this code is identical, therefore both GUI and commmand-line produce identical results.

XML to Dict

As HammerDB is modular under the src directory is a generic directory for shared source and a directory for each of the databases that has the same name as the XML configuration file for that database in the config directory.  HammerDB reads the XML at startup and manages the parameters internally in a 2 level nested dict structure per database.   For example for MySQL the dict is shown and the retrieval of a single value.

HammerDB CLI v3.1
Copyright (C) 2003-2018 Steve Shaw
Type "help" for a list of commands
The xml is well-formed, applying configuration
hammerdb>puts $configmysql
connection {mysql_host mysql_port 3306} tpcc {mysql_count_ware 1 mysql_num_vu 1 mysql_user root mysql_pass mysql mysql_dbase tpcc mysql_storage_engine innodb mysql_partition false mysql_total_iterations 1000000 mysql_raiseerror false mysql_keyandthink false mysql_driver test mysql_rampup 2 mysql_duration 5 mysql_allwarehouse false mysql_timeprofile false} tpch {mysql_scale_fact 1 mysql_tpch_user root mysql_tpch_pass mysql mysql_tpch_dbase tpch mysql_num_tpch_threads 1 mysql_tpch_storage_engine myisam mysql_total_querysets 1 mysql_raise_query_error false mysql_verbose false mysql_refresh_on false mysql_update_sets 1 mysql_trickle_refresh 1000 mysql_refresh_verbose false mysql_cloud_query false}

hammerdb>puts [ dict get $configmysql connection mysql_host ]


As the options boxes in the GUI are filled out or values set at the command-line these values are stored and retrieved from these underlying dicts.  At the top level the database.xml file determines the database specific data is read and the GUI populated at runtime rather than being fixed. For example changing the description field in the database.xml file as follows:

<workloads>TPC-C TPC-H</workloads>
<commands>mysql::sel mysqluse mysqlescape mysqlsel mysqlnext mysqlseek mysqlmap mysqlexec mysqlclose mysqlinfo mysqlresult mysqlcol mysqlstate mysqlinsertid mysqlquery mysqlendquery mysqlbaseinfo mysqlping mysqlchangeuser mysqlreceive</commands>

Will then display as follows in the interface.

Also note that the library field allows another library interface to be modified and recompiled or multiple version of libraries used for different databases.

Once the XML configuration is loaded the database specific files are loaded according to a directory in the database name and file names determined by prefix and the following purpose.

hammerdb>print db
Database Oracle set.
To change do: dbset db prefix, one of:
Oracle = ora MSSQLServer = mssqls Db2 = db2 MySQL = mysql PostgreSQL = pg Redis = redis
Extension Purpose
met Graphical metrics, currently only completed for Oracle
olap Analytic workloads, currently TPC-H related build and driver
oltp Transactional workloads, currently TPC-C related build and driver
opt Graphical options dialogs, takes values from database specific dict and allows modification and saving
otc Database specific online transaction counter

Therefore for example in the directory /src/db2 the file db2opt defines the options dialogs when the db2 database is selected to load and store the dict based values. Then when a schema build is selected or driver script loaded the scripts are populated with those values entered into the dialog.

Virtual Users

Virtual Users within HammerDB are operating system threads.  Each thread loads its own TCL interpreter and its own database interface packages.  If you have not read the article Threads Done Right… With Tcl then now is a good time to do so to understand why HammerDB is so scalable.  (Note that the metrics interface and transaction counter also run in threads).

For example on Windows drilling down on the process explorer shows that the threads are evenly allocated to a different preferred CPU thereby making the best possible utilisation of the resources available.  When the virtual users are run the threads are posted the script in the Script Editor in the GUI (or the loaded script for the command line). At its simplest the following is a Hello World! program in TCL.

puts "Hello World!"

Load this into the script editor and run it (or for one virtual user use the “Test TCL Code” button and the Virtual User will receive and run the program.

Using the File -> Save dialog you can modify existing scripts and then save and reload them for later use.

Running the Build and Driver Scripts

For the schema build or timed tests each virtual user receives and runs an identical copy of the script however is able to do something different. Each virtual user or operating system can identify its own thread id and therefore can be tasked with running a different part of the workload accordingly. This is how multiple virtual users will load a different part of the schema or how the virtual user that monitors a workload is identified.  Also the threads can “talk to each other” and respond to user input such as pressing the stop button using thread shared variables.  Otherwise overheads are minimal each virtual user will run entirely independently without interference from other threads and without locking overhead.  Therefore running HammerDB on a multi-core and multi-threaded CPU is strongly recommended as you will be able to take advantage of that processing power.  Output from virtual users are displayed to the virtual user output window.  Note that operating system displays are not thread safe and cannot be updated by more than one thread at a time, therefore each virtual user sends its output to the master thread for display. Clearly a “Test Script” will not be able to generate the same level of throughput as a “Timed Script” for this reason. HammerDB also manages the virtual user status and reports any errors produced by the virtual users to the console.

Autopilot and Modes

In addition to direct interaction HammerDB also provides an autopilot feature to automate testing.  This feature to be used in conjunction with timed tests allows for multiple tests to be run consecutively unattended without manual interaction. The way this feature works is for a timer to run at the user specified interval and at this timer interval HammerDB will automatically press the buttons that manual interaction would do. From a conceptual standpoint it is important to understand this as autopilot actually presses the same buttons that a user would, the only difference is that this is on a timed basis. Therefore if you want an autopilot run to complete successfully ensure that the time interval is long enough for your test to complete. There is no autopilot feature in the command-line tool as it is easier to script this functionality with the command-line.

Typically HammerDB will run in Local Mode however this modes feature allows an instance of HammerDB to be created as a Master and then multiple Slave instances connected to HammerDB to be controlled by the Master through network communication. This feature has been designed for scenarios where the user wants to direct a workload specifically at multiple database instances in a cluster or in a virtualized environment.


It should now be clear that HammerDB is a simple and modular application yet extremely powerful and high performance because of the multi-threaded architecture and design. HammerDB is also highly extensible and extensions written in C can be added to support for example new databases (or existing extensions used). After this simply adding a new config file and adding these details to database.xml and creating a new directory under the src directory is all that is needed to add another database to HammerDB.  Similarly new workloads can be added by modifying existing files. HammerDB will then take care of running these workloads either graphically or at the command line.

Of the most importance however it should be clear that HammerDB is completely and entirely open source. TCL itself is open source, all of the database extensions are open source and the database libraries themselves with the exception of commerical databases are open-source as well.  Also clearly all of the HammerDB code is open-source and readable within each installation so if you desire you can inspect literally every line of code right from the TCL interpreter to the extensions and HammerDB if you wish to do so.  Therefore nothing is hidden and nothing is proprietary giving absolute confidence in the results that HammerDB produces.  You are also empowered to modify, add and contribute improvements. HammerDB really is all about making database performance open-source and demystifying the often hidden aspects of database benchmarking from workloads to results.

How to improve Redo, Transaction Log and WAL throughput for HammerDB benchmarks

If you are new to running Oracle, SQL Server, MySQL and PostgreSQL TPC-C workloads with HammerDB and have needed to investigate I/O performance the chances are that you have experienced waits on writing to the Redo, Transaction Log or WAL depending on the database you are testing. This post at an entry-level discusses the options you have to improve log throughput in your benchmark environment. 

Note that the parameters in this article do have the potential to cause data loss if implemented on your production database without full understanding so ensure that you have read this post in its entirety before applying the settings to your environment. 

Logging Waits

Firstly we’ll take a look at why these log waits are so important to database performance in an OLTP environment. The diagram below shows a much simplified way how the I/O in a typical relational database operates.

Before  the database is started your data should  be in a consistent state on disk. When the database is started a component of the in-memory database instance is the data buffer. As data is requested (depending on the type of read), data blocks will be fetched and stored in-memory in this buffer, show in-blue.  Over time your workload will modify this data, shown in-red, however these changed data blocks or pages are not written back to the data disk immediately. In fact a single block or page may be modified in-memory multiple times before it is eventually at some point in-time written back to the data disk. In contrast to many people’s understanding of a traditional database against an “in-memory” database it is likely that if the buffer is large enough most of the data you are operating on remains in-memory for the duration of a HammerDB OLTP test. Therefore you are likely to see a spike of reads at the start of the test (this is why there is ramp-up time) and then potentially depending on your configuration the data disk can be relatively quiet for the duration of the test while periodically seeing write activities related to checkpoints (which will be discussed later). 

However it should be clear that in the case of a failure such as a crash of the database all of the data in-memory would be lost.  Therefore you have the redo, transaction or write-ahead log recording all of the changes to the database. Typically we will see a constant stream of write activity to this disk with no (or minimal) read activity.  If there is a failure of the database, subsequently on startup this log will be used to recover the data on the data disk up to the point of the most recent transaction (or the chosen recovery point) i.e. replaying all of those changes in-memory that we lost. To recover right up to the most recent point those writes to the log needed to be synchronous i.e. when a transaction is committed the session waits until the database has confirmed that the changes have been recorded to the log before continuing work.  Note that this does not mean that the log is only written at the time of a commit, changes will be streamed into an in-memory log buffer and written to the log disk constantly however once a transaction is committed the user sessions will wait until this log write is confirmed. This wait by the session is what is typically seen as a log wait.  Bear in mind that writing to the log takes CPU, it will be a log writing thread or process that needs CPU time to be scheduled to be active,  it also requires memory for the log buffer and finally disk for the log itself, therefore this log wait is more than just the disk component. Additionally for the log disk component it is latency for an individual write that is crucial rather than the total I/O bandwidth.

As HammerDB supports graphical metrics for Oracle, this provides the best illustration however the concepts apply in general terms to all databases.

Oracle Log File Sync

The following example takes a snapshot of Oracle metrics showing 3 spikes of activity on a consumer level PC running Oracle within a Virtual Box VM for illustration purposes.The database has been created with the default redo log configuration which is 2 redo logs of 200MB in size each.  The first example shows a data load, the second a TPC-C based workload with 5 virtual users and the 2nd example with 10 virtual users.

What can be seen fairly quickly is that the dominant colour is orange and the majority of database time is spent on “log file sync”, waiting for these synchronous writes to the redo log. As the activity is increased with the 2nd run performance is not improved, instead the database is spending more time waiting for the redo log.

To illustrate the data reads on Oracle we can flush the buffer cache.

SQL> alter system flush buffer_cache;

System altered.


Re-running the same workload we can now see some blue read activity of “db file sequential read” from index reads at the start of the test, Once our data is buffered this read activity is then diminished as the data in memory is sufficient to sustain the workload that is being run by the rate that it is constrained by “log file sync”.

In this scenario the total throughput is considerably lower than the full potential of the system., so what are  your options?

Making Logging asynchronous

Clearly the most obvious option is to upgrade your log disk I/O capabilities to be able to sustain writes at the level that the test can produce and if running on a production system, this (and investigating your log size and configuration such as using the right disk sector size is the way to go). However what if in a test environment you cannot upgrade the log I/O but want to move beyond the log bottleneck to be able to test other system components more fully.  Fortunately all of the database supported by HammerDB include an option to make the logging asynchronous. In other words we will put our changes in the log buffer, issue a commit but not wait until this is confirmed. Instead we will continue under the assumption that the log write eventually made it to disk.  in this scenario it is possible that in the event of a failure data can be lost but for testing purposes it means we can potentially drive much higher levels of throughput than our log disk can typically sustain, showing the system potential if there were a better performing log configuration. Note however that this is not a solution that will make all log related waits disappear. As noted logging also involves CPU and memory and you will still need to make sure all writes reach the disk, the difference is that you are not flushing to disk synchronously on each commit and therefore are aiming to improve throughput by removing this dependency.

Oracle commit_wait and commit_logging

for Oracle the key parameter is commit_wait. By setting this parameter to NOWAIT the session will not wait for the write to be complete, instead continuing its work once the write has been issued.  This can be complemented by the parameter commit_logging being set to BATCH which groups the individual writes to be written in larger writes. 

commit_logging BATCH
commit_wait NOWAIT

These parameters can be set as follows:

SQL> alter system set commit_logging='BATCH' scope=spfile;

System altered.

SQL> alter system set commit_wait='NOWAIT' scope=spfile;

System altered.

Having set these parameters and re-run the previous test it can be seen that the wait events are now very different. In particular log file sync as intended has reduced considerably and been replaced by “db file sequential read” as the top event. Why is this the case? Because we have removed the log sync bottleneck we have now increased the throughput and put emphasis on the amount of data in the buffer that is being read-in and written-out at a much greater rate. Consequently we now need to increase the buffer cache in size if we are to see more CPU activity.  A good example of how tuning is an iterative process.

Checkpoint not complete

in the previous diagram we can also see the wait event log file switch (checkpoint incomplete). Returning to the first illustration it was shown that data will be periodically written to disk, at a checkpoint all of the data on disk is up to date with the same point in the log.  In this example with the default log configuration of 2 redo logs the database is attempting to switch from one log to the other however the checkpoint operation flushing the data from memory to the data disk has not yet caught up with the previous log and to begin writing to it would overwrite changes on the redo log that correspond to this not yet written yet, consequently the database will wait until this operation has been completed.

Thread 1 advanced to log sequence 1402 (LGWR switch)
  Current log# 1 seq# 1402 mem# 0: /home/oracle/app/oracle/oradata/VULCDB1/onlinelog/o1_mf_1_fjj87ghr_.log
Thread 1 cannot allocate new log, sequence 1403
Checkpoint not complete
  Current log# 1 seq# 1402 mem# 0: /home/oracle/app/oracle/oradata/VULCDB1/onlinelog/o1_mf_1_fjj87ghr_.log
Thread 1 advanced to log sequence 1403 (LGWR switch)
  Current log# 2 seq# 1403 mem# 0: /home/oracle/app/oracle/oradata/VULCDB1/onlinelog/o1_mf_2_fjj87gjj_.log

As can be seen this has happened on a consumer PC configuration with the default redo configuration, therefore for higher throughput also ensure that you resize the logs accordingly.


For SQL Server the equivalent parameter for asynchronous logging is set as follows for the parameter DELAYED_DURABILITY


On the same test system it can be seen that without setting this parameter, Logging is shown as the top resource wait. 

After setting ALTER DATABASE TPCC SET DELAYED_DURABILITY = FORCED  it can be seen that the waits on logging are reduced.


For MySQL the dependency is at the storage engine level and in this case for the InnoDB storage engine the parameter is self-explanatory.


Similarly for PostgreSQL logging can be set by setting synchronous_commit to off.

synchronous_commit = off                # synchronization

This post aims to address what is the most common scenario for someone running HammerDB TPC-C OLTP workloads for the first time when seeing waits on logging disks and what parameters can be set in a test based environment to achieve more throughput and observe what performance potentially could be achieved without a log disk bottleneck. 

Driving hammerdbcli from a bash script

A current FAQ is how to drive the command line hammerdbcli from an external bash script. This post gives an example of one way which this can be done.

For an example we will run a bash script that prints output and then runs hammerdbcli to build an Oracle schema before wating for this to finish and printing more output.  Firstly we need the hammerdbcli script.  Note that as HammerDB is multithreaded, adding exit to the end of a script will cause that script to terminate before virtual users are complete. This is unlikely to be the desired outcome. Therefore you need a function that will wait for the script to complete before issuing the exit. The important command here is vucomplete and you need to wait until the condition of this is “true”. An example of this is as follows:

global complete
proc wait_to_complete {} {
global complete
set complete [vucomplete]
if {!$complete} {after 5000 wait_to_complete} else { exit }
dbset db ora
diset connection system_password oracle
diset connection instance vulpdb1 
diset tpcc count_ware 4
diset tpcc num_vu 4
diset tpcc tpcc_def_tab users
print dict

This script has been saved as buildora.tcl.  Now we want the driving bash script that looks as follows:

./hammerdbcli <<!
source buildora.tcl

if you want to redirect your output to a file it can look like the following:

./hammerdbcli <<!>> buildora.output 2>&1
source buildora.tcl

If you run the bash script it will now call hammerdbcli that will run buildora.tcl, this will wait until complete to call exit before returning control back to the bash script. This is just one example of many ways in which hammerdbcli can be blended with Tcl, bash or even Python to automate HammerDB functionality.

If it is needed to strip the control characters from the output this can be done using sed. The stdbuf command can also be used to write the output line by line to the output file.

./hammerdbcli <<! 2>&1 | stdbuf -oL -eL sed -e "s,\x1B\[[0-9;]*[a-zA-Z],,g" -e "s,\r,,g" -e "s,hammerdb>,,g" -e "s,after\#[0-9]*,,g" >> buildora.output
source buildora.tcl

Real Time Oracle Performance Monitoring for Benchmarks

An essential part of database performance testing is viewing the statistics generated by the database during the test and in 2009 HammerDB introduced automatic AWR snapshot generation for Oracle for the TPC-C test. With this feature Oracle generates a wealth of performance data that can be reviewed once the test is complete. However what if you want to review performance data in real time as the test is running? With HammerDB 3.1 you can now do that with Oracle Metrics. 
Before introducing how this works it is important to acknowledge the ASHMON tool from which this functionality was adopted and more importantly its author Kyle Hailey for giving permission to add it as open source.

To get started with Oracle metrics use the previous option for CPU metrics that now shows the service name and system user and password.

With these filled out click the metrics button and once connected to the Oracle database the metrics window will appear embedded within HammerDB. 

you can either resize the window embedded using the grab bar or you can grab the tab at the top of the notebook that says Metrics (with the arrows indicator) and drag the window out to be standalone (closing the window embeds it again).

As this feature uses the Oracle Active Session History, use the mouse to highlight a timeframe of interest and the SQL, wait events and users will appear in the panes to the left. On the right you have the choice of displaying the sql text, explain plan, io or statistics. The CPU option allows you to view the original live CPU metrics window. It is clear from the window below that HammerDB was running a schema build and the top wait event was log file switch (checkpoint incomplete) – time to resize the redo logs or move them to faster disk!

Running a TPC-C performance test information can be viewed live (by not selecting an area) or by highlighting the area of interest. In this example a test with 2 minute rampup and 5 minute test time can be seen.  It is also clear that most significant wait event is “log file sync” and therefore tuning should focus on the redo log performance. The user CPU is highlighted in green and the aim for maximum performance is for the top event to be CPU. 

You can also drill down on particular SQL ID’s be double clicking on them that will highlight the related wait events and in this example the session showing the most “db file sequential read” is highlighted with the associated explain plan showing the index access that we would expect. 

All of this functionality is also available for the TPC-H workload as well so you can see the actual queries how they were run, the explain plans and the events. 

Finally like all HammerDB features everything that is available on Linux is equally available on Windows as well so if running an Oracle client on Windows you can access exactly the same features to monitor your benchmark performance in real time. 

How Many Warehouses for the HammerDB TPC-C Test?

This is a very typical FAQ and although detailed in the documentation some extra details may help in sizing and configuration. For a really quick answer create a schema with 250-500 warehouses per server CPU socket for more details size as follows. 

The official TPC-C test has a fixed number of users per warehouse and uses keying and thinking time so that the workload generated by each user is not intensive. However most people use HammerDB with keying and thinking time disabled and therefore each virtual user can approximately drive the CPU resources of one CPU core on the database server. Therefore the relationship between virtual users, warehouses and cores can be seen, you need considerably fewer virtual users and warehouses to drive a system to maximum throughput than the official test. 
Additionally it is important to understand the workload. By default each virtual user has the concept of a home warehouses where approximately 75% of its workload will take place. With HammerDB this home warehouse is chosen at random at the start of the test and remains fixed. For example it can then be understood that if you configure a schema with 1000 warehouses and run a test with 10 virtual users by default most of the workload will be concentrated upon 10 warehouses. (It is important to note the “by default” clause here as there is an exception to change this behaviour if desired). Also with the home warehouse chosen at random it should be clear that number of warehouses should be configured so that when the maximum number of virtual users that you will run are configured there is a good chance that the selection of a home warehouse at random will be evenly distributed across the available warehouses with one or possibly 2 virtual users selecting the same home warehouse at random but not more.  
As an example configuring a 10 warehouse schema and running 100 virtual users against this schema would be an error in configuration as it would be expected for 10 virtual users or more to select the same warehouse. Doing this would mean that the workload would spend considerably more time in lock contention and would not produce valid results. Typically an option of 4 to 5 warehouses per virtual user would be a minimum value to ensure an even distribution of virtual users to warehouse. Therefore for the 100 virtual users 400 to 500 warehouses should be a minimum to be configured.  As noted configuring more should not have a major impact on results as depending on the number of virtual users used in the test most the warehouses will be idle (and ideally most of the warehouses you are using will be cached in memory in your buffer cache so the I/O to the data area is minimal). 
As one virtual user can drive most of the capacity of one CPU core the actual value for the number of warehouses you choose will depend upon the number of cores per socket. Note that if using CPUs with Hyper-Threading allow for additional CPU capacity, so size as if there were 35% more physical cores.  Also depending on your chosen database some database software will not scale to fully utilise all cores, see the best practice guides for guidance on your chosen database. If CPU utilisation is limited then you will need fewer warehouses configured and virtual users for the test.  
It should also be clear that there is no completely accurate one-size-fits-all type guidance for warehouse sizing as different databases will scale differently and some may need more warehouses and virtual users than others to reach peak performance.  A common error is to size many thousands of warehouses and virtual users with the aim of reaching high performance but instead resulting in high levels of contention and low performance.  Even for highly scalable databases on large systems upper limits for tests without keying and thinking time are in the region of 2000 warehouses for up to 500 virtual users for maximum performance. 
There is one exception to the guidance above and that is when the “Use all Warehouses” checkbox is selected as shown. 
When this option is chosen instead of each virtual user choosing a home warehouse at random instead all of the available warehouses are divided between the virtual users and they then select a new home warehouse for each transaction. This option has been provided to increase the I/O to the data area and therefore when using this option you can choose to configure more warehouses with the knowledge that you will use them in contrast to the default option which will cache most of the warehouses used with the aim of reaching maximum performance and CPU utilisation. 

HammerDB MySQL and MariaDB Best Practice for Performance and Scalability

This post complements the previous best practice guides this time with the focus on MySQL and MariaDB and achieving top levels of performance with the HammerDB MySQL TPC-C test.  As with the previous guides as an Intel employee (#IAMINTEL) the examples are taken from a MySQL 8 on Linux on Intel system and the approach is the same for whatever system you are testing although some of the settings you see may be different. Similarly for this guide MySQL can be swapped for a mySQL based databases such as MariaDB.

As is exactly the same with PostgreSQL for system choice a 2 socket system is optimal for MySQL OLTP performance. As is also the case this limitation is at the database level (especially the storage engine) rather than the hardware level. InnoDB is the storage engine that will deliver the best OLTP throughput and should be chosen for this test. 

HammerDB difference from Sysbench

For anyone benchmarking MySQL with HammerDB it is important to understand the differences from sysbench workloads as HammerDB is targeted at a testing a different usage model from sysbench. Historically MySQL has been positioned for supporting web-based applications this is in contrast to enterprise based database workloads that have been served by commercial databases such as Oracle, Db2 and SQL Server. For this reason sysbench presents a vastly more simplified workload than HammerDB.  

By default sysbench creates a single table with an ascending primary key and an identical pad column. 
MariaDB [sbtest]> select * from sbtest limit 10
    -> ;
| id | k | c | pad                                                |
|  1 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
|  2 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
|  3 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
|  4 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
|  5 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
|  6 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
|  7 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
|  8 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
|  9 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
| 10 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
10 rows in set (0.02 sec)

The workload then interacts with the database with simple SQL statements, for example with the read-only workload statements such as follows:
SELECT SUM(K) from sbtest where id between ? and ?
SELECT DISTINCT c from sbtest where id between ? and ? order by c
SELECT c from sbtest where id=?
Consequently there is no contention and the workload will scale according to the ability of the database to handle these isolated statements. 

HammerDB on the other hand is based on the TPC-C specification (but not identical to a full TPC-C workload) and more closely represents an enterprise type workload. There are 9 tables:
MariaDB [tpcc]> show tables;
| Tables_in_tpcc |
| customer       |
| district       |
| history        |
| item           |
| new_order      |
| order_line     |
| orders         |
| stock          |
| warehouse      |
9 rows in set (0.00 sec)
The tables have more complexity than sbtest:
MariaDB [tpcc]> select * from customer limit 1 G
*************************** 1. row ***************************
          c_id: 1
        c_d_id: 1
        c_w_id: 1
       c_first: 4fTYMkzYdy8EbABc
      c_middle: OE
        c_last: BARBARBAR
    c_street_1: G30TzdyBM1xx
    c_street_2: fRveiqUZDzz54pt9FK8
        c_city: jVRLzrSBopMpcmN4WYO2
       c_state: JK
         c_zip: 603111111
       c_phone: 4610643910936129
       c_since: 2018-10-11 08:48:51
      c_credit: BC
  c_credit_lim: 50000.00
    c_discount: 0.2000
     c_balance: -10.00
c_ytd_payment: 10.00
c_payment_cnt: 1
c_delivery_cnt: 0
c_data: hjtWypvebZZgVhNCdKOhHd50Wn7HQG8XAm9cSHkXTf73KqBHot7IvHq8PtaHdaJ9oMXqFx6aUXrBRxQ44gqLf0k04gkPVWc6Lx3q71gFCu1vZlLhmmIaWQf5zyDD4AAqejVcwYKi50P9rHFegjzURpTf6c9SPEfpupc7378uekwBYTj4Xfm0Od3ukiQIKto8Nlx1Is51pC4qynxLEWWGULhXdBipYckk5EjpbpdKzUjEcMGJ6nCmFpNgXfKDIUpaYsw1dWwgCEhPfXiKjXMO0v0iF56wzD6AOF4w7m8CXSw0x5zKB7URuTqlGedSYK8EvmylYudXLF
1 row in set (0.00 sec)
and the workload is driven by stored procedures:
MariaDB [tpcc]> show procedure status
    -> ;
| Db    | Name               | Type      | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
| tpcc  | DELIVERY           | PROCEDURE | @              | 2018-10-11 08:57:34 | 2018-10-11 08:57:34 | DEFINER       |         | latin1               | latin1_swedish_ci    | latin1_swedish_ci  |
| tpcc  | NEWORD             | PROCEDURE | @              | 2018-10-11 08:57:34 | 2018-10-11 08:57:34 | DEFINER       |         | latin1               | latin1_swedish_ci    | latin1_swedish_ci  |
| tpcc  | OSTAT              | PROCEDURE | @              | 2018-10-11 08:57:34 | 2018-10-11 08:57:34 | DEFINER       |         | latin1               | latin1_swedish_ci    | latin1_swedish_ci  |
| tpcc  | PAYMENT            | PROCEDURE | @              | 2018-10-11 08:57:34 | 2018-10-11 08:57:34 | DEFINER       |         | latin1               | latin1_swedish_ci    | latin1_swedish_ci  |
| tpcc  | SLEV               | PROCEDURE | @              | 2018-10-11 08:57:34 | 2018-10-11 08:57:34 | DEFINER       |         | latin1               | latin1_swedish_ci    | latin1_swedish_ci  |
7 rows in set (0.00 sec)
an example is shown of the shortest of these: 
st_w_id                 INTEGER,
st_d_id                 INTEGER,
threshold               INTEGER
DECLARE st_o_id         INTEGER;
DECLARE stock_count     INTEGER;
DECLARE `Constraint Violation` CONDITION FOR SQLSTATE '23000';
SELECT d_next_o_id INTO st_o_id
FROM district
WHERE d_w_id=st_w_id AND d_id=st_d_id;
SELECT COUNT(DISTINCT (s_i_id)) INTO stock_count
FROM order_line, stock
WHERE ol_w_id = st_w_id AND
ol_d_id = st_d_id AND (ol_o_id < st_o_id) AND
ol_o_id >= (st_o_id - 20) AND s_w_id = st_w_id AND
s_i_id = ol_i_id AND s_quantity < threshold;

As a result there is deliberate contention in the HammerDB workload that is not seen in a sysbench one. Also as HammerDB is making one call to stored procedure that then contains multiple SQL statements the workload throughput is significantly higher than sysbench. 

More recently MySQL and MariaDB has been positioned in the enterprise space competing with commercial database such as Oracle with features such as PL/SQL compatibility.  Therefore if you are testing MySQL or MariaDB’s ability to support web-based applications that use  SQL statements with minimal contention then sysbench is likely to present the best assessment of the system for this type of workload. On the other hand if testing MySQL or MariaDB for the ability to handle a more complex workload such as the use of stored procedures and in particular if looking to compare scalability with a traditional database then HammerDB is focused more towards testing those enterprise features. Finally it is also important to note that this comparison is focused around OLTP based workloads, HammerDB also supports a TPC-H based workload for analytics with complex ad-hoc queries. Traditionally MySQL has not supported such workloads however features such as columnstore in MariaDB now make this possible.  

System Setup: CPU, Memory and I/O Configuration

System setup is covered on the PostgreSQL Best Practice post so it will not be repeated here as the steps are the same. Make sure that the I/O that is able to keep up with writing to the redo log. Also make sure that if using an Intel CPU it is correctly configured with the right drivers and setup for turbo boost with cpupower output as follows: 
./cpupower frequency-info
analyzing CPU 0:   
driver: intel_pstate
  CPUs which run at the same hardware frequency: 0   
CPUs which need to have their frequency coordinated by software: 0   
maximum transition latency:  Cannot determine or is not supported.   
hardware limits: 1000 MHz - 3.80 GHz
available cpufreq governors: performance powersave   
current policy: frequency should be within 1000 MHz and 3.80 GHz.
                   The governor "performance" may decide which speed to use
                   within this range.
 current CPU frequency: Unable to call hardware   
 current CPU frequency: 1.99 GHz (asserted by call to kernel)   
boost state support:
     Supported: yes
     Active: yes

Finally in testing Huge Pages has less of an impact than with PostgreSQL, although performance should not be lower than having Huge Pages disabled. Just note that if using MariaDB in some versions a bug means that Huge Pages cannot be enabled although this does not greatly impact performance.

Install MySQL or MariaDB from Source

(Note that the following section applies in particular to pre-2019 versions of MySQL and MariaDB and more recent versions of MySQL 8 have already been updated for optimal performance on multiple platforms and therefore the change is this section is not required)

The MySQL manual advises that building from source allows you customize build parameters and as with PostgreSQL this can also be the best way to achieve maximum performance. In particular with a workload such as HammerDB there is a lot of emphasis on locking and latching that is not seen in a sysbench workload and therefore you may see gains in modifying the InnoDB storage engine code. In InnoDB some of this behaviour is hardcoded however the CPU PAUSE instruction that is used by the UT_RELAX_CPU macro can and does vary between CPUs.  In particular on Intel Scalable Processors (Skylake architecture) the PAUSE instruction is much longer than previous architectures and therefore calling UT_RELAX_CPU can consume a lot more time resulting in reduced performance.  It may be the case that by the time you are testing this you can modify this behaviour with a parameter. Otherwise find the file in the directory storage/innobase/ut and modify the value accordingly, by default it is set to 50, for Skylake 5 is more appropriate for the delay multiplier. 
for (i = 0; i <delay * 5; i++) {
    j += i;

If unsure on whether to do this run the workload and use the perf command to observe the top functions. If “ut_delay” is consuming most of the CPU (in most cases it will still be one of the top functions) then potentially modifying this code can increase performance. 

Configure MySQL

An example MySQL my.cnf file is shown.  Note that innodb_file_per_table is set then when we use partition in HammerDB it can insert into multiple files one per partition.  This gives a performance gain. One configured start the MySQL or MariaDB database.  For more details on MySQL and optimization see the website by Dimitri Kravtchuk

# general
# files
# buffers
# tune
# perf special
# monitoring
Configure the HammerDB Client

Download and install HammerDB on a test client system, Like PostgreSQL another 2 socket server is ideal. You need the client libraries so  ensure the MySQL 5.7 library file “” is findable – you only need this one file and then run hammerdbcli to check for this file: 

HammerDB CLI v3.1
Copyright (C) 2003-2018 Steve Shaw
Type "help" for a list of commands
The xml is well-formed, applying configuration
Checking database library for MySQL
Success ... loaded library mysqltcl for MySQL
Create the Schema and Run the Test
Just before running the schema creation double check that you have initialised mysql correctly and the mysql directory exists, otherwise the tables will be created and loaded with data but you will get the following error when creating the stored procedures
Error in Virtual User 1: mysqlexec/db server: Table 'mysql.proc' doesn't exist
an example of how to do this is as follows:
$ ./scripts/mysql_install_db --srcdir=/home/mariadb/mariadb-10.2 --defaults-file=./../my.cnf
Installing MariaDB/MySQL system tables in '/home/mariadb/data' ...
If using the HammerDB CLI an example script saved as innodbbuild.tcl is shown update accordingly for your system and make sure partitioning is included:
dbset db mysql
diset connection mysql_host
diset connection mysql_port 3306
diset tpcc mysql_count_ware 800
diset tpcc mysql_partition true
diset tpcc mysql_num_vu 64
diset tpcc mysql_storage_engine innodb
print dict
$ ./hammerdbcli
HammerDB CLI v3.1
Copyright (C) 2003-2018 Steve Shaw
Type "help" for a list of commands
The xml is well-formed, applying configuration 
hammerdb>source innodbbuild.tcl

It will return to the prompt when built – then restart the MySQL instance and run a single test. On an up to date system around mid-2018 expect to see up to 2M MySQL TPM and around 650K NOPM. 

Vuser 1:56 Active Virtual Users configured
Vuser 1:TEST RESULT : System achieved 1994844 MySQL TPM at 658082 NOPM
 Then on a load test client run a script such as the following at the command line this does the same as autopilot in the GUI so make sure you log to temp.
proc runtimer { seconds } {
set x 0
set timerstop 0
while {!$timerstop} {
incr x
after 1000
  if { ![ expr {$x % 60} ] } {
          set y [ expr $x / 60 ]
          puts "Timer: $y minutes elapsed"
if {  [ vucomplete ] || $x eq $seconds } { set timerstop 1 }
dbset db mysql
diset connection mysql_host
diset connection mysql_port 3307
diset tpcc mysql_driver timed
diset tpcc my_rampup 2
diset tpcc my_duration 5
vuset logtotemp 1
foreach z { 1 2 4 8 12 16 20 24 28 32 36 40 44 48 52 56 60 64 68 72 76 } {
puts "$z VU TEST"
vuset vu $z
runtimer 600
after 5000
 Test Results

Grab the TPM or NOPM from the logfile. If everything went well you should get a sequence as follows peaking at around 650,000 NOPM on an up to date system in mid-2018.  To be clear this chart shows the data from 20 performance tests, each with a 2 minute rampup time and 5 minute test. After each test completed HammerDB then increased the virtual user count and repeated the test. Fortunately HammerDB allows all of this process to be automated.  Each data point shows the average transaction rate captured over the 5 minute test so not the peak performance that may be higher. 

Note that after a point, in this case after 56 virtual users, performance will decrease as you increase the number of virtual users. This is to be expected and is due to the limitations of the scalability of the storage engine. If you report the stored procedure response times (see the HammerDB manual for how to do this) you will see the time increase especially for the DELIVERY stored procedure that is updating the ORDER_LINE table while the NEWORD stored procedure is trying to insert into it. As a result the NOPM or New Orders per minute reduces as more contention is experienced. This is not a limitation of HammerDB that has been observed to drive significantly higher transaction rates.  Nevertheless around 2M MySQL TPM is very respectable throughput for an enterprise workload and as there is a lot more CPU available the potential is there to go a lot higher. 

HammerDB Best Practice for PostgreSQL Performance and Scalability

This post gives a HOWTO guide on system configuration for achieving top levels of performance with the HammerDB PostgreSQL TPC-C test.  As an Intel employee (#IAMINTEL) the examples are taken from a PostgreSQL on Linux on Intel system, the approach is the same for whatever system you are testing although some of the settings you see may be different.
Firstly for system choice a 2 socket system is optimal for PostgreSQL OLTP performance at the time of writing. This limitation is at the database level rather than the hardware level, nevertheless with up to date hardware (from mid-2018) PostgreSQL on a 2 socket system can be expected to deliver more than 2M PostgreSQL TPM and 1M NOPM with the HammerDB TPC-C test. 


On the 2 socket system you will need I/O that is able to keep up with writing to the WAL – An example is an NVME PCIe SSD formatted as an XFS file system and mounted as below: 

/dev/nvme0n1p1                                /mnt/ssd        xfs     noatime

The OS  username you are using it can be anything but for people familair with Oracle this becomes the equivalent of the system username.  Many people use postgres.

CPU Configuration

Make sure that your CPU is configured for optimal performance. To do this make sure that the Linux tools package is installed. If so there are a number of tools under the /usr/lib/linux-tools directory under the specific kernel name. 

# ls
acpidbg  cpupower  perf  turbostat  usbip  usbipd  x86_energy_perf_policy
With these installed set the cpufreq governor to performance as follows:
# ./cpupower frequency-set --governor=performance
Setting cpu: 0
Setting cpu: 1
Setting cpu: 2
Check that the settings have been applied and the frequency settings as expected. From the following output key things to check are that the driver is shown as intel_pstate (for Intel CPUs), the governor shows as performance, the frequency range goes to the maximum frequency for the CPU and boost state is supported (if your CPU supports turbo boost). 
# ./cpupower frequency-info
analyzing CPU 0:
  driver: intel_pstate
  CPUs which run at the same hardware frequency: 0
  CPUs which need to have their frequency coordinated by software: 0
  maximum transition latency:  Cannot determine or is not supported.
  hardware limits: 1000 MHz - 3.80 GHz
  available cpufreq governors: performance powersave
  current policy: frequency should be within 1000 MHz and 3.80 GHz.
                  The governor "performance" may decide which speed to use
                  within this range.
  current CPU frequency: Unable to call hardware
  current CPU frequency: 1.99 GHz (asserted by call to kernel)
  boost state support:
    Supported: yes
    Active: yes

Also check that the idle settings are enabled and (for Intel CPUs) that the intel_idle driver is used. Note that the pstate and cstate drivers work together to provide the most efficient use of turbo boost. Initially it is best leave all of these states enabled. Disabling all C-states or Idle states is likely to reduce overall performance by reducing the available turbo frequency. You can experiment with the C-states between ‘all or nothing’ to find the optimal settings. 

# ./cpupower idle-set --enable-all
# ./cpupower idle-info
CPUidle driver: intel_idle
CPUidle governor: menu
analyzing CPU 0:
Number of idle states: 4
Available idle states: POLL C1 C1E C6
Latency: 0
Usage: 10736
Duration: 29182086
You are not quite done. There is another tool in the directory called x86_energy_perf_policy that determines how the boost states are used. By default this is set to normal so you will want to set it to performance. 
# ./x86_energy_perf_policy -rcpu0: EPB 6
cpu0: HWP_REQ: min 38 max 38 des 0 epp 0 window 0x0 (0*10^0us) use_pkg 0
cpu0: HWP_CAP: low 7 eff 10 guar 25 high 38
cpu1: EPB 6
# ./x86_energy_perf_policy performance
# ./x86_energy_perf_policy -rcpu0: EPB 0
cpu0: HWP_REQ: min 38 max 38 des 0 epp 0 window 0x0 (0*10^0us) use_pkg 0
cpu0: HWP_CAP: low 7 eff 10 guar 25 high 38
cpu1: EPB 0
As the name indicates the turbostat tool can be used to monitor the CPU turbo frequencies. 
Huge Page Memory Configuration

For the memory we need to set Huge Pages – so as root add the following line to /etc/sysctl.conf we are going to create 64GB of buffers by adding the vm.nr_hugepages line (Huge Pages are 2MB in size). 

vm.swappiness = 0
kernel.sem = 250 32000 100 128
fs.file-max = 6815744
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
fs.aio-max-nr = 1048576
vm.nr_hugepages = 35000
 Also edit /etc/security/limits.conf and add the following:
postgres soft memlock 100000000
postgres hard memlock 100000000
Now run the command “sysctl –p” as root, when the database is running you will see the memory allocated from huge pages. 
cat /proc/meminfo
HugePages_Total:   35000
HugePages_Free:     6588
HugePages_Rsvd:     4572

To reiterate this will only be when you have reached the stage of starting the database, so firstly you will need to install the software.

Install PostgreSQL from Source
Make sure that you have the necessary software to compile PostgreSQL from source, download the postgresql source and extract it to your PostgreSQL file system configured above. Although 9.6.5 has been used here, feel free to download and use the latest release. 
postgres:/mnt/ssd/postgresqlsrc$ ls
pgsql  postgresql-9.6.5  postgresql-9.6.5.tar
 Find and change change the file pg_config.h (after having run configure first)
find . -name pg_config.h -print

 So it looks like below, this will set the WAL file size to be 1GB instead of the default 16MB. 

/* XLOG_SEG_SIZE is the size of a single WAL file. This must be a power of 2
   and larger than XLOG_BLCKSZ (preferably, a great deal larger than
   XLOG_BLCKSZ). Changing XLOG_SEG_SIZE requires an initdb. */
#define XLOG_SEG_SIZE (1024 * 1024 * 1024)
The default location for the install is in /usr/local/pgsql so the easiest way to configure is to set a symbolic link to a directory from your ssd to this location using the “ln –s” command
pgsql -> /mnt/ssd/postgresqlsrc/pgsql
postgres:/mnt/ssd/postgresqlsrc$ ls -ltr /usr/local
total 32
lrwxrwxrwx 1 root root   28 Oct 11 03:41 pgsql -> /mnt/ssd/postgresqlsrc/pgsql
Now follow the steps previously to compile PostgreSQL from source. When you do “make install” it will create the binaries in this location.  You also need to create a new directory called “data” and if all went well you should now have a directory that looks like this:
postgres:/usr/local/pgsql$ ls
bin  data  include  lib  share
Configure PostgreSQL
Run initdb from the bin directory specifying the data directory
./bin/initdb -D ./data
This creates your database with the username of the OS user you are using as the superuser. Now edit 2 configuration files postgresql.conf and pg_hba.conf. 
For Postgresql.conf you have a number of options. It is your test so you can set the options as you see fit. Remember to set huge_pages=on and note that whereas wal_level minimal=minimal and synchronous_commit=off will give you the best WAL performance for a test it may not be what you would want in a production environment. (and you can run further tests to quantify the impact of these options). 
postgres:/mnt/ssd/postgresqlsrc/pgsql/data$ more postgresql.conf
listen_addresses =''              # what IP address(es) to listen on;
port = 5432                             # (change requires restart)
max_connections = 256                   # (change requires restart)
shared_buffers = 64000MB                        # min 128kB
huge_pages = on                 # on, off, or try
temp_buffers = 4000MB                   # min 800kB
work_mem = 4000MB                               # min 64kB
maintenance_work_mem = 512MB            # min 1MB
autovacuum_work_mem = -1                # min 1MB, or -1 to use maintenance_work_mem
max_stack_depth = 7MB                   # min 100kB
dynamic_shared_memory_type = posix      # the default is the first option
max_files_per_process = 4000            # min 25
effective_io_concurrency = 32           # 1-1000; 0 disables prefetching
wal_level = minimal                     # minimal, archive, hot_standby, or logical
synchronous_commit = off                # synchronization level;
wal_buffers = 512MB                     # min 32kB, -1 sets based on shared_buffers
#checkpoint_segments = 256              # in logfile segments, min 1, 16MB each
checkpoint_timeout = 1h         # range 30s-1h
checkpoint_completion_target = 1        # checkpoint target duration, 0.0 - 1.0
checkpoint_warning = 0          # 0 disables
log_min_messages = error                # values in order of decreasing detail:
log_min_error_statement = error # values in order of decreasing detail:
log_timezone = 'GB'
autovacuum = off                        # Enable autovacuum subprocess?  'on'
datestyle = 'iso, dmy'
timezone = 'GB'
lc_messages = 'en_GB.UTF-8'                     # locale for system error message
lc_monetary = 'en_GB.UTF-8'                     # locale for monetary formatting
lc_numeric = 'en_GB.UTF-8'                      # locale for number formatting
lc_time = 'en_GB.UTF-8'                         # locale for time formatting
default_text_search_config = 'pg_catalog.english'
max_locks_per_transaction = 64          # min 10
max_pred_locks_per_transaction = 64     # min 10
Also change the pg_hba.conf and add the ip addresses for your test server and load testing client running HammerDB.
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all               trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
host    all             all           trust
host    all             all           trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                trust
#host    replication     postgres            trust
#host    replication     postgres       ::1/128                 trust
Start the PostgreSQL Database

Start the database as follows:

./bin/pg_ctl start -D ./data

And check it is running (remember to check the huge page allocation as well).
postgres:/usr/local/pgsql$ ps -ef | grep post
postgres    201539      1  0 Sep19 ?        00:00:00 /mnt/ssd/postgresqlsrc/pgsql/bin/postgres -D ./data
postgres    201541 201539  0 Sep19 ?        00:00:57 postgres: checkpointer process
postgres    201542 201539  0 Sep19 ?        00:00:02 postgres: writer process
postgres    201543 201539  0 Sep19 ?        00:03:18 postgres: wal writer process
postgres   201544 201539  0 Sep19 ?        00:00:04 postgres: stats collector process
You can stop the database as follows:
./bin/pg_ctl stop -D ./data
 Now login set the password for the “superuser” note how this takes the name from the OS user so in this case postgres.
postgres:/usr/local/pgsql$ ./bin/psql -U postgres -d postgres
psql (9.6.5)
Type "help" for help.
postgres=# alter role postgres password 'postgres';
Use –W to test the password
postgres:/usr/local/pgsql$ ./bin/psql -U postgres -d postgres -W
Password for user postgres:
psql (9.6.5)
Type "help" for help.
Configure the HammerDB Client

Download and install HammerDB on a test client system, another 2 socket server is ideal. You need the client libraries so you can either copy the postgres lib directory you have just compiled or compile from source again on this host – you don’t need to create a database or set the config files. Then add the library to the library path:

postgres:~$ export LD_LIBRARY_PATH=/usr/local/pgsql/lib:$LD_LIBRARY_PATH

and check that it loads with a librarycheck test.

$ ./hammerdbcli
HammerDB CLI v3.1
Copyright (C) 2003-2018 Steve Shaw
Type "help" for a list of commands
The xml is well-formed, applying configuration
Checking database library for PostgreSQL
Success ... loaded library Pgtcl for PostgreSQL
Create the Schema and Run the Test
You can now start HammerDB on the client and from the Database options choose PostgreSQL. Follow the HammerDB documentation to build the schema and run the test. Do not select the EnterpriseDB Oracle compatible schema as you installed the software from source. Creating an 800 Warehouse schema is a good starting point, the data should load quickly but note that creating indexes can take up to 20 mins or so this is to be expected. If you have a system with an up to date CPU (as of mid-2018), enough memory and everything installed on a fast SSD then more than 2M PostgreSQL TPM and more than 1M NOPM should be achievable.

HammerDB command line build and test examples

With the HammerDB command line its very easy to script the schema build and automated test so that if you don’t have a GUI or don’t want to use one you can still access all of the HammerDB functionality. The following example is with MySQL on Linux but can easily apply to any database you choose.

Firstly if you don’t have MySQL installed copy the client library to your home directory

$ ls -l
-rw-r--r-- 1 mysql mysql 4237408 Apr 23 09:21
and add it to your path
$ export LD_LIBRARY_PATH=/home/intel:$LD_LIBRARY_PATH
The CLI command librarycheck will verify if the library can be loaded.
$ ./hammerdbcli
HammerDB CLI v3.1
Copyright (C) 2003-2018 Steve Shaw
Type "help" for a list of commands
The xml is well-formed, applying configuration
Checking database library for MySQL
Success ... loaded library mysqltcl for MySQL
Using a text editor create a script with your configuration, such as follows. Note that in this example the build is going to be run on the database server itself.
$ more schemabuild.tcl
dbset db mysql
diset connection mysql_host
diset connection mysql_port 3306
diset tpcc mysql_count_ware 800
diset tpcc mysql_partition true
diset tpcc mysql_num_vu 64
diset tpcc mysql_storage_engine innodb
print dict
Then run the hammerdb CLI and type source schemabuild.tcl – hammerdb will then run the build you specified.
$ ./hammerdbcli
HammerDB CLI v3.1
Copyright (C) 2003-2018 Steve Shaw
Type "help" for a list of commands
The xml is well-formed, applying configuration
hammerdb>source schemabuild.tcl
To run the test you can configure a script to automate a sequence of tests just like the autopilot feature in the GUI. This example runs tests from 1 right through to 104 virtual users. Note in this case the driver is now being run from a client system (remember to install the client library and check that it loads here as well) and logtotemp is set so that all of the output is captured to a file.
$ more mysqlrun.tcl
proc runtimer { seconds } {
set x 0
set timerstop 0
while {!$timerstop} {
incr x
after 1000
  if { ![ expr {$x % 60} ] } {
          set y [ expr $x / 60 ]
          puts "Timer: $y minutes elapsed"
if {  [ vucomplete ] || $x eq $seconds } { set timerstop 1 }
dbset db mysql
diset connection mysql_host
diset connection mysql_port 3306
diset tpcc mysql_driver timed
diset tpcc my_rampup 2
diset tpcc my_duration 5
vuset logtotemp 1
foreach z { 1 2 4 8 12 16 20 24 28 32 36 40 44 48 52 56 60 64 68 72 76 80 84 88 92 96 100 104 } {
puts "$z VU TEST"
vuset vu $z
runtimer 600
after 5000
Then as above run the script, it will now run the script unattended executing the sequence of tests.
$ ./hammerdbcli
HammerDB CLI v3.1
Copyright (C) 2003-2018 Steve Shaw
Type "help" for a list of commands
The xml is well-formed, applying configuration
hammerdb>source mysqlrun.tcl
That’s all you need to build a schema and run a sequence of tests with HammerDB without the GUI.

HammerDB Best Practice for SQL Server Performance and Scalability

With the move to HammerDB v3.0 as well as refactoring a lot of code the documentation moved to Docbook format. As with the Oracle best practice dcoument previously at v2 there was an SQL Server best practice document that is no longer included in the current documentation. As a number of people have asked for it it is published here. 
CPU, Memory and I/O
The key dependence of performance is hardware related with the CPU being the most important factor on the levels of performance available from the rest of the system. At the next level from the CPU is memory with the best levels of performance available from having sufficient memory to cache all of the test database. Finally I/O performance is crucial with modern systems and CPUs available to drive high levels of throughput, In particular for OLTP workloads write performance to transaction logs is critical and often a major resource constraint.  Solid State Disks (SSDs) are strongly recommended for both data areas and transaction logs to provide the I/O capabilities to match the CPU performance of up to date systems.
BIOS Settings
 Systems are shipped with default BIOS and are not necessarily optimized for database performance.  BIOS settings should be checked and settings verified with the vendor to ensure that they are advantageous to SQL Server Performance. A common error is to accept a default setting of “High Performance” that sets a subset of lower level BIOS settings without verifying what these are.  A default setting of “High Performance” will often result in lower performance for a database environment.
Power Options
Open the power options dialog and choose High Performance if not already selected.
 Verify Single Threaded Performance
Verify that you achieve maximum CPU single threaded performance by creating and running the following stored procedure.
USE [tpcc]
         @n numeric(16,6) = 0,
         @a DATETIME,
         @b DATETIME
         @f int
      SET @f = 1
      WHILE @f <= 10000000 
      SET @n = @n % 999999 + sqrt(@f)
            SET @f = @f + 1
         PRINT ‘Timing = ‘ + ISNULL(CAST(DATEDIFF(MS, @a, @b)AS VARCHAR),)
         PRINT ‘Res = ‘+ ISNULL(CAST(@n AS VARCHAR),)
In any database (ie use [ database name ] ) create the stored procedure by running the code above as shown.
Right click on CPUSIMPLE and click execute stored procedure, Click OK and wait – if it runs OK then return value is 0  click on message to see the time the procedure took to run:
For example:
Timing = 8250
Res = 873729.721235
(1 row(s) affected)
In this case the stored procedure took 8.2 seconds to run. Actual performance will depend on your installed CPU however an up to date CPU would be expected to complete this routine in under 10 seconds. For more information on this test and troubleshooting view the older blog entry How to Maximise CPU Performance for SQL Server on Windows.
Network Bandwidth
With a highly scalable system network utilisation may exceed Gigabit capacity between log generation server and SUT.  To increase capacity you may use either use 10GbE or configure NIC teaming for multiple Gigabit Ethernet adapters on both the server and both the LACP compatible switch in LACP mode.   Firstly the network switch must be configured to enable LACP using your switch documentation, then configure NIC teaming from the Server Management Window for the interfaces connected to the same switch as shown
Confirm under Task Manager that Gigabit capacity has been exceeded whilst running a HammerDB workload.
SQL Server Properties

Configure SQL Server according to your available hardware, this can be done either with the graphical tools as shown or manually as follows, the following example shows a memory configuration of between 256GB and 512GB.
exec sp_configure ‘show advanced options’, ‘1’
reconfigure with override
exec sp_configure ‘min server memory’, 256000
exec sp_configure ‘max server memory’, 512000
exec sp_configure ‘recovery interval’,’32767′
exec sp_configure ‘max degree of parallelism’,’1′
exec sp_configure ‘lightweight pooling’,’1′
exec sp_configure ‘priority boost’, ‘1’
exec sp_configure ‘max worker threads’, 3000
exec sp_configure ‘default trace enabled’, 0
reconfigure with override
Also ensure that Processor Affinity is set automatically as shown.
Database Creation
Building the HammerDB schema directly on the database server will be quicker as you will be able to take advantage of more cores and not be required to pass all of the data across the network.
Firstly pre-create an empty database called tpcc (or any other name) as shown, this enables you to choose and configure your storage in advance. Ensure that the initial DATA files are sufficiently large (eg 200GB for 4 socket) to ensure that the files are not continually growing during a test at the expense of performance. You may also wish to configure the Autogrowth properties to ensure that if the file does grow it grows sufficiently to prevent additional performance impact.
Schema Build and Configure
Once built backup the TPCC database, a simple way is to stop SQL Server and copy the TPCC DATA and LOG to a backup directory. To restore the schema delete the existing schema, copy the DATA and LOG directories from backup and use the attach command to reattach the original database, configure the schema using the following commands.
Partition the History Table
For highly scalable systems partitioning the history table can reduce insert contention either use the SQL Server Management Studio or run a statement as follows modifying the highlighted values according to the warehouse count for 1 partition per 100 warehouses.
USE [tpcc]
CREATE PARTITION FUNCTION[HISTORY_PART](int) AS RANGE LEFT FOR VALUES (1, 100,200, 300, 400, 500, 600, 700, 800, 900)
CREATE CLUSTERED INDEX[ClusteredIndex_on_HISTORY_PART_634589363881526517]ON [dbo].[history]
Resize the Transaction Log
 As the recovery interval has already been set to the maximum permitted a checkpoint will occur when the transaction log is 70% full, at high performance depending on the I/O write performance to your data files, the checkpoint produce an I/O effect noticeable in the HammerDB transaction counter as follows:
To observe the checkpoint set DBCC trace 3502, 3504 and 3605 and open the error log.
2014-01-15 14:04:35.18 spid56      DBCC TRACEON 3502, server process ID (SPID) 56. This is an informational message only; no user action is required.
2014-01-15 14:06:52.74 spid56      DBCC TRACEON 3504, server process ID (SPID) 56. This is an informational message only; no user action is required.
2014-01-15 14:06:52.74 spid56      DBCC TRACEON 3605, server process ID (SPID) 56. This is an informational message only; no user action is required.
The following example shows that the checkpoint took 14.7 seconds writing at 1GB/sec that coincided with the drop in performance.
2014-01-16 11:41:11.75 spid20s     FlushCache: cleaned up 1932082 bufs with 948934 writes in 14739 ms (avoided 25317 new dirty bufs) for db 5:0
2014-01-16 11:41:11.75 spid20s                 average throughput: 1024.11 MB/sec, I/O saturation: 418155, context switches 562834
2014-01-16 11:41:11.75 spid20s                 last target outstanding: 31729, avgWriteLatency 26
2014-01-16 11:41:11.75 spid20s     About to log Checkpoint end.
To postpone the checkpoint until after the test, resize the logfile. Being too small will cause checkpoints before the test is complete – too large impact will performance – the following example resizes the logfile to 64GB.
use tpcc
dbcc shrinkfile(‘tpcc_log’,truncateonly)
alter database tpcc modify file (name=‘tpcc_log’, size=64000)
dbcc loginfo(‘tpcc’)
Select Checkpoint when complete to checkpoint after the timed test has completed.
Observe performance for an entire test to ensure that the transaction counter is level showing that SQL Server performance is consistent without a drop in performance for checkpoints.
Use the Open Source Fusion-IO MS SQL Server scripts to identify the resource constraints. In this example WRITELOG is the main constraint and therefore adding LOG write I/O capacity is the best method to improve performance (as CPU is not saturated).
If the CPU is saturated on a single load generation client (and not the SUT Server) then multiple load test clients can be used using the “Master-Slave” functionality to drive a load from 2 clients simultaneously.   If the CPU is not saturated on the Server without having identified other resource constraints such as I/O or network then increase the Virtual User count on the load generation client.
Processor Group Affinity
Windows Server 2008 and prior releases supported up to 64 CPUs only. Windows Server 2008 R2, 2012 and 2012 R2 supports up to 256 processors however divides these processors up into processor groups of 64 processors with handling of processor groups improving with each release of Windows.  For example with 120 logical processors Windows Server 2012 R2 presents these CPUs as 2 Processor Groups of 60 logical processors each. HammerDB Metrics can be used to illustrate the usage of Processor Groups by SQL Server as shown.
Most applications (including the HammerDB client are restricted to operating within a single processor group). SQL Server is able to operate across multiple Processor Groups however may restrict client connections to a single Processor Group or subset of Processor Groups for an individual test.
Once initially assigned sessions do not move between Processor Groups and therefore performance and CPU utilisation may be artificially limited.  Using the FusionIO Monitoring Scripts previously referenced when this Processor Group limitation occurs the top wait event is shown as “Threading and CPU”.   When running a test the aim is to achieve an even distribution of CPU utilization across all processors to result in good performance. Processor Group allocation is affected by modifying the allocation time.  To achieve this balance, in the HammerDB virtual user options modify the User Delay parameter to 10ms, this means that the user will wait for 10ms after the previous user logged on.
A shortened connection time typically ensures an even distribution of connections across the server. Use the HammerDB Metrics tool  to monitor the system to ensure good utilisation of system resources.