Preface

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.

    We have a lot of methods to diagnose
problems in our system such as strace,pstack,gstack,gdb,pt-pmp,etc.But
sometimes there will be some fitful performance issues which are not so
easy to trace.Thus,pt-stalk may help us in diagnosing these kind of
problems.

 

 

Introduce

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.

    pt-stalk is a tool to collect detail
diagnostic data base on triggers you specified such as
gdb,oprofile,strace,tcpdump.The trigger is not the conception of trigger
in database.They’re different at all.pt-stalk provides various options
to collect comprehensive data you need.It’s really useful and
helpful in performance diagnosing.Let’s see some details of it.

 

 

Procedure

Procedure

 

 

Official web site:

Usage

https://github.com/akopytov/sysbench

1 pt-stalk [OPTIONS]

 

 

Git clone address:

**Common Parameters**

https://github.com/akopytov/sysbench.git

 1 --collect //Collect diagostic data based on spedified triggers.
 2 --collect-gdb //Collect gdb data.It prints stack traces from all threads.
 3 --collect-oprofile //Collect oprofile data.
 4 --collect-strace //Collect strace data.Do not specify it when "--collect-gdb" has been used.
 5 --collect-tcpdump //Collect tcpdump data.
 6 --cycles //Limite the times when triggering condition occurs(default "5").
 7 --dest //Specify the position to store diagnostic data(default "/var/lib/pt-stalk").
 8 --disk-bytes-free //Prevent the lack of least disk space.It does not collect data until it has the necessary disk space(default "100M",valid surffixes of unit is k,M,G and T).
 9 --disk-pct-free //It's similar with "--disk-bytes-free" but specify the percentage of disk space.
10 --function //Specify the contents to watch for the triggers(default "status",other value is "processlist" or <yourfilename>).
11 --iterations //Limit the times to collect.It will run forever if not give a specific value.
12 --log //Specify the postion to record logs(default "/var/log/pt-stalk.log").It only generates when deamonized.
13 --match //The pattern to specify when using "--function processlist".
14 --mysql-only //Merely collect the MySQL relevant diagnostic data but disk space is the exception.
15 --retention-time //Specify the purge days of diagnostic data(default "30").
16 --run-time //Specify how many seconds to collect diagnostic data(default "30").It should not be longer than the value of "--sleep".
17 --sleep //Specify how many seconds to sleep after last collection.It used to prevent too many operations of collection(default "300").
18 --stalk //Watch and wait for trigger occur(default "yes").
19 --no_stalk //The option can be specify if you want to collect diagnostic data immediately without waiting for trigger to occur.
20 --threshold //Specify the maximum value for collecting(default "25").
21 --variable //Specify the variables to compared against(default "Threads_running").

 

 

Download it from
github.

Examples

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"

 

 

Generate benchmark with
sysbench.

**Install
sysbench.**

 1 [root@zlm2 08:07:54 ~/sysbench-1.0/src/lua]
 2 #sysbench oltp_read_write.lua --mysql-host=192.168.1.101 --mysql-port=3306 --mysql-user=zlm --mysql-password=aaron8219 --mysql-db=sysbench --tables=10 --table-size=100000 --mysql-storage-engine=innodb cleanup
 3 sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)
 4 
 5 Dropping table 'sbtest1'...
 6 Dropping table 'sbtest2'...
 7 Dropping table 'sbtest3'...
 8 Dropping table 'sbtest4'...
 9 Dropping table 'sbtest5'...
