In order to improve rsync efficiency, I wonder if sql-sync should set --ordered-dump option by default. This affects the sql-dump that gets created ... Someone can disable this behaviour via --no-ordered-dump on CLI if they really want.

CommentFileSizeAuthor
#6 drush-default-ordered-dump.patch1.64 KBgreg.1.anderson
#4 drush-sql-sync-ordered-dumps.patch1.86 KBgreg.1.anderson

Comments

greg.1.anderson’s picture

greg.1.anderson commented

The easiest way to implement this would be to add a drushrc.php file at the drush root that contained:


$options['command-specific']['sql-sync'] = array('ordered-dump' => TRUE);

moshe weitzman’s picture

moshe weitzman commented

Thats a good idea if we decide not to set the option by default. I am proposing to just set it and not expect users to add a drushrc entry.

greg.1.anderson’s picture

greg.1.anderson commented

If we added a drushrc.php to the drush install directory, then the options in it would automatically take effect; the user would not have to add anything. We would probably also want to add a comment that said "Don't add your own options here; copy examples/example.drushrc.php to $HOME/.drush/drushrc.php and customize it there."

greg.1.anderson’s picture

greg.1.anderson commented
Status: Active » Needs review
StatusFileSize
new drush-sql-sync-ordered-dumps.patch1.86 KB

Here's a patch that shows what I mean more clearly. This will add --ordered-dump to sql-sync commands, and does not require the user to do anything to make it happen.

moshe weitzman’s picture

moshe weitzman commented

That would work, but it muddies the water IMO. It is one more configuration file to keep an eye on. I think we should call drush_set_option from within sql-sync callback, and do so such that no-ordered-dump still takes precedence.

greg.1.anderson’s picture

greg.1.anderson commented
StatusFileSize
new drush-default-ordered-dump.patch1.64 KB

Sure, that works for me too. Patch enclosed.

moshe weitzman’s picture

moshe weitzman commented

Would it work to omit the conditional and just pass a context as 3rd param such as: drush_set_option('ordered-dump', TRUE, 'default');

greg.1.anderson’s picture

greg.1.anderson commented

#7 would work to turn it on, but with that implementation you would have to turn it off with --ordered-dump=0; --no-ordered-dump would not work.

Maybe we should do both...?

moshe weitzman’s picture

moshe weitzman commented
Status: Needs review » Reviewed & tested by the community

Now I see that our special 'no-' handling happens in drush_command_default_options() during dispatch. I was sort of hoping that it happens dynamically inside of drush_get_option().

Lets just go with your posted patch.

greg.1.anderson’s picture

greg.1.anderson commented
Status: Reviewed & tested by the community » Fixed

Yes, I have considered adding that sort of handling to drush_get_option. Maybe later...

Committed #6.

Status: Fixed » Closed (fixed)

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

drewish’s picture

drewish commented

I'm not sure I want to re-open this but I think we might have picked the wrong default for this. I'm working with a large database and I'm not sure this gives the performance improvement that's suggested. Dumping the database using --ordered-dump results in a 3.6G file, with --no-ordered-dump it's only 2.0G. That's a 180% increase in size. That might be acceptable if it does improve the transfer time for rsync (I have my doubts) but the lack of extended inserts means that it restores much slower. I'll work on some number comparing the restore times for the two formats this afternoon.

drewish’s picture

drewish commented

I posted some numbers on this over on #1380658: Optimize database dump settings for sql-sync.

greg.1.anderson’s picture

greg.1.anderson commented
Title: sql-sync should automatically set --ordered-dump option » Re-visit automatically setting --ordered-dump option in sql-sync
Version: » 8.x-6.x-dev
Status: Closed (fixed) » Active

We've had a couple of reports of this option causing problems for folks with large databases -- above, and recently in #638282: Make drush sql dumps easier to integrate in VCS.

I just did a test myself, and found that for my database, it took about 3 minutes with or without the --no-ordered dump option on sql-sync. By coincidence, I did the test from a hotel room in Tokyo, so there was some distance involved between the source (U.S. server) and target (local laptop). (The network does seem pretty fast here, though.)

It's not super-critical to get the default right, as folks can always set the --no-ordered-dump option in their drushrc.php files. It seems to me, though, that if these options are not helping much for small databases, but hurting people with really large databases, perhaps we should flip the default.

Anyone who feels strongly about this should post a justification here, with timing from multiple runs, with and without ordered dump, using different-sized databases. If that sounds like too much work, please just set $options['no-ordered-dump'] = TRUE in your drushrc.php file.

Will close this again shortly if no one steps up.

owen barton’s picture

owen barton commented

There are some numbers favoring --no-ordered at http://knackforge.com/blog/sivaji/how-make-drush-sql-sync-faster

I have nearly always found that the write performance of the drive the dump file is being written to (or read from, for that matter) can be a huge factor - piping to/from gzip/zcat is normally a big boost, as is using tmpfs.

Another consideration, if duration is an issue and dump time > sync time, then using a tool like https://launchpad.net/mydumper is probably going to produce much more dramatic gains than this mysqldump flag (although it may be risky for "production moves" - probably fine for sandboxes though).

greg.1.anderson’s picture

greg.1.anderson commented

This issue is about the effect of the ordered dump options on import times. Tools that improve dump times could also be addressed in a separate issue.

owen barton’s picture

owen barton commented

Sorry for the noise - I didn't notice that I had clicked over from #1380658: Optimize database dump settings for sql-sync :)

I can try out some dumps with larger databases and report back. I think we need to determine the effect on dump performance as well as rsync performance (with typical deltas).

greg.1.anderson’s picture

greg.1.anderson commented

No problem -- I should apologize for closing #1380658: Optimize database dump settings for sql-sync as a "duplicate" when the only follow-on issue is of much more limited scope than the original issue. However, the original issue was broad, and had not gotten much play. I've been trying to clean up the issue queue a little, and expect folks can make their own follow-on issues if they want to continue in other areas.

If you have time to do some measurements on larger databases, that would be great. I agree that we need to balance the cost of transmission time with the extra time needed to import at the destination.

greg.1.anderson’s picture

greg.1.anderson commented
Status: Active » Closed (won't fix)
Issue tags: +Needs migration

This issue was marked closed (won't fix) because Drush has moved to Github.

If this feature is still desired, you may copy it to our Github project. For best results, create a Pull Request that has been updated for the master branch. Post a link here to the PR, and please also change the status of this issue to closed (duplicate).

Please ask support questions on Drupal Answers.