In my current setup, I’m managing a two-node Percona XtraDB Cluster on Ubuntu 16.04.6, running MySQL - mysql Ver 14.14 Distrib 5.7.23-23, along with xtrabackup version 2.4.12, based on MySQL server 5.7.19. However, I’ve encountered a persistent problem. Whenever there’s a failure or an unexpected shutdown in MySQL, the affected node fails to utilize IST (Incremental State Transfer) and defaults to using SST (State Snapshot Transfer). This results in data deletion on the node, leading to time-consuming recovery from scratch. Given the substantial 3TB of data, I’m looking for ways to recover a node using IST or force its utilization. I’m also interested in additional manual steps that could assist in promoting IST after a crash or ungraceful MySQL shutdown.
To address this issue, I’m in the process of adding a third node to the cluster, following best practices in a Percona cluster. This will enable me to experiment with various recovery mechanisms without jeopardizing the two nodes responsible for the main production workload. However, this addition has caused a significant spike in Memory Usage of MySQL on the master node, even though the master isn’t the donor for the newly added node. I had to shut down the extra node due to memory constraints on the master. My primary goal is to find a solution to add a new node without risking the current workloads.
I would appreciate your insights and assistance in resolving these challenges I’m encountering.