| Bug #100190 | Allow cleanup of gtid_executed, gtid_purged on a master, live | ||
|---|---|---|---|
| Submitted: | 12 Jul 2020 7:13 | Modified: | 13 Jul 2020 5:35 |
| Reporter: | Shlomi Noach (OCA) | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
| Version: | 5.7, 8.0 | OS: | Any |
| Assigned to: | MySQL Verification Team | CPU Architecture: | Any |
| Tags: | GTID, Replicaiton | ||
[12 Jul 2020 7:13]
Shlomi Noach
[12 Jul 2020 16:53]
Jeremy Tinley
In the spirit of other similar functions, I'd suggest PURGE GTID_UUID as the command. From a larger scale operations POV, it's fairly trivial for me to find the UUIDs of all active severs in my topology and then query the current leader to find the UUIDs that do not exist in my topology. From there, it's just a matter of running PURGE GTID_UUID from the difference of the two sets.
[12 Jul 2020 17:24]
Simon Mudd
As per the original bug report I have come across this quite a lot. I think it is good to provide some requirements on what we mean when we say we want a "cleanup" as otherwise any implementation could be surprising. My take on this would be * The purging of a UUID:<range(s)> should be done for a one or more UUIDs at a time in a single command and imply removing all the ranges for that/those UUIDs. Given I have seen hundreds of UUIDs in my servers' gtid_executed variable it makes sense to be able to purge more than one UUID at a time. * This command should itself trigger a GTID event as this allows us to track the "exact position/moment in time" the change happened. After this event the purged UUIDs no longer exist, but before they DO. The server MUST be aware of this in order to handle slaves correctly. * The command SHOULD replicate downstream to all slaves. What that means in a situation like Group replication I'm not 100% sure, but suspect that the group will together accept the "event" like any other so it will be pushed out to all members at the same time. * binlogs SHOULD NOT be removed / purged as they contain information which is crucial to the normal workflow in master/slave relationships and without binlogs any slave that is behind will be unable to connect to its master and continue replicating * a slave that connects to the master and during the exchange with the master indicates it knows the UUID(s) that have been purged is indicating that it wants to connect from a moment in time PRIOR to the purging of those UUIDs. This SHOULD be possible and the master SHOULD be able to synchronise with the slave at the given position and provide the subsequent binlog events (including the purge event which was written later, and which the slave will process "normally"). If the slave indicates that it only knows events which look like they are after the "purge moment" then the master should also be able to server the binlogs from the moment requested. If the master is unable to determine the exact moment in the binlogs then it SHOULD provide an error. * when binlogs containing the "purge events" are not longer held on the server there is no need for the server to have and specific knowledge of them and to all intents and purposes the only events the server needs to be aware of is for all UUIDs less those that have been purged. * I suspect that it is not a good idea to try to purge a UUID if this has already been done, there are binlogs prior to the purge still containing this UUID. This is because for slaves connecting with a delay it may not be possible to uniquely determine where to start streaming binlogs as the location from which to do that may not be unique. That is UUIDs are supposed to be unique so re-using them if you have tried to remove them is going to cause issues. * It SHOULD be possible to execute the "PURGE GTID_UUIDS" command more than once for different UUIDs at different points in time so the master may be aware of 0..n such "events" and be able to serve the binlog stream along the whole set of binlogs to slaves that connect at any point in time. * It probably DOES NOT make sense to PURGE GTID_UUIDS for a master's own UUID, or for a group replication cluster's UUID and it might be appropriate to not allow this as it's likely to cause problems. However, this restriction should NOT be applied if these commands are being received via replication as the receiving "slave" may once have been a master and it is quite valid to want to potentially remove that "old master's" UUID:<range>. I hope these thoughts make sense. I've been meaning to write up a more formal specification and while the above is not formal I think it pretty much identifies some of the things we have to think about and take into account.
[12 Jul 2020 17:53]
Shlomi Noach
Strong agree to Simon's assessment that this should be a binlog event. That is, we'd run the function/command on the master ("source" in new terminology), and this would be written to the binary log, to be applied by replicas. It's imperative that this is the case, so that lagging replicas, or replicas that happen to be offline, or replicas in process of restoring from backup would be able to pick it up.
[13 Jul 2020 5:19]
MySQL Verification Team
Hi, Bug #92836 is verified and pushed up the chain to the devs for fixing. Not sure how come the status of the #92836 remained the "need feedback" but the important thing is it's being triaged by the replication team. I'm setting this one as a duplicate of #92836 as there really is no point of having the same discussion in three places, I hope you agree. Kind regards Bogdan p.s. I'll see if we can push for some updates on the progress as I do understand how annoying this bug must be
[13 Jul 2020 5:35]
Shlomi Noach
> it's being triaged by the replication team. Thank you. Can you please clarify: - Where should we continue the discussion? - Whether the solution is going to be binlog event based (desired) or other (undesired and unuseful). I have no visibility into your thought process and whether suggested solutions are considered. What I fear is, that with lack of discussion, the team will come up with a solution that does not solve the problem in real production systems, so visibility is appreciated. Thank you!
[7 Apr 2022 14:14]
Wes Deviers
Is there also an interaction here with the now undependable show (slave|replica) status? We will often do cascading replication to perform online migrations. So you have a primary and replica set, and then you spin up a new primary with relay logging turned on, and replicas for it. Thus, the newest replicas have 3 GTID sets (or more, depending on how many times you've done this.) One of those GTID sets will never, ever, see transactions again because the old server has been destroyed. It appears that since seconds-behind uses these gtid sets in its calculation, a SHOW SLAVE STATUS will alternate between the slave-lag of the "real" GTID set that is replicating, and the one that has not seen writes in months or years. 0 -> 14 -> 0 -> 0 -> 0 -> 16 -> 0 -> 0 -> 0 Turning on multi-threaded slave writes seems to fix this problem, but if somebody is not paying attention in their monitoring "Seconds_Behind_Master: 0" comes up often enough that you can't use it for monitoring any more. But it looks like letting us operationally mark a gtid set in gtid_purged as "gone forever!" fixes both the display problem as well as a naive fix for seconds_behind.
