Sysbench

What is Sysbench ?

sysbench is a scriptable multi-threaded benchmark tool. It is most frequently used to benchmark database performance of both mysql and postgresql databases.It is also extiensively used to create arbitary complex workload on the servers.

What are the benchmarks Sysbench perform ?

sysbench performs following benchmarks on the servers.

oltp* indicates the oltp transaction benchmarks that can be performed either on MySQL and PostgreSQL databases.

• oltp_*.lua  -  a collection of OLTP-like database benchmarks
• fileio      -  a filesystem-level benchmark
• cpu         -  a simple CPU benchmark
• memory      -  a memory access benchmark
• threads     -  a thread-based scheduler benchmark
• mutex       -  a POSIX mutex benchmark

Installation

Sysbench can be installed directly from source code or yum repository or directly from rpms.

Below examples shows the yum installation of sysbench on CentOS and fedora servers.

On RHEL/CentOS:

curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash sudo yum -y install sysbench

On Fedora:

curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash    sudo dnf -y install sysbench

Make sure sysbench is installed without any errors/issues. Verify the installation of sysbench.

[vgurram@localhost ~]$ rpm -qa |grep sysbench sysbench-1.0.11-1.el7.centos.x86_64

Usage

Benchmarking the performance of Database/server using sysbench is based on the lua files that were generated by the rpm. location of them in genral will be /usr/share/sysbench.Location of these lua files can also be found using rpm -ql sysbench-1.0.11-1.el7.centos.x86_64.
Pre-requisites:

• Running mysql instance
• Database "sbtest" to be present on the instance.
• A user with all privileges on "sbtest" database. user should be able to connect remotely or using socket depending on location of sysbench being installed.

The general command line syntax for sysbench is:

           sysbench [options]... [testname] [command]

Options can be of Log options, Database options and General options to run the tests.

testname indicates the test that is being ran.

Sysbench can run only below 4 commands for each specific test.

prepare  – it performs preparative actions for those tests which need them like creating tables for DB related tests.

run  -  Runs the actual test which is given as part of sysbench command.

cleanup  -  removes temporary data if any created during the run of tests.

help  - displays the usage information for the test specified with testname as an argument.

Detailed Options of Sysbench

Options of Sysbench can be classified into

1. General Command Line Options
2. Database Options
3. Log Options.

Below explains in details of all the options that can be passed as command line arguments in sysbench.

General Command Line Options

These options define the property or the manner how sysbench needs to be run to perform various tests.

Option	Description	default value
--threads	No. of threads to create/use	1
--time	Total execution Time(sec)	10
--report-interval	periodically report intermediate statistics with a specified interval in seconds	0
--rate=N	Average Transaction rate (unlimited - 0)	0
--events=N	Limit the Total no. of events(no limit - 0)	0
--tables	No. of Tables	1
--table_size	Size of each Table(in terms of No.of rows)	10000
--index_updates=N	No. of UPDATE index queries per transaction	1
--non_index_updates=N	No. of UPDATE non-index queries per transaction	1
--delete_inserts=N	No. of DELETE and INSERT combination per transaction	1
--point_selects	No. of point SELECT queries per transaction	10
--simple_ranges	No. of simple range SELECT queries per transaction	1
--sum_ranges	No. of SELECT SUM() queries per transaction	1
--order_ranges	No. of SELECT ORDER BY queries per transaction	1
--distinct_ranges	No. of SELECT DISTINCT queries per transaction	1
--range_selects	Enable/disable all range SELECT queries	true
--random_points	No. of random points in the IN() clause in  SELECTs	10
--number_of_ranges	No. of random BETWEEN ranges per SELECT works only for select_random_ranges benchmark	10
--delta	Size of BETWEEN ranges works only for select_random_ranges benchmark	5
--auto_inc	Use AUTO_INCREMENT column as Primary Key(MySQL). If disabled, use client-generated IDs	true
--secondary	Use secondary index in place of the PRIMARY KEY	false
--create_secondary	Create a secondary index in addition to the PRIMARY KEY	true

