How does the MySQL stress test approach use Mysqlslap to test MySQL pressure? _mysql

In fact, the MySQL test is not so complex, in addition to some commonly used select\insert\update\deletc these, in fact, testing his concurrency is the most important. For example, in connection with the number of 1K, the concurrent volume can meet the current request \ Server performance, memory CPU usage. To be blunt, testing MySQL is testing his profile and concurrency and server performance.

One, tools
Preferred tool MySQL with: Mysqlslap

–auto-generate-sql,-A
Automatically generate test tables and data

–auto-generate-sql-load-type=type
The type of the test statement. Values include: Read,key,write,update and mixed (default).

–number-char-cols=n, X-N
The number of character type columns that are included in the automatically generated test table, default 1

–number-int-cols=n, Y-N
How many numeric types of columns are included in the automatically generated test table, default 1

–number-of-queries=n
Total number of test queries (number of concurrent clients x per customer query)

–query=name,-q
Use custom scripts to perform tests, such as a custom stored procedure or SQL statement that can be invoked to execute a test.

–create-schema
In the schema,mysql of the test, the schema is database

–commint=n
How many DML are submitted once

–compress,-C
If both the server and client support are compressed, the message is compressed

–concurrency=n, C-N
concurrency, which is how many clients perform a select at the same time. Multiple values can be specified, with a comma or a value specified by the –delimiter parameter as a separator

–engine=engine_name, E-engine_name
Create the storage engine used by the test table to specify multiple

–iterations=n, I-N
The number of iterations that the test performed

–detach=n
Disconnect a connection after executing n statements

–debug-info, T
Print memory and CPU information

–only-print
Print only test statements without actually executing

———————————————————————————————-
The test process needs to generate a test table, insert the test data, this mysqlslap can be automatically generated, the default generation of a Mysqlslap schema, if already exist to delete first, here to note, do not use –create-schema to specify the existing library, Otherwise the consequences could be serious. You can use –only-print to print the actual test process:

# Mysqlslap-a–only-print

DROP SCHEMA IF EXISTS ' mysqlslap ';
 CREATE SCHEMA ' Mysqlslap ';
 Use Mysqlslap;
 CREATE TABLE ' t1 ' (Intcol1 INT (), Charcol1 VARCHAR (128));
 INSERT into T1 VALUES (1804289383, ' mxvtvmc9127qjnm06sgb8r92q2j7vtiiitrd9rdxbl ');
 ... SELECT intcol1,charcol1 from T1;
 INSERT into T1 VALUES (364531492, ' qma5suko4m5om7ldvissc6wk9rsntgfxkdj4eaww ');
 DROP SCHEMA IF EXISTS ' Mysqlslap ';

You can see the action of the schema that was last created by the deletion, and the entire test will not leave traces in the database after it has been completed. If we perform a test, 50 and 100 concurrent, and execute the 1000-time total query, then:

# mysqlslap-uroot-p123456-a–concurrency=50,100–number-of-queries 1000–debug-info

Benchmark
Average number of seconds to run all queries:0.375 seconds
Minimum number of seconds to run all queries:0.375 seconds
Maximum number of seconds to run all queries:0.375 seconds
Number of clients running QUERIES:50
Average number of queries per client:20

Benchmark
Average number of seconds to run all queries:0.453 seconds
Minimum number of seconds to run all queries:0.453 seconds
Maximum number of seconds to run all queries:0.453 seconds
Number of clients running queries:100
Average number of queries per Client:10

User time 0.29, System time 0.11
Maximum resident Set Size 0, Integral resident set size 0
Non-physical pagefaults 4032, physical pagefaults 0, Swaps 0
Blocks in 0 out of 0, Messages in 0 out 0, signals 0
Voluntary context Switches 7319, involuntary context switches 681

The above results show that 50 and 100 concurrent results of a single test result (Benchmark), the more concurrent number, the longer the execution of all queries. For accuracy, multiple iterations can be tested several times:

# mysqlslap-a–concurrency=50,100–number-of-queries 1000–iterations=5–debug-info

