Hi Gordan,
On 2026/02/10 11:27, Gordan Bobic wrote:
On Tue, 10 Feb 2026 at 05:34, Jaco Kroon via discuss
<[email protected]> wrote:
Hi,
On 2026/02/09 20:07, Gordan Bobic via discuss wrote:
On Mon, 9 Feb 2026 at 19:35, Xavier Figari via discuss
<[email protected]> wrote:
According to the MariaDB operator documentation, the snapshot is taken
as follows:
- Temporarily pause the MariaDB writes by executing a `FLUSH TABLES WITH
READ LOCK` command in one of the secondary Pods.
- Create a VolumeSnapshot resource of the data PVC mounted by the
MariaDB primary Pod.
- Wait until the VolumeSnapshot is provisioned by the storage system.
When timing out, the operator will delete the VolumeSnapshot resource
and retry the operation.
- Issue a `UNLOCK TABLE` statement.
If your snapshots are atomic (and they should be on any
snapshot-capable solution, or the snapshots are useless anyway), you
shouldn't need to do that.
Our experience has been that you're right. We still do flush tables
with read lock prior to making snapshots (using LVM, the thin variant on
newer systems, and desperately wishing we could convert our "thick"
volumes). We then use rsync to clone the snapshots off-host. We still
flush tables because we have encountered cases where somehow the backup
was unusable without it - I *suspect* it relates to a specific host
where disk IO was of such a nature (I really dislike virtual machines)
that we had to tune innodb to be less resilient in order to cope with
load.
The problem is that unless you are running with:
- innodb_flush_log_at_trx_commit=1
- sync_binlog = 1 (if binlog is enabled)
- sync_master_info = 1 (if slave)
writes can be out of order which causes a problem.
This explains our observations, thank you.
With ZFS, you can keep these set to 1, but at FS layer set sync=disabled.
This gives you the same performance benefit as disabling flushes at
application layer, but ZFS will preserve write ordering guided by the
flushes sent from the application layer so everything will still
always be point-in-time consistent. You might lose up to a few seconds
of writes, but the recovered state will be internally consistent (i.e.
redo log and binlog being in sync).
This is interesting, will look into that.
And in the case of galera it's also (as per my understanding)
less important to have all the relevant ACID safety settings enabled
since if a node crashes it'll simply SST from another again (although,
where we can we leave it on so that upon restart we can utilize an IST
rather where possible as this is much faster to restore).
The problem is that you need ACID settings enabled for backup
consistency, unless you specifically block writes and flush (flush
tables with read lock).
The problem with this is that on a busy system running long queries,
this can take minutes during which the database will be unwritable.
We do a two-stage for this:
FLUSH TABLES;
FLUST TABLES WITH READ LOCK;
Since we do this at relatively quite times, this will result in the
first FLUSH essentially prepping the latter, the latter is then usually
a lot faster (minutes vs seconds).
The lvcreate to create the snapshot is invariably fast. Especially in
the case of thin snapshots. Nor do these impact performance nearly as
bad as traditional/thick snapshots.
Given this usually runs late pm/early am, 3 to 5 seconds of blocking on
writes is considered acceptable.
InnoDB is crash-safe, so just taking a snapshot will still be
restorable, you'll just have to wait for redo log replay at startup,
which is not a big deal if the redo log is sanely sized.
Indeed. Assuming all settings are correctly set.
That is the key point. :-)
Regarding restoring galera - these snapshots will bring up a single node
(you will need to force it as it will be marked crashed, so you need to
set safe_to_bootstrap: 1 in grastate.dat upon restore).
If you are restoring from a backup, that backup needs to be internally
consistent. For backup purposes you cannot cut corners the way you do
for regular Galera operation.
And herein lies the kicker, I've stopped counting how many systems I've
been called in with "backups are killing our performance" or "it
crashed, backups are not working, how do we recover" where the settings
weren't correctly set (usually both innodb_flush_log_at_trx_commit and
sync_binlog gets set to 0 for "performance reasons").
Fortunately in most of these cases backups are made with
mysqldump/mariadb-dump - usually doing the read lock thing anyway
(rather than --single-transaction), so fortunately usually I get called
in before it's catastrophic, just want to know why their systems are
unusable during backup windows, in which case we get to sort out the
problem before it's a real problem.
Kind regards,
Jaco
_______________________________________________
discuss mailing list -- [email protected]
To unsubscribe send an email to [email protected]
