Principle of Function
Principle of Implementation
As shown in the figure, a read-write separation middleware consists of two high-performance Proxy nodes and the Ucloud distributed load balancing product ULB.
The two Proxies are deployed in dual-active mode, with the front end using ULB for load balancing and disaster recovery to ensure no single point in the entire system. Customers can customize the distribution method of read requests (for the configuration method, see below), and Proxy nodes distribute read requests according to customer configurations.
The way the read-write separation middleware handles business requests is straightforward, with three basic principles:
1. Identify Select SQL from business requests. Only Select SQL is considered for read-write separation;
2. If this Select SQL is in a transaction, send the Select SQL to the master node. If the Select SQL is not in a transaction, then, according to the read request distribution strategy, send the Select SQL to the master node or slave node.
3. For some statements that must be broadcast, such as Use database, Set Session variables, etc., the middleware broadcasts them. If the broadcast is not successful, the client connection is interrupted to strictly ensure the data consistency of each node.
And some corrections for special circumstances:
1. Select statements involving locks, such as Select For Update, Select Lock, etc., will be sent to the main node.
2. Divide the variables in the Set statement into three types: Session, Global, User. Set Session, Set User variable statements will be broadcast; considering the data consistency between nodes, Set Global will only be sent to the main node. The subsequent Select statement containing global variables will also be sent to the main node.
Function Limitations
1.MySQL Protocol Limitations
1.1 SSL encryption is not supported
1.2 Compression protocol is not currently supported
1.3 Binding of ports other than 3306 is not currently supported, and the ports of UDB master and slave nodes must also be 3306
2.SQL Limitations
2.1 support savepoint statements (this statement will be sent to the master node), but rollback to savepoint is not currently supported
2.2 XA transaction commands are not currently supported
2.3 Lock Tables/Unlock Tables will be sent to the main node, and the Proxy layer will not have any Lock state. Therefore, the lock caused by Lock Tables does not affect the slave node.
2.4 Stored procedures and Select statements after stored procedures are all sent to the main node. Such as:
call udb\_test('000001',@pp,@qq); select @pp,@qq; select \* from t1;The above two Select statements will all be sent to the main node.
2.5 show processlists, Show master/slave status, kill query, COM_PROCESS_INFO, COM_STATISTICS commands are currently only forwarded to the main node, A richer system management command is being developed for middleware and database system management scenarios.
2.6 COM_TABLE_DUMP and COM_CHANGE_USER protocols are not currently supported.
3.Special Note for Set Statements
3.1 Set Session, Set User variable statements will be broadcast to the master and slave nodes, if the broadcast fails, Proxy will disconnect from the client, to revoke the data inconsistency caused by the failure of the broadcast; considering the data consistency between the nodes, Set global variable statements will only be sent to the main node. Subsequent Select statements containing global variables will also be sent to the main node.
3.2 It is not allowed to appear Global variables and Session, User variables in a single Set statement.
4.Scenarios Not Recommended for Using Read-Write Separation
a. All SQLs of the business are transaction SQLs (all SQLs are included in the transactions), as transactions can only be routed to the main node, so in this scenario, UDB read-write separation cannot separate read requests
b. The business uses a large number of stored procedures. Since stored procedures can only be routed to the main node, so in this scenario, UDB read-write separation cannot separate read requests
c. Short-term connection to access read-write separation is not recommended for businesses. The logic of UDB read-write separation middleware handling business database connections is: Every time a business initiates a connection to the read-write separation middleware, read-write separation will establish a connection to each master and slave node for subsequent SQL forwarding. Therefore, if the business uses a short-term connection to access read-write separation, and the frequency of the business initiating short-term connections is very high, the read-write separation middleware will frequently establish-terminate connections, creating a large number of TIME WAIT TCP connections within the process, occupying or even exhausting the handle count of the process, leading to the inability to establish new incoming connections.
