Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

4 Answers 4

up vote 3 down vote accepted

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.

share|improve this answer
    
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 '10 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 '10 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 '10 at 9:59
    
We end up with 50+ GB dump files –  Craig May 24 '10 at 15:38

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!

share|improve this answer

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

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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