MySQL Mysqlslap stress test case

A test Case

1 Write stress tests:

Use case: Automatically create table write 1 million data client concurrent 500 test InnoDB storage Engine

Command: Mysqlslap--auto-generate-sql-add-autoincrement-a--number-of-queries=1000000--number-char-cols=4-- number-int-cols=4--engine=innodb--debug-info--concurrency=1000-uroot-ppass-hmysql_ip-- Auto-generate-sql-load-type=write

Test results:

Benchmark

Running for Engine InnoDB

Average number of seconds to run all queries:65.679 seconds

Minimum number of seconds to run all queries:65.679 seconds

Maximum number of seconds to run all queries:65.679 seconds

Number of clients running queries:1000

Average number of queries per client:1000

User time 2.19, System time 8.72

Maximum resident set Size 55156, Integral resident set size 0

Non-physical pagefaults 9038, physical pagefaults 0, Swaps 0

Blocks in 0 off 0, Messages in 0 out 0, signals 0

Voluntary context Switches 1701179, involuntary context switches 130

2 read-only stress test:

Use case: Automatically create test table (test table will automatically write 100 data) Full table query (similar to select * from TestTable;) 1 million client concurrency 1000 test InnoDB storage Engine

Command: Mysqlslap--auto-generate-sql-add-autoincrement-a--number-char-cols=4--number-int-cols=4--engine=innodb-- Debug-info--concurrency=1000-urootchar-cols=4--number-int-cols=4--engine=innodb--debug-info--concurrency=1000- es=1000000 [[email protected] ~]# mysqlslap--auto-generate-sql-add-autoincrement-a--number-char-cols=4--numbe r-int-cols=4--engine=innodb--debug-info--concurrency=1000-uroot-ppass-hhostname--auto-generate-sql-load-type= Read--number-of-queries=1000000

Test results:

Benchmark

Running for Engine InnoDB

Average number of seconds to run all queries:472.059 seconds

Minimum number of seconds to run all queries:472.059 seconds

Maximum number of seconds to run all queries:472.059 seconds

Number of clients running queries:1000

Average number of queries per client:1000

User time 76.06, System time 79.68

Maximum resident set Size 121716, Integral resident set size 0

Non-physical pagefaults 61682, physical pagefaults 0, Swaps 0

Blocks in 0 off 0, Messages in 0 out 0, signals 0

Voluntary context Switches 15308027, involuntary context switches 82

3 read-write hybrid test:

Use case: Read-write hybrid Test 1 million statement, client concurrent 1000 test InnoDB storage engine. Select statement is a non-full table query that contains conditional expressions similar (WHERE id = ' xxx ';)

Command: Mysqlslap--auto-generate-sql-add-autoincrement-a--number-char-cols=4--number-int-cols=4--engine=innodb-- Debug-info--concurrency=1000-uroot-ppass-hmysql_ip--auto-generate-sql-load-type=mixed--number-of-queries= 1000000

Test results:

Benchmark

Running for Engine InnoDB

Average number of seconds to run all queries:45.507 seconds

Minimum number of seconds to run all queries:45.507 seconds

Maximum number of seconds to run all queries:45.507 seconds

Number of clients running queries:1000

Average number of queries per client:1000

User time 4.41, System time 7.64

Maximum resident set Size 56900, Integral resident set size 0

Non-physical pagefaults 9601, physical pagefaults 0, Swaps 0

Blocks in 0 off 0, Messages in 0 out 0, signals 0

Summary: Reading stress tests and read-write mixed pressure tests use a different SELECT statement, resulting in a large difference in results. The read stress test is a query of all records in a full table, while the SELECT statement in a read-write hybrid test uses conditional filtering. You can add the--only-print parameter to the original command to print the statements used in the actual test procedure.

B parameter Description:

Refer to self-http://www.ha97.com/5182.html to revise the--number-of-queries interpretation.

--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 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 and is supported from version 5.1.18.

--number-char-cols=n,-x N Auto-generated test table contains the number of character type columns, default 1

--number-int-cols=n,-y N auto-generated test table contains how many number types of columns, default 1

--NUMBER-OF-QUERIES=N number of SQL statements for total tests (number of concurrent customers × number of queries per customer)

--query=name,-q uses a custom script to perform tests, such as a custom stored procedure or SQL statement that can be invoked to perform the test.

--create-schema represents a custom test library name, and the Schema,mysql schema in the test is database.

--commint=n how many DML to submit once.

--compress,-c compressed information is passed if both the server and client support are compressed.

--concurrency=n,-c N represents the concurrency, which is how many clients execute 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.

--iterations=n,-I N the number of iterations of the test execution, which represents how many times to run each test in a different concurrency environment.

--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.

This article is from "I bo so in" blog, please be sure to keep this source http://encoding.blog.51cto.com/1403836/1745390

MySQL Mysqlslap stress test case