After triggering HA, the web operation maintenance, but the proxysql status shows abnormality. #560
Comments
|
Yes by default the master is not in the reader group can you try |
|
Hi svaroqui Thank you for your quick response, proxy-servers-read-on-master=true helped me, but I still have the following issues that need assistance: Q1: When I initially start repman, I can perform maintenance on the master, but when I trigger HA and perform the same operation, I get an error, and the weight and max_replication_lag for hostgroup_id = 10 change. The proxysql status is as follows: Moreover, regardless of whether I perform maintenance on the master or slave, the weight and max_replication_lag for hostgroup_id = 10 are changed. I tried adding proxysql-janitor-weights and found that it can control the change in weights, but I still can't find a parameter to control max_replication_lag. Any thoughts on why this is happening, or how to fix it? I'm happy to provide more information if needed. |
|
Hi @peterpan104, max_replication_lag will not affect anything when server is a writer hostgroup. it will only affect the reader hostgroup as it need to check the replication lag before fetching read_only data |
|
I will make an enhancement for the proxysql max_replication_delay and weight options in proxysql. |
|
hi ahfa92 I have a few more questions that I hope can be answered: Q1: The results I get when performing maintenance on the master during the initial repman startup and after triggering HA are different. What are your thoughts on this? Q2: When performing maintenance on the slave, some settings for the writer hostgroup were also modified, which I found strange. Shouldn't repman only make adjustments to the slave host:port being operated on? |
|
hi @peterpan104, |
|
I never wanted to used weight for proxysql only for the janitor proxysql that was added to open access to multiple clusters inside an infrastructure like janitor proxysql ( map user to specific cluster )-> multiple proxysql -> master -> slave |
|
hi @ahfa92 Thank you for your patient response! The following shows my operation process and the status of proxysql. Q1:The results I get when performing maintenance on the master during the initial repman startup and after triggering HA are different. What are your thoughts on this? The proxysql status is as follows: The above is the status of proxysql when I initially started repman. My default mysql_servers only existed in hostgroup_id = 11, but proxysql will add the server with read_only = 0 to hostgroup_id = 10. Now when I operate master online -> maintenance in repman, The proxysql status is as follows: we can see that the row with hostgroup_id = 10 has disappeared, and the status of the master (172.24.47.131) has changed to OFFLINE_SOFT, which is what I expected. Now, when I change the master from maintenance -> online, The proxysql status is as follows: we can see that the row with hostgroup_id = 10 has reappeared, which is also what I expected. Now I manually trigger HA. The proxysql status is as follows: We can see that the weights of the two servers in mysql_servers have been swapped, which is what I modified through the repman HA trigger script, and everything is as I expected. Now, when I change the master online -> maintenance, The proxysql status is as follows: I see that a new row has been added to mysql_servers, and the max_replication_lag for hostgroup_id = 10 has changed from 500 -> 0. In reality, the weight will also be changed, but I have set the repman parameter proxysql-janitor-weights=1, so the change is not visible. Additionally, the servers are still in the online state. These changes are not what I expected in runtime_mysql_servers. Now, when I change the master from maintenance -> online, The proxysql status is as follows: the status in mysql_servers remains unchanged. Although both my read and write groups exist, mysql_servers and runtime_mysql_servers have deviated from my expectations, and the fact that the same operation yields different results has left me puzzled. I suspect that this operation causes repman to write to the mysql_servers table in proxysql, but I can't find a way to verify this. The above is the operation process for the first question. Now, let me proceed to demonstrate the operation process for the second question. Q2:When performing maintenance on the slave, some settings for the writer hostgroup were also modified, which I found strange. Shouldn't repman only make adjustments to the slave host:port being operated on? Now, let me restore the proxysql status and restart repman, and then follow the same operation steps up until after triggering HA. The proxysql status is as follows: We can see that everything is as I expected. Now, when I change the slave online -> maintenance, The proxysql status is as follows: The result is the same as when performing maintenance on the master. A new row has been added to mysql_servers, and the max_replication_lag for hostgroup_id = 10 has changed from 500 -> 0. The proxysql status has deviated from my configuration, and the fact that operating on the slave (172.24.47.131) also modified the settings for the master (172.24.47.132) is puzzling and not what I expected. Do you have any thoughts on these two issues? |
|
hi @svaroqui I want to implement read-write separation through the weight parameter in mysql_servers, so that most SELECT queries will go to the slave, and only when the slave is down, the query will go to the master. Maybe there is a better way to set up repman to achieve the effect, but I haven't found it yet. :((( |
|
Hi @peterpan104, thank you for the reply. I already found the root cause. It is because of the function only create a row with minimum requirement to proxysql. The impact is the max_replication_lag is not set, and it will take default value of insert which is 0 |
|
Hi @ahfa92 Thank you for the reply. I think when repman operates maintenance, it should only update the status of mysql_servers without modifying other places. In this way, whether repman needs to modify the parameters of proxysql through parameters is not so important, right? There may be other situations that need to be set up that I have not considered, but I think in most cases proxysql should not be interfered with too much. |
|
Replication manager designed to handle more than one slave and sometimes it use multiple masters. So the process will delete the master first and re-pick from readers. To prevent master as reader you can use As you can see in router/proxysql.go it will only change the variables using update query if you are not using read-on-master. Maybe we can develop more specific configurator in GUI for proxysql in later versions. |
|
hi @ahfa92 Maybe the hostgroup_id doesn't need to be updated? I think this might be causing the problem. The following shows my operation process and the status of proxysql. repman parameter settings Now,I restore proxysql status and restart repman. This is the initial state of mysql_servers I set the msater maintain,it will only update the status column of mysql_servers I set the master online and the slave settings maintenance, it will only update the status column of mysql_servers. Now I trigger HA, the master will be transferred to server 172.24.47.132,And mysql_servers will be as follows. I set the master maintenance, it will only update the status column of mysql_servers. I set the master online,hostgroup_id is updated to 10,it should not be updated. Now,I restore the proxysql state to when HA is complete. I set the slave (172.24.47.131) maintenance, In addition to changing the slave's status to offline_soft, also change the msater's hostgroup_id to 10,I think this is a wrong update. Finally,I set the slave (172.24.47.131) online,At this time mysql_servers has deviated from my settings. The above shows why hostgroup_id shouldn't be updated , and |
|
Hi @peterpan104 I will also check that too. Thanks for informing us. |
|
Can you show me the dashboard with log level info? It should have some kind of clue what process is happening when the hostgroup is changing. |
|
Hi @ahfa92 after HA,set the master maintenanceafter HA,set the master onlineafter HA,set the slave maintenanceafter HA,set the slave online |
|
I never study putting the master in maintenance any reason for doing this it would break all writes for a long time ? Does not mean we should not cover that case as well. |
|
Normally, there aren't many chances to perform maintenance on the master, but I have to consider every possible scenario. The main issue now is that before triggering HA, no matter how I perform maintenance, all the results meet my expectations. However, after triggering HA, performing maintenance on either the master or slave causes the mysql_servers table in ProxySQL to deviate from my default settings. I think this result is unreasonable, and this is what I'm trying to express. |
|
Thank you for the feedback. I will make a fix ok this one. Hope I can
commit the fix this week.
…On Mon, Apr 29, 2024, 3:47 PM peterpan104 ***@***.***> wrote:
Normally, there aren't many chances to perform maintenance on the master,
but I have to consider every possible scenario.
The main issue now is that before triggering HA, no matter how I perform
maintenance, all the results meet my expectations. However, after
triggering HA, performing maintenance on either the master or slave causes
the mysql_servers table in ProxySQL to deviate from my default settings. I
think this result is unreasonable, and this is what I'm trying to express.
—
Reply to this email directly, view it on GitHub
<#560 (comment)>,
or unsubscribe
<https://github.com/notifications/unsubscribe-auth/AIM5KTEPZCQMLSQEUWZEXXTY7YCIZAVCNFSM6AAAAABGSJFUNCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDAOBSGE3TSMJWHA>
.
You are receiving this because you were mentioned.Message ID:
***@***.***>
|
|
Agree our priority is.to avoid loosing user setting, this is indeed is not acceptable. thanks for raising the issue |
|
I'm glad this issue will be fixed. Thank you again for both of your reply! |
|
Hi @peterpan104 , |
|
hi @ahfa92 The issues mentioned above have not been resolved yet, Additionally, I used tcpdump to capture what settings repman made to proxysql, showing as follows: I must emphasize again that UPDATE mysql_servers should not modify hostgroup_id, and I think this is the cause of the problem! During the trigger switchover, it also updates mysql_servers, but since I've set failover_script, the proxysql configuration doesn't deviate from my defaults. After the trigger switch, repman's operations on proxysql increased significantly, and I am still skeptical about these additional operations. I hope the above process can help you better understand the problem. Maybe the query in router/proxysql.go should be modified as follows: |
|
Hi @peterpan104 We already committed for the HA, as for maintenance mode we already merged and should be able to see in next release. |
|
Hi @peterpan104 This thread is for ProxySQL maintenance mode and HA. For binary timestamp error will be discussed in #579 |
|
hi @caffeinated92 |
|
After I installed the latest version of repman, proxysql no longer deviates from my default settings. But as I mentioned above ,after triggering the switchover, as well as performing maintenance operations on the master or slave, the following functions are used: Although repman did not successfully modify mysql_servers as I got the following error: When I start repman for the first time, operating maintenance will not call these functions. Is there any way to avoid using function? |
|
Hi @peterpan104, can you give me the state of mysql_servers and runtime_mysql_servers in the proxysql DB before and after switchover? Thank you. |
|
Well UNIQUE constraint failed. |
|
When I start repman for the first time, operating maintenance will not call these functions? Is there any way to avoid using function? did you try disable proxysql-bootstrap=false ? |
|
I will add more checking on read on master |
|
@caffeinated92 The reason I made these changes is to ensure that the mysql_servers on any node in the proxysql cluster would be the same. If I followed the old configuration (please refer to the configuration mentioned in the first comment), node1's mysql_servers would only have two rows. But node2 and node3's mysql_servers would both have three rows. I think this is because the proxysql cluster synchronizes data through the runtime status, so even if the three nodes have the same runtime_mysql_servers, the mysql_servers would be different. So when repman triggers the functions (SetReader, SetWriter), it would cause node1 to successfully execute the update, but node[2-3] would get Error 1045, and the mysql_servers would deviate from my default settings again, causing the proxysql cluster to become ineffective. That's why I set up the new mysql_servers to avoid this situation. However, I still hope this can be resolved because repman's operations when initially started do not trigger the functions (SetReader, SetWriter), but they start triggering after a switchover, which makes me confused. |
|
Hi @peterpan104 , I saw you're using: While in your mysql_servers you're having master as reader. That's inconsistent. Can you set it to true and see what happens? If you're using false, means you'll not add master in hostgroup 11 If you don't want your proxysql to be managed by Signal18, you can simply disable proxysql config. |
|
@svaroqui |
|
Hi @caffeinated92 The following is the operation of repman on proxysql: Thanks for your assistance. |
|
Hi @peterpan104 , Glad to know your issue is solved. Actually, I think there are too many actions while switching maintenance mode. I will check if I can optimize the flow, but this is not the priority since it's not operation issue. |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
hi
As title,
Replication Manager : v2.3.14
ProxySQL: v2.5.5
The following are my basic parameter settings for repman and proxysql:
When repman starts initially, the web operation will set 172.24.47.132:3306 -> maintenance
The proxysql status is as follows:
After I trigger HA, the master is transferred to 172.24.47.132:3306, which triggers the script to adjust the proxysql status as follows:
At this time, when I perform a web operation to set 172.24.47.131:3306 -> maintenance, the row with hostgroup_id=11 and hostname=172.24.47.132 disappears from the proxysql status
The proxysql status is as follows:
Any thoughts on why this is happening, or how to fix it? I'm happy to provide more information if needed.
The text was updated successfully, but these errors were encountered: