Very large databases can take a long time to load, in part because indexes are regenerated after each insert, which is useless when simply loading a database dump, as happens when provision clones or migrates a site.

By default mysqldump is already using the options unique_checks=0, foreign_key_checks=0, and disable keys (although the latter has no effect on InnoDB tables, the D7 default). But we should add --no-autocommit, which wraps each table as follows:

set autocommit=0;
INSERT ...
INSERT ...
INSERT ...
commit;

Assigning version 7.x-3.x, but backport to 6.x-2.x should be trivial.

CommentFileSizeAuthor
#1 add_no_autocommit-2372653-1.patch1.26 KBhelmo
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

helmo’s picture

Status: Active » Needs review
FileSize
1.26 KB
mvc’s picture

  • helmo committed 6809d4e on 7.x-3.x
    Issue #2372653 by helmo: Add --no-autocommit when dumping MySQL tables
    

  • omega8cc committed 61d9518 on 6.x-2.x authored by helmo
    Issue #2372653 by helmo: Add --no-autocommit when dumping MySQL tables
    
omega8cc’s picture

Status: Needs review » Reviewed & tested by the community
omega8cc’s picture

Status: Reviewed & tested by the community » Fixed

Status: Fixed » Closed (fixed)

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