Mysqlslap use of MySQL performance testing tools

Mysqlslap is MySQL's own benchmark tool, the advantages: query data, simple syntax, flexible and easy to use. The tool can simulate multiple clients concurrently issuing query updates to the server. The performance test data is given and the performance comparison of various engines is provided. MSQLSLAP provides an intuitive verification basis before and after MySQL performance optimization, and recommends that system operations and DBA personnel should have some common stress testing tools Can accurately grasp the online database support user traffic limit and its anti-pressure problems.

Common options
--concurrency number of concurrent, multiple can be separated by commas
--engines to test the engine, can have multiple, separated by delimiters, such as--engines=myisam,innodb
--iterations how many times to run these tests
--auto-generate-sql test with the SQL script generated by the system itself
--auto-generate-sql-load-type to test whether to read or write or to mix the two (read,write,update,mixed)
--number-of-queries how many queries to run in total. The number of queries that each client runs can be calculated using the total number of queries/concurrency
--debug-info additional output CPU and memory related information
--number-int-cols the number of int fields to create a test table
--number-char-cols number of chat fields to create a test table
--create-schema Testing the database
--query your own SQL script to perform the test
--only-print If you just want to print and see what the SQL statement is, you can use this option

Various test parameter instances (-P followed by the root password of MySQL):

Single thread testing. What the test did.
# mysqlslap -a -uroot -p123456
Multithreaded testing. Use –concurrency to simulate concurrent connections.
# mysqlslap -a -c 100 -uroot -p123456
Iterative testing. Used to perform tests that require multiple executions to get an average.
# mysqlslap -a -i 10 -uroot -p123456

# mysqlslap ---auto-generate-sql-add-autoincrement -a -uroot -p123456
# mysqlslap -a --auto-generate-sql-load-type=read -uroot -p123456
# mysqlslap -a --auto-generate-secondary-indexes=3 -uroot -p123456
# mysqlslap -a --auto-generate-sql-write-number=1000 -uroot -p123456
# mysqlslap --create-schema world -q "select count(*) from City" -uroot -p123456
# mysqlslap -a -e innodb -uroot -p123456
# mysqlslap -a --number-of-queries=10 -uroot -p123456


Test the performance of the different storage engines at the same time:
# mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --engine=myisam,innodb --debug-info -uroot -p123456

Perform a test, respectively, 50 and 100 concurrently, and perform 1000 total queries:
# mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --debug-info -uroot -p123456

50 and 100 concurrently get a test result (Benchmark), the more concurrent the number, the longer it takes to complete all queries. For the sake of accuracy, multiple iterations can be tested several times:
# mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --debug-info -uroot -p123456

Instance 1
Description: Test 100 concurrent threads, test 1 times, automatically generate SQL test script, read, write, update Hybrid test, self-growth field, test engine for InnoDB, run 5,000 queries
#mysqlslap-h127.0.0.1-uroot-p123456789--concurrency=100--iterations=1--auto-generate-sql-- Auto-generate-sql-load-type=mixed--auto-generate-sql-add-autoincrement--engine=innodb--number-of-queries=5000
Benchmark
Running for engine InnoDB
Average number of seconds to run all queries:0.351 seconds 100 It takes 0.351 seconds for the client (concurrency) to run these SQL statements on average
Minimum number of seconds to run all queries:0.351 seconds
Maximum number of seconds t o Run all queries:0.351 seconds
Number of clients running queries:100   Total 100 clients (concurrency) run these SQL queries
Ave Rage number of Queries per client:50 each client (concurrency) runs 50 queries on average (corresponding--concurrency=100,--number-of-queries=5000;5000/ 100=50)

Example 2
#mysqlslap-h127.0.0.1-uroot-p123456789--concurrency=100,500,1000--iterations=1--auto-generate-sql-- Auto-generate-sql-load-type=mixed--auto-generate-sql-add-autoincrement--engine=innodb--number-of-queries=5000- -debug-info
Benchmark
Running for Engine InnoDB
Average number of seconds to run all queries:0.328 seconds
Minimum number of seconds to run all queries:0.328 seconds
Maximum number of seconds to run all queries:0.328 seconds
Number of clients running queries:100
Average number of queries per client:50

Benchmark
Running for Engine InnoDB
Average number of seconds to run all queries:0.358 seconds
Minimum number of seconds to run all queries:0.358 seconds
Maximum number of seconds to run all queries:0.358 seconds
Number of clients running queries:500
Average number of queries per Client:10

Benchmark
Running for Engine InnoDB
Average number of seconds to run all queries:0.482 seconds
Minimum number of seconds to run all queries:0.482 seconds
Maximum number of seconds to run all queries:0.482 seconds
Number of clients running queries:1000
Average number of queries per Client:5

User time 0.21, System time 0.78
Maximum resident set Size 21520, Integral resident set size 0
Non-physical pagefaults 12332, physical pagefaults 0, Swaps 0
Blocks in 0 off 0, Messages in 0 out 0, signals 0
Voluntary context Switches 36771, involuntary context switches 1396

Example 3 (custom SQL statement)
#mysqlslap-h127.0.0.1-uroot-p123456789--concurrency=100--iterations=1--create-schema=rudao--query= ' select * FROM ServerList, '--engine=innodb--number-of-queries=5000--debug-info
Benchmark
Running for Engine InnoDB
Average number of seconds to run all queries:0.144 seconds
Minimum number of seconds to run all queries:0.144 seconds
Maximum number of seconds to run all queries:0.144 seconds
Number of clients running queries:100
Average number of queries per client:50

User time 0.05, System time 0.09
Maximum resident set Size 6132, Integral resident set size 0
Non-physical PageFaults 2078, physical pagefaults 0, Swaps 0
Blocks in 0 off 0, Messages in 0 out 0, signals 0
Voluntary context Switches 6051, involuntary context switches 90

Instance 4 (Specify SQL script)
#mysqlslap-h127.0.0.1-uroot-p123456789--concurrency=100--iterations=1--create-schema=rudao--query=/tmp/ Query.sql--engine=innodb--number-of-queries=5000--debug-info
Warning:using a password on the command line interface can is insecure.
Benchmark
Running for Engine InnoDB
Average number of seconds to run all queries:0.157 seconds
Minimum number of seconds to run all queries:0.157 seconds
Maximum number of seconds to run all queries:0.157 seconds
Number of clients running queries:100
Average number of queries per client:50

User time 0.07, System time 0.08
Maximum resident set Size 6152, Integral resident set size 0
Non-physical pagefaults 2107, physical pagefaults 0, Swaps 0
Blocks in 0 off 0, Messages in 0 out 0, signals 0
Voluntary context Switches 6076, involuntary context switches 89

Mysqlslap use of the MySQL performance test tool