[MXS-2744] Switchover improvements Created: 2019-10-29  Updated: 2023-06-19  Resolved: 2023-06-19

Status: Closed
Project: MariaDB MaxScale
Component/s: mariadbmon
Affects Version/s: None
Fix Version/s: 23.08.0

Type: New Feature Priority: Major
Reporter: Wagner Bianchi (Inactive) Assignee: Esa Korhonen
Resolution: Fixed Votes: 7
Labels: None

Issue Links:
Relates
relates to MXS-4226 Force a Switchover Closed
Sprint: MXS-SPRINT-183, MXS-SPRINT-184

 Description   

Rogue transactions executed by old connections during a switchover prevent the old master from rejoining the cluster. To prevent this, active transactions should be killed during failover. The transparent switching of the master can be achieved with a combination of waiting for transactions to commit and using transaction_replay to migrate them to the new master.

Original description:


As per the chat we had on our meeting to discuss issues about MaxScale and the switchover, this is for documenting what we discussed. The case is that our customers are running native GTID based replication (required by the MaxScale automatic features) used to execute the switchover to perform a rolling upgrade on servers.

Most of the time, when we execute the below command...

maxctrl call command mariadbmon switchover <monitor-name> <new-master>

...we have replication broken due to having the former master, now a slave, with GTIDs in a most advanced position as the current promoted master. So, neither replication and grid_strict_mode works at this point. The conversation went to the following question:

How do we deal with a long transaction, not committed yet, running during the switchover?

  • Wait for the long transaction to finish?
  • Have the transaction replay (2.3++) and kill the master to force the failover?
  • Have the transaction replay (2.3++), set maintenance --force, force the failover?

We have these options available so we can exercise these.



 Comments   
Comment by Massimo [ 2019-10-31 ]

When a switch happen in a intensive write environment, there are few things to keep in mind
1- is anyone using root/super privileges at the time the switch is request? by passing the read-only, maxscale has the privilege to check the processlist and the super privileges as well if anyone is using it
2- At the moment you monitoring the GTID there could be a query that is performing and increase the gtid even if dont see it
3- As i reported already, it seems a strong approach is the best starting point: on the db server primary, all connections should be cut, and if you want to redirect to a slave you can do at maxscale level, no leave connection open just for select on the previous master. At the moment connection remain
in a very busy env, switch master:
1- apply read_only to the PRIMARY (Make all non-temporary tables read-only, with the exception for replication (slave) threads and users with the SUPER privilege )
2- KILL all connections on database related to application ( do twice ina row) -> keep in mind that you could also have connections come direct to the server. not using maxscale, and maxscale should be able to coop also with those!
3- check not new connections are getting open, include root or super/privileges one
4- take the position
5- check there are is no changing position anymore
6- go to the secondary that is going to be promoted and check if the position is the same,besure all the relay has been consumed.
7- at this ( let say 1 microsecond) time, no one is able to write anywhere anymore
8- deal with reset slave all on the new master
9- set the replication on old master and start slave
10- check all topology is in order
11- remove read_only on the new master and give it to maxscale
If you could share the exactly step maxscale does it will be simple, i am sure there is any check about super privileges and root, even maxscale is able to check. Even more,we know atm, maxscale does not kill any connections after read_only apply, we need to have a better way to perform the switch.

Comment by Rick Pizzi [ 2019-10-31 ]

When performing a switchover, we could wait for the transaction to complete, but at the same time we should block new ones from starting. And if the long transaction takes time, we are actually freezing the master. This is what is commonly done with backup, when FTWRL is issued to freeze non transactional tables when taking the backup position.
So I would rather kill any running transaction, which will be rolled back. A properly written application should retry the transaction if we kill it the proper way. The truth is, rarely applications do retry transactions, because it's actually easier to err out But this is not our fault, isn't it.
Don't know how transaction replay works in 2.3 and newer, if that can be done transparently for the application I would go for it.

Rick

Generated at Thu Feb 08 04:16:22 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.