running tar completely locks up mysql
Had the exact same problem. Hardware as below...
- HP DL180-G6 Nearline Server
- 4x 300 GB SAS 15k drives
- 2x 1TB SATA 10k drives
- 2x Xeon 5340 2.53 GHz CPU's (8 cores total)
- 32 GB DDR3 1066 MHz
- HP Storageworks HBA P410 (PCI Express - 1 for all HDD's)
- HP Storageworks HBA P212/Zero (PCI Express - 1 for the external tape drive)
- HP Ultrium LTO 4 external SAS tape drive (800/1600 MB)
When we'd run the daily tape backup with tar -options -source from /mnt/backup -destination to /dev/st0 (tape), it would basically lock up the whole damn computer. The first service to suffer was MySQL, which would be unreachable through the Unix filesystem socket (/var/lib/mysql/mysql.sock), and then processes would crash one by one. Even the terminal (bash prompt) was unuseable, and forget about opening anything from within the gui (Gnome Desktop).
The solution was not to use 'nice', but rather use 'ionice'. It wasn't CPU loading that was the issue but disk loading. The disks and the processors are fast enough, but the backbone (hard disk adapter / PCI-express bus / etc.) just could not keep up.
So, here was the fix...
Old tar backup command:
[root@somewhere]# /bin/tar -clpzvf /dev/st0 /mnt/backup
New tar backup command:
[root@somewhere]# /usr/bin/ionice -c2 -n5 /bin/tar -clpzvf /dev/st0 /mnt/backup
For your reference, here is the manpage for 'iowait' command... it is supported on kernels 2.6.13 and newer: - http://linux.die.net/man/1/ionice - ionice priorities for class 2 systems have 'sane' values between 3 and 5 if you are trying to slow something down without making it take forever. where 3 is moderately slowed down and 5 is very much slowed down.
Effectively doubled the time it takes to run the tape backup (from half an hour, now it is about an hour), but who cares, it is now working as desired.
The problem is contention. The fact that the load level is high confirms this.
The sorta-ok solution would be to run the tar process with nice to lower the priority. That may or may not be enough to get mysql to not choke.
The better solution is to put mysql on to different spindles. I assume by the device names this is all running on one local disk. I would suggest getting another disk and moving mysql to it.