Detailed tutorial on using sysbench to test MySQL performance

sysbench is a modular, cross-platform, multi-threaded benchmarking tool designed to evaluate and test database load against a variety of system parameters.
Currently sysbench code hosted on launchpad, address of the project: https: / / launchpad net/sysbench (original website http: / / sysbench sourceforge. net has been unavailable), source adopts bazaar management.

1. Download the source package
After installing the epel package to install the bzr client:

rpm -Uvh http://dl.fedoraproject.org/pub/epel/5/i386/epel-release-5-4.noarch.rpm

You are then ready to install the bzr client:

yum install bzr

From there, you can start downloading the ES27en-ES28en source code with the bzr client.

cd /tmp
bzr branch lp:sysbench

MySQL Chinese web easy download address:

http://imysql.com/wp-content/uploads/2014/09/sysbench-0.4.12-1.1.tgz

sysbench supports the following test modes:

1, CPU operational performance
2. Performance of disk IO
3. Scheduler performance
4. Memory allocation and transmission speed
5. POSIX thread performance
6. Database performance (OLTP benchmark)
The main support mysql sysbench, drizzle pgsql, oracle etc. Several database.

2. Compile and install
Compiling is very simple. Refer to the README documentation. The simple steps are as follows:

cd /tmp/sysbench-0.4.12-1.1
./autogen.sh
./configure --with-mysql-includes=/usr/local/mysql/include --with-mysql-libs=/usr/local/mysql/lib && make

#  if  make  There is no error, it will be in  sysbench  Generate under directory 2 Base command line tool  sysbench
ls -l sysbench
-rwxr-xr-x 1 root root 3293186 Sep 21 16:24 sysbench

3. Preparation before OLTP test
Initialize the test library environment (10 test tables in total, 100000 records per table, populated with randomly generated data) :

cd /tmp/sysbench-0.4.12-1.1/sysbench
mysqladmin create sbtest

./sysbench --mysql-host=1.2.3.4 --mysql-port=3317 --mysql-user=tpcc --mysql-password=tpcc \
 --test=tests/db/oltp.lua --oltp_tables_count=10 --oltp-table-size=100000 --rand-init=on prepare

Explanation of these parameters:

-- test = tests/db/oltp. lua said call tests/db/oltp lua oltp mode test script
--oltp_tables_count=10 means that 10 test tables will be generated
-- ES86en-ES87en-ES88en =100000 means that the data volume of each test table is 100000
-- ES90en-ES91en =on means that each test table is populated with random data

If you are on this machine, you can also use the mysql-ES96en file named socket to connect. The duration of loading test data depends on the amount of data. If the process takes a long time, you need to wait patiently.

In a real test scenario, no fewer than 10 tables are recommended and no less than 5 million rows of data per table, depending on the server hardware configuration, of course. For high IOPS devices such as SSD or PCIE SSD, a minimum of 100 million rows of data per table is recommended.

4. Test for OLTP

On the basis of the above initialization data parameters, add 1 more parameters, and the test can start:

./sysbench --mysql-host=1.2.3.4. --mysql-port=3306 --mysql-user=tpcc \
--mysql-password=tpcc --test=tests/db/oltp.lua --oltp_tables_count=10 \
--oltp-table-size=10000000 --num-threads=8 --oltp-read-only=off \
--report-interval=10 --rand-type=uniform --max-time=3600 \
 --max-requests=0 --percentile=99 run >> ./log/sysbench_oltpX_8_20140921.log

A couple of choices to explain a little bit

-- ES114en-ES115en =8 means to initiate 8 concurrent connections
-- ES117en-ES118en-ES119en =off indicates that you do not want to do a read-only test, that is, you can use a read-write mixed mode test
--report-interval=10 means to output a test progress report every 10 seconds
-- ES125en-ES126en =uniform means that the random type is fixed, and several other random modes are optional: uniform(fixed),gaussian(Gaussian),special(specific),pareto(Pareto)
-- ES133en-ES134en =120 means that the maximum execution time is 120 seconds
-- ES136en-ES137en =0 means 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; You can also set the total number of requests without setting the maximum execution time
--percentile=99 means to set the sampling ratio, the default is 95%, that is, to discard 1% of the long request, and take the maximum value in the remaining 99%

That is, OLTP test was performed on 10 tables in simulation, with 10 million rows recorded in each table, and the continuous pressure test time was 1 hour.

In real test scenarios, it is recommended that the duration of continuous pressure measurement should not be less than 30 minutes, otherwise the test data may not be of reference significance.

5. Interpretation of test results:

The test results are interpreted as follows:

sysbench 0.5:  multi-threaded system evaluation benchmark
Running the test with following options:

Number of threads: 8

Report intermediate results every 10 second(s)

Random number generator seed is 0 and will be ignored


Threads started!

--  every 10 Second report 1 The test results, tps , reading/writing/second, 99% The above response time statistics 

[  10s] threads: 8, tps: 1111.51, reads/s: 15568.42, writes/s: 4446.13, response time: 9.95ms (99%)

[  20s] threads: 8, tps: 1121.90, reads/s: 15709.62, writes/s: 4487.80, response time: 9.78ms (99%)

[  30s] threads: 8, tps: 1120.00, reads/s: 15679.10, writes/s: 4480.20, response time: 9.84ms (99%)

[  40s] threads: 8, tps: 1114.20, reads/s: 15599.39, writes/s: 4456.30, response time: 9.90ms (99%)

[  50s] threads: 8, tps: 1114.00, reads/s: 15593.60, writes/s: 4456.70, response time: 9.84ms (99%)

[  60s] threads: 8, tps: 1119.30, reads/s: 15671.60, writes/s: 4476.50, response time: 9.99ms (99%)

OLTP test statistics:

    queries performed:

        read:                            938224    --  The total number of read 

        write:                           268064    --  Write the total number of 

        other:                           134032    --  Other operation total (SELECT , INSERT , UPDATE , DELETE Other operations, for example COMMIT Etc. )

        total:                           1340320    --  The total number of all 

    transactions:                        67016  (1116.83 per sec.)    --  The total number of transactions ( Transactions per second )

    deadlocks:                           0      (0.00 per sec.)    --  Total number of deadlocks occurring 

    read/write requests:                 1206288 (20103.01 per sec.)    --  Read and write the total number of ( Reads and writes per second )

    other operations:                    134032 (2233.67 per sec.)    --  Other operation total ( Other operations per second )
General statistics:    -- 1 Some statistical results 

    total time:                          60.0053s    --  The total time consuming 

    total number of events:              67016    --  How many transactions occur 

    total time taken by event execution: 479.8171s    --  Add up all transaction times ( The parallelism factor is not considered )

    response time:    --  Response time statistics 

         min:                                  4.27ms    --  The minimum time consuming 

         avg:                                  7.16ms    --  The average time 

         max:                                 13.80ms    --  The longest time-consuming 

         approx.  99 percentile:               9.88ms    --  More than 99% The average time 
Threads fairness:

    events (avg/stddev):           8377.0000/44.33

    execution time (avg/stddev):   59.9771/0.00