Mysqldump solution for high CPU and IO usage

Using default mysqldump it quickly increases the server load out of control and locks up everything. Searching a solution online usually starts with suggestions with nice and ionice:

nice -n 10 ionice -c2 -n 7 mysqldump ...

However, this is wrong. The mysql queries are running on the mysql server and the nice command will have very little effect on the performance of the mysql command. Ionice could have a positive effect if mysqldump wrote directly to the disk. But this is not the case if the command uses the pipe or gzip to write the dump to the disk. Therefore, setting ionice on mysqldump will not do anything.

Here’s a snippet from the mysqldump documentation:

To dump large tables, you should combine the –single-transaction option with –quick.

Setting the nice to the mysql server is not possible, but if the disk usage is the bottleneck, ionice needs to be set where disk write is happening. We will use the tee command so we can apply ionice because > is a shell builtin, it can’t be a parameter for ionice. The command will look like this:

mysqldump ... --single-transaction --quick | ionice -c2 -n 7 tee mysqldump.sql > dev/null

If the CPU usage is less important than IO, then we can also combine this with gzip:

mysqldump ... --single-transaction --quick | gzip | ionice -c2 -n 7 tee mysqldump.sql.gz > dev/null

I hope this will clear up a few misconceptions about ionice and mysqldump.