10 Dropping table 'sbtest6'...
11 Dropping table 'sbtest7'...
12 Dropping table 'sbtest8'...
13 Dropping table 'sbtest9'...
14 Dropping table 'sbtest10'...
15 
16 [root@zlm2 08:08:08 ~/sysbench-1.0/src/lua]
17 #sysbench oltp_read_write.lua --mysql-host=192.168.1.101 --mysql-port=3306 --mysql-user=zlm --mysql-password=aaron8219 --mysql-db=sysbench --tables=10 --table-size=100000 --mysql-storage-engine=innodb prepare
18 sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)
19 
20 Creating table 'sbtest1'...
21 Inserting 100000 records into 'sbtest1'
22 Creating a secondary index on 'sbtest1'...
23 Creating table 'sbtest2'...
24 Inserting 100000 records into 'sbtest2'
25 Creating a secondary index on 'sbtest2'...
26 Creating table 'sbtest3'...
27 Inserting 100000 records into 'sbtest3'
28 Creating a secondary index on 'sbtest3'...
29 Creating table 'sbtest4'...
30 Inserting 100000 records into 'sbtest4'
31 Creating a secondary index on 'sbtest4'...
32 Creating table 'sbtest5'...
33 Inserting 100000 records into 'sbtest5'
34 Creating a secondary index on 'sbtest5'...
35 Creating table 'sbtest6'...
36 Inserting 100000 records into 'sbtest6'
37 Creating a secondary index on 'sbtest6'...
38 Creating table 'sbtest7'...
39 Inserting 100000 records into 'sbtest7'
40 Creating a secondary index on 'sbtest7'...
41 Creating table 'sbtest8'...
42 Inserting 100000 records into 'sbtest8'
43 Creating a secondary index on 'sbtest8'...
44 Creating table 'sbtest9'...
45 Inserting 100000 records into 'sbtest9'
46 Creating a secondary index on 'sbtest9'...
47 Creating table 'sbtest10'...
48 Inserting 100000 records into 'sbtest10'
49 Creating a secondary index on 'sbtest10'...
50 
51 [root@zlm2 08:09:23 ~/sysbench-1.0/src/lua]
52 #sysbench oltp_read_write.lua --mysql-host=192.168.1.101 --mysql-port=3306 --mysql-user=zlm --mysql-password=aaron8219 --mysql-db=sysbench --threads=3 --time=3600 --report-interval=10 --rand-type=uniform run
53 sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)
54 
55 Running the test with following options:
56 Number of threads: 3
57 Report intermediate results every 10 second(s)
58 Initializing random number generator from current time
59 
60 
61 Initializing worker threads...
62 
63 Threads started!
64 
65 [ 10s ] thds: 3 tps: 258.68 qps: 5176.49 (r/w/o: 3624.11/1034.82/517.56) lat (ms,95%): 15.83 err/s: 0.00 reconn/s: 0.00
66 [ 20s ] thds: 3 tps: 286.25 qps: 5726.15 (r/w/o: 4008.67/1144.89/572.60) lat (ms,95%): 15.00 err/s: 0.00 reconn/s: 0.00
67 [ 30s ] thds: 3 tps: 270.82 qps: 5416.13 (r/w/o: 3790.80/1083.69/541.64) lat (ms,95%): 16.12 err/s: 0.00 reconn/s: 0.00
68 [ 40s ] thds: 3 tps: 280.98 qps: 5619.75 (r/w/o: 3934.26/1123.53/561.97) lat (ms,95%): 16.12 err/s: 0.00 reconn/s: 0.00
69 [ 50s ] thds: 3 tps: 298.42 qps: 5968.38 (r/w/o: 4177.83/1193.70/596.85) lat (ms,95%): 14.46 err/s: 0.00 reconn/s: 0.00
70 [ 60s ] thds: 3 tps: 278.88 qps: 5578.20 (r/w/o: 3904.92/1115.52/557.76) lat (ms,95%): 15.83 err/s: 0.00 reconn/s: 0.00
71 [ 70s ] thds: 3 tps: 280.91 qps: 5617.66 (r/w/o: 3932.21/1123.63/561.82) lat (ms,95%): 15.83 err/s: 0.00 reconn/s: 0.00
72 [ 80s ] thds: 3 tps: 281.68 qps: 5632.83 (r/w/o: 3942.77/1126.71/563.35) lat (ms,95%): 16.12 err/s: 0.00 reconn/s: 0.00
73 [ 90s ] thds: 3 tps: 281.60 qps: 5631.55 (r/w/o: 3942.07/1126.39/563.10) lat (ms,95%): 16.12 err/s: 0.00 reconn/s: 0.00
74 [ 100s ] thds: 3 tps: 287.62 qps: 5753.86 (r/w/o: 4028.02/1150.49/575.35) lat (ms,95%): 15.55 err/s: 0.00 reconn/s: 0.00
75 [ 110s ] thds: 3 tps: 308.99 qps: 6180.45 (r/w/o: 4326.12/1236.35/617.97) lat (ms,95%): 13.95 err/s: 0.00 reconn/s: 0.00
76 ... //Omitted.
 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 #

 

 

**Collect diagnostic data using
pt-stalk.**

网站地图xml地图