We have a two server Percona setup with Orchestrator and ProxySQL (also implementing TDE with a HashiCorp cluster for key management). On heavy load our master is not responding to Orchestrator and so the slave is promoted. Q1: what is a good way to prevent this? We are looking at lessening the load in various ways, but it seems that this shouldn’t happen just because Percona’s master server is very busy.
Q2: We are having trouble getting the old master to resync with new master. We would like this to be automatic, but our admin is finding he needs to manually dump the new master and reload into old master. In his words:
Every day after {the busy time that causes Orchestrator to promote slave to master} i am needing to re-get the replication sync going by doing a full backup because I have tried numerous times to just do a join process but that is always whackamole as it will be like “I can’t delete because of duplicate primary keys” so i fix that then it stops almost immediately with a “I cannot delete rows x because I cannot find it” and I cannot see a known way of knowing when there was last commonality between the two percona servers.
My sense is that old master has gotten out of sync with new master by continuing to process transactions after new master is promoted. When attempting to sync old master to new master, is there a way to have old master rollback transactions until the last one processed to new master prior to new master being promoted? Or some other such mechanism. We just want to be doing the normal best practices for this very common setup and scenario.
Hello @JoeMurray,
Q1: what is a good way to prevent this?
Well, isn’t that the purpose of failover? 
If the source becomes unresponsive, promote a replica. If you really want to prevent this, just remove the server from the Orc config for automatic failover. When you do that, any failover would need to be manually triggered.
Q2: We are having trouble getting the old master to resync with new master.
What your admin describes, in my words is, “welcome to being a DBA” There are tasks that every dba must do when failover happens, and he’s correct, and the activity of dumping/restoring a failed source is a pretty standard task. There are some things that can be done, however, to ease this.
Use a hook in Orc to execute a script which updates the proxysql config and make the failed source OFFLINE. This will prevent ProxySQL from continuing to send it traffic. ProxySQL knows nothing about replication topology. So if it sees your old source online, whether it is a source or a replica, it will continue sending traffic. Orc should also be attempting to put the failed source into read_only=1, I would verify that.
Ensure you are using GTID for replication, as this will allow easier “reverse replication” setup once the old replica becomes new source.
You could also configure source-source replication between the two. That way anything written to A replicates to B and anything written to B replicates to A (make sure to set auto_increment_increment and auto_increment_offset correctly if you do this). This + GTID should make for a fairly robust setup that should not require manual resync each time it fails over.
is there a way to have old master rollback transactions until the last one processed to new master
No, there is not.
Have your admin look at the native CLONE plugin. It’ll save him time/effort the next time he needs to resync.
Hi Matthew,
Thanks for your response. The aim of the first question was to determine if there are configurations we could/should change in order to affect the threshold that Orc uses to determine that the current primary is so unhealthy that a secondary needs to be promoted. For example, can we increase the length of time Orc waits before deciding to switch to a different primary?
In our case, we can further clarify that the failure is occurring nightly when we are backing up both the primary and secondary with this command:
xtrabackup --backup --target-dir=/data/backups/mysql --user=root
It seems the backup process puts the db into a lock during the backup (to retain consistency no doubt), and the resulting delay in responding causes Orc to try to switch master. We believe that other queries are queued during the backup processing, and that these queued queries are executed on the old primary prior to Orc’s instruction to set read_only =1 which turns it into a secondary. By contrast, the current secondary executes set read_only=0 right away, prior to receiving the log entries resulting from the old primary executing the queued queries. So we consistently have old primary out of sync with new primary. I suspect we are also losing those queued transactions.
Should we only run backups on secondary, and perhaps use incremental backups, in order to prevent the master being delayed longer than Orc’s timeout period?
FWIW, we are using GTID for replication. Thanks for the suggestion to use the CLONE plugin, we’re looking into it.
You should not need to backup both. That’s sorta the point behind having a replica, so you can take backups without impacting the source/prod. Use pt-table-checksum to ensure the source and replica are byte-for-byte the same, then only backup the replica.
That should not happen. PXB uses LOCK TABLES FOR BACKUP which is a lightweight backup command for backing up non-InnoDB tables. Do you have many MyISAM tables? For all other transactional tables, a simple transaction is used, thus no locking required. Can you please post the first 100 and last 100 lines of PXB output from a backup?
I would add --parallel 4 to your PXB command to make the backup go faster.