Installation and use of MYSQL Performance test tool Sysbench

Working with the MySQL service for AWS and Azure, you need to test the performance of the two cloud services. So start Baidu + Google, find performance testing tools. Finally decided to use Sysbench.

Sysbench Introduction

Sysbench is an open source multi-ThreadsPerformance Testingtool that can performCPU/Memory/thread/IO/Database and other aspects of performance testing.

database currently supports mysql/ Oracle /postgresql. This article simply demonstrates the use of several tests and is ready to use Sysbench to perform a series of tests on MySQL. Some of the specific parameter settings need to be adjusted according to different test requirements.

Installation

1. Download the installation package, address https://github.com/akopytov/sysbench

#wget Https://github.com/akopytov/sysbench/archive/1.0.zip-O "Sysbench-1.0.zip" #unzip SYSBENCH-1.0.ZIP#CD sysbench-1.0

2. Installing dependent libraries

#yum Install Automake libtool-y

3. Start the installation

#./autogen.sh#./configure#error:cannot Find MySQL libraries. If you want to compile with MySQL support did not find MySQL library need to specify with parameters under--with-mysql-includes and--with-mysql-libs#./configure--with-m ysql-includes=/alidata/server/mysql5.7/include/--with-mysql-libs=/alidata/server/mysql5.7/lib/

4. Because it is a yum installed MySQL, I do not know where the path is. How do I find the installation path for MySQL?

Install Mysql-devel in order to use Mysql_config

#yum  install -y mysql-devel#mysql_config -helpusage: /usr/bin/mysql_config-64 [ Options]options:--cflags         [-i/usr/include/mysql -g   -fstack-protector  -m64 -fpic  -g -fabi-version=2 - fno-omit-frame-pointer -fno-strict-aliasing]--cxxflags       [-i/usr/ Include/mysql -g  -fexceptions -fstack-protector  -m64 -fpic  -g  -fabi-version=2 -fno-omit-frame-pointer -fno-strict-aliasing]--include         [-I/usr/include/mysql]--libs            [-L/usr/lib64/mysql -lmysqlclient -lpthread -lm -lrt -ldl]--libs_r          [-l/usr/lib64/mysql -lmysqlclient -lpthread -lm  -lrt -ldl]--plugindir      [/usr/lib64/mysql/plugin]--socket          [/var/lib/mysql/mysql.sock]--port            [0]--version        [5.6.39]--libmysqld-libs [-l/usr/lib64/ Mysql -lmysqld -lpthread -lm -lrt -lcrypt -ldl -laio -lnuma]--variable =var   var is one of:pkgincludedir [/usr/include/mysql]pkglibdir      [/usr/lib64/mysql]plugindir     [/usr/lib64/mysql/plugin]

5. Execute config again to successfully

#./configure--with-mysql-includes=/usr/include/mysql--with-mysql-libs=/usr/lib64/mysql#make

6. Execute the following command:

#sysbench--help#sysbench:error while loading shared libraries:libmysqlclient.so.20:cannot open Shared object File:no Such file or directory# problem reason: Sysbench unable to find the MySQL library files, it is possible that the environment variable Ld_library_path not set, set up to resolve the problem: #export ld_library_path=/ Alidata/server/mysql5.7/lib/lib#sysbench--versionsysbench 1.0.12 (using bundled Luajit 2.1.0-beta2)

Preparing test tables and data

1. Create a Test database:

Mysql>create database dbtest;

2. Test commands

#/home/mysql/sysbench-1.0/src/sysbench--test=/home/mysql/sysbench-1.0/tests/include/oltp_legacy/oltp.lua-- Mysql-host=mysql-host-ip--mysql-port=3306--mysql-user=envision--mysql-password=password--mysql-db=dbtest-- oltp-tables-count=10--oltp-table-size=500000--report-interval=10--rand-init=on--max-requests=0--oltp-read-only =off--max-time=120--num-threads=30 [Prepare | run | cleanup]

# #三选一

Prepare preparation;

Run;

Cleanup cleanup data.

Note the last line, before a test starts with prepare to prepare the table and data, run performs a true cleanup, which is used to clear the data and tables.

3. Execute sysbench Command parameter explanation:

#-test=/root/sysbench-1.0/tests/include/oltp_legacy/oltp.lua indicates that the Oltp.lua script is called for OLTP mode testing #--oltp_tables_count=10 Indicates that 10 test tables are generated #--oltp-table-size=500000 indicates that each test table is populated with a data volume of 500000 #--rand-init=on indicates that each test table is populated with random data to indicate the initiation of a #-num-threads=8 8 Concurrent Connections #--oltp-read-only=off indicates that the read-only test is not performed, that is, the read-write mixed mode test #--report-interval=10 indicates that the test progress report is output every 10 seconds #--rand-type=uniform Represents a random type of fixed mode, several other optional random modes: uniform (fixed), Gaussian (Gaussian), special (specific), Pareto (Pareto) #--max-time=120 represents a maximum execution time of 120 seconds #-- Max-requests=0 indicates that the total number of requests is 0, because the total execution time has been defined above, so the total number of requests can be set to 0, or only the total number of requests, do not set the maximum execution time #--percentile=99 the set sampling ratio, the default is 95%, that is discarded 1% Long request, take the maximum value in the remaining 99%

4. Test preparation: 30 concurrent connections, 10 tables filling 50W Data maximum request time per table 120s

