Jump to content
Sign in to follow this  
I am Bored

Global Message before Database Backup

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

  • Forum Statistics

    15,832
    Total Topics
    173,490
    Total Posts
  • Recently Browsing

    No registered users viewing this page.

  • Upcoming Events

    No upcoming events found
  • Recent Event Reviews

×
×
  • Create New...