Mysqlslap simple MySQL stress testing tool tutorial

Source: Internet
Author: User

MySQL starts with version 5.1.4 with a stress testing toolMysqlslapIt is very easy to use by simulating multiple concurrent clients to access mysql. You can use mysqlslap-help to obtain available options. Here are some of the main parameters. For more information, see the official manual.

Copy codeThe Code is as follows:
-Auto-generate-SQL,-
Automatically generate test tables and data

-Auto-generate-SQL-load-type = type
The type of the test statement. Optional values: read, key, write, update, and mixed (default ).

-Number-char-cols = N,-x N
The number of character columns in the automatically generated test table. The default value is 1.

-Number-int-cols = N,-y N
Number of columns in the automatically generated test table. The default value is 1.

-Number-of-queries = N
Total number of test queries (number of concurrent customers × number of queries per customer)

-Query = name,-q
Use a custom script to perform the test. For example, you can call a custom stored procedure or SQL statement to perform the test.

-Create-schema
The schema used for testing. The schema in MySQL is also the database.

-Commint = N
How many DML records are submitted once?

-Compress,-C
If both the server and client support compression, the compressed information is transmitted.

-Concurrency = N,-c N
Concurrency, that is, the number of clients that are simulated to execute the select statement at the same time. Multiple values can be specified. Use a comma or the value specified by the-delimiter parameter as the delimiter.

-Engine = engine_name,-e engine_name
The storage engine used to create a test table. You can specify multiple storage engines.

-Iterations = N,-I N
Test execution iterations

-Detach = N
Disconnect after executing N statements

-Debug-info,-T
Print memory and CPU Information

-Only-print
Print only test statements without actually executing them


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

Common Parameters [options:
-- Concurrency indicates the number of concurrent tasks. multiple concurrent tasks can be separated by commas. Example: -- concurrency = 50,200,500
-- Engines indicates the engine to be tested, which can be separated by separators. Example: -- engines = myisam, innodb, memory
-- Iterations indicates the number of times a test is run in different Concurrent Environments.
-- Auto-generate-SQL indicates that the SQL script generated by mysqlslap is used to test the concurrency pressure.
-- Auto-generate-SQL-add-auto-increment indicates that the auto_increment column is automatically added to the generated table, starting with version 5.1.18,
-- Auto-generate-SQL-load-type indicates whether the test environment is a combination of read, write, update, and mixed)
-- Number-of-queries indicates the total number of queries to be run.
-- Debug-info indicates that information about the CPU and memory needs to be output.
-- Number-int-cols indicates the number of INTEGER attributes in the example table.
-- Number-char-cols indicates the vachar type attributes in the example table.
-- Create-schema indicates the name of the custom test database.
-- Query indicates a custom test SQL script.

Note:

During the test, you need to generate a test table and insert the test data. This mysqlslap can be automatically generated. By default, a schema of mysqlslap is generated. if it already exists, delete it first. You can use-only-print to print the actual test process. After the test is completed, no trace is left in the database.


Tutorial steps:

Exercise 1:
Single-threaded testing. Test what is done.
>./Bin/mysqlslap-a-uroot-p111111
Multi-threaded testing. Use -- concurrency to simulate concurrent connections.
>./Bin/mysqlslap-a-c 100-uroot-p111111
Iterative testing. Used to obtain the average value after multiple tests are performed.
>./Bin/mysqlslap-a-I 10-uroot-p111111

Exercise 2:
>./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 3:
Execute a test, 50 and 100 concurrent queries respectively, and execute 1000 total queries:
>./Bin/mysqlslap-a -- concurrency = 50,100 -- number-of-queries 1000 -- debug-info-uroot-p111111

50 and 100 concurrency get a test result (Benchmark) respectively. The more concurrency, the longer the query execution time. For accuracy, you can perform multiple iteration tests:
>./Bin/mysqlslap-a -- concurrency = 50,100 -- number-of-queries 1000 -- iterations = 5 -- debug-info-uroot-p111111

The performance of different storage engines is compared during the test:
>./Bin/mysqlslap-a -- concurrency = 50,100 -- number-of-queries 1000 -- iterations = 5 -- engine = myisam, innodb -- debug-info-uroot-p111111

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.