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

What is the adaptive routing query tracked by GTID in MySQL? I believe many inexperienced people are at a loss about it. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Adaptive routing query based on GTID Trackin

ProxySQL is a database agent that works in the seventh layer (application layer) and supports MySQL protocol. ProxySQL itself has its own highly available and high-performance functions, and contains a rich set of features. The features in the upcoming version 2.0 will be more excited!

The most commonly used functions in ProxySQL are the analysis and statistics of queries and the separation of read and write based on routing queries.

When the client connects to the ProxySQL to execute the query, the ProxySQL first checks the route according to the predetermined routing rules and distributes it to the instance where the statement should be executed. The simplest example is to route all read queries to the slave database and all write queries to the master database. Of course, this case is not practical in production because the read query may read non-up-to-date data from the library. Therefore, we recommend that all requests be sent to the master database, and because of ProxySQL's support for SQL statistics, DBA can create more accurate routing rules to route specific queries to the slave database. For more information and other cases, please refer to the previous article.

Highly customized routing rule settings are supported in ProxySQL, and you can even specify through the next_query_flagIN parameter that the next query connected to the current query is still executed on the same set of (hostgroup) instances. For example, through this feature, you can specify that the query statement that follows the insert data statement is still executed on the same instance. However, only when DBA is very clear about the application logic, can we know which functions or queries are written first and then checked, so the practical application of this feature is more complex.

Context-consistent read

Although some applications have high requirements for real-time data, they are compatible with context-consistent reading, that is, clients can read the modified data in the same ProxySQL connection. This feature is not guaranteed by MySQL's default asynchronous replication structure. In the case of asynchronous replication, after writing is submitted on the master, the slave database may not be able to read the latest data that has just been modified at the same time because of the transfer time or the difference in execution speed.

So how do we ensure that queries are routed to the slave library by ProxySQL only when write events are fully synchronized to the slave library?

Based on GTID

GTID helps in this.

After the update of MySQL 5.7.5, the client can know the GTID that it last wrote to the transaction, and can read on any slave library that has been executed by the transaction represented by the current GTID. Lefred gives an example of this process in his blog, as follows: after the MySQL instance server turns on the session_track_gtids parameter (this is a global and thread-level parameter that returns the tag and GTID number for the successful execution of the current transaction) The client can then use the function of SELECT WAIT_FOR_EXECUTED_GTID_SET ('3E11FA47-71CA-11E1-9E33-C80AA9429562:1-5') on the slave library to determine whether the write transaction just executed in the master library has been executed on the slave library.

Although this operation can be used to ensure context-consistent reading, this method is cumbersome and impractical for production environments for the following reasons:

Adding WAIT_FOR_EXECUTED_GTID_SET every time you execute a business query from the library will increase the response time of a single query

For efficiency, if you want to find instances from several instances that have finished executing the specified GTID, you may need to run it on all slave libraries

It is very likely that all slave libraries did not complete the synchronization of the GTID within the acceptable delay wait time.

That is to say, the above operation is not practical in the production environment.

Can you track GTID?

Because ProxySQL plays the role of MySQL client, when session_track_gtids is turned on, ProxySQL can also track all requests from the front end of the GTID, and can accurately know the final GTID value of each front-end connection. This information can be used to route read requests to the correct slave instance (that is, the slave instance that has finished executing the GTID transaction specified by a thread). So how does ProxySQL track whether the specified GTID has been executed on the slave library?

It can be divided into two ways:

Active query: ProxySQL regularly queries the GTID execution of all instances.

Listen to tell that every time a new write event is generated and the GTID number is generated, ProxySQL will be informed immediately.

It should be noted that because there is always a certain interval between each query, it is inevitable that there is a delay based on the query interval in the active query method. The more frequent the query, the more accurate the information obtained, but it will increase the load of MySQL instances, and will consume query bandwidth and traffic when there are too many ProxySQL instances. All in all, in theory, this approach is inefficient and the architecture is not scalable.

So listen to the information.

Get the GTID value that has been executed in real time

Technically, it is easy to get the GTID values that have been executed so far, as long as you consume (analyze) the binlog in real time. However, this method requires that the machine where the ProxySQL instance resides is simulated as a slave library. If a single ProxySQL loads many MySQL instances, it is bound to consume more CPU. Furthermore, if many ProxySQL instances are deployed on a cabinet or switch, transmitting binlog can also test the bandwidth of the entire network. For example, if there are 4 clusters, and the master library in each cluster generates 40GB binlog every day and hangs 5 slave libraries and adds 30 ProxySQL instances, then each ProxySQL instance needs to simulate itself as a slave library of 24 master-slave instances. Total daily network bandwidth consumption of 30TB. For self-built computer rooms, it may be possible to add hardware directly vertically or horizontally, but for CVMs, huge traffic fees cannot be avoided every day.

ProxySQL Binlog Reader

Proactive questioning of GTID performance was considered a non-scalable and resource-intensive method in the previous paragraph. ProxySQL Binlog Reader tools emerge as the times require:

ProxySQL Binlog Reader is a lightweight that runs on the MySQL instance machine and tracks all GTID events by simulating itself as a process that connects from the instance to the MySQL instance.

ProxySQL Binlog Reader itself is a server, and whenever a front-end connection comes in, it begins to stream Binlog in an efficient and bandwidth-saving way.

Speaking of which, I think you are smart enough to guess that the ProxySQL instance plays the role of the client of the ProxySQL Binlog Reader service.

Real-time routing

ProxySQL Binlog Reader lets ProxySQL know the current GTID execution of each MySQL instance. Then, when the client executes a read-write separation query, ProxySQL immediately knows which slave server the request should be routed to. To say the least, even if all the current slave instances do not complete the execution of the GTID, then ProxySQL will understand that this is a master write, slave read query.

Meticulous settings to support multi-cluster

ProxySQL is highly customized, as are the features described in this article. The most important thing is that you can set whether the read request supports context consistency (targeting groups in ProxySQL). You can't finish setting context consistency. For example, for a read request, you need to specify that group B meets context consistency with group A (the group here means that the Hostgroup,A group in ProxySQL should be a write group, and group B is writable and readable, as long as you ensure that if the same connection is written on A, but when the read is routed to B, you can read the operation that has just been written). It not only supports context-consistent reading between master and slave, but also supports context-consistent read query (routed to An or B) in the highly available architecture of Amurb composed of slice cluster A + slice cluster B.

Request

Context-consistent reads based on GTID need to meet the following conditions:

Casual reads using GTID is only possible if:

Versions of ProxySQL 2.0 or later

The backend uses MySQL version 5.7.5 or above, and the old version does not support session_track_gtids

Binlog format is in line format

Turn on GTID

MySQL,MariaDB whose backend is limited to Oracle or Percona branch does not support session_track_gtids

Conclusion

ProxySQL can route query requests to slave instances where the specified GTID has been executed. This solution has excellent scalability, low network resource consumption, and has been verified in the real environment.

After reading the above, have you mastered the method of adaptive routing query for GTID tracking in MySQL? 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.