Common solutions for Mysql read-write separation expiration

The pitfalls of MySQL read-write separation

The main goal of read-write separation is to share the pressure of the main database, and the client selects the backend database for query. Another architecture is that there is an intermediate proxy layer between MYSQL and the client. The client connects to the proxy, and the proxy determines the distribution route of the request based on the request type and context.

• Client direct connection solution: Because there is one less layer of proxy forwarding, the query performance is slightly better, and the overall architecture is simple, making it easier to troubleshoot problems. However, this solution requires understanding the backend deployment details, so when operations such as master-slave switching and database migration occur, the client will be aware of it and needs to adjust the database connection information.
• With proxy architecture: more friendly to clients. The client does not need to pay attention to the backend details. Connection maintenance, backend information maintenance and other tasks are all completed by the proxy. But in this case, the requirements for the back-end maintenance team will be higher.
  

Regardless of the architecture used, due to possible delays between the master and slave, if the client initiates a query immediately after executing an update transaction, and if the query selects the slave, it is possible to read the status before the transaction update. We will call this phenomenon of "reading an expired state of the system on the slave library" "expired read" for the time being.

Solution 1: Force the main database solution

Query requests are divided into two categories:

• For requests that must obtain the latest results, they are forced to be sent to the main database. For example, on a trading platform, after a seller publishes a product, he or she must immediately return to the main page to see whether the product has been published successfully. Then, if this request needs to get the latest result, it must go through the main database.
• Only requests that can read old data are sent to the slave database. On this trading platform, when buyers browse the store page, it is acceptable for them to see the latest products a few seconds late. Then, such requests can go through the library. The biggest problem with this solution is that it may encounter the requirement that all queries are not "expired reads", such as financial services. In this case, read-write separation must be abandoned, and all the pressure is on the main database. The following scheme was adopted.

Solution 2: Sleep Solution

After the master database is updated, sleep is performed before reading the slave database, similar to executing the select sleep (1) command. The assumption of this solution is that in most cases the master-slave delay is within 1 second, and a sleep can highly likely obtain the latest data.

Taking the example of a seller publishing a product, after the product is published, Ajax is used to directly display the content entered by the client as a "new product" on the page instead of actually querying the database. In this way, the seller can use this display to confirm that the product has been successfully released. By the time the seller refreshes the page and checks the product, some time has actually passed, thus achieving the purpose of sleep and solving the problem of expired reading.

Solution 3: Determine whether the master and backup have no delay:

The first method: First use the value of the seconds_behind_master parameter in the show slave status result to measure the length of the master-slave delay. First determine whether the parameter value is 0. If it is not 0, you must wait until the parameter becomes 0 before executing the request.

The second method: comparison sites ensure that there is no delay in the primary and backup.

• Master_Log_File and Read_Master_Log_Pos indicate the latest position of the master database read;
• Relay_Master_Log_File and Exec_Master_Log_Pos indicate the latest execution position of the standby database.

If the values ​​of Master_Log_File, Relay_Master_Log_File, Read_Master_Log_Pos, and Exec_Master_Log_Pos are exactly the same, it means that the received logs have been synchronized.

The third method: Compare GTID (Global Transaction ID) to ensure no delay between master and slave

• Auto_Position=1 means that the GTID protocol is used for this master-slave relationship.
• Retrieved_Gtid_Set is the GTID set of all logs received by the slave database;
• Executed_Gtid_Set is a set of all GTIDs that have been executed on the standby database.

If the two sets are the same, it means that the logs received by the standby database have been synchronized.

Scheme 4: Equal master library site scheme

select master_pos_wait(file, pos[, timeout]);

This command is executed on the slave database. The parameters file and pos refer to the file name and position on the master database. Timeout means that this function waits for a maximum of N seconds.

• The normal result returned by this command is a positive integer M, which indicates how many transactions have been executed from the start of the command execution to the completion of applying the binlog positions indicated by file and pos.
• If an exception occurs in the standby database synchronization thread, null is returned.
• If the wait exceeds N seconds, return -1
• If it is found that it has been executed at the beginning of execution, it returns 0

As shown in the figure: first execute trx1, then execute the logic of a query request. To ensure that the correct data can be found, we can use

This logic

1. After the trx1 transaction is updated, immediately execute show master status to obtain the File and Position currently executed by the master database;

2. Select a slave database to execute the query statement;

3. Execute select master_pos_wait(File, Position, 1) on the slave database;

4. If the return value is a positive integer >= 0, execute the query statement in this slave database;

5. Otherwise, go to the main database to execute the query statement.

Here we assume that this select query will wait for a maximum of 1 second on the slave database. Then, if master_pos_wait returns an integer greater than or equal to 0 within 1 second, it is ensured that the query result executed on the slave database must contain the data of trx1.

5 Executing query statements on the main database is a common degradation mechanism for this type of solution. Because the delay time of the slave database is uncontrollable and cannot be waited indefinitely, if the wait times out, you should give up and check the master database. According to our requirement of not allowing expired reads, there are only two options: one is to give up after timeout, and the other is to switch to the main database for query.

Concurrent connections and concurrent queries

The innodb_thread_concurrency parameter controls the upper limit of concurrent threads of Innodb. Once this value is exceeded, new requests will be put on hold.

• The thousands of connections seen by show processlist are concurrent connections, while the statements currently being executed are concurrent queries. Concurrent connections have little impact, they just take up more memory, while concurrent queries are the CPU killer.
• After the thread enters the lock wait, the concurrent thread count will be reduced, that is, the thread waiting for the row lock is not counted in the concurrent query. Because waiting no longer consumes CPU

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.