Original link: http://www.2cto.com/database/201303/195303.html, Mysqlslap introduction Mysqlslap MySQL5.1 is a benchmark benchmark tool, similar to the Apache Bench load generation tool, generates schema, loads data, executes Benckmark and queries data, and is simple, flexible and 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 a custom script, such as a custom stored procedure that can be called orSQL statement 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. Third, the demo instance below we use several demo instance to carry on the 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 out 0, Messages in 0 out 0, Signal s 0Voluntary Context Switches 27221, involuntary context switches 42412, Demo2: Specify 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= --debug-info-uroot-p123abc# notes using 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 type 35 column, Test 2 engine Myisam,innodb read performance, Test the server with 50,100,200 clients for a total of 200 query statements execute 20 queries benchmark Running for engine myisam & nbsp 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 sec Onds to run all queries:4.889 seconds number of clients running queries:50 &NBSP ; Average number of queries per client:4benchmark Running for engine myisam Average number of seconds to run all queries:0.620 seconds Minimum number of sec Onds to run all queries:0.231 seconds Maximum number of seconds to run all queries:4.898 Seco nds number of clients running queries:100 Average number of queries Per client:2benchmark Running for engine myisam Average number of Seconds to run all queries:0.503 seconds Minimum number of seconds to run all queries:0.257 s econds Maximum number of seconds to run all queries:4.269 seconds number of clients run Ning queries:200 Average number of queries per client:1benchmark R Unning for engine innodb Average number of seconds to run all queries:1.049 seconds &nbs P Minimum number of seconds to run all queries:0.244 seconds Maximum number of sec Onds to run all queries:5.292 seconds number of clients running queries:50 &NBSP ; Average number of queries per client:4benchmark Running for engine innodb Average number of seconds to run all queries:0.712 seconds Minimum number of sec Onds to run all queries:0.246 seconds Maximum number of seconds to run all queries:6.585 Seco nds &NBsp Number of clients running queries:100 Average number of queries per client:2benchmark& nbsp 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 &NB Sp Maximum number of seconds to run all queries:0.328 seconds number of clients running queries: 200 Average number 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
