Detailed explanation of GaussDB for MySQL performance optimization

background

Let's first look at the general process of transaction submission in MySQL 8.0

The above process is an implementation of the WAL principle in MySQL 8.0. This process means that the submission of any transaction must complete the write buffer and flush to disk processes.

However, there is a problem in this process: the CPU of each server is limited, and the number of threads that the server can process is also limited. So when the number of concurrent transactions in our business is much larger than the number that our server can process in parallel, the subsequent transactions can only be processed after the previous transactions are submitted. Before that, they could do nothing. Therefore, in a high-concurrency scenario, how to further improve thread utilization is a key to writing high-concurrency transactions.

Inspiration comes from life

An optimization is not imagined out of thin air; sometimes, it often comes from real life. Next, let's take a look at an example around us that is very similar to the transaction submission process: express delivery.

In today’s express delivery, generally one courier is responsible for an area. When express delivery first started to become popular, the quantity was small, so one courier could basically complete the delivery within the specified time.

However, as the number of express deliveries increases, a courier has to spend a long time delivering in one community before reaching the next community, which often results in the courier being unable to deliver on time. Driven by this problem, a new industry began to emerge - express delivery stations.

Optimization principles of express delivery

Next, let’s take a look at what problems the express delivery station actually solves.

In the express delivery process, the most time-consuming part is not loading or unloading, but phone calls and waiting. The time it takes to deliver to a community depends on the time when the last person comes to pick up the package. After the last person collects the money, the courier can't do anything else except making a phone call (there is no way to notify the people in the next community because the time when the last person comes to pick up the package is uncertain). Then this waiting time is a waste for the courier.

Express delivery stations can solve this problem to a large extent. After the courier arrives, he only needs to unload the express and go to the next community. The rest of the work can be done by the station staff, which greatly improves the courier's delivery efficiency.

analyze

Going back to the database, if we regard the Transaction thread as a courier and the file on the storage as the person who picks up the courier, we will find that the two are very similar. So can we optimize the transaction processing process like we optimize express delivery? The answer is yes.

According to the optimization principle of express delivery stations, we know that express delivery stations save couriers the time of waiting for customers to pick up the goods. So, is there any waiting process in the transaction processing process? The answer is yes, storage IO is a longer wait. Developers with rich experience in database use all know that the disk IO performance of waiting for redo logs to be written to storage largely determines the write performance of the database. For modern databases, especially for databases like GaussDB (for MySQL) that separate computing from storage, the storage IO time accounts for a large proportion of the total transaction processing time. Although log buffer merge writes can improve the overall throughput in concurrent situations, if these threads can do other things (such as processing other waiting transactions) during the waiting time for IO. Then there will be further performance improvements.

Optimization of GaussDB (for MySQL)

Now that we have found the waiting point, we can create a "express delivery station" for the database, just like the optimization method of express delivery, and let the "express delivery station" do the waiting, while the transaction thread can handle other waiting transactions, so that the CPU will not be "idle".

As shown in Figure 5, GaussDB (for MySQL) can commit a transaction after the flush to disk action of the redo log is completed. However, it does not respond to the client at this time, but directly processes the next transaction. At the same time, a small number of "post commit worker threads" are used to wait for the completion of log writing in batches (the waiting process does not actually occupy the CPU) and respond to the client. This allows the "waiting" and "processing of the next transaction" to be parallelized, keeping the CPU "busy".

Actual test

According to actual tests, under the standard sysbench write model, when Post Commit is not used, the maximum performance is about 350,000 QPS. After using Post Commit, the QPS can reach more than 420,000, which improves the write performance by 20%.

The above is a detailed explanation of GaussDB for MySQL performance optimization. For more information about GaussDB for MySQL performance optimization, please pay attention to other related articles on 123WORDPRESS.COM!