MySQL Stress test Tool

MySQL Stress test Tool

Since mysql5.1 comes with a tool for stress testing mysqlslap

Mysqlslap can generate test scripts automatically, or you can use test scripts provided by third parties.

Mysqlslap indicators that can be tested

(1). Maximum concurrency of the system

(2). Performance comparisons between different storage engines

(3). Performance comparison of the system when the same variable is set to a different value

(4). Performance comparisons between different versions

The specific use of mysqlslap can be viewed directly by the help Mysqlslap--help

The commonly used parameters are:

--concurrency represents the number of concurrent, multiple can be separated by commas, of course, you can also be separated by their own separators, this time to use the--delimiter switch.

--engines represents the engine to be tested, can have multiple, separated by delimiters.

--iterations represents how many times to run these tests.

The--auto-generate-sql represents a SQL script generated by the system itself.

Whether the--auto-generate-sql-load-type represents a read or write or a mixture of the two (read,write,update,mixed)

--number-of-queries represents the total number of queries to run. The number of queries that each client runs can be calculated using the total number of queries/concurrency. such as the penultimate result 2=200/100.

The--debug-info represents the additional output of CPU and memory related information.

There are several properties for the integer type in the--number-int-cols representation example table.

--number-char-cols meaning ibid.

--create-schema represents its own defined pattern (in MySQL, which is the library).

The--query represents its own SQL script.

--only-print If you just want to print and see what the SQL statement is, you can use this option.

Use Mysqlslap to bring your own script stress test:

[Email protected] ~]# mysqlslap--defaults-file=/usr/my.cnf--concurrency=100,200--iterations=1--number-int-cols=4 --number-char-cols=35--auto-generate-sql--auto-generate-sql-add-autoincrement--auto-generate-sql-load-type= Mixed--engine=innodb,myisam--number-of-queries=10000--debug-info-uroot-p

Enter Password:

Benchmark

Running for Engine InnoDB

Average number of seconds to run all queries:17.092 seconds

Minimum number of seconds to run all queries:17.092 seconds

Maximum number of seconds to run all queries:17.092 seconds

Number of clients running queries:100

Average number of queries per client:100

Benchmark

Running for Engine InnoDB

Average number of seconds to run all queries:17.373 seconds

Minimum number of seconds to run all queries:17.373 seconds

Maximum number of seconds to run all queries:17.373 seconds

Number of clients running queries:200

Average number of queries per client:50

Benchmark

Running for Engine MyISAM

Average number of seconds to run all queries:11.350 seconds

Minimum number of seconds to run all queries:11.350 seconds

Maximum number of seconds to run all queries:11.350 seconds

Number of clients running queries:100

Average number of queries per client:100

Benchmark

Running for Engine MyISAM

Average number of seconds to run all queries:13.761 seconds

Minimum number of seconds to run all queries:13.761 seconds

Maximum number of seconds to run all queries:13.761 seconds

Number of clients running queries:200

Average number of queries per client:50

User time 0.54, System time 0.57

Maximum resident set Size 10316, Integral resident set size 0

Non-physical pagefaults 6279, physical pagefaults 9, Swaps 0

Blocks in 1776 off 0, Messages in 0 out 0, signals 0

Voluntary context Switches 74086, involuntary context switches 42

Output Explanation:

Benchmark

Running for Engine MyISAM

Average number of seconds to run all queries:13.761 seconds

Minimum number of seconds to run all queries:13.761 seconds

Maximum number of seconds to run all queries:13.761 seconds

Number of clients running queries:200

Average number of queries per client:50

10,000 requests, 200 threads execute concurrently, and each thread completes an average of 50 requests.

Using third-party scripting tests

[Email protected] ~]# mysqlslap--defaults-file=/usr/my.cnf--create-schema= ' test '--concurrency=50,100--iterations =1--query= ' select * from Test.temp01 limit '--number-of-queries=5000--debug-info-uroot-p

Enter Password:

Benchmark

Average number of seconds to run all queries:6.133 seconds

Minimum number of seconds to run all queries:6.133 seconds

Maximum number of seconds to run all queries:6.133 seconds

Number of clients running QUERIES:50

Average number of queries per client:100

Benchmark

Average number of seconds to run all queries:6.238 seconds

Minimum number of seconds to run all queries:6.238 seconds

Maximum number of seconds to run all queries:6.238 seconds

Number of clients running queries:100

Average number of queries per client:50

User time 1.53, System time 1.46

Maximum resident set Size 37512, Integral resident set size 0

Non-physical pagefaults 141862, physical pagefaults 0, Swaps 0

Blocks in 0 off 0, Messages in 0 out 0, signals 0

Voluntary context Switches 504977, involuntary context switches 43

This article is from the "SQL Server MySQL" blog, so be sure to keep this source http://dwchaoyue.blog.51cto.com/2826417/1588255

MySQL Stress test Tool