Database Options

These options are mostly related to establishing the Connection to Database,db-driver information etc..

Below options are defined only for MySQL Database instance.

Option	Description	Default
--mysql-host	The hostname of the Mysql Server	localhost
--mysql-port	The Port on which MySQL instance is running	3306
--mysql-socket	The socket file for Connection info
--mysql-user	User to establish a connection to MySQL Instance	sbtest
--mysql-password	Password for the DB user
--mysql-db	Name of DB where the benchmark needs to be run	sbtest
--db-driver	Type of the database where the test needs to be run

Log Options

These options provides the debug and analysis information of the test that is being run on the sysbench.

Option	Description	Default
--verbosity	Verbosity Level {  Debug - 5, Critical -0 }	3
--percentile	percentile to calculate in latency statistics(1-100). 0 defines disabling the percentile calculation	95
--histogram	print latency histogram in report	off

Only the options that can be used frequently is mentioned here. For few other options use sysbench --help  command.

Benchmark test for mysql

Benchmark test on the MySQL Database Instance can be customized using the above options and the tests that are compiled in the sysbench.

Following are the different oltp test that can be performed on the MySQL Database Instance.

• Insert Only
• Write Only
• Read Write
• Update Index
• Update Non-Index
• Delete Only
• Read Only
• Point Select
• Select Random Points
• Select Random Ranges
• Bulk Insert

For all the tests mentioned above, the lua file name has to be changed as per the benchmark test that is being performed.

Types of Queries

FollowingTable gives the sample query type for each classification of sysbench benkmark test. Based on the required test work load, general command line options has to be chosen effectively.

Query Type	Sample Query
inserts	INSERT INTO sbtest% (id, k, c, pad) VALUES (?, ?, ?, ?)
index_updates	UPDATE sbtest% SET k=k+1 WHERE id=? ( k will be having secondary index)
non_index_updates	UPDATE sbtest% SET c=? WHERE id=?
deletes	DELETE FROM sbtest% WHERE id=?
point_selects	SELECT c FROM sbtest% WHERE id=?
simple_ranges	SELECT c FROM sbtest% WHERE id BETWEEN ? AND ?
sum_ranges	SELECT SUM(k) FROM sbtest% WHERE id BETWEEN ? AND ?
order_ranges	SELECT c FROM sbtest% WHERE id BETWEEN ? AND ? ORDER BY c
distinct_ranges	SELECT DISTINCT c FROM sbtest% WHERE id BETWEEN ? AND ? ORDER BY c
select_random_points	SELECT id, k, c, pad FROM sbtest1 WHERE k IN (%s)
select_random_ranges	SELECT count(k) FROM sbtest1 WHERE k BETWEEN ? AND ? OR k BETWEEN ? AND ? ...

Preparation

Before performing the Benchmark test, DB needs to be Prepared i.e., Tables needs to be created and data needs to be inserted into the DB instance.

Below command can be used to prepare the DB

sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --mysql-user=root --mysql-password='' --mysql-host=172.16.2.217 --mysql-port=3310  --tables=8 --table-size=1000000 --threads=8 prepare

The Options that are used here are 

i) --db-driver=mysql   → This is a mandatory option as it is used to prepare statements based on the type of the Database.

ii) --mysql-user=root  → Using the root user for establishing the Connection to the DB instance.

iii) --mysql-password=''   → Password for the --mysql-user. Password needs to be passed in command itself. It will not prompt to enter the password later.

iv) --mysql-host=172.16.2.217  → DB is running on 172.16.2.217 server.

v) --mysql-port=3310  → MySQL Port where DB instance is up and running.

vi) --tables=8  → No. of tables to be created inside the --mysql-database (sbtest). If this option is not specified only 1 table will be created.

vii) --table-size=1000000  → Size of each Table in terms of No. of rows. If this is not specified, the default of 10000 rows will be created per table.

viii) --threads=8  → No. of threads used to prepare(i.e., create table and insert data). During preparation, if there are sufficient thread savailable, each table operation will be done by a single thread. excess threads will be ignored.

