Use sysbench to test MySQL Performance

This article describes how to use sysbench to test MySQL performance, including a series of operations from sysbench compilation and installation to initializing the test database environment, for more information, see sysbench. it is a modular, cross-platform, and multi-threaded benchmark tool used to evaluate the database load of different system parameters.
Currently sysbench code hosted on launchpad, Project address: https://launchpad.net/sysbench (the original official http://sysbench.sourceforge.net has been unavailable), the source code uses bazaar management.

1. download the source code package
Install the epel package to install the bzr client:

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

Then you can start to install the bzr client:

yum install bzr

Then you can use the bzr client to download the tpcc-mysql source code.

cd /tmpbzr branch lp:sysbench

MySQL Chinese network is convenient:

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

Sysbench supports the following test modes:

1. CPU performance
2. disk I/O performance
3. Scheduler Performance
4. memory allocation and transmission speed
5. POSIX thread performance
6. database performance (OLTP benchmark test)
Currently, sysbench mainly supports mysql, drizzle, pgsql, oracle, and other databases.

II. compilation and installation
The compilation process is very simple. for details, refer to the README document. 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 does not report an error, the binary command line tool sysbenchls-l sysbench-rwxr-xr-x 1 root 3293186 Sep 21 sysbench will be generated in the sysbench directory.

3. Prepare for OLTP test
Initialize the test database environment (a total of 10 Test tables, with 100000 records in each table, filling in randomly generated data ):

cd /tmp/sysbench-0.4.12-1.1/sysbenchmysqladmin 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

Explanations of these parameters:

-- Test = tests/db/oltp. lua indicates that the tests/db/oltp. lua script is called to test the oltp mode.
-- Oltp_tables_count = 10 indicates that 10 Test tables will be generated.
-- Oltp-table-size = 100000 indicates that the amount of data filled in each Test table is 100000
-- Rand-init = on indicates that each test table is filled with random data.

If it is on the local machine, you can also use-mysql-socket to specify the socket file to connect. The duration of loading test data depends on the amount of data. if the process is long, you need to wait patiently.

In a real test scenario, it is recommended that there be no less than 10 data tables and no less than 5 million rows of data in a single table, depending on the server hardware configuration. If you are equipped with a high IOPS device such as SSD or pcie ssd, we recommend that you have at least 0.1 billion rows of data in a single table.

4. perform OLTP testing

On the basis of the above initialization data parameters, add some parameters to start the test:

./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

Several options are explained a little.

-- Num-threads = 8 indicates that eight concurrent connections are initiated.
-- Oltp-read-only = off indicates that the read-only test is not performed, that is, the read/write mixed mode test is used.
-- Report-interval = 10 indicates that a test progress report is output every 10 seconds.
-- Rand-type = uniform indicates that the random type is fixed mode. the other several optional random modes are uniform (fixed), gaussian (gaussian), special (specific), and pareto ()
-- Max-time = 120 indicates the maximum execution duration is 120 seconds.
-- Max-requests = 0 indicates that the total number of requests is 0. because the total execution duration has been defined above, the total number of requests can be set to 0. you can also set only the total number of requests, do not set the maximum execution duration
-- Percentile = 99 indicates that the sampling ratio is set. the default value is 95%, that is, to discard a 1% long request, and take the maximum value in the remaining 99%.

That is, the concurrent OLTP test is simulated for 10 tables. Each table has 10 million rows of records and the stress test duration is 1 hour.

In a real test scenario, it is recommended that the duration of the stress test be no less than 30 minutes. Otherwise, the test data may not be of reference significance.

5. test result explanation:

The test results are described as follows:

The code is 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!
-- Test results are reported every 10 seconds. tps, read per second, write per second, response time of more than 99%
[10 s] threads: 8, tps: 1111.51, reads/s: 15568.42, writes/s: 4446.13, response time: 9.95 ms (99%)
[20 s] threads: 8, tps: 1121.90, reads/s: 15709.62, writes/s: 4487.80, response time: 9.78 ms (99%)
[30 s] threads: 8, tps: 1120.00, reads/s: 15679.10, writes/s: 4480.20, response time: 9.84 ms (99%)
[40 s] threads: 8, tps: 1114.20, reads/s: 15599.39, writes/s: 4456.30, response time: 9.90 ms (99%)
[50 s] threads: 8, tps: 1114.00, reads/s: 15593.60, writes/s: 4456.70, response time: 9.84 ms (99%)
[60 s] threads: 8, tps: 1119.30, reads/s: 15671.60, writes/s: 4476.50, response time: 9.99 ms (99%)
OLTP test statistics:
Queries saved Med:
Read: 938224 -- Total number of reads
Write: 268064 -- Total number of writes
Other: 134032 -- Total number of other operations (operations other than SELECT, INSERT, UPDATE, and DELETE, such as COMMIT)
Total: 1340320 -- total number of all
Transactions: 67016 (1116.83 per sec.) -- Total number of transactions (transactions per second)
Deadlocks: 0 (0.00 per sec.) -- Total number of deadlocks
Read/write requests: 1206288 (20103.01 per sec.) -- Total read/write count (read/write times per second)
Other operations: 134032 (2233.67 per sec.) -- Total number of other operations (other operations per second)

General statistics: -- some statistical results
Total time: 60.0053 s -- total time consumed
Total number of events: 67016 -- total number of transactions
Total time taken by event execution: 479.8171 s -- Sum of time consumed by all transactions (parallel factors not considered)
Response time: -- response time statistics
Min: 4.27 ms-minimum time consumed
Avg: 7.16 ms-average time consumption
Max: 13.80 ms -- maximum time consumed
Approx. 99 percentile: 9.88 ms-over 99% average time consumption

Threads fairness:
Events (avg/stddev): 8377.0000/44.33
Execution time (avg/stddev): 59.9771/0.00