Mysqlslap Introduction Mysqlslap is MySQL5.1 after the benchmark benchmark tool, similar to Apache bench load generation tool, generate schema, load data, execute Benckmark and query data, simple syntax, flexible , easy to use. The tool can simulate multiple clients concurrently issuing query updates to the server, giving performance test data and providing performance comparisons for multiple engines. Mysqlslap for MySQL performance optimization before and after providing an intuitive verification basis, the author suggested that the system operators should master some common pressure testing tools, so as to more accurately grasp the online system can support the user flow limit and the pressure resistance and so on. www.2cto.com Method Introduction You can use the Mysqlslap--help to show how to use: 1)--concurrency represents the number of concurrent, multiple can be separated by commas, for example: concurrency= 10,50,100, the number of concurrent connection threads is 10, 50, and 100 concurrency respectively. 2)--engines represents the engine to be tested, can have multiple, separated by separators. 3)--iterations represents how many times to run these tests. 4)--auto-generate-sql represents the SQL script generated by the system itself. 5) Whether the--auto-generate-sql-load-type represents a read or write or a mix of both (read,write,update,mixed) 6)--number-of-queries represents the total number of queries to run. The number of queries that are run per client can be calculated using the total number of queries/concurrency. 7)--debug-info represents the additional output of CPU and memory related information. 8)--number-int-cols: Number of int fields to create test table 9)--auto-generate-sql-add-autoincrement: Represents the automatic addition of auto_increment columns to the generated table. Starting from version 5.1.18)--number-char-cols The number of char fields that created the test table. One) the--create-schema test Schema,mysql in the schema is the database. --query Perform tests using custom scripts, such as a custom stored procedure or SQL statement that can be invoked to perform the test. --only-print If you just want to print a look at what the SQL statement is, you can use this option. Three, demo example below we use several demo instances to test 1, demo1:[[email protected] ~]# mysqlslap-uroot-p123abc--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# Note This test takes 100 concurrent threads, tests 1 times, automatically generates SQL test scripts, reads, writes, updates hybrid tests, self-growing fields, test engine for MyISAM, Run a total of 10 queries, the output CPU resource information return information as follows: Rement--engine=myisam--number-of-queries=10--debug-infobenchmark Running for engine myisam Average number of seconds to run all queries:0.129 SECONDS&NB Sp Minimum number of seconds to run all queries:0.107 seconds Maximum number of seconds to run all queries:0.264 seconds number of clients running queries:100 &nbs P Average number of queries per Client:0user time 0.16, System time 0.25Maximum resident set size 4624, Integ RAL resident set Size 0non-physical pagefaults 7346, physical PageFaults 0, Swaps 0Blocks in 0 off 0, Messages in 0 out 0, signals 0Voluntary context switches 27221, involuntary conte XT Switches 42412, Demo2: Specifying database and SQL statements mysqlslap-h192.168.202.84-p3309--concurrency=100--iterations=1--create-schema = ' MMS_SDMTV '--query= ' select * from role; '--number-of-queries=10--debug-info-uroot-p123abc# Note Use MYSQLSLAP to specify SQL statements for testing 3, DEMO3: Test Cases [[email protected]/]# 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=/var/lib/mysql/ mysql.sock-uroot-p123abc# System script test, add int type 4 column char 35 column, Test 2 engine Myisam,innodb read performance, 50,100,200 client to test the server for a total of 200 query statements Perform 20 queries benchmark Running for engine myisam Average number of seconds To run all queries:0.666 seconds Minimum number of seconds to run all Queries:0.223 seconds Maximum number of seconds to run all queries:4.889 seconds Number of clients running queries:50 Average number of queries per Client:4benc hmark Running for engine myisam Average number of seconds-run all queries:0.620 seconds Minimum number of seconds to run all queries:0.231 seconds   ; Maximum number of seconds to run all queries:4.898 seconds number of clients run Ning queries:100 Average number of queries per client:2benchmark R Unning for engine myisam Average number of seconds to run all queries:0.503 seconds &nbs P Minimum number of seconds to run all queries:0.257 seconds Maximum number of sec Onds to run all queries:4.269 seconds number of clients running queries:200 Average N Umber of queries per client:1benchmark Running for engine innodb AV Erage number of seconds to run all queries:1.049 seconds Minimum number of seconds to run all queries:0.244 seconds Maximum number of seconds to run all queries:5.292 seconds Number of clients running queries:50 Average number of queries per Client:4ben chmark Running for engine innodb Average number of seconds to run Al L queries:0.712 seconds Minimum number of seconds to run all queries:0.246 seconds &nbs P Maximum number of seconds to run all queries:6.585 seconds number of clients run Ning Queries:100&nbsP Average number of queries per client:2benchmark Running for engine InnoDB Average number of seconds to run all queries:0.269 seconds Minimum Number of seconds to run all queries:0.175 seconds Maximum number of seconds to run all Queri es:0.328 seconds number of clients running queries:200 Average num ber of queries per client:14, self-built SQL test Case Mysqlslap--create=/yourpath/test1.sql--query=/yourpath/test2.sql-- concurrency=50,100,200--iterations=20--engine=myisam,innodb -u root-p123abc# Create your test SQL text Test1 and Test2 in the set Yourpath directory and perform 50, 100, and 200 simulated concurrency tests
Mysqlslap Use instance of the MySQL database benchmark stress test tool
