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

Example 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 in total
#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 seconds100 clients (concurrency) running these SQL statements at the same time takes an average of 0.351 seconds
Minimum number of seconds to run all queries:0.351 seconds
Maximum number of seconds to run all queries:0.351 seconds
Number of clients running queries:100a total of 100 clients (concurrency) run these SQL queries
Average Number of queriesper client: each client (concurrency) runs 50 queries on average (corresponding to--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