Question 41: How does group submission improve performance

ask

In the graphic series, we introduced the concept of group submission ( https://mp.weixin.qq.com/s/_WVE1xtGdtXEcwkTf7j_rA), This time we will observe its effects through experiments

experiment

As usual, wide oil to start a database:

Here we have adjusted the brushing rhythm to make the phenomenon more obvious. At the same time, the double-one brushing configuration is also the recommended configuration for maintaining reliable data in the production environment.

We conduct a stress test:

While opening the pressure test, use pt-ioprofile to monitor IO:

Let's perform a pressure test again, this time double the pressure concurrently:

At the same time of pressure measurement, use pt-ioprofile to monitor the number of IOs:

We can see that:

In the case of doubling the pressure, the overall running time has increased from 55s to 67s (an increase of 20%), but the number of IOs for each file in MySQL has not increased too much (here, take "the number of times to brush the binlog" as an example, 1% increase)

Let's take a more intuitive look at the group submission situation:

Through last_committed in binlog, you can determine which commit group the transaction is assigned to

Next, analyze the number of transactions in the two tests:

It can be seen that the number of commit groups in the two tests has increased by 25%, the number of transactions has increased by 100%, and most of the transactions have been merged into the commit group for submission.

This is the role of group submission: put multiple transactions in one IO for submission to save the number of IOs

Tips

If you use MySQL 8.0 to experiment and configure binlog_transaction_dependency_tracking to WRITESET or WRITESET_SESSION, the method of identifying the submission group according to the last_commit field will be inaccurate.

---

What else do you want to know about the technical content of MySQL? Hurry up and leave a message to tell the editor!