Hello everyone.

I have some mysql databases (roughly 13m~18m records depending on the day of the month) that I dump using mysqldump. Because of the amount of data that needs to be dumped and bzipped, this consumes a lot of system resources. I have noticed that while these cron jobs run, my linode is nearly unresponsive.

I am already 'nice'-ing all the processes the best that I can. Is there anything else I can do to make the system more responsive (or at least not ignore network traffic) while these cron jobs run (they run about 5 minutes)?

One possibility is that it's not a CPU problem (thus no impact from nice) but an I/O bottleneck to the disk. Then, most other activities (even a network connection) eventually want to do some disk I/O too (for logging, or to let you execute something) and get caught up in the queued I/O. Ordinarily I wouldn't expect this to be so much of a problem, but if you are also already a bit memory overcommitted on your Linode (and swapping), then just about any action might require disk I/O.

If you had control over the I/O and could space it out over a longer time, it might minimize the impact, but I suspect once the dump starts you're stuck.

Might be overkill given that you're only talking about 5 minutes or so (is there no "off hour" 5 minute period you can allocate them to?), but you could provision a second Linode and move your database there. That way, any dump processing won't interfere with other work the main node is doing. Using the private network between the two should keep that from being a bottleneck.

Or if you'd rather keep the database local, and it's own performance could conflict with the dump if on the same node, just use the second box for the dump process, pulling the database from the first. That way if the dump box gets bogged down, it'll just reduce the pull rate from the database, which can continue operating normally for other purposes.

Given pro-rated pricing, it would certainly be easy enough to grab a second Linode temporarily and experiment to see what that did to the load and responsiveness on your primary box.

-- David

Does your command-line lock all the tables for this five minutes? If yes, then you're locking your tables. If not, then you may get a corrupt backup. This is another argument for a second Linode running as a slave.

That said, you might want to try adding a second disk and writing your dump or reading mysql there. Not sure which is causing the wait time.

Adding additional disk images to a Linode isn't going to change anything, they'll still be on the same physical disks on the host. You'd need to get a second linode on a different host and then mount a disk image from that Linode remotely over the private network.

I forgot to mention I *am* dumping the mysql slave. My master runs on dedicated hardware far, far away.

I realize this is an I/O issue, and was wondering if there is a way to 'nice' IO operations for a process. I was also thinking of stopping the mysql server and simply copying the data files, but that might not behave well since it's a slave to a very active master. (and I wish to do hourly snapshots).

I don't think there's a way to nice I/O directly, and you already mentioned nicing the processes themselves.

But one thing you could try is just throttling the I/O from mysqldump itself, since that's just something you're redirecting (presumably) to the disk. It would prolong the total dump time, but help reduce peak load during the process, hopefully maintaining responsiveness. The ideal throttle rate may vary with other host activity (so this might not be as robust as a separate Linode), but worth trying...

I had written an earlier version of this response describing how to do a simple throttling bit of code and experiment with delays, but then I just happened to find a throttling utility at http://linux.softpedia.com/get/System/N ... 6742.shtml, and after trying it a bit, I think it may be just what you want. It even supports listening on a pipe for commands to adjust its options while running. Neat.

After building it on my Linode (just "./configure" and "make"), I did a quick dd test (dd if=/dev/zero of=out bs=1024 count=#####) and was getting a touch under 10MB/s to the disk on average. You need to transfer at least several hundred MB or else you're too impacted by write caches. During that time, the system was very sluggish, and I was seeing wait % times (in top) of like 30-50%.

But if I sent it through throttle, keeping the rate at 1MB/s, ala:

dd if=/dev/zero bs=1024 count=#### | throttle -M 1 > out

then I never got above 1% (well, I think I saw 1.9% briefly) wait and everything stayed very responsive. Of course, the overall transfer would take about 9x as long as going full out. But I tried 3MB/s too and it still behaved well, so that's only 3x as long. I suspect the best value is somewhat overall host load specific. Throttle itself barely uses any CPU (I had to run a 100MB/s stream through it to /dev/null to get it to show up on top at all).

If you let throttle listen on a fifo (add "-l <filename>" to the command), then if you find things getting bogged down you can adjust the rate on the fly by running a separate throttle command with "-t <filename>" to transmit the options to the existing throttle, so you could tune things while the dump was still proceeding in a pinch. Note that I found that new commands only take effect on a window (-w) boundary, by default 60s.

Anyway - you could pump mysqldump through that with a reasonably low bandwidth limit, depending on how much time you were willing to let the dump take.

Oh, and BTW, I'm assuming that you're gzipping during the dump process and not dumping, and then gzipping separately. If you're doing the latter, I'd build a pipeline like mysqldump | gzip | throttle, so that you throttle the final disk I/O and only perform it once for the final result. Of course, by swapping the throttle and gzip stages of the pipeline you can help prevent saturating the CPU with gzip since you'll be throttling the rate of data it receives for compressing.

-- David

Have you tried using mysqlhotcopy as an alternative to mysqldump?

http://dev.mysql.com/doc/refman/5.0/en/ ... tcopy.html

Have you looked into ionice?