When we do a DB backup at the moment the entire DB is locked to ensure a consistent state (as far as I can tell) we have a site that gets a lot of INSERTs all the time, and these just queue up during a DB backup, which can take around 5~10 minutes. This wasn't really acceptable, so I've found that you can pass the following options to mysqldump to make this better:

--single-transaction - Which means that the DB dump is consistent, but for those with InnoDB (Everyone?) it doesn't block anything.
--quick - This makes dumping large tables faster and more efficient.

What do people think about adding those?

Comments

Anonymous’s picture

Anonymous (not verified) CreditAttribution: Anonymous commented

I think we should invoke drush sql-dump, which already invokes --single-transaction.

Note that --single-transaction is only useful for tables using InnoDB engine. Which most everyone should be on anyway, but in some cases, may still be on MyISAM for various reasons.

Anonymous’s picture

Anonymous (not verified) CreditAttribution: Anonymous commented

Note that using drush sql-dump instead of our crazy file-descriptor-magic mysqldump command, would allow people to put 'skip-tables' options in the site drushrc.php which will allow the dump to skip cache,watchdog tables etc, which some people may want to do if they don't care about restoring this data if necessary, further speeding up the process as a result.

Edit: actually I think you have to pass --skip-tables args to drush sql-dump for this to work, so probably is a moot point.

Steven Jones’s picture

Steven Jones CreditAttribution: Steven Jones commented

@mig5 agreed that using drush more would be a good way to go for the 2.x branches, but I feel that doing that in 1.x may cause too many headaches.

anarcat’s picture

anarcat CreditAttribution: anarcat commented

I agree with using upstream as much as possible.

However this means that anybody with shell access to the server can read anyone else's credentials in 4.x, as far as I know, as upstream refused our crazy filedescriptor hack.

Maybe that's fixed in 5.x, i don't know.

Steven Jones’s picture

Steven Jones CreditAttribution: Steven Jones commented
Status: Active » Fixed

Fixed in 6.x-1.x and 6.x-2.x in commits c07fb0365fb8b2257f891c4386158d32b7b3af6e and 929b75c3f46e42b7f40aba2505df9b853c7fa9f7 respectively.

Steven Jones’s picture

Steven Jones CreditAttribution: Steven Jones commented
Priority: Normal » Critical
Status: Fixed » Needs work

Apparently this just broke the Jenkins tests, lame!

Steven Jones’s picture

Steven Jones CreditAttribution: Steven Jones commented
Priority: Critical » Normal
Status: Needs work » Fixed

Fixed that in two follow up commits: 8aa52f86b618b818f155c92391198d4384797e86 and 2498ba148b0c5081cd0bacd95a2cf75e1aab0873

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

  • Commit c07fb03 on 6.x-1.x, dev-drupal-8, dev-ssl-ip-allocation-refactor, dev-1205458-move_sites_out_of_platforms, 7.x-3.x, dev-subdir-multiserver, 6.x-2.x-backports, dev-helmo-3.x by Steven Jones: 
    Issue #1439196 by Steven Jones: Fixed Don't lock the DB during backups.
  • Commit 929b75c on 6.x-2.x, dev-ssl-ip-allocation-refactor, dev-1205458-move_sites_out_of_platforms, 7.x-3.x, dev-subdir-multiserver, 6.x-2.x-backports, dev-helmo-3.x by Steven Jones:
    Issue #1439196 by Steven Jones: Fixed Don't lock the DB during backups.