Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to optimize the MySQL kernel in detail. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

one。 Optimization of kernel performance

Since the DB on Tencent Cloud basically requires cross-campus disaster recovery, the optimization of CDB for MySQL is mainly aimed at the master / slave DB deployed in the cross-campus network topology, focusing on solving the performance problems in the real deployment environment. After analysis and investigation, the idea of optimization is summarized as follows: "eliminate redundant I _ pico, shorten I _ peg O path and avoid big lock competition". Here are some examples of kernel performance:

1. Replication Optimization between active and standby DB

Analysis of problems

As shown in the figure above, in the native MySQL replication architecture, the Master side continuously sends Binlog events to the Slave I / O thread through the Dump thread, and the Slave I / O thread has two main actions after receiving the Binlog event:

Write to the Relay Log, which competes with the Slave SQL thread for the lock to protect the Relay Log.

Update replication metadata (including information such as the location of the Master).

Optimization method

After analysis, our optimization strategy is:

Slave I _ Thread O thread and Slave SQL thread are typical single-write and single-read producer-consumer model, which can be designed without lock, so the implementation idea is that after each time the Slave I _ Thread O thread writes data, the atom updates the length information of Relay Log, and the Slave SQL thread reads Relay Log with the length information as the boundary. In this way, the competitive Relay Log lock will be reduced to no lock.

Because there is an one-to-one correspondence between the GTID (Global Transaction Identifier) and the DB transaction in the Binlog event, the data in Relay Log already contains the replication metadata needed, so we can not write the Master info file and eliminate the redundant file Ibank O

In DB, we take transaction as update granularity, because on Relay Log file, we can greatly improve disk Imax O by merging discrete small Imax O into large one with transaction granularity.

Optimization effect

As shown in the figure above, optimized: 35.79% of lock contention (futex) in the figure on the left has been completely eliminated; under the same manometric pressure, 56.15% of the file Imab O overhead is optimized to 19.16% of the min Slave Iago O thread is optimized to the expected Imax O-intensive thread.

two。 Optimization between main Library transaction Thread and Dump Thread

Analysis of problems

As shown in the figure above, in a native MySQL, multiple transaction commit threads TrxN and multiple Dump threads compete for the protection lock of Binlog file resources at the same time, multiple transaction commit threads write to Binlog, and multiple Dump threads read data from the Binlog file and send it to Slave. All threads are executed serially!

Optimization method

After analysis, our optimization strategy is:

The read and write are separated, and multiple writing threads are executed serially under lock protection. After each writing thread completes writing, the length information of the current Binlog is updated. Multiple Dump threads take the length information of the Binlog file as the reading boundary, and multiple Dump threads execute in parallel. This is the way to make Dump threads in the replication topology send faster!

Effect.

After testing, the optimized kernel not only improves the performance of transaction commit thread, but also greatly improves the performance of master-slave replication in the case of more Dump threads.

two。 Optimization of interaction process between main and standby libraries

Analysis of problems

As shown in the figure above, the data transmission and ACK response between the master and slave libraries in the native MySQL are simple serial execution, and the next event is not allowed to be sent until the previous event ACK response arrives; this behavior performs very poorly in the case of cross-campus (RTT 2-3ms) and does not make good use of the bandwidth advantage.

Optimization method

After analysis, our optimization strategy is:

The sending and receiving of ACK responses are separated into different threads. Since both sending and receiving are based on the transmission of TCP streams, the timing is guaranteed. In this way, the sending thread can continue to send before receiving the ACK, and after receiving the ACK, the receiving thread wakes up the waiting thread to perform the corresponding task.

Effect.

According to the actual use case test, the optimized TPS is improved to about 15%.

three。 Optimization of kernel function 1. Reserved quota for the number of connections to the account of operation and maintenance

On Tencent Cloud, user APP exceptions or BUG are encountered from time to time, thus filling the maximum connection limit of DB. This is because the CDB OSS account cannot log in for emergency operation and maintenance operations. In view of this situation, we have separately opened a configurable quota for the number of connections in the MySQL kernel. Even in the above scenario, the OPS account can still connect to DB for emergency operation and maintenance operations. It greatly reduces the risk of anarchy in DB under abnormal conditions. The account only has the authority of database operation and maintenance, can not obtain user data, and ensures the security of user data.

two。 Active and standby strong synchronization

In view of the high data consistency requirements of some applications, CDB makes a deep optimization on the basis of MySQL original semi-synchronization to ensure that a transaction must be replicated to at least one standby database before committing on the main database. Ensure the consistency of the data when the main library is down.

four。 Optimization of peripheral system

In addition to the partial optimizations on the kernel side of MySQL mentioned above, we have also made a number of optimizations on the peripheral OSS platform. For example, asynchronous MySQL ping protocol is used to monitor a large number of instances, distributed technology is used to strengthen the functions of HA/ service discovery and automatic expansion of the original system, and many optimizations are made in data security / failover and fast recovery.

This is the end of this article on "how to do MySQL kernel deep optimization". I hope the above content can be helpful to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

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.