DB Performance Testing - The 3 Common Suites - A Step-by-Step Guide to Running sysbench

Posted on In , Views: Word count in article: 1.2k Reading time ≈ 4 mins.
Performance Testing -- DB Performance Testing - The 3 Common Suites - A Step-by-Step Guide to Running sysbench

Abstract

Sharing a note I wrote in the past. The three most commonly used database testing suites are: sysbench – OLTP testing, tpch – OLAP testing, and tpcc – transaction performance testing.
This article walks you through running sysbench step by step.

The whole process is divided into:

  • Introduction
  • Preparation
  • Compilation
  • Testing
  • Troubleshooting

Introduction

Here’s a passage from a foreign source to introduce sysbench:

1
2
3
4
5
6
7
8
9
10
sysbench is a scriptable multi-threaded benchmark tool based on LuaJIT. It is most frequently used for database benchmarks, but can also be used to create arbitrarily complex workloads that do not involve a database server.

sysbench comes with the following bundled benchmarks:

    * 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

Today we’ll mainly use the oltp series of tests.

Preparation

Install the required packages:

1
2
3
yum -y install gcc gcc-c++ autoconf automake make libtool bzr mysql-devel git mysql
yum -y install make automake libtool pkgconfig libaio-devel
yum -y install openssl-devel

Run the following commands to configure the Sysbench client so the kernel can use all CPU cores to process packets (the default is set to use 2 cores), while reducing context switching between CPU cores.

1
2
3
4
sudo sh -c 'for x in /sys/class/net/eth0/queues/rx-*; do echo ffffffff>$x/rps_cpus; done'
sudo sh -c "echo 32768 > /proc/sys/net/core/rps_sock_flow_entries"
sudo sh -c "echo 4096 > /sys/class/net/eth0/queues/rx-0/rps_flow_cnt"
sudo sh -c "echo 4096 > /sys/class/net/eth0/queues/rx-1/rps_flow_cnt"

Note: ffffffff means using 32 cores. Adjust it according to your actual configuration—for example, for an 8-core ECS instance, enter ff.

Compilation

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
git clone https://github.com/akopytov/sysbench.git
## Download sysbench from Git

cd sysbench
## Enter the sysbench directory

git checkout 1.0.18
## Switch to sysbench version 1.0.18. You can also skip this and use master directly.

./autogen.sh
## Run autogen.sh

./configure --prefix=/usr --mandir=/usr/share/man

make
## Compile

make install

Testing

A sysbench test typically looks like this, divided into 3 phases: prepare, run, and cleanup.

1
2
3
4
5
6
7
8
sysbench --db-driver=mysql --mysql-host=XXX --mysql-port=XXX --mysql-user=XXX --mysql-password=XXX --mysql-db=sbtest --table_size=25000 --tables=250 --events=0 --time=600  oltp_write_only prepare
## Prepare the data

sysbench --db-driver=mysql --mysql-host=XXX --mysql-port=XXX --mysql-user=XXX --mysql-password=XXX --mysql-db=sbtest --table_size=25000 --tables=250 --events=0 --time=600   --threads=XXX --percentile=95 --report-interval=1 oltp_write_only run
## Run the workload

sysbench --db-driver=mysql --mysql-host=XXX --mysql-port=XXX --mysql-user=XXX --mysql-password=XXX --mysql-db=sbtest --table_size=25000 --tables=250 --events=0 --time=600   --threads=XXX --percentile=95  oltp_write_only cleanup
## Clean up

Explanation of the options:

1
2
3
4
5
6
7
8
9
10
11
12
13
--mysql-host       IP
--mysql-port       Port number
--mysql-db         The database you want to connect to
--mysql-user       Username
--mysql-password   Password
--table_size       The number of rows each table is initialized with
--tables           The number of tables to initialize
--threads          The number of threads to start
--time             The run time; set to 0 to run with no time limit
--report-interval  Logging during the run, in seconds
--events           The maximum number of requests; once set, --time is not required
--rand-type        The random generation function used when accessing data. Options: "special", "uniform", "gaussian", "pareto". Default is special; in earlier versions it was uniform.
--skip_trx=on      In read-only tests you can enable or disable transactions; the default is enabled

The author wrote an automated testing script. Readers can download it from https://github.com/longdafeng/test/tree/master/shell/sysbench
Remember to place the script under src/lua in the sysbench source directory.

