Usually, we will stress test MySQL for the following purposes:

1. Confirm the performance difference of the new MySQL version compared to the previous one, such as changing from 5.6 to 5.7 , or changing from the official version to the Percona branch version; 2. Confirm whether the performance of the new server is higher and how much higher it can be, such as the CPU upgrade , The array card cache has been enlarged, and the mechanical disk has been replaced with an SSD disk; 3. After confirming how some new parameter adjustments will affect performance, such as innodb_flush_log_at_trx_commit , sync_binlog and other parameters; 4. Confirm that the new business that is about to go online will load MySQL How much is affected, whether it can be carried, whether it is necessary to expand or upgrade the server configuration;

For the purposes of the above-mentioned pressure tests, the corresponding test methods are also different.

Let me talk about the fourth one, which needs to be integrated with online business.At this time, you need to develop your own test tools, or use tcpcopy to direct actual online user requests to the test environment for simulation testing.

For the first three, we usually use benchmark tests. More commonly used MySQL benchmark stress testing tools TPCC-MySQL , SysBench , mysqlslap a few and so on.

Regarding the use of stress testing tools, you can check my previous sharing on ORACLE Technology Carnival: MySQL stress testing experience , I won’t go into details here.

Based on the purpose of promoting communication among peers, unifying MySQL stress testing standards, and sharing, comparing, and referencing test results with each other. Therefore, Lao Ye specially initiated the MySQL stress test benchmark value initiative. It is recommended that you use the following benchmark values for stress testing.

You can also view the excel file attached to this article: Stress Test Benchmark Recommendations and Data Collection Templates , which have included recommendations for data collection points related to stress testing, sorting of stress testing results, and automatic generation of comparison charts. Welcome all colleagues to put forward different opinions and supplementary opinions.Thank you in advance.

On the other aspects of stress testing:

1.How to avoid the impact of the cache during stress testing
[Old Ye’s suggestion] There are 2 suggestions
a.Filling test data is larger than physical memory, at least more than the value of innodb_buffer_pool_size , and you cannot load all the data into memory unless you intend it.Just want to test the performance of MySQL in full memory.
b.After each round of testing is completed, restart the mysqld instance, and use the following method to delete the system cache, release swap (if swap is used), and even restart the entire OS.

[ root@imysql . com ]# sync   - flush dirty data to disk [ root@imysql . com ]# echo 3 > /proc/ sys / vm / drop_caches   - clear OS Cache [ root@imysql . com ]# swapoff - A && swapon - A

2.How to reflect the real characteristics of online business as much as possible
[Old Ye’s suggestion] There are two suggestions
a.As mentioned above, it is to develop test tools by yourself or use tcpcopy (or similar switch mirror function) to connect actual users online Request-oriented test environment for simulation test.
b.Use  http_load or siege tools to simulate real user request URLs for stress testing.I am not too professional in this regard, so I can consult my internal stress testing colleagues.

3.How to interpret the stress test results
[Old Ye's suggestion] In addition to the tps/TpmC indicators, the stress test results should also pay attention to the system load data during the stress test, especially iops, iowait, svctm, %util, I/O words per second Number of sections (I/O throughput), transaction response time (all in the test records printed by tpcc-mysql/sysbench). In addition, if the I/O device can provide device-level IOPS, read and write delay  data, it should also be paid attention to.

If the tps/TpmC results of the two tests are the same, then whose  transaction response time, iowait, svctm, %util, and read and write latency are  lower, it means that the test mode has a higher performance improvement space.

4.How to speed up the efficiency of tpcc_load loading data
[old leaf suggestion] tpcc_load can actually be loaded in parallel.On the one hand, it can distinguish  four dimensions of ITEMS, WAREHOUSE, CUSTOMER, and ORDERS .
In addition, for example, if you want to load 1000 warehouses in the end, you can also divide them into 1000 concurrent loads. Look at the parameters of the tpcc_load tool to know:

usage : tpcc_load [ server ] [ DB ] [ user ] [ pass ] [ warehouse ]    ORtpcc_load [ server ] [ DB ] [ user ] [ pass ] [ warehouse ] [ part ] [ min_wh ] [ max_wh ] * [ part ]: 1 = ITEMS 2 = WAREHOUSE 3 = CUSTOMER 4 = ORDERS

I originally wanted to write a parallel loading script by myself, but later found that someone had done it on the universal github, so I used it directly.This is the project link tpcc_load_parallel.sh , and the loading efficiency is at least 10 times higher.

Further reading:

• tpcc-mysql installation, use, result interpretation

• Added sysbench and tpcc-mysql source code package download

• Release the branch version of tpcc-mysql based on percona

• sysbench installation, use, result interpretation