I am Bored Posted March 27, 2015 Report Share Posted March 27, 2015 Currently MD does a DB backup every day at 4:00 server time. During this time MD becomes completely inaccessible for 2-5 minutes, depending on the day. I propose that a global message be sent 1 minute before this occurs, to inform users online at the time of the upcoming backup. The 1 minute before is to allow users the time to actually receive the message. Rophs 1 Quote Link to comment Share on other sites More sharing options...
I am Bored Posted March 27, 2015 Author Report Share Posted March 27, 2015 (edited) So, it appears Chewett wasn't aware that the DB backup was causing an issue like this. Working under the assumption that MD is non-responsive due to the SQL server being slammed by the dump, I was instructed to look into the details and present my findings. My proposed solution is to add the --single-transaction option to the invocation of mysqldump. The --single-transaction parameter is to ensure a single, consistent, state is backed up. It also means that every table doesn't have to be locked while the backup is occurring. It should be noted that MyISAM tables, as they don't support transactions, will be accessed in whatever state they are at the time. As hinted in the first answer to this StackOverflow question, it might be necessary to add --skip-lock-tables to the options list, however I believe it would be redundant. It may also be worth looking into using --result-file rather than piping the console output to disk. I doubt this will have any impact on dump speed, but it's worth mentioning. My initial response included adding the --quick option, but, upon further reading, it is enabled by default, and specifying it manually is not needed. I was going to suggest mysqlhotcopy if MD contained solely MyISAM and ARCHIVE tables, however it was deprecated in MySQL 5.6.20 and removed in MySQL 5.7. An initial alternative idea was to have the dump occur via a user that had a resource limitation of the number of queries a second, but, as the only query limitation available is a per-hour limit, and, as the result of too many queries is that the server will return an error, rather than waiting until the user is allowed to make more queries, this is not a viable way to limit the resource usage of the DB backup process. Edited March 27, 2015 by I am Bored Quote Link to comment Share on other sites More sharing options...
I am Bored Posted March 29, 2015 Author Report Share Posted March 29, 2015 As Chewett wasn't aware that the backup was causing an issue like this, this might be better placed in the bugs section. If it's moved, I can do a bit of re-writing of the title and opening post's message to reflect its status as a bug rather than a feature request.. Quote Link to comment Share on other sites More sharing options...
Root Admin Chewett Posted March 29, 2015 Root Admin Report Share Posted March 29, 2015 Ann. 3396 - [2015-03-30 02:36:30 - Stage 12] - Permalink - Posted By Chewett [background=#f9f0d5]The db backup script has been improved so there should be a LOT less lag when it runs.[/size][/background] ---Feedback please when todays run Quote Link to comment Share on other sites More sharing options...
I am Bored Posted March 30, 2015 Author Report Share Posted March 30, 2015 I didn't notice anything when it ran today, so the fix appears to have solved the problem. Quote Link to comment Share on other sites More sharing options...
I am Bored Posted May 2, 2015 Author Report Share Posted May 2, 2015 As there is no longer a need for this, it can probably be marked as implemented/dismissed. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.