The -q option to mysqldump is poorly documented and misunderstood as a result.
Several folks at Yahoo have been having trouble with mysqldump running out of memory. At first I figured it was some odd problem, specific to one person's setup. But after it happened again, I started to look into it.
I did a bit of testing and found that when dumping really big tables, mysqldump was using up large amounts of memory. It was very good about freeing up the memory when it moved from table to table, but eventually it'd hit The Big One and die.
I reasoned that it must be buffering everything in RAM before dumping. (Of course, you can watch the long pauses in output to the dump file to notice this too.) I then thought that PHP has a way of fetching results without sucking tons of memory (mysql_unbuffered_query()) and began to wonder what it'd take to get mysqldump to do the same.
I ran thru the PHP code to figure out how they did it and found that it was the difference between calling mysql_store_result() and mysql_use_result() at the C API level. That's it, really. So I checked the source code to mysqldump only to find that the option already existed, but I'd have never found it.
The docs for mysqldump's -q flag say:
Don't buffer query, dump directly to stdout. Uses mysql_use_result() to do this.
Huh?
You see, unless you know the C API and understand the difference between buffered and unbuffered results from MySQL's point of view, the description in the manual is useless. I wouldn't have thought to try the -q option to solve this problem.
Ugh!
Sounds pretty trivial, doesn't it? It's not. The docs probably should say something about using it on larger tables and how you might run out of memory of you don't use it.
In fact, I'd go so far as to argue that maybe that option should be on by default. There's little downside for folks with small tables, and there's a lot of benefit for those with really big tables.
Anyway, this ends my public service announcement on the matter.
Posted by jzawodn at April 29, 2003 12:14 PM
I don't think there's much to misunderstand about: "Don't buffer query, dump directly to stdout."
?!? :-)
- ask
Me neither. ;-)
Just use the --opt option and you'll get -q/--quick along with other goodies like multi-line INSERTs. :-)
I've found with large (100+ million row) tables that I not only need the -q option to dump without buffering, but also need the -e syntax to significantly lessen the write output via the extended insert syntax. With these two options, it makes it a lot easier to deal with large amounts of data. And of course, piping mysqldump output to gzip lessens the write load even further (given that I usually have a couple free CPUs during a dump, whereas I'm almost always disk i/o bound)...
Yup. The --opt option is your friend. :-) Also, the help description for -q made perfect sense to me the first time I saw it.
Maybe I'm just stupid, but I really appreciated this tip. I would have never thought that you'd buffer a raw data dump like that. Who'd'a thought it?
Yeah, maybe the wording is fine, but the fact that it's not the default is baffling.
He still makes a valid point -- it should probably be on by default.
Hey Z,
Thanks for this tip just fixed a problem that I was having.
-T
A big thanks from me as well. I did see the mysqldump process growing and growing when mysqldumping a big fat table and couldn't figure out why it would do that. I did play with a lot of other options and didn't recognize the -q option for solving thes problem either. After reading this, it's pretty obvious and I feel kind of stupid for running into this wall ;-)
I first thought that my database had gotten corrupted, since my dumpscript had been running for a year on a daily basis with no problems at all. Now, my server somehow crashed and went away during dumping. I'll paste my exact client crash error message here so others facing the same problem might be able to find a solution for their problem here:
mysqldump: Got error: 2013: Lost connection to MySQL server during query when retrieving data from server
Not sure so it is the same problem as Maurice but it is quite similar...Somehow the server crashes as well (but not mysql server but the http server which connects to mysql server) and I got this error:
Lost connection to MySQL server at 'reading authorization packet', system error: 0
See if this helps with the option.
will check if that works for me!
Maybe I too am stupid but I greatly appreciate this tip.
It certainly wasn't obvious to me that the default behaviour was to buffer in memory.
hmmm. I'm having problems with mysqldump on MyISAM tables. The db isn't all that large either.
I run this:
mysqldump --opt db >db.sql
fails with
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table
I got further when I ran thsi:
mysqldump -q -e db >db.sql
failed on row 3,650,867 (previous failure was on row 1,544,510 - total is 4,679,973). There are 2 other huge tables that it sometimes fails on?
The memory usage is fine. This is so weird.
tips? suggestions?
thanks,
erin
I get the 2013 error when I redirect the mysqldump output to an NFS mount. It doesn't consistently happen on the same table or row, its always different. If I create the file on a local file system, its works fine every time. Any ideas?
