7

I do a mysqldump manually every night.

I just noticed that after it is done and I try to access the website it is very slow. After I take a look at the free -mh I notice that the server is now swapping when it otherwise wasn't before the mysqldump.

What am I to do in this case? Just restart the server every time I backup? That doesn't seem very effective.

My database file raw is 1.1gb after the dump.

Improve this question

4 Answers 4

Reset to default
9

We dump much larger MySQL databases nightly without any swapping issues. Here is the command line that is executed:

mysqldump --host=$HOST -u $USER --password=$PASSWORD --max_allowed_packet=512M --port=3306 --single-transaction --skip-add-locks --quick -e databasename

Are you including the "--quick" option? This prevents mysqldump from retrieving large tables in a single query. This could be what is forcing your server to swap. It makes mysqldump get the large tables row by row.

Improve this answer
9
  • Is it possible that it's also the "max_allowed_packet" part as well? Is there a way to force it to not use swapping?
    – Daniel Fischer
    Feb 4, 2010 at 2:03
  • You can eliminate the max_allowed_packet, or lower the value. We use because of the '-e' option for extended inserts in combination with some large data rows we don't want the dump to crash because of a large row. If your data row size is small and fixed, you can lower that value to close to the max size of the row. You can eliminate the '-e' option as well. That may lower memory usage, but will result in larger dump files. Swapping is a function of the OS, and the memory needed by running processes. There is nothing you can set wrt mysqldump to eliminate swapping from ever happening.
    – Craig
    Feb 4, 2010 at 22:33
  • how large is "large"? is a 100MB table (in sql dump) large or are we talking about GB scale?
    – cherouvim
    May 12, 2010 at 9:59
  • We end up with 50+ GB dump files
    – Craig
    May 24, 2010 at 15:38
  • @Craig I know this is really old but having the same issue. How did you calculate the 512M max_allowed_packet size? Is it based on available RAM?
    – John Mellor
    Nov 3, 2016 at 13:18
2

If you leave out the --quick option for mysqldump, the server will buffer the entire response before sending it to the client, which could easily cause swapping with large tables.

Also, if you save the dump to the host running the server it may fight mysqld for cache. Try using --quick and running mysqldump over the network, if you're not doing these two things already.

Good luck!

Improve this answer
0

Mysqldump lock all necesary tables while backuping, try using mysqlhotcopy instead.

Improve this answer
0

Sounds like mysqldump might be loading much more data into caches than necessary. Anyway, if your server swaps, you probably have your cache values set too high.

Try running SHOW GLOBAL STATUS before and after the dump, and compare values

Also, try restarting mysql after the dump. If that helps, it could be cache related

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .