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

[Adam Thompson]

Unfortunately, you're describing normal, documented, InnoDB behaviour that's at least partially "for historical reasons".  Backups take a lock on tables by default, and because the READ LOCAL lock mysqldump uses is a MyISAM-only thing, the lock gets promoted to a write lock automatically in InnoDB.  MySQL (et al.) UPDATE statements take a write lock at the *beginning* of query execution - NOT at the moment they want to write something.  Ditto for DELETEs, AFAIK.  Normally not a problem for transactional behaviour, but inconvenient here.

There's one glimmer of hope, which I'll get to in a sec...

mysqldump(1) documents that --opt includes --lock-tables, and also that --opt is turned on by default, so locking is the default.

You can use --skip-lock-tables, at the risk of potentially getting an even more inconsistent (non-isolated) view of the data during backups.

Note that you are NOT guaranteed full transaction isolation / consistency during a mysqldump anyway, as --lock-tables says:
>> [...] The tables are locked with READ LOCAL to allow concurrent inserts in the case of MyISAM tables. For transactional tables such as InnoDB, --single-transaction is a much better option than --lock-tables because it does not need to lock the tables at all.
>> Because --lock-tables locks tables for each database separately, this option does not guarantee that the tables in the dump file are logically consistent between databases. Tables in different databases may be dumped in completely different states.

I would try --single-transaction and test; I don't have any convenient way of testing it right now.

If you absolutely need 100% *perfect* self-consistent backups while the underlying tables are still being written to, you need a different RDBMS.  For example, PostgreSQL does transaction-isolated backups correctly, out of the box.  I think --single-transaction should get you most of the way there without switching products.

-Adam

-----Original Message-----
From: Roundtable <roundtable-bounces at muug.ca> On Behalf Of Trevor Cordes
Sent: Friday, February 3, 2023 9:37 PM
To: MUUG RndTbl <roundtable at muug.ca>
Subject: [RndTbl] mysql update delays when no rows match, when backup running

Question:
Mysql (MariaDB actually, fairly recent version) issue.  Innodb.

Full db backup (table by table) runs each night 5am.

There's a massive (few GB) table that takes a couple of mins to backup.

When this table is getting backed-up, updates to the table pause until the
backup is done.  Selects don't seem to pause(?).  However, even updates
that will match zero rows seem to pause!  Shouldn't the engine be doing a
select (within a transaction internally) and then quitting the query?  It
seems like it's wanting to get some sort of lock before doing anything for
the update, even the select step.

Maybe this makes sense?  I suppose if I was doing the locking with
transactions in my code (I'm not), I would do a select FOR UPDATE and then
update if I had to?  Would the "select for update" also pause on the
select step?

I was thinking change my db library to make all update calls a
select-for-update then the update only if needed, but if my hunch is
correct, it won't fix anything, and slow things down a touch because I'm
doing the internal work myself?

And I can't blanket replace all updates with select/update without a "for
update" because there could be race conditions between the select/update?

Maybe the correct approach is on an instance-by-instance basis where I
know I don't care at all about races (like this case) I could replace the
update with select (no "for update") plus an update.

If I make that an option in my db library, and use it liberally, will I be
slowing down (much) the common case of updates running outside of
backup time, because then both I and the engine are doing a select?
There's no way I'm going to change it to "if backup running, do select
first, if not do update by itself"  :-)   At least, I hope I'm not going
to do that!!

It's really only 1 table of mine that is multi-GB and has a long backup
time, otherwise this would be a non-issue.  I was kind of hoping inno took
care of all this stuff for me...
_______________________________________________
Roundtable mailing list
Roundtable at muug.ca
https://muug.ca/mailman/listinfo/roundtable