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.
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
#!/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
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.
(Statically copied from previous site)
willy replied on May 28, 2014 - 6:41am PERMALINK
Nice !! thank you
anthonyherve replied on October 24, 2016 - 1:59am PERMALINK
Thank you for your help ! :-)