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
  • 1
    I don't see "lots" of free memory. 31427 / 32177 = 97.6% used memory, 749 / 32177 = 2.4% free memory. – ypercubeᵀᴹ May 1 '15 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 '17 at 15: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

0

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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