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

1. Tool detail Parameters

--HELP Display Help

--concurrency represents the number of concurrent, multiple can be separated by commas

--engines test engine, multiple separators separated

How many times--iterations run these tests

--auto-generate-sql the SQL script generated by the system itself test

--auto-generate-sql-load-type test whether to read or write or Mix (read,write,update,mixed)

--number-of-queries total number of queries to run

--debug-info additional output CPU and memory related information

--number-int-cols the number of int fields to create a test table

--auto-generate-sql-add-autoincrement The generated table automatically adds auto_increment columns

--number-char-cols number of char fields in the test table

--create-schema Testing the database

--query performing tests with custom scripts

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

2. Test instance (single concurrency)

[[email protected] bin]#./mysqlslap-uroot-psystem--concurrency=100--iterations=1--auto-generate-sql-- Auto-generate-sql-load-type=mixed--auto-generate-sql-add-autoincrement--engine=myisam--number-of-queries=10-- Debug-info
warning:using A password on the command line interface can be insecure.
Benchmark
 running for engine MyISAM
 average number of seconds-run all queries:0.209 seconds
  ; Minimum number of seconds to run all queries:0.209 seconds
 maximum number of seconds to run all queries:0.209 s Econds
 number of clients running queries:100
 average number of queries per client:0

User time 0.02, System time 0.09
Maximum resident set Size 4684, Integral resident set size 0
Non-physical pagefaults 1476, physical pagefaults 0, Swaps 0
Blocks in 0 off 0, Messages in 0 out 0, signals 0
Voluntary context Switches 2797, involuntary context switches 25
[Email protected] bin]#

Description: Test 100 concurrent threads, test 1 times, automatically generate SQL test script, read, write, update Hybrid test, self-growth field, test engine for MyISAM, run 10 queries, output CPU resource information

3. Test instance (multiple concurrency)

[[email protected] bin]#./mysqlslap--concurrency=50,100,200--iterations=20--number-int-cols=4-- number-char-cols=35--auto-generate-sql--auto-generate-sql-add-autoincrement--auto-generate-sql-load-type=read- -engine=myisam,innodb--number-of-queries=200--verbose--socket=/tmp/mysql.sock-uroot-psystem
Warning:using A Password on the command line interface can is insecure.
Benchmark
 running for engine MyISAM
 average number of seconds to run all queries:0.758 seconds
& nbsp Minimum number of seconds to run all queries:0.671 seconds
 maximum number of seconds to run all queries:0.870 s Econds
 number of clients running QUERIES:50
 average number of queries per Client:4

Benchmark
Running for Engine MyISAM
Average number of seconds to run all queries:0.840 seconds
Minimum number of seconds to run all queries:0.645 seconds
Maximum number of seconds to run all queries:1.117 seconds
Number of clients running queries:100
Average number of queries per Client:2

Benchmark
Running for Engine MyISAM
Average number of seconds to run all queries:0.959 seconds
Minimum number of seconds to run all queries:0.802 seconds
Maximum number of seconds to run all queries:1.192 seconds
Number of clients running queries:200
Average number of queries per client:1

Benchmark
Running for Engine InnoDB
Average number of seconds to run all queries:0.701 seconds
Minimum number of seconds to run all queries:0.632 seconds
Maximum number of seconds to run all queries:0.778 seconds
Number of clients running QUERIES:50
Average number of queries per Client:4

Benchmark
Running for Engine InnoDB
Average number of seconds to run all queries:0.754 seconds
Minimum number of seconds to run all queries:0.560 seconds
Maximum number of seconds to run all queries:0.920 seconds
Number of clients running queries:100
Average number of queries per Client:2

Benchmark
Running for Engine InnoDB
Average number of seconds to run all queries:0.860 seconds
Minimum number of seconds to run all queries:0.709 seconds
Maximum number of seconds to run all queries:1.173 seconds
Number of clients running queries:200
Average number of queries per client:1

[Email protected] bin]#

Description: Test 50,100,200 concurrency, run 20 times, storage engine MyISAM and InnoDB, run 200 queries

4. Test instance (custom SQL statement)

[Email protected] bin]#./mysqlslap--concurrency=100--iterations=1--query= ' select * from tong.t; '--number-of-querie s=10--debug-info-uroot-psystem

Benchmark
Average number of seconds to run all queries:2.211 seconds
Minimum number of seconds to run all queries:2.211 seconds
Maximum number of seconds to run all queries:2.211 seconds
Number of clients running queries:100
Average number of queries per client:0

User time 0.05, System time 0.27
Maximum resident set Size 7324, Integral resident set size 0
Non-physical pagefaults 1686, physical pagefaults, Swaps 0
Blocks in 7104 off 0, Messages in 0 out 0, signals 0
Voluntary context Switches 3449, involuntary context switches 40
[Email protected] bin]#

5. Test example (detection script)

[Email protected] bin]#/mysqlslap--create=/yourpath/test1.sql--query=/yourpath/test2.sql--concurrency= 50,100,200--iterations=20--engine=myisam,innodb-u Root-psystem

This article from "Together Through the Days" blog, declined reprint!

Mysqlslap of the MySQL performance test tool