亚搏app官方网站,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:

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 /vagrant
3 
4 [root@zlm1 12:12:20 ~]
5 #wget https://github.com/akopytov/sysbench/archive/1.0.zip -O "sysbench-1.0.zip"

 

*www.yabovip4.com,*Install
sysbench.*利用sysbench进行MySQL OLTP基准测试。*

 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 #

利用sysbench进行MySQL OLTP基准测试。 

**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

 

利用sysbench进行MySQL OLTP基准测试。**Lua scripts of
sysbench.*利用sysbench进行MySQL OLTP基准测试。*

 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*利用sysbench进行MySQL OLTP基准测试。*

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.

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注