hsqldb.org Home
                  Page

HSQLDB - 100% Java Database

Performance Tests

Performance Test Case TPC-B

TPC-B is a simple OLTP (online transaction processing) stress test which is useful for measuring the sheer performance of update operations.

The official TPC-B specification specifies strict test conditions and reporting requirements for the combination of software / hardware used for the tests. These tests should not be considered as TPC-B tests. The description and measurements follow:

The database consists of several bank branches, each with 10 tellers and 100,000 accounts. A transaction updates a row in each of the branch, teller and account tables, selects the updated row from the account table, and inserts a row into the history table. A total of 5 operations are performed per transaction.

The HyperSQL SVN repository contains the class org.hsqldb.test.TestBench used for these tests. We used a scale factor of 40, therefore there were 40 branches, 400 tellers and 4,000,000 accounts in the database. We performed all tests with 4 connections, each performing 8,000 transactions (32,000 transactions or 160,000 database operations per round). Each test was performed several rounds, one of which is reported here. A dual core 2.5 GHz computer (2008 spec) was used.

The maximum speed was 55,363 transactions per second (276,815 operations per second)

You are encouraged to run the tests for yourself and report the results to our Open Discussion Forum or Users mailing list. The test program works with other database engines and can be used for comparison.

The first test uses memory tables and no logging. This demonstrates the sheer speed of the multithreaded engine.

java -server -Xmx1536M org.hsqldb.test.TestBench -tps 40 -driver org.hsqldb.jdbcDriver -url jdbc:hsqldb:mem:test;hsqldb.tx=mvcc -user sa -init -clients 4 -tpc 8000

* Benchmark Report *
--------------------
Time to execute 32000 transactions: 0.578 seconds.
Max/Min memory usage: 849712968 / 747964456
0 / 32000 failed to complete.
Transaction rate: 55363.32179930796 txn/sec.


The second test uses memory tables and logs the statement, performing automatic checkpoints (hsqldb.log_size=200) when the log reaches 200 MB. Logging the transactions takes about the same amount of time as performing them. Also when the checkpoint does occur, it slows down the particular test round.

java -server -Xmx1536M org.hsqldb.test.TestBench -tps 40 -driver org.hsqldb.jdbcDriver -url jdbc:hsqldb:file:test;hsqldb.log_size=200;hsqldb.tx=mvcc -user sa -init -clients 4 -tpc 8000

* Benchmark Report *
--------------------
Time to execute 32000 transactions: 1.266 seconds.
Max/Min memory usage: 888403376 / 744425784
0 / 32000 failed to complete.
Transaction rate: 25276.46129541864 txn/sec.

The third test uses cached tables and logs the statements similar to the previous test.

java -server -Xmx128M org.hsqldb.test.TestBench -tps 40 -driver org.hsqldb.jdbcDriver -url jdbc:hsqldb:file:test;hsqldb.default_table_type=cached;hsqldb.log_size=200;hsqldb.tx=mvcc -user sa -init -clients 4 -tpc 8000

* Benchmark Report *
--------------------
Time to execute 32000 transactions: 5.375 seconds.
Max/Min memory usage: 46327392 / 13014720
0 / 32000 failed to complete.
Transaction rate: 5953.488372093023 txn/sec.

January 2018 Update - tests on up-to-date hardware

131,147 transactions per second

The original tests were run in 2011 on a dual-core 2.5 GHz computer (2008 spec). For comparison, the first in-memory test was rerun in 2018 on a quad-core 4.4 GHz computer (2015 spec)

java -server -Xmx8000M org.hsqldb.test.TestBench -tps 40 -driver org.hsqldb.jdbcDriver -url jdbc:hsqldb:mem:test;hsqldb.tx=mvcc -user sa -init -clients 4 -tpc 8000

* Benchmark Report *
--------------------
Time to execute 32000 transactions: 0.244 seconds.
Max/Min memory usage: 1099958 / 850497 kb
0 / 32000 failed to complete.
Transaction rate: 131147.54098360657 txn/sec.
1099958;850497;0;131147.54098360657

1,438,202 selects per second on up-to-date hardware

The latest version of TestBench supports the -select switch for testing only the SELECT query in TPC-B

java -server -Xmx8000M org.hsqldb.test.TestBench -tps 40 -driver org.hsqldb.jdbcDriver -url jdbc:hsqldb:mem:test;hsqldb.tx=mvcc -user sa -init -clients 4 -tpc 64000

* Benchmark Report *
--------------------
Time to execute 256000 transactions: 0.178 seconds.
Max/Min memory usage: 891894 / 784390 kb
0 / 256000 failed to complete.
Transaction rate: 1438202.2471910112 txn/sec.
891894;784390;0;1438202.2471910112


Performance Test Case TPC-C

TPC-C is a more advanced OLTP test compared to TPC-B as there are different types of transactions for order placement, stock level query, etc. and each transaction queries, inserts, or updates multiple rows in several tables. However TPC-C was not designed as a stress tests because it emulates human-operated computer terminals with delays introduced between different stages of transactions. With the dominance of the World Wide Web, this access pattern no longer represents a realistic scenario. There is a Java test suite based on TPC-C here http://jtpcc.sourceforge.net/ and a modified version to use compiled statements here http://sourceforge.net/projects/benchmarksql/. We modified the original test and removed the delays, thus turning it into a stress test.

HyperSQL performed the TPC-C derivative test at 26,000 transactions per minute over a sustained period using memory tables without logging (20,000 with logging). The database was set up with 2 warehouses and eight clients were used in the test.

The test implementation does not look optimised and would certainly perform better (probably twice as fast) if it is modified to use compiled statements. We have not tried the second version.

It would be interesting to run the test documented here http://community.voltdb.com/node/134 which is used by VoltDB, a super-fast next generation RDBMS that actually incorporates the HyperSQL code for processing SQL statements.

See the Performance page for different configurations of HSQLDB and how they affect performance.
 

SourceForge Logo

This page last updated 4 Jan 2018
Contents of this page are ©2001-2024 The HSQL Development Group. All rights reserved.