2

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
2
  • 1
    I don't see "lots" of free memory. 31427 / 32177 = 97.6% used memory, 749 / 32177 = 2.4% free memory. May 1, 2015 at 9:37
  • There is 10GB free, the problem is that mysqldump bloats the pagecache. It’s a pitty Linux still has that problem.
    – eckes
    Nov 25, 2017 at 15:29

2 Answers 2

1

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.

3
  • there is quick option in mysql config file: [mysqldump] quick max_allowed_packet = 16M
    – seanlin
    Mar 18, 2014 at 2:57
  • i dont understand you comment ? was my answer correct ! it improved the speed of your mysqldump ?
    – Up_One
    Mar 18, 2014 at 11:37
  • i have using the --quick option already. but the same.
    – seanlin
    Mar 20, 2014 at 8:29
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.