Forcing a mysqldump to always be nice to CPU and I/O // Brad Erickson

Exporting or dumping a database with mysqldump, pg_dump or sqlite is a common procedure. The problem is the programs always run as fast as possible generally monopolizing your hard disk and sometimes your CPU. This can noticeably slow down other processes. Generally, responding to a HTTP request or a simply a ls command is more important than the database dump. If the dump takes slightly longer you’ll probably never notice it.

You can correct this issue by using the nice and ionice. The nice command adjusts the CPU priority of a process.

The ionice command adjusts the I/O (hard disk) priority of a process. The following is written for mysqldump, but the approach can be reused for nearly any other program.

First we’ll make mysqldump nice to the CPU, by reducing it’s priority to 10. The nice scale is -20 (most favorable scheduling) to 19 (least favorable.)

nice -n 10 mysqldump db-name | gzip > db-name.sql.gz

Let’s stack the commands make it nice to the hard disk by setting the I/O scheduling class to Best-effort at the lowest priority.

nice -n 10 ionice -c2 -n 7 mysqldump db-name | gzip > db-name.sql.gz

The problem with this is you’ve got to type out that entire command every time you need to export a database and modify existing scripts. Instead of that we can override the default mysqldump program by placing a script in the search path. Start by determining which copy of mysqldump is the default.

user@server ~ $ which mysqldump
/usr/bin/mysqldump

Then check the search path to determine where a script can go to override the default.

user@server ~ $ echo $PATH
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin

See how /usr/bin is the fourth item on the list? That means if an executable file named mysqldump is found in an earlier path, it will be run instead of the program in /usr/bin/mysqldump. So let’s create a script to be called instead.

user@server ~ $ sudo nano /usr/local/sbin/mysqldump

Add the following; making sure the full path to the real mysqldump is used (found above with which.)

#!/bin/bash
# This little script forces mysqldump to be nice to CPU and I/O.
nice -n 10 ionice -c2 -n7 /usr/bin/mysqldump "$@"

Save and close the editor. Then make the script executable.

user@server ~ $ sudo chmod +x /usr/local/sbin/mysqldump

Now check to make sure the new script is used instead of standard mysqldump.

user@server ~ $ which mysqldump
/usr/local/sbin/mysqldump

Done! Happy exporting!

Final note: You can create a Bash alias instead of a new script, but the point is to learn another method.

Comments