Why MySQL dump consume memory after finished

Question

Hi I have a cron job to backup a lot of my database. I create a script to run the dump command for each database every night, I created it like this.

mysqldump --defaults-extra-file=$DEFAULT_FILE -e --compact --skip-comments $DATABASE -r $filename; [[ $? -eq 0 ]] && gzip $filename

I can understand if it will consume much memory, but I don't know why it keep hit the peak memory after all of my cron was finished.

Here is the my memory traffic for last 2 weeks. I run it for the first time at 2/12. It keep increases every time the cron is running. I try to shut it down at 2/16 to make sure it comes from the cron job. Any one know what's happen? or is anything wrong with my mysql dump command? I try to avoid restart mysql server every night.

Answer 1

Not a problem. Well, not yet.

mysqldump works by SELECTing all the data and generating a dump file from that. For InnoDB tables the SELECTs read the data into the "buffer pool". That expands to a maximum as indicated by innodb_buffer_pool_size but never shrinks.

That setting should be tuned to use up most of the remaining memory, but not go past all of RAM. (Going past would lead to swapping, which is terrible for performance.)

Some guesses based on the graph:

• The "working set" of your data is under half of the data. (The graph is lower before 2/12.)
• Today, the graph is touching 100% -- Suggest lowering that setting a little so it won't start swapping.
• There is no need for a restart.

How much RAM do you have? How much data do you have? What is the setting of innodb_buffer_pool_size?