Hacker Newsnew | past | comments | ask | show | jobs | submitlogin

My experience has been that MySQL backup/restore ops (mysqldump; mysql < dump.sql) are orders of magnitude slower than MongoDB backup/restore ops. We use LVM snapshotting for MySQL backups because the "normal" method is just too slow.

I just did a test dump of a 1.7GB MongoDB database:

    real    0m5.781s
    user    0m0.570s
    sys     0m4.817s
I'm not in a position to do a restore just now, but my experience has been that restores are similarly quick. 120GB is obviously a far larger amount of data, but unless you've gone insanely nuts with the indexing, I can't imagine that it would take hours upon hours to do.


5 seconds for a 1.7 GB working set sounds really low: it would mean you could write > 200 Mb / sec assuming you could get data from mongodb infinitely fast. Also, what happens to your mongo database while you export data (can you still write to it ?).

In any case, mysqldump is rarely the bottleneck - restoring is. For the latter, you really want to use mysql from percona, because their version of mysqldump has an option to void the data without index first, and create the index later (--innodb-optimize-keys=True). The effect will depend on the database schema of course, but I often see one order of magnitude difference for simple, large tables with a few simple indexes.


5 seconds does seem very fast, but this is on a high-end Rackspace server with what I'm sure are very fast hard drives and some 64GB of RAM. It's a bit slower on "normal" boxes, but still quite quick.

You can still write to your database, yes. There's a flag, --oplog, which when passed to mongodump, will record the oplog position at the time the backup starts, then dump any ops since the backup started as a part of the backup, so they get replayed on restore. This leaves your data consistent with the database state at the end of the backup without the need to issue a write lock.


If you are in a position to do so, I can't recommend enough using the Percona XtraBackup[1] tool to enable online snapshotting of MySQL. (Also, if you are able to upgrade to XtraDB I would highly recommend looking into it).

A mysqldump / restore can be dependent on many variables when discussing the rate of import. Grouped indexes, delayed FK constraints, buffer sizes, and hardware specs to name a few. My point was more about how one is going to suffer this reindexing problem regardless of data storage facility :)

[1]http://www.percona.com/docs/wiki/percona-xtrabackup:start




Guidelines | FAQ | Lists | API | Security | Legal | Apply to YC | Contact

Search: