How can I test the pressure on MySQL?

The combination of LANMP and LAMP on production servers has been stable for a while. However, I have been trying to perform stress tests on MYSQL for many times because of the high concurrency of MYSQL. I just saw an article about MYSQL stress testing. It's really good. Add it to favorites first. Reposted from: xfshean. blog.163.comblogstatic60206566

The combination of LANMP and LAMP on production servers has been stable for a while. However, I have been trying to perform stress tests on MYSQL for many times because of the high concurrency of MYSQL. I just saw an article about MYSQL stress testing. It's really good. Add it to favorites first. Reprinted from: http://xfshean.blog.163.com/blog/static/60206566

The combination of LANMP and LAMP on production servers has been stable for a while. However, I have been trying to perform stress tests on MYSQL for many times because of the high concurrency of MYSQL. I just saw an article about MYSQL stress testing. It's really good. Add it to favorites first.

Reprinted from: http://xfshean.blog.163.com/blog/static/60206566201272434556544/

In fact, mysql testing is not so complex. Apart from some commonly used select \ insert \ update \ deletc, testing the concurrency is the most important. For example, when the number of connections is 1 kb, can the concurrency meet the performance of the current request, server, and memory CPU usage. To put it bluntly, testing mysql is to test its configuration file, concurrency, and server performance.

I. Tools
Mysql's preferred tool: mysqlslap

-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

--------------------------------
During the test, you need to generate a test table and insert test data. This mysqlslap can be automatically generated. By default, a schema of mysqlslap is generated. If yes, delete the table first, do not use-create-schema to specify an existing database. Otherwise, the consequences may be severe. 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(32) ,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 schema action created at the beginning of the deletion. After the test is completed, no trace is left in the database. If we perform a test with 50 and 100 concurrent queries and execute 1000 total queries, 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 0, Messages in 0 out 0, Signals 0
Voluntary context switches 7319, Involuntary context switches 681

The above results show that 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:

# 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 0, Messages in 0 out 0, Signals 0
Voluntary context switches 36796, Involuntary context switches 4093

The performance of different storage engines is compared during the test:
# 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 0, Messages in 0 out 0, Signals 0
Voluntary context switches 87306, Involuntary context switches 10326

# Mysqladmin-uroot-p123456-I 10 extended status refresh every 10 seconds

The number of connection times of the MySQL server that failed the Aborted_connects attempt.
The number of times that Connections attempted to connect to the MySQL server.
Created_tmp_tables: Number of implicit temporary tables created when the statement is executed.
The number of threads that Delayed_insert_threads is using to insert a delayed processor.
The number of rows written by Delayed_writes Using insert delayed.
The number of rows in which Delayed_errors writes data using insert delayed (which may duplicate key values.
The number of times Flush_commands executes the FLUSH command.
The number of rows that Handler_delete requests to delete from a table.
The number of times the Handler_read_first request reads the first row of the table.
The Handler_read_key request number is based on the key-read row.
The number of times the Handler_read_next request reads a row based on a key.
The number of times the Handler_read_rnd request reads a row based on a fixed position.
The number of times Handler_update requests to update a row in the table.
The number of times a Handler_write request inserts a row into a table.
The number of Key_blocks_used Blocks Used for keyword cache.
The number of times Key_read_requests requests read a key value from the cache.
The number of times that Key_reads reads a key value from the disk physically.
Number of times that Key_write_requests requests write a key block to the cache.
The number of times that Key_writes physically writes a key-Value block to a disk.
The maximum number of connections simultaneously used by Max_used_connections.
Not_flushed_key_blocks has been changed in the key cache but has not been cleared to the disk.
The number of rows that Not_flushed_delayed_rows is waiting to write in the insert delay queue.
The number of open tables in Open_tables.
The number of open files in Open_files.
Number of open streams in Open_streams (mainly used for log recording)
The number of open tables in Opened_tables.
The number of queries sent by Questions to the server.
Slow_queries takes more than long_query_time.
The number of connections currently opened by Threads_connected.
The number of threads whose Threads_running is not sleeping.
How many seconds does the Uptime server work.

Original article address: how to test MySQL pressure ?, Thanks to the original author.