Instructions for use of mysqlslap pressure measuring tools

PS: Today a colleague asked me to have a more reliable MySQL stress test tool available. In fact, MySQL comes with a pressure test tool called Mysqlslap, or a good simulation. Let me give you an example. Mysqlslap is an official MySQL-provided stress testing tool starting with version 5.1.4. Perform stress testing by simulating multiple concurrent client access to MySQL, while providing detailed data performance reports on "High-load attack MySQL". And it is very good to compare the concurrency pressure performance difference of multiple storage engines in the same environment. The available options can be obtained through MYSQLSLAP–HELP, where the main parameters are listed, and the official manuals are described in more detail. If the system comes with or uses the RPM package installs the MySQL, installs the mysql-client side the package to have mysqlslap this tool.

is the number of connection processes for MySQL running mysqlslap-a-c 500-i 10-uroot-p123456 test:

Use the following syntax:
# Mysqlslap [Options]

common parameters [options] Detailed description:

--auto-generate-sql,-a automatically generates test tables and data, indicating that the concurrency pressure is tested using the SQL script generated by the Mysqlslap tool itself. The type of the
--auto-generate-sql-load-type=type test statement. Represents whether the environment to be tested is a read or write operation or a mixture of the two. The values are: Read,key,write,update and mixed (default). The
--auto-generate-sql-add-auto-increment represents the automatic addition of the Auto_increment column to the generated table, starting with version 5.1.18.
--number-char-cols=n,-x N auto-generated test table contains the number of character types of the column, the default 1
--number-int-cols=n, the y-N auto-generated test table contains how many number types of columns, the default 1
-- Number-of-queries=n total number of test queries (number of concurrent customers x per customer query)
--query=name,-q executes the test using a custom script, such as a custom stored procedure or SQL statement that can be invoked to perform the test. The
--create-schema represents a custom test library name, and the Schema,mysql schema in the test is database.
--commint=n How many DML are submitted once.
--compress,-c compressed information is passed if both server and client support are compressed.
--concurrency=n,-c N represents the concurrency, which is how many clients perform a select at the same time. You can specify more than one value, either as a delimiter or as a comma or as the value specified by the--delimiter parameter. For example:--concurrency=100,200,500.
--engine=engine_name,-e engine_name represents the engine to be tested, can have multiple, separated by delimiters. For example:--engines=myisam,innodb. The number of iterations that the
--iterations=n,-I N tests perform, represents how many times to run each test in a different concurrency environment. The
--only-print only prints test statements and does not actually execute.
--detach=n breaks the re-connection after executing N statements.
--debug-info,-t print memory and CPU related information.


Description
The test process needs to generate a test table, insert the test data, the MYSQLSLAP can be generated automatically, by default generate a Mysqlslap schema, if already exist, first delete. Can be used --only-print to print the actual test process, the entire test will not leave a trace in the database.

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

Instructions for use of mysqlslap pressure measuring tools