1
2
3
4
5
6
cd sysbench
cd src/lua
wget https://raw.githubusercontent.com/longdafeng/test/master/shell/sysbench/start-sysbench.sh 
wget https://raw.githubusercontent.com/longdafeng/test/master/shell/sysbench/start.sh
nohup ./start.sh hostxxx portxxx userxxx passwordxxx dbxxx > run.log 2>&1 &
tail -f run.log

Replace hostxxx, portxxx, userxxx, passwordxxx, and dbxxx with your real MySQL parameters.
This script sets different large-table sizes, different random parameters, different thread counts, and whether to enable or disable transactions, then runs the 3 integrated tests oltp_read_only, oltp_write_only, and oltp_read_write in sequence.
In the src/lua directory there are also many individual tests, such as insert, point_select, update_index, update_non_index, select_random_points, and select_random_ranges, each targeting a different scenario.

1
2
3
[root@kudu lua]# ls *.lua
bulk_insert.lua  oltp_common.lua  oltp_insert.lua        oltp_read_only.lua   oltp_update_index.lua      oltp_write_only.lua  select_random_points.lua
empty-test.lua   oltp_delete.lua  oltp_point_select.lua  oltp_read_write.lua  oltp_update_non_index.lua  prime-test.lua       select_random_ranges.lua

Troubleshooting

Poor Performance

sysbench is a test that’s extremely sensitive to CPU/memory/network. I often see customers find that performance during testing differs significantly from expectations. Digging deeper, it turns out the sysbench “zombie machine” (the client) and the target database aren’t on the same LAN or in the same VPC (for cloud customers). For many cloud databases, the client machine and the target MySQL must be in the same region and the same VPC, and the connection string must use the private connection address, not the public one—public connection addresses go through many hops.

Suppose the statement is:

1
sysbench oltp_read_write.lua --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-db=sbtest --mysql-user=root --mysql-password=123456 --table_size=200000000 --tables=1 --threads=500 --events=500000 --report-interval=10 --time=0

no such built-in test, file or module

If, when running, you get the prompt FATAL: Cannot find benchmark ‘oltp_read_write.lua’: no such built-in test, file or module

Switch to the sysbench source directory (the path where sysbench.tar.gz was extracted):

1
2
find ./ -name oltp_read_write.lua
./src/lua/oltp_read_write.lua

Then switch to the src/lua directory and run the statement again.

“Can not connect to MySQL server. Too many connections”

If, when running, the command line prompts “Can not connect to MySQL server. Too many connections” – MySQL error 1040:

1
2
3
4
5
shell>mysql -uroot -p****
mysql>show variables like 'max_connections';  (Check the current maximum number of connections)
mysql>set global max_connections=1000;  (Set the maximum number of connections to 1000; you can check again whether it was set successfully)
mysql>show variables like 'max_connections';  (Check the current maximum number of connections)
mysql>exit

sysbench Won’t Run

1
ldd /usr/bin/sysbench

Under normal circumstances, all of sysbench’s dependent libraries should resolve correctly. If at some point a dependent library isn’t found, the most common case is that MySQL’s library isn’t found.
You need to install:

1
yum -y install mysql-devel mysql

Then recompile the sysbench source.

If the problem persists, you can try manually creating a link:

1
2
3
4
5
6
Find it in the root directory:
find / -name "*mysqlclient_r*"
/usr/lib64/mysql/libmysqlclient_r.so.18
/usr/lib64/mysql/libmysqlclient_r.so.18.1.0
The library file exists, but with a numeric suffix. Create a symlink for the library file:
ln -s /usr/lib64/mysql/libmysqlclient_r.so.18 /usr/lib64/mysql/libmysqlclient_r.so

If the problem still can’t be resolved, the last resort is to download the MySQL source from GitHub, compile and install the MySQL source first, then recompile sysbench.

1
2
3
# The --with-mysql-includes option specifies MySQL's include folder, which contains .h header files such as mysql.h. Without mysql-community-devel-version installed, there is no include folder.
# The --with-mysql-libs option specifies some of MySQL's libs, which contain .a and .so files such as libmysqlclient.a and libmysqlclient.so
# For example: ./configure --prefix=/usr   --with-mysql-includes=/usr/include/mysql --with-mysql-libs=/usr/lib64/mysql