Home / server / Questions / 109131
Accepted
Daniel Fischer
Asked: 2010-02-04 03:32:28 +0800 CST

Doing a mysql dump causes swapping issues

  • 772

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.

backup mysql

4 Answers

  1. Best Answer

    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.

    • 9

  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!

    • 2

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

    • 0

  4. 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

    • 0