Distributed | Why is the common read sql sent to the master instance in the scenario of dble read-write separation

Author: Ma Yingle

A member of the R&D team of Aikesen, responsible for the testing of mysql middleware. I am a testing technology enthusiast, welcome to try the new features of dble~

Source of this article: original contribution

*The original content is produced by the open source community of Aikesheng, and the original content shall not be used without authorization. For reprinting, please contact the editor and indicate the source.

---

Configure rwSplitMode=3, why the read sql is sent to the master instance

Problem source and background

The question comes from the feedback from the community user @大鹏 of the dble community QQ group (QQ:669663113), asking:

• When dble's read-write separation is configured, queries are always routed to the master node regardless of the value set to rwSplitMode.
• Determine the way to send the master and send the slave: log in directly to 3 mysql nodes, execute show processlist to check, the master node has it, but the slave node has no query statement

Learned in further communication:

• The pure read-write separation function of dble is used
• The query statements are all select statements associated with multiple tables, and there are simple references to the view.

Subsequent local recurrences

Local reproduction I

In the pure read-write separation scenario of local testing, select is sent from the instance, no problem

However, when rwSplitMode=3 on the community user side, such reading sql can send the main instance stably. The screenshot of db.xml is as follows

The information that can be obtained from the screenshots provided by the other party is:

• rwSplitMode=3
• The heartbeat sql is show slave status , and there is an open delay detection delayThreshold="100"

In the introduction document of db.xml is this:

Currently reviewing the above information, the suspect points in this scenario are:

1. Check whether the heartbeat of the slave node is abnormal, show @@heartbeat;
2. Whether the master-slave replication status is normal, you can directly issue it on the slave instance show slave status Check whether the master-slave replication status is normal
3. To check whether the delay time of master-slave replication exceeds the threshold, you can refer to the show slave status Seconds_Behind_Master indicator of ---03ac841333798f0ea68b060d392f9884---, related Q&A: #3104

A few days later, the same community user @大鹏 asked a new question

dble If the backend connects to MySQL MGR, what should be configured in the heartbeat statement in db.xml? Use show slave status, there is a warning message when starting

At that time, it was suspected that when the mysql version was not lower than 8.0.26, the update of the master-slave replication state (Slave_IO_State and Slave_SQL_Running_State) caused dble's misjudgment of the replication state (subsequent proofs were not for this reason, see: Local Reproduction II ),
Ignore the key information that community users use mysql's group replication,
In fact, if you search according to the keyword of the error report, you can retrieve the Q&A of this error report: After starting dble, the log will always report "found MySQL master/slave Replication err"

Local Reproduction II

In the subsequent local test process, it was found that dble is normal when using the ordinary master-slave replication of mysql8.0.26 to do the mysql backend with read-write separation:

• Heartbeat status is normal
  
• There is no related error in dble.log
  
• Read sql normally sent to slave
  

Therefore, the suspicion at that time was wrong, dble supports the master-slave replication of the backend mysql8.0.26, and the read sql can be sent from the slave normally.

Local Reproduction III

Returning to this problem, if you try MGR, you can reproduce the errors found by community users, and read and write SQL will be sent to the primary instance.

Test configuration: build 3-node MGR single-master mode as the back-end dbGroup, configure the heartbeat statement to be show slave status； , start dble

Although dble can be started normally, and the output of show @@heartbeat; has always been normal, the same error as provided by the above community users can be found in the log

dble.log

In the pure read-write separation scenario, retest, read and write SQL will be sent to the primary instance

Read and write SQL will be sent to the primary instance

summary

Reading sql is not sent from the instance as expected, you can start the investigation from the following aspects (Q&A has been added)

1. First, determine whether the sql sender meets the current expectations in the scenario of pure read-write separation of dble of the corresponding version. For details, please refer to the official documentation and Q&A.
2. It is determined that it should be sent to the slave, but the master has been sent. You can check whether the heartbeat status is normal. (According to this problem, in addition to observing show @@heartbeat , it is better to check whether the dble.log exists. other problems)

3. When the heartbeat sql used is show slave status , 2 points need to be considered:

   • Whether the configuration delay detection parameter is enabled or not, when there are delay-related parameters, when the master-slave replication delay exceeds the threshold, reading SQL to send the master instance is in line with expectations
   • It may be that the result of show slave status； does not meet expectations (the high probability is that the replication status is abnormal)

4. If you use the latest version of dble, version 3.22.01.0, you also need to consider whether there is a stickiness of the read-write separation backend instance in the pure read-write separation scenario

   Does dble support group replication? (The following content has been updated in the corresponding Q&A)

   Refer to Q&A #3184 , but in view of the above, when using MGR, you need to pay attention to avoid using show slave status as a heartbeat statement , the reason is that the return value of this sql is empty in group replication mode and cannot be used to judge group-member The replication status is OK or not. If show slave status is configured as a heartbeat statement in MGR mode, the following effects will occur:

5. After starting dble, the log will always report "found MySQL master/slave Replication err"
6. When the load balancing rwSplitMode that starts the read operation is 1/2/3, the read operation cannot be balanced to the slave (there will be an obvious error for 1, but when the configuration is 2, 3, the read sql will be silently sent to the master, and it will not work. load balancing to read operations)

Epilogue

Thanks to the community user @大鹏 for being able to actively feedback the problem in the community QQ group when encountering a problem, so that the problem can come to everyone's vision.

I would also like to thank the dble R&D classmates who review the weekly questions. They are keen-eyed and connect the same user's question clips for several days. They are scientifically rigorous and put forward conjectures, so that the real cause of the problem can surface.

Finally, when you find something that can be improved, or the need for high-frequency usage scenarios, you are welcome to raise an issue on the project for feedback! Thanks~!