April 29, 2003

mysqldump performance tip

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 12:14 PM

AT&T/Comcast Cable Sucks (again)

Following in the footsteps of Excite @Home and then AT&T, it appears that Comcast (or is it still AT&T? I can't keep track.) can't run a network either.

                           Matt's traceroute  [v0.52]
cow                                                    Tue Apr 29 10:23:09 2003
Keys:  D - Display mode    R - Restart statistics    Q - Quit
                                           Packets               Pings
Hostname                                %Loss  Rcv  Snt  Last Best  Avg  Worst
 1. cable-router.zawodny.com               0%   10   10     1    1    1      1
 2. 10.150.152.1                          20%    8   10    11   11   21     41
 3. 12.244.98.193                         20%    8   10    16   12   24     48
 4. 12.244.67.17                          10%    9   10    39   12   25     62
 5. 12.244.72.210                          0%   10   10    13   12   15     21
 6. gbr5-p40.sffca.ip.att.net             10%    9   10    51   13   29     51
 7. tbr1-p013501.sffca.ip.att.net         10%    9   10    21   13   36     69
 8. ggr1-p340.sffca.ip.att.net            20%    8   10    14   13   23     57
 9. svl-brdr-02.inet.qwest.net            20%    8   10    19   19   38     64
10. svl-core-03.inet.qwest.net            20%    8   10    56   20   30     56
11. chi-core-01.inet.qwest.net            30%    7   10    85   61   71     85
12. chi-edge-01.inet.qwest.net            10%    9   10    88   60   75     91
13. 65.112.65.14                          10%    9   10    72   71   98    136
14. tlp3-atm1-0.toledo.oar.net            20%    8   10    82   80   88    112
15. wcnet-atm1-0s53.toledo.oar.net         0%   10   10   130   80   98    130
16. wcnet-fasteth3-0.toledo.oar.net       12%    8   10    84   81   91    129
17. family.zawodny.com                    12%    8   10    91   84  105    144

Notice the packet loss at the first hop! I guess it's a good thing I held on to my DSL service too.

Sigh.

Posted by jzawodn at 10:49 AM

Apple could learn a lot from Amazon.com

I just installed the new iTunes 4.0 and decided to check out Apple's new music store. I launched iTunes, clicked on the new "Music Store" icon and was taken to a web page that offered lots of stuff I might sample and buy. But there was one big, glaring problem with it.

Much of the music on that page is irrelevant to me. There's a wealth of information in my iTunes music library. Apple could use data about which music I own, how often I listen to various songs/artists/genres to make suggestions. You know, Amazon.com-style stuff.

I know why they don't do this by default (privacy), but the first time I visited, they really should have asked if they could use my music library and listening habits to better suggest music that I might want to try and possibly buy.

What are they thinking?

Amusingly, they even have a "Buy an iPod" link near the top of the page. Guess what, Apple. I own a 20GB iPod that's only 20% full. You could know this. I really have no need to buy another one right now.

There's so much potential here, yet so little of it is used.

On the plus side, at least Apple has the balls to try making the "pay for downloading music" model actually work. I give them a lot of credit for that.

Posted by jzawodn at 12:28 AM