mysqldump 5.5.25 use swap instead of memory

Question

mysql version 5.5.25, while use mysqldump to dump big tables, it eat lots of swap while there is lots of free memory.

# free -m
             total       used       free     shared    buffers     cached
Mem:         32177      31427        749          0        208      10747
-/+ buffers/cache:      20471      11705
Swap:        16002      12952       3049

# cat /proc/sys/vm/swappiness 
5

#[mysqldump]
quick
max_allowed_packet = 16M

Answer 1

In this instance, you can only do but so much. I would recommend upgrading to 5.7

SUGGESTION 1 : Increase Buffer Pool Instances

To address some swapping issues in the past, I have set innodb_buffer_pool_instances to the number of CPUs. (See my answer to How do you tune MySQL for a heavy InnoDB workload?)

If you upgrade to MySQL 5.7, the default is 8. This will lower the incidence of swapping.

SUGGESTION #2 : Dump the Buffer Pool to Disk

Dump the Buffer Pool before the mysqldump

mysql> SET GLOBAL innodb_buffer_pool_dump_now = 1;

Execute the mysqldump, then run

mysql> SET GLOBAL innodb_buffer_pool_load_now = 1;

Now the Buffer Pool is has the same Data and Index Pages it had before the dump

This is only possible in MySQL 5.6/5.7

Answer 2

Your problem is, that mysqldump normally caches the results it produces.
You have to use the --quick option, to prevent this.

 --quick, -q

This option is useful for dumping large tables. It forces mysqldump to retrieve rows for a table from the server a row at a time rather than retrieving the entire row set and buffering it in memory before writing it out.