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

[Scott Toderash]

Assuming you use mysqldump. It does lock tables. If it didn't then data 
could change while the backup of that table is happening, producing 
unpredictable results.

You could set up MySQL replication to another machine and run your 
backup on that one instead. That scenario would avoid the delay you are 
seeing but seems like a lot of trouble for this unless you need 
constantly consistent performance.



On 2023-02-03 21:36, 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?
> 
> 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