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...
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@muug.ca https://muug.ca/mailman/listinfo/roundtable
On 2023-02-04 Scott Toderash wrote:
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.
Doh! I didn't see your reply as for some reason it wasn't in the thread. I need to eyeball things more carefully. Glad you thought up the same replication idea that dawned on me in the interim. And yes, I needed to also consider the implications on not doing a full db-wide lock. I think the new transactional method Adam found will achieve the same thing as a db-wide lock without actually doing a db-wide lock: it looks like the xaction will be initiated db-wide (i.e. once for the whole db).
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@muug.ca> On Behalf Of Trevor Cordes Sent: Friday, February 3, 2023 9:37 PM To: MUUG RndTbl <roundtable@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@muug.ca https://muug.ca/mailman/listinfo/roundtable
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.
Your third option would work quite nicely, albeit at the cost of quite some complexity. Running two copies of MySQL on the same system is fully supported, and then can in a replication topology. If you use ZFS or RH's VDO deduping fses, you could do it without taking up nearly as much disk space as you imagine. The secondary copy of MySQL doesn't need much resources, either. And it's trivial to even shut it down completely if you want fs backups instead. Yes, replication is sort of async, but I think you have the option of sync 2-phase commits if you really want them. -Adam Get Outlook for Android<https://aka.ms/AAb9ysg> ________________________________ From: Trevor Cordes <trevor@tecnopolis.ca> Sent: Monday, February 6, 2023 6:50:41 PM To: Adam Thompson <athompso@athompso.net> Cc: Continuation of Round Table discussion <roundtable@muug.ca> Subject: Re: [RndTbl] mysql update delays when no rows match, when backup running 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.
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
participants (4)
-
Adam Thompson -
Brian Lowe -
Scott Toderash -
Trevor Cordes