Benchmark
Average number of seconds to run all queries:0.380 seconds
Minimum number of seconds to run all queries:0.377 seconds
Maximum number of seconds to run all queries:0.385 seconds
Number of clients running QUERIES:50
Average number of queries per client:20

Benchmark
Average number of seconds to run all queries:0.447 seconds
Minimum number of seconds to run all queries:0.444 seconds
Maximum number of seconds to run all queries:0.451 seconds
Number of clients running queries:100
Average number of queries per Client:10

User time 1.44, System time 0.67
Maximum resident Set Size 0, Integral resident set size 0
Non-physical pagefaults 17922, physical pagefaults 0, Swaps 0
Blocks in 0 out of 0, Messages in 0 out 0, signals 0
Voluntary context Switches 36796, involuntary context switches 4093

Test performance comparisons for different storage engines:
# mysqlslap-uroot-p123456-a-concurrency=50,100–number-of-queries 1000–iterations=5–engine=myisam,innodb– Debug-info
Benchmark
Running for Engine MyISAM
Average number of seconds to run all queries:0.200 seconds
Minimum number of seconds to run all queries:0.188 seconds
Maximum number of seconds to run all queries:0.210 seconds
Number of clients running QUERIES:50
Average number of queries per client:20

Benchmark
Running for Engine MyISAM
Average number of seconds to run all queries:0.238 seconds
Minimum number of seconds to run all queries:0.228 seconds
Maximum number of seconds to run all queries:0.251 seconds
Number of clients running queries:100
Average number of queries per Client:10

Benchmark
Running for Engine InnoDB
Average number of seconds to run all queries:0.375 seconds
Minimum number of seconds to run all queries:0.370 seconds
Maximum number of seconds to run all queries:0.379 seconds
Number of clients running QUERIES:50
Average number of queries per client:20

Benchmark
Running for Engine InnoDB
Average number of seconds to run all queries:0.443 seconds
Minimum number of seconds to run all queries:0.440 seconds
Maximum number of seconds to run all queries:0.447 seconds
Number of clients running queries:100
Average number of queries per Client:10

User time 2.83, System time 1.66
Maximum resident Set Size 0, Integral resident set size 0
Non-physical pagefaults 34692, physical pagefaults 0, Swaps 0
Blocks in 0 out of 0, Messages in 0 out 0, signals 0
Voluntary context Switches 87306, involuntary context switches 10326

# Mysqladmin-uroot-p123456-i Extended status Refresh once every 10 seconds

Aborted_connects the number of attempts to connect to a MySQL server that has failed.
Connections the number of attempts to connect to the MySQL server.
Created_tmp_tables the number of hidden temporary tables that have been created when the statement is executed.
Delayed_insert_threads the number of deferred insert processor threads being used.
Delayed_writes the number of rows written with insert delayed.
Delayed_errors the number of rows in which some errors (possibly duplicate key values) were written with insert delayed.
Flush_commands the number of times the Flush command was executed.
The number of times the Handler_delete request deletes rows from a table.
Handler_read_first the number of times the first row in the table is requested to be read.
The Handler_read_key request number is based on the key read line.
The number of times a handler_read_next request reads in a row based on a key.
The number of times a HANDLER_READ_RND request reads a row based on a fixed position.
Handler_update the number of times a row in the table was requested to be updated.
The number of times the Handler_write request inserts a row into the table.
key_blocks_used the number of blocks used for the keyword cache.
Key_read_requests the number of times a key value is requested to be read from the cache.
Key_reads the number of times a key value is physically read from disk.
Key_write_requests request to write a key block to the cache count.
Key_writes the number of times a key-value block is physically written to disk.
The maximum number of connections that the max_used_connections uses at the same time.
Not_flushed_key_blocks a key block that has been changed in the key cache but has not been emptied to disk.
Not_flushed_delayed_rows the number of rows waiting to be written in the Insert delay queue.
Open_tables the number of tables opened.
Open_files the number of open files.
Number of Open_streams open streams (mainly for log records)
Opened_tables the number of tables already open.
Questions the number of queries sent to the server.
Slow_queries the number of queries to spend more than long_query_time time.
threads_connected the number of connections currently open.
Threads_running the number of threads that are not sleeping.
How many seconds the Uptime server worked.