Mysqlslap a MySQL database stress test Tool

In XEN/KVM virtualization, CPU, memory, and network I/O are generally more efficient in virtualization, and disk I/O virtualization is inefficient, so the disk can be a bottleneck. In general, the database has high disk I/O requirements, and can measure the performance of running MySQL, Oracle, and other database services on the client (although I know that it is also more efficient to run the database server in the client). This article does not describe the performance of MySQL in virtual machines (which may be considered in the future), but rather introduces a MySQL database stress test tool,--msqlslap.

Mysqlslap is the official stress testing tool from the beginning of MySQL version 5.1.4. stress testing is performed by simulating multiple concurrent clients accessing MySQL concurrently, providing a detailed performance report of SQL execution data, and a good comparison of performance differences between multiple storage engines (MYISAM,INNODB, etc.) under the same concurrency pressure in the same environment.
Mysqlslap Official Introduction: http://dev.mysql.com/doc/refman/5.6/en/mysqlslap.html

The MYSQLSLAP operation has the following 3 steps:
1. Create schema, table, test data, etc., using a single connection (in MySQL, the schema is database);
2. Run the load test, you can use multiple concurrent client connections;
3. Test environment Cleanup (delete created data, tables, etc.), using a single connection.

The syntax for MYSQLSLAP is as follows:
Shell> Mysqlslap [Options]

mysqlslap Common parameters [options] in detail (with the online document modification, and personal summary, original document, or refer to MySQL official instructions for Mysqlslap),:

--host=host_name,-H HOST_NAME host name (or IP address) of the MySQL server to which the default is native Localhost--user=user_name,-u user_name The user name--password[=password] that was used to connect to the MySQL service,-p[password] The password used to connect to the MySQL service--create-schema the name of the custom test library, the schema of the test, The schema in MySQL is database. (You may encounter an error when you do not specify which database to use Mysqlslap:error when connecting to server:1049 Unknown database ' Mysqlslap ')--query=name,-q Use a custom script to perform tests, which can be SQL strings or scripts, for example, you can call a custom stored procedure or SQL statement to perform the test. --create the SQL (which can be SQL string or script) required to create the table--concurrency=n, the-c N represents the concurrency, that is, how many clients execute query 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 (performs 100, 200, 500 concurrency respectively). --iterations=n,-I N tests the number of iterations performed, representing how many times to run the tests individually in different concurrency environments, and running multiple times to make the results more accurate. --number-of-queries=n total number of test queries (number of concurrent customers x per customer query)--engine=engine_name,-e engine_name represents the engine to be tested, can have multiple, separated by delimiters. For example:--engines=myisam,innodb,memory. --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. --auto-generate-sql-load-type=type the type of the test statement. Represents whether the environment to be tested is a read or write operation or a mixture of the two. The values include: Read (scan tables), write (insert into tables), key (read primary keys), update (updATE primary keys), or mixed (half inserts, half scanning selects). The default value is: Mixed.--auto-generate-sql-add-auto-increment represents the automatic addition of auto_increment columns to the generated table, starting with the 5.1.18 release. --number-char-cols=n,-x n automatically generated test table contains the number of character types of the column, default 1--number-int-cols=n, Y-n automatically generated test table contains how many number types of columns, default 1--commint=n The number of DML submits once. --compress,-c compressed information is passed if both the server and client support are compressed. --only-print only prints test statements and does not actually execute. The--detach=n executes the N statement and disconnects the re-connection. --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. You can use –only-print to print the actual test process, leaving no traces in the database after the entire test is complete.

Some test parameter instances (-p followed by the password of the MySQL user root):

Single thread testing. What the test did. # mysqlslap-a-uroot-p123456 multithreaded test. 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 (*) F Rom City "-uroot-p123456# mysqlslap-a-e innodb-uroot-p123456# mysqlslap-a--number-of-queries=10-uroot-p123456 test at the same time Comparison of performance of different storage engines: # mysqlslap-a--concurrency=50,100--number-of-queries--iterations=5--engine=myisam,innodb-- Debug-info-uroot-p123456 performs a test, respectively 50 and 100 concurrent, executes 1000 times total query: # mysqlslap-a--concurrency=50,100--number-of-queries 1000- -DEBUG-INFO-UROOT-P12345650 and 100 concurrently get a test result (Benchmark), the more concurrent the number, the longer it takes to execute 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 

50 and 100 concurrently get a test result (Benchmark), the more concurrent the number, the longer it takes to execute all queries.
For the sake of accuracy, multiple iterations can be tested several times:
# mysqlslap-a--concurrency=50,100--number-of-queries--iterations=5 \
--debug-info-uroot-p123456

Mysqlslap a MySQL database stress test Tool