Now the DB instance preparation is completed. Sysbench test can be ran on the Instances to benchmark the performance.

Running Benchmark Tests

Insert only

Performs only insert operation. Each insert statement is a transaction.

sysbench /usr/share/sysbench/oltp_insert.lua --db-driver=mysql --mysql-user=root --mysql-password='' --mysql-host=172.16.2.217 --mysql-port=3310 --report-interval=2 --tables=8 --threads=8 --time=60 run

The options that are used here(in difference with prepare command)

i) --report-interval=2  → displays the statistics of the operation every 2 seconds.

ii) --time=60  → Perform the operations continuously for 60 seconds.

Write Only

Each Transaction here performs 

i) Index Updates ( 1 or --index_updates(if specified) )

ii) Non-index updates ( 1 or --non_index_updates(if specified) )

iii) delete_inserts (1 or --delete_inserts(if specified) )

Using default values, 4 statements comprise a single transaction. The behaviour can be changed using the options as specified.

To test the performance of the batch of statements, change any of the index_updates/non_index_updates/delete_inserts value to a greater value.

sysbench /usr/share/sysbench/oltp_write_only.lua --db-driver=mysql --mysql-user=root --mysql-password='' --mysql-host=172.16.2.217 --mysql-port=3310  --report-interval=2 --tables=8 --threads=8 --time=60 run

Using the above statement runs a benchmark test for 60 seconds, each transaction with 4 statements (1 insert,1 index update,1 non-index update,1 delete).

If a batch of 100 statements needs to be committed for a single transaction, any of the below statements can be used.

sysbench /usr/share/sysbench/oltp_write_only.lua --db-driver=mysql --mysql-user=root --mysql-password='' --mysql-host=172.16.2.217 --mysql-port=3310  --report-interval=2 --tables=8 --threads=8 --index_updates=100 --non_index_updates=0 --delete_inserts=0 --time=60 run sysbench /usr/share/sysbench/oltp_write_only.lua --db-driver=mysql --mysql-user=root --mysql-password='' --mysql-host=172.16.2.217 --mysql-port=3310  --report-interval=2 --tables=8 --threads=8 --index_updates=0 --non_index_updates=100 --delete_inserts=0  --time=60 run sysbench /usr/share/sysbench/oltp_write_only.lua --db-driver=mysql --mysql-user=root --mysql-password='' --mysql-host=172.16.2.217 --mysql-port=3310  --report-interval=2 --tables=8 --threads=8 --index_updates=0 --non_index_updates=0 --delete_inserts=50  --time=60 run sysbench /usr/share/sysbench/oltp_write_only.lua --db-driver=mysql --mysql-user=root --mysql-password='' --mysql-host=172.16.2.217 --mysql-port=3310  --report-interval=2 --tables=8 --threads=8 --index_updates=25 --non_index_updates=25 --delete_inserts=25  --time=60 run

Read Write

Each transaction here is a combination of 

Point Select (10 or --point_selects(if specified) )
Index Update ( 1 or --index_updates(if specified) )
Non-index Update ( 1 or --non_index_updates(if specified) )
Delete Insert (1 or --delete_inserts(if specified) )
Simple Range (1 or --simple_ranges(if specified), unless --range_selects=false is defined )
Sum Range ( 1 or --sum_ranges(if specified), unless --range_selects=false is defined )
Order Range ( 1 or --order_ranges(if specified), unless --range_selects=false is defined )
Distinct Range ( 1 or --distinct_ranges(if specified), unless --range_selects=false is defined )

Using the default values, a transaction is a combination of 18 statements ( Read-14, Write- 4). The No. of statements per transaction can be changed based on the options provided.

sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --mysql-user=root --mysql-password='' --mysql-host=172.16.2.217 --mysql-port=3310  --report-interval=2 --tables=8 --threads=8 --time=60 run

Update Index

Each transaction here updates 1 or --index_updates(if specified) number of rows. This will be run for 10 seconds(default) or --time amount of time.

Considering the default value, each statement will be a transaction here.

sysbench /usr/share/sysbench/oltp_update_index.lua --db-driver=mysql --mysql-user=root --mysql-password='' --mysql-host=172.16.2.217 --mysql-port=3310  --report-interval=2 --tables=8 --threads=8 --time=60 run

Update Non-Index

Each transaction here updates 1 or --non_index_updates(if specified) number of rows. This will be run for 10 seconds(default) or --time amount of time.

Considering the default value, each statement will be a transaction here.

sysbench /usr/share/sysbench/oltp_update_non_index.lua --db-driver=mysql --mysql-user=root --mysql-password='' --mysql-host=172.16.2.217 --mysql-port=3310  --report-interval=2 --tables=8 --threads=8 --time=60 run

Delete Only

Performs only Delete Operations. Each Delete statement is a transaction.

sysbench /usr/share/sysbench/oltp_delete.lua --db-driver=mysql --mysql-user=root --mysql-password='' --mysql-host=172.16.2.217 --mysql-port=3310  --report-interval=2 --tables=8 --threads=8 --time=60 run

Read Only

Each transaction here is a combination of 

Point Select (10 or --point_selects(if specified) )
Simple Range (1 or --simple_ranges(if specified), unless --range_selects=false is defined )
Sum Range ( 1 or --sum_ranges(if specified), unless --range_selects=false is defined )
Order Range ( 1 or --order_ranges(if specified), unless --range_selects=false is defined )
Distinct Range ( 1 or --distinct_ranges(if specified), unless --range_selects=false is defined ).

Considering the default values, each transaction is a combination of 14 Read operations.

sysbench /usr/share/sysbench/oltp_read_only.lua --db-driver=mysql --mysql-user=root --mysql-password='' --mysql-host=172.16.2.217 --mysql-port=3310  --report-interval=2 --tables=8 --threads=8 --time=60 run

Point Select

Performs only one point select operation per transaction. Defining the "point_selects" to some value doesn't increase the size of the transaction.

sysbench /usr/share/sysbench/oltp_point_select.lua --db-driver=mysql --mysql-user=root --mysql-password='' --mysql-host=172.16.2.217 --mysql-port=3310  --report-interval=2 --tables=8 --threads=8 --time=60 run

Select Random Points

This benchmark works only when --tables=1 only.Support for multiple tables is not yet present.

Each statement here includes 10 or --random_points in the IN clause of the select query. Each statement is a transaction.

sysbench /usr/share/sysbench/select_random_points.lua --db-driver=mysql --mysql-user=root --mysql-password='' --mysql-host=172.16.2.217 --mysql-port=3310  --report-interval=2 --tables=1 --threads=8 --time=60 run

 Select Random Ranges

This benchmark works only on sbtest1 table. Support for Multiple tables is not yet present.

Each statement here has 10 or --number_of_ranges number if BETWEEN clauses and size of each between operation is 5 or --delta.Each statement is a transaction.

sysbench /usr/share/sysbench/select_random_ranges.lua --db-driver=mysql --mysql-user=root --mysql-password='' --mysql-host=172.16.2.217 --mysql-port=3310  --report-interval=2 --tables=1 --threads=8 --time=60 run

Bulk Insert

This benchmark creates a new table with 2 integer columns and prepares the insert statement with , seperated values for multiple rows and makes a bulk insert statement and drops the table at the end of the operation.

sysbench /usr/share/sysbench/bulk_insert.lua --db-driver=mysql --mysql-user=root --mysql-password='' --mysql-host=172.16.2.217 --mysql-port=3310  --report-interval=2 --threads=8 --time=60 run

Clean up

Cleanup is used to remove all the data that is being created by sysbench which it has used for testing purpose.

Cleanup can be either 

• Manual Cleanup
• Using Sysbench command

Manual Cleanup

Login to DB server and drop the database sbtest

Using sysbench

Use below command to cleanup the data which is created by sysbench.

sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --mysql-user=root --mysql-password='' --mysql-host=172.16.2.217 --mysql-port=3310  --tables=8 --table-size=1000000 --threads=8 cleanup