« MySQL Users Conference Winds Down | Main | DBA Code of Ethics »
April 30, 2006
Limits in mysqldump?
Coming away from the MySQL Users Conference I have a short list of questions and/or ideas to research over the next few weeks.
The first is a question posed by an individual after the session on backups. She had been told that mysqldump works good for small installations but once you get above a gigabyte of data it can't handle the dump or restore process.
I've been using mysqlhotcopy for a few years now, but before that was using mysqldump on a few fairly large databases. At the time we switched over to mysqlhotcopy I think our system was around 20 gigs of data, and although the backup/restore process took a good chunk of time, it never failed because of problems with table or database size.
But since it's been awhile I thought I'd do a rudimentary check using a few gigs of data. I grabbed the Sakila sample database and dumped a bunch of data into it, enough to grow it to just over 2 gigabytes (this is all on my MacBook Pro laptop).
At 2 gigs It takes 5m 16s to dump with mysqldump and 11m 49s to restore that database from the SQL statements in that dump file. So it's definitely doable when you get over a gig of data.
How far up can you go? I've used it successfully dumping in the 20-30 gig range. It takes awhile when you get up into these amounts of data but still does the job. It seems like when you get up in this range with MyISAM tables you start looking at mysqlhotcopy as a faster alternative.
Anyone have evidence against this (or in favor)?
Posted by mike at April 30, 2006 2:35 PM
Comments
The volume of updates needs to be taken into consideration when talking about backup performance. It could be the case that her database recives so many updates that having all tables locked for 5+ miunutes is enought to have the server reach it's max_connections limit and/or fall over from load. This is the main reason why I would not recommend mysqldump for databases with more than 2GB of data. The other main issue is the size of the dump file when completed. It can easily be larger than your original table size when you have lots of rows.
Posted by: Matthew Montgomery at April 30, 2006 7:06 PM
yeah, there's no hard limit. It might not be feasible......
Are any of the tables you're testing over 1G each? I wonder if she's running into per-file limitations. But 1G should be handle-able, even on a 32 bit system.
It sounds like she was given a "rule of thumb" which isn't hard and fast, only "when the backups get around 1GB you may want to experiment with ways other than mysqldump.
We have 30G of data, and we use innodb hot backup (which also uses mysqlhotcopy) AND back up individual tables, so that if someone deletes themselves but wants to be put back, we don't need to restore EACH table. It also is great if one table is corrupt, or if we want to check out the status of ONE or A FEW table from a previous day -- saves us from having to restore the whole thing, just to get one table. Our largest single table is 311M.
(although we also do a full mysqldump of our biggest database, which we pipe into gzip -c -- gzip -c takes standard output and zips it, but doesn't have to wait until it's all done. So mysqldump sends info to gzip -c, gzip -c adds it to the zip file, mysqldump sends more info, etc.)
Perhaps that information will help her?
Posted by: Sheeri at May 1, 2006 10:50 AM
I've offloaded all backup duties to a replication slave. Great for a hot spare, but you can take it down for a long clean backup without affecting production. As soon as it comes back online - it catches up to the master.
Add multiple slaves for more redundancy. My slave is a virtual machine just for backups.
Posted by: Jason at July 13, 2007 2:52 PM