Using Sysbench for MySQL stress testing

Installation pressure test tool: Sysbench

1> Install dependent Environment Pack:
Yum Install-y BZR
Yum Install-y Libtool
To download the main package:
BZR branch Lp:sysbench//download to current directory, file name: Sysbench
To start the installation:
CD Sysbench
./autogen.sh
./configure--with-mysql-includes=/usr/local/mysql/include--with-mysql-libs=/usr/local/mysql/lib
Make
Make install
Copy execution file to environment change below:
CP bin/sysbench/usr/local/bin/

Query help command:
[Email protected] net]# Sysbench--help

Data Test parameter Description:

Test Tool: Sysbench 0.5
Test flow: The performance of each specification of RDS instance is obtained by means of pressure measurement of each specification instance
Test model: Using an OLTP model (read-write ratio of 7:3) and InnoDB engine. The general order is as follows:

Common Command parameters:
/usr/local/sysbench-0.5/bin/sysbench
--mysql-host=test.mysql.rds.aliyuncs.com #数据库host
--mysql-port=3306 #数据库端口
--mysql-user=your_username #数据库用户名
--mysql-password=your_password #数据库密码
--mysql-db=your_db_for_test #数据库名
--oltp-tables-count=10 #模拟的表的个数, the higher the size, the greater the value
--oltp-table-size=6000000 #模拟的每张表的行数, the higher the size, the greater the value
--num-threads=50 #模拟的并发数量, the higher the size, the greater the value
--max-requests=100000000 #最大请求次数
--max-time=20 #最大测试时间 (with--max-requests as long as there is one over, then exit)
--report-interval=1 #每1秒打印一次当前的QPS等值
--test=/tmp/sysbench-0.5/sysbench/tests/db/oltp.lua #选用的测试脚本 (LUA), this script can be found from the sysbench-0.5 source code file directory
[Prepare | run | cleanup] #prepare准备数据, run executes the test, cleanup cleans up the data

--oltp_table_count=1: Specify the number of tables during the test, 0.5 new, 0.4 the entire test process has only one table.
--oltp-table-size=: Specifies the size of the table, and if 1000 is specified, it initializes 1000 data to the list
--rand-init=on: Whether to initialize the data randomly, if not randomization then the initial good data each row content in addition to the primary key is different outside the identical.
--num-threads=: The number of concurrent threads in the test process, to see the test requirements to set the concurrency pressure.
--otlp-read-only=off: Know whether read-only testing
--REPORT-INTERVAL=10: How often to print statistics, units per second, 0.5 new

Reference page: http://help.aliyun.com/view/11108238_13440406.html

To test the initialization data:
[Email protected] sysbench]# sysbench--test=tests/db/oltp.lua--mysql-table-engine=innodb--oltp-table-size= 1000000--max-requests=5--max-time=300--num-threads=16--oltp-tables-count=10
--report-interval=10--mysql-host=localhost--mysql-port= ' 3306 '--mysql-user= ' xiong '--mysql-password= ' 123456 '-- mysql-db= ' test ' prepare

#导数据 take a long time

./sysbench–test=tests/db/oltp.lua–max-time=7200–oltp-dist-type=uniform–max-requests=0 \

–MYSQL-USER=ROOT–MYSQL-TABLE-ENGINE=INNODB–OLTP-TABLE-SIZE=5000000–OLTP-TABLES-COUNT=48 \

–oltp-range-size=40–oltp-point-selects=10–oltp-simple-ranges=1–oltp-sum-ranges=1 \

–oltp-order-ranges=1–oltp-distinct-ranges=1–oltp-index-updates=1–oltp-non-index-updates=1 \

–num-threads=200–mysql-socket=/u01/mysql/run/mysql.sock \

–oltp-read-only=on Perpare

#开始压测 mixed Read and write

./sysbench–test=tests/db/oltp.lua–max-time=7200–oltp-dist-type=uniform–max-requests=0–mysql-user=root– MYSQL-TABLE-ENGINE=INNODB–OLTP-TABLE-SIZE=5000000–OLTP-TABLES-COUNT=48 \
–oltp-range-size=40–oltp-point-selects=10–oltp-simple-ranges=1–oltp-sum-ranges=1–oltp-order-ranges=1– Oltp-distinct-ranges=1–oltp-index-updates=1–oltp-non-index-updates=1 \
–num-threads=200–mysql-socket=/u01/mysql/run/mysql.sock Run

#开始压测 Read-only

./sysbench–test=tests/db/oltp.lua–max-time=7200–oltp-dist-type=uniform–max-requests=0–mysql-user=root– MYSQL-TABLE-ENGINE=INNODB–OLTP-TABLE-SIZE=5000000–OLTP-TABLES-COUNT=48 \
–oltp-range-size=40–oltp-point-selects=10–oltp-simple-ranges=1–oltp-sum-ranges=1–oltp-order-ranges=1– Oltp-distinct-ranges=1–oltp-index-updates=1–oltp-non-index-updates=1 \
–num-threads=200–mysql-socket=/u01/mysql/run/mysql.sock–oltp-read-only=on Run

Operating system parameters Test:

CPU Performance Test
According to the official website, the CPU test uses a 64-bit integer to test the time required to calculate the prime number up to a maximum value.
[Email protected] net]# sysbench--test=cpu--cpu-max-prime=20000 Run
Sysbench 0.5:multi-threaded System Evaluation Benchmark

Running the test with following options:
Number of Threads:1
Random number generator seed is 0 and would be ignored

Primer numbers limit:20000
Threads started!

General Statistics:
Total time:34.3813s
Total number of events:10000
Total time taken by event execution:34.3759s
Response Time:
Min:3.40ms
Avg:3.44ms
Max:8.00ms
Approx. Percentile:3.45ms

