利用sysbench进行MySQL OLTP基准测试

 

Preface

 

    In order to know clearly about the real performance threshold of database server,we usually do database benchmarks.What's it?It's similar with preasure test of database in the whole system.The difference is that the database benchmark may not care about the real business performance but the direct performance with virtual data.It's simple and convenient to implement whereas what is more complex in presure test.The items what we really care about in database benchmark is TPS/QPS/RT(Response Time)/Concurrency,etc.I'm gonna use sysbench(one of the benchmark test tools) to do some basic oltp read write test of MySQL database today.

 

Introduce

 

    sysbench is a common tool used in database bentchmark to test MySQL database(of cource,it supports PostgreSQL and Oracle,too).It provide many test case through several built-in lua scripts by specifying option of "testname".We can use the tool to get performance statistics about CPU,IO,Memory,etc.

 

Procedure

 

Official web site:

 

Git clone address:

 

Download it from github.

1 [root@zlm1 12:12:16 ~]
2 #cd /vagrant
3 
4 [root@zlm1 12:12:20 ~]
5 #wget https://github.com/akopytov/sysbench/archive/1.0.zip -O "sysbench-1.0.zip"

 

Install sysbench.

 1 [root@zlm1 12:15:26 /vagrant]
 2 #cp -R sysbench-1.0 ~
 3 
 4 [root@zlm1 12:16:02 ~]
 5 #cd sysbench-1.0/
 6 
 7 [root@zlm1 12:16:07 ~/sysbench-1.0]
 8 #./autogen.sh 
 9 ./autogen.sh: running `libtoolize --copy --force' 
10 libtoolize: putting auxiliary files in AC_CONFIG_AUX_DIR, `config'.
11 libtoolize: copying file `config/ltmain.sh'
12 libtoolize: putting macros in AC_CONFIG_MACRO_DIR, `m4'.
13 libtoolize: copying file `m4/libtool.m4'
14 libtoolize: copying file `m4/ltoptions.m4'
15 libtoolize: copying file `m4/ltsugar.m4'
16 libtoolize: copying file `m4/ltversion.m4'
17 libtoolize: copying file `m4/lt~obsolete.m4'
18 ./autogen.sh: running `aclocal -I m4' 
19 ./autogen.sh: running `autoheader' 
20 ./autogen.sh: running `automake -c --foreign --add-missing' 
21 configure.ac:59: installing 'config/ar-lib'
22 configure.ac:45: installing 'config/compile'
23 configure.ac:27: installing 'config/config.guess'
24 configure.ac:27: installing 'config/config.sub'
25 configure.ac:32: installing 'config/install-sh'
26 configure.ac:32: installing 'config/missing'
27 src/Makefile.am: installing 'config/depcomp'
28 parallel-tests: installing 'config/test-driver'
29 ./autogen.sh: running `autoconf' 
30 Libtoolized with: libtoolize (GNU libtool) 2.4.2
31 Automade with: automake (GNU automake) 1.13.4
32 Configured with: autoconf (GNU Autoconf) 2.69
33 
34 [root@zlm1 12:16:18 ~/sysbench-1.0]
35 #./configure && make && make install
36 //Omitted.
37 [root@zlm1 12:18:40 ~/sysbench-1.0]
38 #sysbench --version
39 sysbench: error while loading shared libraries: libmysqlclient.so.20: cannot open shared object file: No such file or directory
40 
41 [root@zlm1 12:19:14 ~/sysbench-1.0]
42 #whereis sysbench
43 sysbench: /usr/local/bin/sysbench
44 
45 [root@zlm1 12:19:27 ~/sysbench-1.0]
46 #ldd /usr/local/bin/sysbench
47     linux-vdso.so.1 =>  (0x00007fff2abfe000)
48     libmysqlclient.so.20 => not found //Only lack this lib.
49     libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f86e2986000)
50     librt.so.1 => /lib64/librt.so.1 (0x00007f86e277e000)
51     libdl.so.2 => /lib64/libdl.so.2 (0x00007f86e257a000)
52     libaio.so.1 => /lib64/libaio.so.1 (0x00007f86e2377000)
53     libm.so.6 => /lib64/libm.so.6 (0x00007f86e2075000)
54     libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f86e1e5f000)
55     libc.so.6 => /lib64/libc.so.6 (0x00007f86e1a9d000)
56     /lib64/ld-linux-x86-64.so.2 (0x00007f86e2bab000)
57 
58 [root@zlm1 12:19:41 ~/sysbench-1.0]
59 #find / -name libmysqlclient //There's no result found.
60 
61 [root@zlm1 12:21:00 ~/sysbench-1.0]
62 #

 

Solve the lack of mysqlclient.so.20 file.

 1 [root@zlm1 12:24:27 ~/sysbench-1.0]
 2 #vim /etc/ld.so.conf.d/mysql.conf
 3 
 4 [root@zlm1 12:26:17 ~/sysbench-1.0]
 5 #cat /etc/ld.so.conf.d/mysql.conf
 6 /usr/local/mysql/lib
 7 
 8 [root@zlm1 12:26:22 ~/sysbench-1.0]
 9 #ldconfig
10 
11 [root@zlm1 12:26:41 ~/sysbench-1.0]
12 #ldd /usr/local/bin/sysbench
13     linux-vdso.so.1 =>  (0x00007fff767fe000)
14     libmysqlclient.so.20 => /usr/local/mysql/lib/libmysqlclient.so.20 (0x00007fb2ca8a8000)
15     libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fb2ca68b000)
16     librt.so.1 => /lib64/librt.so.1 (0x00007fb2ca483000)
17     libdl.so.2 => /lib64/libdl.so.2 (0x00007fb2ca27f000)
18     libaio.so.1 => /lib64/libaio.so.1 (0x00007fb2ca07d000)
19     libm.so.6 => /lib64/libm.so.6 (0x00007fb2c9d7a000)
20     libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007fb2c9b64000)
21     libc.so.6 => /lib64/libc.so.6 (0x00007fb2c97a3000)
22     libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007fb2c949b000)
23     /lib64/ld-linux-x86-64.so.2 (0x00007fb2caec7000)
24 
25 [root@zlm1 12:26:53 ~/sysbench-1.0]
26 #sysbench --version
27 sysbench 1.0.14

 

Lua scripts of sysbench.

 1 [root@zlm1 13:24:38 ~/sysbench-1.0/src/lua]
 2 #ls -l
 3 total 124
 4 -rwxr-xr-x 1 root root  1452 Jul  1 12:15 bulk_insert.lua
 5 drwxr-xr-x 2 root root  4096 Jul  1 12:18 internal
 6 -rw-r--r-- 1 root root 25855 Jul  1 12:17 Makefile
 7 -rwxr-xr-x 1 root root  1219 Jul  1 12:15 Makefile.am
 8 -rw-r--r-- 1 root root 25401 Jul  1 12:16 Makefile.in
 9 -rwxr-xr-x 1 root root 14369 Jul  1 12:15 oltp_common.lua
10 -rwxr-xr-x 1 root root  1290 Jul  1 12:15 oltp_delete.lua
11 -rwxr-xr-x 1 root root  2415 Jul  1 12:15 oltp_insert.lua
12 -rwxr-xr-x 1 root root  1265 Jul  1 12:15 oltp_point_select.lua
13 -rwxr-xr-x 1 root root  1649 Jul  1 12:15 oltp_read_only.lua
14 -rwxr-xr-x 1 root root  1824 Jul  1 12:15 oltp_read_write.lua
15 -rwxr-xr-x 1 root root  1118 Jul  1 12:15 oltp_update_index.lua
16 -rwxr-xr-x 1 root root  1127 Jul  1 12:15 oltp_update_non_index.lua
17 -rwxr-xr-x 1 root root  1440 Jul  1 12:15 oltp_write_only.lua
18 -rwxr-xr-x 1 root root  1919 Jul  1 12:15 select_random_points.lua
19 -rwxr-xr-x 1 root root  2118 Jul  1 12:15 select_random_ranges.lua

 

Usage

1 sysbench [options]... [testname] [command]
2 Commands implemented by most tests: prepare run cleanup help

 

Parameters

 1 //Common Options.
 2 --threads //Specify the threads to use(default 1).
 3 --time //Specify the execution time in seconds(default 10s).
 4 --events //Specify the number of events(default 0).
 5 --rate //Speicify the transactions rate(default 0).
 6 --db-driver //Specify the database driver(default mysql).
 7 --rand-type //Specify the random numbers distribution(defautl "special",others "uniform","gaussian","pareto").
 8 --warmup-time //Specify the time to warmup before real execution to avoid inaccurate result(default 0).
 9 --report-interval //Specify the time about the report interval(default 0,means disable intermediate report).
10 --table //Specify the number of tables in test database.
11 --table-size //Specify the number of records in each test table.
12 
13 //MySQL Driver Options.
14 --mysql-host //Specify the host(default localhost).
15 --mysql-port //Specify the port(default 3306).
16 --mysql-socket //Specify the socket.
17 --mysql-user //Default sbtest.
18 --mysql-password //Specify the password of user.
19 --mysql-db //Specify the test db(default sbtest).
20 --mysql-dry-run //It does not really run but treat MySQL client API calls are successful.
21 
22 //Command Options(depends on "testname").
23 prepare //creates necessary files or data that the peticular test need.
24 run //actually runs the specific test which is specified by "testname" option.
25 cleanup //Removes all the test files or data which are relevant with.
26 help //Shows the information of particular test.

 

Example

 

Prepare

 1 [root@zlm1 14:31:03 ~/sysbench-1.0/src/lua]
 2 #sysbench oltp_read_write.lua --mysql-host=192.168.56.100 --mysql-port=3306 --mysql-user=zlm --mysql-password=aaron8219 --mysql-db=sysbench --tables=10 --table-size=100000 --mysql-storage-engine=innodb prepare
 3 sysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)
 4 
 5 Creating table 'sbtest1'...
 6 Inserting 100000 records into 'sbtest1'
 7 Creating a secondary index on 'sbtest1'...
 8 Creating table 'sbtest2'...
 9 Inserting 100000 records into 'sbtest2'
10 Creating a secondary index on 'sbtest2'...
11 Creating table 'sbtest3'...
12 Inserting 100000 records into 'sbtest3'
13 Creating a secondary index on 'sbtest3'...
14 Creating table 'sbtest4'...
15 Inserting 100000 records into 'sbtest4'
16 Creating a secondary index on 'sbtest4'...
17 Creating table 'sbtest5'...
18 Inserting 100000 records into 'sbtest5'
19 Creating a secondary index on 'sbtest5'...
20 Creating table 'sbtest6'...
21 Inserting 100000 records into 'sbtest6'
22 Creating a secondary index on 'sbtest6'...
23 Creating table 'sbtest7'...
24 Inserting 100000 records into 'sbtest7'
25 Creating a secondary index on 'sbtest7'...
26 Creating table 'sbtest8'...
27 Inserting 100000 records into 'sbtest8'
28 Creating a secondary index on 'sbtest8'...
29 Creating table 'sbtest9'...
30 Inserting 100000 records into 'sbtest9'
31 Creating a secondary index on 'sbtest9'...
32 Creating table 'sbtest10'...
33 Inserting 100000 records into 'sbtest10'
34 Creating a secondary index on 'sbtest10'...
35 
36 //Create 10 tables with 10w rows each.
37 [root@zlm1 14:32:59 ~/sysbench-1.0/src/lua]
38 #

 

 Run

 1 [root@zlm1 14:37:31 ~/sysbench-1.0/src/lua]
 2 #sysbench oltp_read_write.lua --mysql-host=192.168.56.100 --mysql-port=3306 --mysql-user=zlm --mysql-password=aaron8219 --mysql-db=sysbench --threads=3 --time=60 --warmup-time=30 --report-interval=10 --rand-type=uniform run
 3 sysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)
 4 
 5 invalid option: --warmup-time=30 //It's not supported in my version.
 6 
 7 [root@zlm1 14:37:34 ~/sysbench-1.0/src/lua]
 8 #sysbench oltp_read_write.lua --mysql-host=192.168.56.100 --mysql-port=3306 --mysql-user=zlm --mysql-password=aaron8219 --mysql-db=sysbench --threads=3 --time=60 --report-interval=10 --rand-type=uniform run
 9 sysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)
10 
11 Running the test with following options:
12 Number of threads: 3 //There're three threads.
13 Report intermediate results every 10 second(s) //Show report every 10s.
14 Initializing random number generator from current time
15 
16 
17 Initializing worker threads...
18 
19 Threads started!
20 
21 [ 10s ] thds: 3 tps: 134.07 qps: 2684.74 (r/w/o: 1879.80/536.59/268.34) lat (ms,95%): 48.34 err/s: 0.00 reconn/s: 0.00 //Notice the low value here.
22 [ 20s ] thds: 3 tps: 273.74 qps: 5473.50 (r/w/o: 3831.29/1094.64/547.57) lat (ms,95%): 15.00 err/s: 0.00 reconn/s: 0.00
23 [ 30s ] thds: 3 tps: 273.30 qps: 5467.40 (r/w/o: 3827.60/1093.20/546.60) lat (ms,95%): 14.73 err/s: 0.00 reconn/s: 0.00
24 [ 40s ] thds: 3 tps: 250.50 qps: 5009.82 (r/w/o: 3506.61/1002.20/501.00) lat (ms,95%): 17.95 err/s: 0.00 reconn/s: 0.00
25 [ 50s ] thds: 3 tps: 261.07 qps: 5222.57 (r/w/o: 3656.33/1044.09/522.15) lat (ms,95%): 16.41 err/s: 0.00 reconn/s: 0.00
26 [ 60s ] thds: 3 tps: 264.52 qps: 5289.24 (r/w/o: 3702.14/1058.07/529.03) lat (ms,95%): 15.55 err/s: 0.00 reconn/s: 0.00
27 SQL statistics:
28     queries performed:
29         read:                            203980 //Executions of writing.
30         write:                           58280 //Executions of reading.
31         other:                           29140 //Operations excetp CURD.
32         total:                           291400
33     transactions:                        14570  (242.76 per sec.) //TPS is 242.
34     queries:                             291400 (4855.30 per sec.) //QPS is 4855.
35     ignored errors:                      0      (0.00 per sec.)
36     reconnects:                          0      (0.00 per sec.)
37 
38 General statistics:
39     total time:                          60.0155s
40     total number of events:              14570 //Total transactions.
41 
42 Latency (ms):
43          min:                                    3.17
44          avg:                                   12.35
45          max:                                  887.76
46          95th percentile:                       16.41 //Response time of former 95%
47          sum:                               179957.65
48 
49 Threads fairness:  //Statistics of concurrency.
50     events (avg/stddev):           4856.6667/21.23
51     execution time (avg/stddev):   59.9859/0.00
52 
53 
54 [root@zlm1 14:39:06 ~/sysbench-1.0/src/lua]
55 #

 

 summary

• sysbench is more powerful than mysqlslap(built-in program of MySQL).
• Be ware of  the diffrence between database benchmark test and presure test.
• There're also some other tools can be used to test database performance such as mysql-tpcc,YCSB,etc.
• Database benchmark really helps us to have a explicit overview about our newly arrived server.More test is necessary.