#/home/mysql/sysbench-1.0/src/sysbench --test=/home/mysql/sysbench-1.0/tests/include/oltp_legacy/oltp.lua  --mysql-host=mysql-host-ip --mysql-port=3306 --mysql-user=envision --mysql-password= password --mysql-db=dbtest --oltp-tables-count=10 --oltp-table-size=500000 -- report-interval=10 --rand-init=on --max-requests=0 --oltp-read-only=off --max-time=120  --num-threads=30 prepareCreating table  ' Sbtest1 ' ... inserting 100000 records into  ' sbtest1 ' creating secondary indexes on  ' Sbtest1 ' ... creating table  ' Sbtest2 ' ... inserting 100000 records into  ' sbtest2 ' creating secondary indexes on  ' Sbtest2 ' ..... inserting 100000 records into  ' Sbtest9 ' creating secondary indexes on  ' Sbtest9 ' ... creating table  ' sbtest10 ' ... inserting 100000 records into  ' sbtest10 ' Creating secondary indexes on  ' sbtest10 ' ... 

5. Perform sysbench test and output test report

#/home/mysql/sysbench-1.0/src/sysbench--test=/home/mysql/sysbench-1.0/tests/include/oltp_legacy/oltp.lua-- Mysql-host=mysql-host-ip--mysql-port=3306--mysql-user=envision--mysql-password=password--mysql-db=dbtest-- oltp-tables-count=10--oltp-table-size=500000--report-interval=10--rand-init=on--max-requests=0--oltp-read-only =off--max-time=120--num-threads=128 Run >/tmp/liang/mysql-report.txt

6. View the test report

30 x Threads

sysbench 1.0.12  (USING BUNDLED LUAJIT 2.1.0-BETA2) running the test  With following options:number of threads: 30report intermediate results  every 10 second (s) initializing random number generator from current  timeinitializing worker threads ... threads started! [ 10s ] thds: 30 tps: 188.63 qps: 3795.16  (r/w/o: 2659.89/ 755.31/379.96)  lat  (ms,95%):  223.34 err/s: 0.00 reconn/s: 0.00[ 20s  ] thds: 30 tps: 177.80 qps: 3563.97  (r/w/o: 2496.95/711.31/ 355.71)  lat  (ms,95%):  248.83 err/s: 0.00 reconn/s: 0.00[ 30s ]  thds: 30 tps: 177.20 qps: 3542.62  (r/w/o: 2479.82/708.30/354.50)  lat  (ms,95%):  235.74 ERR/S: 0.00 RECONN/S:&NBsp;0.00[ 40s ] thds: 30 tps: 189.60 qps: 3797.38  (r/w/o:  2658.99/759.20/379.20)  lat  (ms,95%):  227.40 err/s: 0.00 reconn/s: 0.00[  50s ] thds: 30 tps: 190.40 qps: 3798.09  (r/w/o: 2655.29/ 762.20/380.60)  lat  (ms,95%):  211.60 err/s: 0.00 reconn/s: 0.00[ 60s  ] thds: 30 tps: 179.70 qps: 3598.40  (r/w/o: 2520.50/718.40/ 359.50)  lat  (ms,95%):  235.74 err/s: 0.00 reconn/s: 0.00[ 70s ]  thds: 30 tps: 187.30 qps: 3740.91  (r/w/o: 2617.61/748.50/374.80)  lat  (ms,95%):  227.40 err/s: 0.00 reconn/s: 0.00[ 80s ] thds : 30 tps: 187.40 qps: 3755.29  (r/w/o: 2630.19/750.40/374.70)  lat  (ms,95%):  211.60 ERR/S:&NBsp;0.00 reconn/s: 0.00[ 90s ] thds: 30 tps: 188.10 qps:  3762.11  (r/w/o: 2632.80/753.10/376.20)  lat  (ms,95%):  211.60 err/s: 0.00  reconn/s: 0.00[ 100s ] thds: 30 tps: 183.70 qps: 3676.35   (r/w/o: 2575.16/733.69/367.49)  lat  (ms,95%): 227.40 err/s: 0.00  reconn/s: 0.00[ 110s ] thds: 30 tps: 190.80 qps: 3816.33  (r/ w/o: 2671.42/763.41/381.50)  lat  (ms,95%):  211.60 err/s: 0.00 reconn/s:  0.00[ 120s ] thds: 30 tps: 195.49 qps: 3907.36  (r/w/o:  2733.40/783.17/390.79)  lat  (ms,95%): 204.11 err/s: 0.00 reconn/s:  0.00sql statistics:    queries performed:         read:                             313488 --Read Total         write:                    Total          89568  --Write          other:                            44784 --Other operations (operations other than curd, such as commit)         total:                             447840 --Total     transactions:                         22392   (186.27 per sec.)  --Total transactions (number of transactions per second)     queries:                               447840  (3725.43 per sec.) Total number of  --(total per second)     ignored errors:                       0        (0.00 per sec.)   --Total Ignore errors (ignore errors per second)     reconnects:                            0       (0.00 per sec.)  --total number of re-connected (number of re-connect per second) GENERAL STATISTICS:    TOTAL TIME:&NBsp;                          120.2098s --Total Time     total number  of events:              22392  --total number of transactions latency  (MS):         min:                                    105.91 --minimum time-consuming           avg:                                    160.86 --average Time-consuming           max:                                   850.77 --Longest Time           95th percentile:                       223.34 --over 95% average time-consuming          sum:                                3601892.56Threads fairness:    events  (Avg/stddev):            746.4000/4.95 --Total processing events/Standard deviation      execution time  (Avg/stddev):    120.0631/0.05--Total execution time/standard deviation

After testing, the MySQL performance of AWS and Azure is comparable. Sysbench tools are also simple and intuitive to use. A simple performance test for MySQL. Follow the time, and then study the Sysbench on the Cpu,io and other performance testing situation and the use of feelings.

Reference Documentation:

http://blog.csdn.net/oahz4699092zhao/article/details/53332105

Http://www.jb51.net/article/93924.htm

Https://nsimple.top/archives/mysql-sysbench-tool.html

Installation and use of MYSQL Performance test tool Sysbench