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

How to understand the quadruple customization of deep optimization of MySQL high availability database kernel? I believe many inexperienced people don't know what to do about it. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Recently, our database team has made in-depth optimizations on many aspects of native replication to improve the functionality and performance of UDB's highly available databases. Today, I use the community as a platform to share one or two with you.

1. UDB high availability database architecture

UDB builds a two-node high availability architecture with virtual IP, HAProxy, and single-node UDB databases:

Dual-node UDB database ensures full redundancy of database data and database availability at the same time.

HAProxy connects only one UDB node at a time to avoid data conflicts caused by multiple writes.

Dual-node HAProxy ensures the availability of Proxy

Virtual IP switches the HAProxy by IP drift when the HAProxy is down, and users do not need to modify the IP again.

In the above architecture, whether the data of the slave node UDB is complete and whether it is consistent with the master database is the key to the whole high availability architecture, so semi-synchronous replication for data transmission plays a vital role. We have made in-depth optimization at the kernel level for native semi-synchronous replication.

II. Deep optimization of UDB database

UDB is based on the open source database MySQL Community Server 5.7.16 as the baseline version, and does deep kernel optimization around the highly available architecture.

The replication process, as shown in the figure above, mainly goes through the following steps:

After MySQL Server successfully executes SQL, record binlog

After reading the binlog, the Dump thread sends it to the slave IO thread

The IO thread records the received binlog to the relay log and records the reception progress to the master.info

SQL reads the contents of the log in relay log to reproduce, and records the progress of copying the log to relay-log.info.

We do deep kernel optimization on the basis of native replication, and improve the function and performance of some of the steps in the above process to make UDB more stable.

1. Optimization of Binlog log replication

Existing problems

There is a degradation problem in native semi-synchronous replication. when the network jitter leads to timeout or catch up with the progress of the master log from the library, the replication will degenerate from semi-synchronous replication to asynchronous replication.

Compared with reliable semi-synchronous replication, in the process of asynchronous replication, the slave library has no way to perceive whether the received relay log is consistent with the binlog of the master library. If there is a downtime, there is no way to confirm whether the slave database data is consistent with the master database and whether database switching can occur. This kind of uncertainty is something we do not want to see.

Optimization scheme

Establish dual-channel replication, and add a UDB replication channel to the original semi-synchronous replication:

The establishment of a new replication channel is parallel to the original replication, and the two channels are independent of each other.

The new replication channel does not transfer data, only the SQL execution progress of the main library (file name and location of the binlog).

The new replication channel uses semi-synchronous replication protocol, but does not degenerate, reconnects after timeout, and only receives SQL execution progress of *.

The new replication channel does not have the problem of catching up data, as long as the network is normal, the slave library can always be aware of the progress of SQL execution.

As shown in the figure above, master-slave replication stops when the slave library goes down or the network fails. When the slave database replication returns to normal, the original replication channel performs data recovery through asynchronous replication, and the UDB replication channel only receives * * binlog record locations, which can * reduce the time of asynchronous replication between master and slave. That is, in the case of network connectivity, whenever a downtime occurs, the slave database knows whether it is in the same state as the master database (or how much it lags behind).

2. Optimization of Relay log file records.

Existing problems

In MySQL, binlog is recorded in event as the basic unit. Take binlog in MySQL 5.7ROW format (enable GTID) as an example, a DML (insert) will be recorded in binlog in the format of five event (other operations are composed of one or more event and are no longer listed one by one), which are:

GTID_EVENT: the GTID that records the current transaction

QUERY_EVENT: transaction starts

TABLE_MAP_EVENT: table corresponding to the operation

WRITE_ROW_EVENT: inserting records

XID_EVENT: commit transaction

All the event form a complete transaction, and the complete transaction will be correctly reproduced to the slave library by the SQL thread. When the current IO thread receives binlog, it receives it in units of event, that is, it receives an event, records it in relay log, and then continues to receive the next one. This approach is inefficient and does not make full use of MySQL's own file cache.

Optimization scheme

Optimize the way that IO threads record relay log, which is recorded in event units and modified to record in transaction units. Merge IO operations with small IO threads to improve IO performance.

Merge a single event write operation into multiple event unified write operations, merge small IO operations into larger IO operations, and improve IO performance.

3. Optimization of Master.info file records.

Existing problems

When building replication, the Master.info file records the relevant information about the connection to the main library, such as IP and PORT, and records the file name and location of the binlog received by the IO thread from the main library during the replication process. The file and location will be updated each time the relay log is recorded successfully.

After building replication based on GTID, the binlog file and location recorded in master.info are no longer used as the basis for replication, so the file and location of binlog recorded in master.info are no longer valid data, so it is not necessary to update every time.

Optimization scheme

After the IO thread records the relay log successfully, add a judgment before updating the master.info file. If GTID is turned on and GTID is used as the auto_position=1 for replication, the file and location of binlog in master.info are no longer updated.

Other master.info operations remain, such as change master, shutdown, and so on.

4. Optimization of Relay log lock

Existing problems

When the replication progress of IO thread and SQL thread is similar, the same file cache will be used when operating relay log. When reading and writing file cache, locks are needed to ensure the correctness of the operation. On the other hand, IO and SQL threads need to read and write this piece of common memory frequently, which requires frequent competition for the same lock, resulting in performance degradation.

Optimization scheme

Split the operations of the IO thread and the SQL thread on relay log and no longer use the same file cache. Although doing so will cause the SQL thread to add a read IO operation. However, the competition for locks is eliminated, and the overall performance of IO threads and SQL threads is greatly improved.

III. Summary

The optimized replication flow chart is as follows:

The database native replication process includes recording binlog, recording relay log, recording master.info, relay-log.info, and so on. In view of some of the steps in the above process and other unlisted optimizations, the function and performance of the UDB high availability database have been significantly improved.

After reading the above, have you mastered how to understand the quad customization method of deep optimization of MySQL high availability database kernel? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.