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.

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, 201511 yr by I am Bored

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..

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

I didn't notice anything when it ran today, so the fix appears to have solved the problem.

As there is no longer a need for this, it can probably be marked as implemented/dismissed.