Threads Fairness:
Events (Avg/stddev): 10000.0000/0.00
Execution Time (Avg/stddev): 34.3759/0.00

Threading (thread) test
Test the performance of the thread scheduler. It is useful to test the behavior of the thread scheduler under high load conditions:
[Email protected] net]# sysbench--test=threads--num-threads=64 Run
Sysbench 0.5:multi-threaded System Evaluation Benchmark

Running the test with following options:
Number of threads:64
Random number generator seed is 0 and would be ignored

Threads started!

General Statistics:
Total time:2.3363s
Total number of events:10000
Total time taken by event execution:148.9687s
Response Time:
Min:0.25ms
Avg:14.90ms
Max:201.12ms
Approx. Percentile:67.48ms

Threads Fairness:
Events (Avg/stddev): 156.2500/16.95
Execution Time (Avg/stddev): 2.3276/0.00

Mutex (mutex)
Test the performance of a mutex by simulating that all threads run concurrently at the same time and are briefly requesting a mutex
[Email protected] net]# sysbench--test=mutex--num-threads=16--mutex-num=2048--mutex-locks=1000000--mutex-loops= The Run
Sysbench 0.5:multi-threaded System Evaluation Benchmark

Running the test with following options:
Number of Threads:16
Random number generator seed is 0 and would be ignored

Threads started!
General Statistics:
Total time:7.7748s
Total number of events:16
Total time taken by event execution:123.2084s
Response Time:
Min:7550.27ms
Avg:7700.52ms
Max:7774.71ms
Approx. Percentile:7773.72ms

Threads Fairness:
Events (Avg/stddev): 1.0000/0.00
Execution Time (Avg/stddev): 7.7005/0.07

Memory test tests the continuous read and write performance of memory
The above statement specifies the amount of 2G of data transferred during the entire test, and the size of each block is 8K (uppercase K). The test results are as follows, we are most concerned about throughput (8030.45MB/SEC), and the subsequent disk IO test results show that the continuous read and write memory is more than 10 times times faster than the disk
[Email protected] net]# sysbench--test=memory--memory-block-size=8k--memory-total-size=2g--num-threads=16 Run
Sysbench 0.5:multi-threaded System Evaluation Benchmark

Running the test with following options:
Number of Threads:16
Random number generator seed is 0 and would be ignored

Threads started!
Operations performed:262144 (695055.19 ops/sec)

2048.00 MB Transferred (5430.12 mb/sec)

General Statistics:
Total time:0.3772s
Total number of events:262144
Total time taken by event execution:3.2142s
Response Time:
Min:0.00ms
Avg:0.01ms
Max:2.28ms
Approx. Percentile:0.05ms

Threads Fairness:
Events (Avg/stddev): 16384.0000/578.01
Execution Time (Avg/stddev): 0.2009/0.00

File IO Benchmark test
The file IO (FILEIO) benchmark can test the performance of the system under different IO loads. This is useful for comparing different hard drives, different RAID cards, and different raid modes. The IO subsystem can be adjusted based on the test results. The file IO benchmark simulates many of the InnoDB IO features.
The first step in testing is to prepare the (Prepare) phase, generate the data files used for the test, and generate a data file that is at least larger than the memory. If the data in the file can be completely put into memory, the operating system caches most of the data, causing the test results to fail to reflect IO-intensive workloads. First create a DataSet with the following command:
Sysbench--test=fileio--file-total-size=40g Prepare
This command creates a test file under the current working directory, and the subsequent run phase is tested by reading and writing the files. The second step is the run phase, which has different test options for different IO types:
SEQWR Sequential writes
SEQREWR Sequential Overrides
Seqrd Sequential Reads
RNDRD Random Read
Rndwr Random Write
RNDRW mixed Random Read/write
The following command runs the file I/O hybrid random Read/write benchmark:
Sysbench--test=fileio--file-total-size=40g--file-test-mode=rndrw\
--init-rng=on--max-time=300--max-requests=0 Run
The difference between simple and--oltp-read-only
Both the simple mode and the open read-only option in complex mode contain only SELECT statements. But the simple mode contains only the simplest SELECT statements, and conversely, in complex mode, if we turn on the read-only option, which is--oltp-read-only=on, it will contain complex SQL statements. Such as:
SELECT SUM (K) from Sbtest WHERE ID between N and M
SELECT DISTINCT C from sbtest WHERE ID between N and M ORDER by C
Test your own storage engine
Testing your own storage engine needs to tell Sysbench whether the storage engine supports transactions.
As shown below:
Get ready
Sysbench--TEST=OLTP--mysql-table-engine=tnt--mysql-engine-trx=yes \
--oltp-table-size=100000--mysql-user=root--mysql-db=test \
--mysql-socket=/data/ntse/lmx/sysbench/var/mysqld.sock \
Prepare
Test
Sysbench--TEST=OLTP--mysql-table-engine=tnt--mysql-engine-trx=yes \
--oltp-table-size=100000--mysql-user=root--mysql-db=test \
--mysql-socket=/data/ntse/lmx/sysbench/var/mysqld.sock \
--oltp-test-mode=complex--num-threads=16--max-time=720 \
--max-requests=0 Run
Clear
Sysbench--TEST=OLTP--mysql-table-engine=tnt--mysql-engine-trx=yes \
--oltp-table-size=100000--mysql-user=root--mysql-db=test \
--mysql-socket=/data/ntse/lmx/sysbench/var/mysqld.sock \
Clean
4. Sysbench 0.5

This article is from the "Spin Wood Technology Blog" blog, please be sure to keep this source http://mrxiong.blog.51cto.com/287318/1569651

Using Sysbench for MySQL stress testing