MySQL Pressure performance Test (MYSQLSLAP) tool

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 for "High load attack MySQL". And can be a good comparison of multiple storage engine in the same environment of concurrent pressure performance differences.

Its use syntax is as follows:
Shell>/usr/local/mysql/bin/mysqlslap [Options]

Commonly used parameters "options" Detailed introduction:
--concurrency represents concurrent quantities, and multiple can be separated by commas. For example:--concurrency=50,200,500
--engines represents the engine to be tested, can have multiple, separated by delimiters. For example:--engines=myisam,innodb,memory
The--iterations represents how many times each test is to be run in a different concurrency environment.
--auto-generate-sql represents the SQL script that was generated with the Mysqlslap tool to test concurrent pressure.
--auto-generate-sql-add-auto-increment represents the automatic addition of auto_increment columns to the generated table, starting with the 5.1.18 Version,
--auto-generate-sql-load-type represents whether the environment to test is read or write or mixed (read,write,update,mixed)
--number-of-queries represents the total number of queries to run.
The--debug-info represents information about the extra CPU and memory to be exported.
There are several instances of the integer type in the--number-int-cols representation table.
There are several properties of the Vachar type in the--number-char-cols example table.
--create-schema represents a custom test library name.
--query represents a custom test SQL script.

Description

The process of testing needs to generate a test table, insert test data, this mysqlslap can be automatically generated, default to generate a Mysqlslap schema, if already exist, delete first. You can use-only-print to print the actual test process, and the entire test will not leave traces in the database after completion.

Experiment steps:

Practice One:
Single-thread testing. What the test did.
>./bin/mysqlslap-a-uroot-p111111
Multithreading test. Use--concurrency to simulate concurrent connections.
>/bin/mysqlslap-a-C 100-uroot-p111111
Iterative testing. For averages that require multiple tests to be performed.
>/bin/mysqlslap-a-I 10-uroot-p111111

Exercise two:
>/bin/mysqlslap-auto-generate-sql-add-autoincrement-a-uroot-p111111
>/bin/mysqlslap-a-auto-generate-sql-load-type=read-uroot-p111111
>/bin/mysqlslap-a-auto-generate-secondary-indexes=3-uroot-p111111
>/bin/mysqlslap-a-auto-generate-sql-write-number=1000-uroot-p111111

>/bin/mysqlslap--create-schema world-q "SELECT COUNT (*) from City"-uroot-p111111
>/bin/mysqlslap-a-E innodb-uroot-p111111
>/bin/mysqlslap-a--number-of-queries=10-uroot-p111111

Exercise Three:
Perform a test, 50 and 100 concurrent, and perform a 1000-time total query:
>/bin/mysqlslap-a--concurrency=50,100--number-of-queries 1000--debug-info-uroot-p111111

50 and 100 concurrent results of a test result (Benchmark), the more the number of concurrent, the longer the execution of all queries. For accuracy, multiple iterations can be tested several times:
>/bin/mysqlslap-a--concurrency=50,100--number-of-queries 1000--iterations=5--debug-info-uroot-p111111

Test performance comparisons for different storage engines:
>./bin/mysqlslap-a--concurrency=50,100--number-of-queries 1000--iterations=5--engine=myisam,innodb-- debug-info-uroot-p111111