利用sysbench进行MySQL OLTP基准测试

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"

 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 #

 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

 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

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

 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.

 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 #

 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 #