Skip to main content

OLTP Performance Test Method

SysBench introduction

SysBench is a modular, cross-platform, and multithreaded benchmark tool for evaluating parameters that are important for a system that runs a database under heavy load. The idea of this benchmark suite is to quickly get an impression about system performance without setting up complex database benchmarks or even without installing a database at all.

Test description

CREATE TABLE `sbtest1` (
`k` int(11) NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
KEY `k_1` (`k`)

Percentage proportion of each type of SQL statements:

SELECT TypePercentage (%)SQL Statement Example
point_selects10SELECT c FROM sbtest%u WHERE id=?
simple_ranges1SELECT c FROM sbtest%u WHERE id BETWEEN ? AND ?
sum_ranges1SELECT SUM(k) FROM sbtest%u WHERE id BETWEEN ? AND ?
order_ranges1SELECT c FROM sbtest%u WHERE id BETWEEN ? AND ? ORDER BY c
distinct_ranges1SELECT DISTINCT c FROM sbtest%u WHERE id BETWEEN ? AND ? ORDER BY c
index_updates1UPDATE sbtest%u SET k=k+1 WHERE id=?
non_index_updates1UPDATE sbtest%u SET c=? WHERE id=?
  • In this test, operations involved in all SQL statements are read operations.
  • StoneDB does not require secondary indexes, so index_updates is equivalent to non_index_updates.

Performance metrics

  • Transactions Per Second (TPS): the number of transactions committed per second.
  • Queries Per Second (QPS): the number of SQL statements executed per second, including INSERT, SELECT, UPDATE, and DELETE statements.

Additional information

  • In the standard OLTP read/write scenario provided by SysBench, a transaction consists of 18 read/write SQL statements. (Because StoneDB does not support DELETE operations, the DELETE statement is removed in this test.)
  • In the standard OLTP read-only scenario provided by SysBench, a transaction consists of 14 read SQL statements: 14 primary key statements and 4 range statements.
  • In the standard OLTP write-only scenario provided by SysBench, a transaction consists of 4 write SQL statements: 2 UPDATE statements, 1 DELETE statement, and 1 INSERT statement. (Because StoneDB does not support DELETE operations, a DELETE statement and an INSERT statement that is associated with the DELETE statement are removed.)

Install SysBench

yum install gcc gcc-c++ autoconf automake make libtool bzr mysql-devel git mysql
git clone
## Download SysBench from Git.
cd sysbench
## Open the directory that saves SysBench.
git checkout 1.0.18
## Switch the SysBench version to 1.0.18.
## Run
./configure --prefix=$WROKSPACE/sysbench/ --mandir=/usr/share/man
## Compile
make install

Statement example for testing:

cd $WROKSPACE/sysbench/
# Prepare data.
bin/sysbench --db-driver=mysql --mysql-host=xx.xx.xx.xx --mysql-port=3306 --mysql-user=xxx --mysql-password=xxxxxx --mysql-db=sbtest --table_size=800000 --tables=230 --time=600 --mysql_storage_engine=StoneDB --create_secondary=false --test=src/lua/oltp_read_only.lua prepare

# Run workloads.
bin/sysbench --db-driver=mysql --mysql-host=xx.xx.xx.xx --mysql-port=3306 --mysql-user=xxx --mysql-password=xxxxxx --mysql-db=sbtest --table_size=800000 --tables=230 --events=0 --time=600 --mysql_storage_engine=StoneDB --threads=8 --percentile=95 --range_selects=0 --skip-trx=1 --report-interval=1 --test=src/lua/oltp_read_only.lua run

# Clear test data.
bin/sysbench --db-driver=mysql --mysql-host=xx.xx.xx.xx --mysql-port=3306 --mysql-user=xxx --mysql-password=xxxxxx --mysql-db=sbtest --table_size=800000 --tables=230 --events=0 --time=600 --mysql_storage_engine=StoneDB --threads=8 --percentile=95 --range_selects=0 --skip-trx=1 --report-interval=1 --test=src/lua/oltp_read_only.lua cleanup

SysBench parameter description

db-driverThe database driver.
mysql-hostThe address of the test instance.
mysql-portThe port used to connect to the test instance.
mysql-userThe username of the test account.
mysql-passwordThe password of the test account.
mysql-dbThe name of the test database.
table_sizeThe size of the table.
tablesThe number of tables.
eventsThe number of connections.
timeThe time that the test lasts.
threadsThe number of threads.
percentileThe percentile to calculate in latency statistics. The default value is 95.
report-intervalThe interval for generating reports about the test progress, expressed in seconds. Value 0 indicates that no such report will be generated, and only the final report will be generated.
skip-trxWhether to skip transactions.
- 1: yes
- 0: no
mysql-socketThe .sock file specified for the instance. This parameter is valid if the instance is a local instance.
create_secondaryWhether to create secondary indexes. The default value is true.