[RndTbl] mysql update delays when no rows match, when backup running

[Trevor Cordes]

On 2023-02-04 Adam Thompson wrote:
> 
> I would try --single-transaction and test; I don't have any
> convenient way of testing it right now.

Oooh... --single-transaction is perfect!!!  I didn't know about that!
All my tables are inno.  I definitely won't do any create/drop/rename
/truncate while backing up.

Seriously, the perfect solution and one that actually makes sense.
It's just the same as saying START TRANSACTION before a bunch of
queries in my code.  That transaction gets a view of data frozen in
time so you have consistencies even across tables... WITHOUT holding up
the rest of the system from doing its normal inserts/updates/selects.

You just saved me writing some insert/update workaround asynchronous
queue!

> If you absolutely need 100% *perfect* self-consistent backups while
> the underlying tables are still being written to, you need a

In this instance we don't have to be perfectly consistent across
tables, though it is always desirable.  I'm pretty sure, though, that 
--single-transaction will provide this perfection.  No?  I can't see a
downside.

On 2023-02-05 Brian Lowe wrote:
> If the process that updates the database can be stopped momentarily
> without inconveniencing users, and your the system is set up with
> logical volume management and a few spare GB in the volume group, you
> can stop the process, shut down MariaDB, create a snapshot volume
> (which is very quick), then restart MariaDB and the process that uses
> it. You now have a clean copy of all the MariaDB files on the
> snapshot volume. You can copy them to backup storage and dismiss the
> snapshot.

This is also a very good idea.  Turns out the default rackspace RHEL
install does use LVM, so we could use this as an option.  Shutting down
the system for 60s at 05:00 would actually be feasible, at least at
present.

I'll start with --single-transaction and if that is utopia I'll thank
my lucky stars.  If not, I'll investigate LVM.  Still better than
writing an async update queue for this big contentious table.

I thought of a possible third option, but would have too big a cost:
run a second instance of mysql probably on another box with mysql
replication turned on from first box to new box.  Then run the backup
on box 2.  I'm pretty sure the comms between box 1 & 2 are queue-like
(in the "binary log") so that box 1 never waits for box 2 to confirm a
transaction.  So box 2 can lock that table for 2 mins whilst box 1
continues like nothing is going on.  Purely theoretical purely based on
my not so pure understanding of the theory of mysql mirroring which
I've never toyed with in practice.

I knew there was a reason I'm a MUUG member!  :-)  MUUG members for the
win.