Shulou(Shulou.com)06/01 Report--

| | introduction |

Recently, in cooperation with a colleague to do a performance stress test, we found that under the same amount of data, the same database parameters, the same sysbench pressure, the same database version and sysbench version, and the same server hardware environment, the pressure test results of my colleagues and I were very different: after an hour of stress test, there was a high frequency periodic blocking in my stress test results (tps,qps is 0). However, there was no blockage in the pressure test results of colleagues (tps,qps was stable from beginning to end). Under normal circumstances, such a huge performance difference is not possible when the environment is exactly the same. But this time, it did happen what was impossible. After re-testing and investigation, the secret was finally discovered.

Server hardware information

Database host

* CPU:72 process

* memory:128G

* disk: a storage of 100g

* Network card: intel 10 Gigabit network card

Database version: MySQL 5.7.21

Sysbench version: 1.0.9

Sysbench host

* CPU:20 process

* memory:128G

* disk: local SAS 50g

* Network card: intel 10 Gigabit network card

| | retest result data |

First, let's take a look at the pressure test results, as shown in the following figure (Note: only the data of the first few seconds are intercepted here for comparison):

My pressure test results (as can be seen in the following figure, the obvious periodic tps,qps is 0)

The pressure test results of colleagues (as can be seen in the figure below, tps,qps is relatively stable and there is no blocking)

| | grab wait event |

Seeing the results in section 1 above, if you judge quickly based on past experience and common sense. I think it will be foolish! When I learned that my colleague's test results were very stable, I instinctively thought: pull out the waiting event information and have a look! The following is the waiting event information intercepted by my colleagues and me during the retest (the retest time is 3 minutes) (parents who do not want to take a closer look at the contents of the waiting event can skip to Section 3)

Operation operation time statistics (query once per second, dozens of queries and 5 times with the longest interception time)

When using the lua script to randomly generate the primary key value: suppose that when the delete operation deletes the data row of id=1, then insert uses the same primary key value of id=1. Therefore, there is a high probability that innodb will reuse the pages where delete data rows are located to store insert data. under the high concurrency pressure of sysbench, most insert data stores may only need to operate in pages that already exist in memory, without too much IO operations.

Instead of using the primary key value randomly generated by the lua script, the primary key value is generated using the self-increasing attribute of the table: suppose that when the delete operation deletes the data row of id=1, then insert is generated by the self-increasing attribute of the table itself, that is to say, it is almost impossible to id=1, so there is a high probability that innodb will reapply for a data page to write insert data, under the pressure of high sysbench concurrency. Most insert data stores may need to reapply space from disk files, and IO operations are more frequent.

At this point, the reason for the great difference between the stress test results of my colleagues and me is roughly determined, and after repeated verification, it is also confirmed that it is caused by the slight difference in the oltp.lua script. Because we use the local disk most of the time, and the IO latency of the local disk is low, usually this small difference in sysbench stress testing is easy to be ignored. In this case, due to the use of a storage device in the environment we tested, the IO latency is greatly increased compared with the local site, resulting in a huge difference in the final stress test results due to the slight difference in the oltp.lua script.

Tip: if you don't want to change the lua script, but also want to avoid primary key conflicts, is there a way to solve it? Yes, starting with sysbench version 0.5, a new hidden option, mysql-ignore-errors, has been added to ignore the specified error, and to avoid primary key conflicts, specify the option mysql-ignore-errors=1062.

| | author profile |

IT, a senior database technology expert in Luo Po Walk Technology, has worked for many years, and has served as an operation and maintenance engineer, senior operation and maintenance engineer, operation and maintenance manager, and database engineer. He has participated in the design and preparation of version release system, lightweight monitoring system, operation and maintenance management platform and database management platform. He is familiar with MySQL architecture and Innodb storage engine, and likes to specialize in open source technology and pursue perfection.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.