Can I prevent a galera cluster node from writing gcache.page files?

Question

I have the following setup: 5 hosts, each of them running two galera clusters. One of those two clusters has a production database, the second one a test database. Once a week (early monday morning), the test database gets cleared and filled from the production database.

Shortly before midnight, I use mysqldump to dump the production database, a few hours later, I drop the tables in test and re-fill them from the production dump.

Doing this while the whole galera cluster is running is quite unfeasable because it takes a lot longer than just filling a single node + resyncing the others (using wsrep_sst). So I shutdown 4 of the 5 databases first, fill the 5th, then start the other 4 again, one by one.

Now, my problem is, the single node still knows it's a galera node, so it'll log all the inserts to galera.cache and, when that one fills, to many many gcache.page files. Those files are never used though, as the other nodes, when they rejoin the cluster, will just do the wsrep_sst to rsync everything.

This is a problem because all those files mean a lot of unneccesary writes, and rsync picks them up, syncing them to the rejoining nodes (which takes more unneccesary time and disk writes), and it seems like each node mmap's all of them, resulting in the processes being much larger than they should be; my production mariadb processes are ~60 GB in size, while the test processes are ~250 GB.

So what I'm doing right now is, after filling the single test node, shut it down, remove all those gcache.page files, start it up again, then start the other nodes.

But I'd rather prevent those files from being written at all. Is there any way to do that?

Galera version is 26.4.10, mariadb version 10.6.5.

Answer 1

Plan A:

I don't have a solution to your Question, but contemplate the following alternative.

1. Toss the test nodes.
2. Take one of the production nodes out of the production cluster.
3. Turn that node into the first test node.
4. Then, as you are doing currently, populate the other 4 test nodes.

Meanwhile, use SST to rebuild the stolen production node. (I don't know whether to do this first, last, or in parallel.)

Plan B:

Another thought... Use LVM. But this probably means having separate disk partitions (or physical drives) for production and test. Or maybe a single partition, but with 2 live snapshots!

If you could snapshot all the nodes simultaneously, you could get an identical copy of production into test in a minute or two. Unfortunately, this would require a complex, coordinated, very brief, outage.

Back to a hybrid.

1. Take one node out of the production cluster.
2. Perform LVM snapshot (a minute or so)
3. Put that node back into production. (IST will quickly resync.)
4. Now use the snapshot as the first node of Test. (If you need to anonymize names, etc, this would be the most efficient step to do it.)
5. SST to get rest of Test.

Answer 2

As a solution in a similar environment I do the following:

1. Dump the production database.
2. Shutdown all the test nodes, except one.
3. On the active test node, comment(#) all the wsrep variables in my.cnf file and restart mysql as a single instance.
4. Import the database (If you have general log, bin log or slow log enabled, you can disable them temporary to speedup the process).
5. Uncomment (#) all the wsrep variables in my.cnf file and bootstrap mysql forming again the cluster.
6. Start the rest test nodes one by one.