Therefore, how to make good use of MYSQL becomes very important. On the one hand, it is necessary to find out the bottleneck of system reading and writing through MYSQL optimization to improve database performance. On the other hand, it is necessary to reasonably involve the data structure and adjust the parameters to improve the user's operational response; At the same time, it also saves system resources as much as possible, so that the system can provide services with greater load. This article will introduce how Tencent Cloud team optimizes Mysql at the kernel level.
The early CDB was mainly based on the open source Oracle MySQL branch, focusing on optimizing the operation and maintenance of OSS system. In Tencent Cloud, due to more and more users, CDB has higher and higher requirements for MySQL. Tencent Cloud CDB team deeply customized and optimized CDB for MySQL branch according to the user's demand and the technical trend of industry development. Optimization focuses on three dimensions: kernel performance, kernel function and peripheral OSS system. The specific method is as follows:
1. Kernel performance optimization
Because the DB on Tencent Cloud basically needs the characteristics of cross-campus disaster tolerance, CDB's optimization for MySQL is mainly to solve the performance problems in the real deployment environment on the premise of deploying master-slave DB in the cross-campus network topology. After analysis and investigation, we summarize the optimization idea as "eliminating redundant I/O, shortening I/O path and avoiding lock competition". Here are some examples of kernel performance:
1. Replication optimization between primary database and standby database
problem analysis
As shown in the above figure, in the replication architecture of native MySQL, the master 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 events:
Write to the relay log, this process will compete with the slave SQL thread for locks to protect the relay log.
Update replication metadata (including information such as the location of the master server).
optimization method
After analysis, our optimization strategy is:
Slave I/O thread and slave SQL thread are typical producer-consumer models, which can be designed without locks. Therefore, the realization idea is that the slave I/O thread atomically updates the length information of the relay log after each data write, and the slave SQL thread takes the length information as the boundary when reading the relay log. In this way, the original fierce competition relay log lock will be resolved into no lock;
Because the GTID (Global Transaction Identifier) in the Binlog event corresponds to the DB transaction one by one, and the data in the relay log itself already contains the required replication metadata, we don't need to write the Master info file, thus eliminating redundant file I/O;
In DB, the update granularity is a transaction, because on the I/O of the relay log file, we greatly improve the disk I/O by merging small discrete I/O into large I/O with transaction granularity.
Optimization effect
As shown in the above figure, after optimization: 35.79% of futex in the left figure has been completely eliminated; Under the same pressure measurement, the file I/O overhead of 56. 15% is optimized to 19. 16%, and it is optimized from I/O thread to expected I/O intensive thread.
2. Optimization between main library transaction thread and dump thread
problem analysis
As shown in the above figure, in native MySQL, multiple transaction submission threads TrxN and multiple Dump threads will compete for the protection lock of Binlog file resources at the same time, multiple transaction submission threads will write to Binlog, and multiple Dump threads will read data from Binlog file and send it to Slave. All threads are executed serially!
optimization method
After analysis, our optimization strategy is:
Read and write are separated, and multiple write threads are still executed in series under the protection of locks. After each writing thread finishes writing, it updates the length information of the current Binlog. Multiple dump threads use the length information of the Binlog file as the reading boundary, and multiple dump threads execute in parallel. Use this method to make the dump thread in the replication topology send faster!
affect
The optimized schematic diagram is as follows:
After testing, the optimized kernel not only improves the performance of transaction submission thread, but also greatly improves the performance of master-slave replication when there are many dump threads.
2. Optimization of the interactive process between the main library and the standby library.
problem analysis
As shown in the above figure, in native MySQL, the data transmission and ACK response between the primary and standby libraries are simply performed serially, and it is not allowed to continue sending the next event until the ACK response of the previous event arrives. This behavior is very poor in the case of cross-campus (RTT 2-3ms), and the bandwidth advantage cannot be well utilized.
optimization method
After analysis, our optimization strategy is:
The sending and receiving of ACK response are separated into different threads, because both sending and receiving are based on TCP stream transmission, so the timing is guaranteed; In this way, the sending thread can continue sending before receiving the ACK, and the receiving thread wakes up the waiting thread to perform the corresponding task after receiving the ACK.
affect
According to the actual use case test, the optimized TPS is improved by about 15%.
Three. Kernel function optimization
1. Reserved line of connection number of operation and maintenance account.
On Tencent Cloud, users will encounter APP exceptions or bugs from time to time, thus occupying the maximum connection limit of DB. This is because the CDB OSS account cannot be logged in for emergency operation and maintenance. In view of this situation, we set a configurable connection quota in the MySQL kernel. Even in the above scenario, the operation and maintenance account can still be connected to DB for emergency operation and maintenance. It greatly reduces the risk of anarchy in DB under abnormal conditions. This account only has database operation and maintenance management authority, so it can't obtain user data, and it also ensures the security of user data.
2. Strong synchronization between primary equipment and standby equipment.
In order to meet the high consistency requirements of some applications, CDB has been deeply optimized on the basis of MySQL native semi-synchronization to ensure that a transaction must have been copied to at least one standby database before being submitted to the main database. Ensure data consistency when the main library is closed.
Four. Peripheral system optimization
In addition to the partial optimization of MySQL kernel mentioned above, we also made several 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 enhance the HA/ service discovery and automatic capacity expansion functions of the original system, and many optimizations have been made in data security/failover and rapid recovery.
Relevant suggestions
Tencent cloud database CDB for MySQL product related documents
Summary of MySQL database design
This article was published by Tencent Cloud Technology Community authorized by the author. Please indicate the source to get more dry goods of cloud computing technology. Please go to Tencent Cloud Technology Community.
Wechat official account: QcloudCommunity
Tencent Cloud Database Team: Talking about how to deeply optimize MySQL kernel
Tags: update environment, network transmission information, secure file technology sharing.