Preface
In order to know clear 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:
https://github.com/akopytov/sysbench
Git clone address:
https://github.com/akopytov/sysbench.git
Download it from github.
1 [root@zlm1 12:12:16 ~]2 #cd /vagrant3 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.231 Automade with: automake (GNU automake) 1.13.432 Configured with: autoconf (GNU Autoconf) 2.6933 34 [root@zlm1 12:16:18 ~/sysbench-1.0]35 #./configure && make && make install36 //Omitted.37 [root@zlm1 12:18:40 ~/sysbench-1.0]38 #sysbench --version39 sysbench: error while loading shared libraries: libmysqlclient.so.20: cannot open shared object file: No such file or directory40 41 [root@zlm1 12:19:14 ~/sysbench-1.0]42 #whereis sysbench43 sysbench: /usr/local/bin/sysbench44 45 [root@zlm1 12:19:27 ~/sysbench-1.0]46 #ldd /usr/local/bin/sysbench47 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 #ldconfig10 11 [root@zlm1 12:26:41 ~/sysbench-1.0]12 #ldd /usr/local/bin/sysbench13 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 --version27 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.lua10 -rwxr-xr-x 1 root root 1290 Jul 1 12:15 oltp_delete.lua11 -rwxr-xr-x 1 root root 2415 Jul 1 12:15 oltp_insert.lua12 -rwxr-xr-x 1 root root 1265 Jul 1 12:15 oltp_point_select.lua13 -rwxr-xr-x 1 root root 1649 Jul 1 12:15 oltp_read_only.lua14 -rwxr-xr-x 1 root root 1824 Jul 1 12:15 oltp_read_write.lua15 -rwxr-xr-x 1 root root 1118 Jul 1 12:15 oltp_update_index.lua16 -rwxr-xr-x 1 root root 1127 Jul 1 12:15 oltp_update_non_index.lua17 -rwxr-xr-x 1 root root 1440 Jul 1 12:15 oltp_write_only.lua18 -rwxr-xr-x 1 root root 1919 Jul 1 12:15 select_random_points.lua19 -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 time15 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.0023 [ 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.0024 [ 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.0025 [ 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.0026 [ 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.0027 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: 29140033 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.0155s40 total number of events: 14570 //Total transactions.41 42 Latency (ms):43 min: 3.1744 avg: 12.3545 max: 887.7646 95th percentile: 16.41 //Response time of former 95%47 sum: 179957.6548 49 Threads fairness: //Statistics of concurrency.50 events (avg/stddev): 4856.6667/21.2351 execution time (avg/stddev): 59.9859/0.0052 53 54 [root@zlm1 14:39:06 ~/sysbench-1.0/src/lua]55 #
summary
