Aug 302008
 

One issue which has interested me for a while is moving databases from one server to another. My traditional approach involves taking down the website, dumping the database, copying the dump file, loading it up, and then changing the DNS to point to the new server.

With the goal of minimizing downtime, I’ve been considering this approach:

  1. dump the database
  2. scp the dump to the new server
  3. turn off updates on the old server
  4. dump again
  5. rsync to the new server
  6. load the dump onto the new server
  7. change the DNS

The goal is to reduce the time between turning off updates and changing the DNS. I have previous timings for dump and scp times. Let’s now look at rsync times. We will also trying dumping without compression.

[dan@nyi:~/transfer] $ time pg_dump freshports.org > freshports.sql

real    26m12.041s
user    0m17.144s
sys     0m19.165s
[dan@nyi:~/transfer] $

Now, when I do another rsync, after the dump, it takes just 10 minutes to rsync:

 $ time /usr/local/bin/rsync -e "ssh -i /home/dan/.ssh/supernews-maildir-rsync-key" --recursive --delete -avz --stats --progress dan@nyi:transfer ~/
receiving file list ...
2 files to consider
transfer/freshports.sql
  2832187798 100%    4.50MB/s    0:10:00 (xfer#1, to-check=0/2)

Number of files: 2
Number of files transferred: 1
Total file size: 2832187798 bytes
Total transferred file size: 2832187798 bytes
Literal data: 95236822 bytes
Matched data: 2736950976 bytes
File list size: 97
File list generation time: 0.011 seconds
File list transfer time: 0.000 seconds
Total bytes sent: 425578
Total bytes received: 21865883

sent 425578 bytes  received 21865883 bytes  36099.53 bytes/sec
total size is 2832187798  speedup is 127.05

real    10m16.993s
user    0m25.544s
sys     0m11.944s
[dan@supernews:~/bin] $

Now let’s try a gzip approach.


 $ time pg_dump freshports.org | gzip >  freshports.sql.gz
zreverse-i-search)`gzip': time pg_dump freshports.org | gzip >  freshports.sql.gz

real    23m45.288s
user    5m42.822s
sys     0m15.884s
[dan@nyi:~/transfer] $

Initial reports indicate dumping to gzip is faster than no gzip.

Now trying an rsync of a gzip file… It’s not looking promising.

transfer/freshports.sql.gz
   552743046 100%  222.41kB/s    0:40:26 (xfer#1, to-check=0/2)

Number of files: 2
Number of files transferred: 1
Total file size: 552743046 bytes
Total transferred file size: 552743046 bytes
Literal data: 552014422 bytes
Matched data: 728624 bytes
File list size: 92
File list generation time: 0.001 seconds
File list transfer time: 0.000 seconds
Total bytes sent: 164646
Total bytes received: 552200103

sent 164646 bytes  received 552200103 bytes  227076.98 bytes/sec
total size is 552743046  speedup is 1.00

real    40m31.289s
user    0m16.055s
sys     0m7.961s

Well, that takes forty minute. it seems better to rsync raw text.

Website Pin Facebook Twitter Myspace Friendfeed Technorati del.icio.us Digg Google StumbleUpon Premium Responsive