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

[Brian Lowe]

On Friday, February 3, 2023 9:36:58 P.M. CST Trevor Cordes wrote:
> 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?

This is probably unhelpful, but I'll throw it in.

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.

The downside is the MariaDB files take considerably more space than the SQL required to 
create them, even if they're compressed. The upside is a restore is as fast as copying/
decompressing the files from the backup medium--no need to go through a lengthy SQL 
reload.

If you want a SQL file, you can mount the snapshot volume, start a second MariaDB process to 
connect to the database on that volume, and perform a mysqldump.

Of course, all this assumes the application in question can be shut down for 30 seconds to a 
minute. Most of that time is spent in MariaDB shutting down cleanly and restarting.

Brian
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://muug.ca/pipermail/roundtable/attachments/20230205/650f9a79/attachment-0001.htm>