List:General Discussion« Previous MessageNext Message »
From:Madison Kelly Date:December 16 2009 8:56pm
Subject:Importing large databases faster
View as plain text  
Hi all,

I've got a fairly large set of databases I'm backing up each Friday. The 
dump takes about 12.5h to finish, generating a ~172 GB file. When I try 
to load it though, *after* manually dumping the old databases, it takes 
1.5~2 days to load the same databases. I am guessing this is, at least 
in part, due to indexing.

My question is; Given an empty target DB and a dump file generated via:

ssh root@server "mysqldump --all-databases -psecret" > /path/to/backup.sql

How can I go about efficiently loading it into a new database? 
Specifically, can I disable triggers, indexes and what not until after 
load finishes? I can only imagine that a single "ok, go create your 
indexes now" at the end would be faster. Perhaps some way to hold off 
commits from happening as often? The target server has 32Gb of RAM, so I 
suspect I should be able to hold things in memory and commit to disk 
relatively rarely.

I am currently loading via this command:

mysql -psecret < /path/to/backup.sql

The source and destination MySQL versions are:

mysql  Ver 14.13 Distrib 5.1.19-beta, for unknown-linux-gnu (x86_64) 
using readline 5.0

mysql  Ver 14.12 Distrib 5.0.77, for redhat-linux-gnu (x86_64) using 
readline 5.1

The reason for the discrepancy is that the old server was setup from 
source on CentOS 4.x by a previous tech and the destination server is 
the stock version from CentOS 5.x. The source server will be phased out 
soon, so no real attempt at maintaining matching versions was done.


Importing large databases fasterMadison Kelly16 Dec
  • RE: Importing large databases fasterGavin Towey16 Dec
    • Re: Importing large databases fasterMadison Kelly17 Dec
      • RE: Importing large databases fasterGavin Towey17 Dec
  • Re: Importing large databases fasterShawn Green17 Dec
  • Re: Importing large databases fasterJay Ess17 Dec
    • Re: Importing large databases fastermos17 Dec
      • Re: Importing large databases fasterBrent Clark18 Dec