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

Can the following command on a live server

mysqldump -h XXXX -u root -pXXXXX --skip-opt --flush-privileges 
    --flush-logs --routines --triggers --allow-keywords 
    --create-options --add-locks --quick --single-transaction 
    --extended-insert --all-databases

cause a web application (which relies on databases of server) to hang?

share|improve this question
    
You are going to need more information. Of course it can, depending on the environment. –  Peter Jan 23 '13 at 9:43
    
The syntax is mysqldump -uroot -ppassword [...] other options and a destination > mydump-$(date +%F).sql –  val0x00ff Jan 23 '13 at 9:45
    
It depends on may a factors, for instance I once had a live webapp stalled when I tried dumping because one of the tables was really huge and was locked out during the dump. –  Dark Star1 Jan 23 '13 at 9:53
    
Yeah we need some more informations. Like, what engine is running your database tables. And i guess you would like to have a consistent dump right? –  zwarag Jan 23 '13 at 10:30

1 Answer 1

up vote 5 down vote accepted

Some possibilities:

  • MyISAM tables only support table-level locking, so while your mysqldump is reading the table, any writes will be queued up until it's finished. If a write gets queued, any other reads will then be queued up behind the write, effectively locking the application. There's nothing special about mysqldump in this sense, it's just a single SELECT * FROM table that takes long enough for a write to happen and get queued.

  • InnoDB tables support row-level locking, meaning that reads and writes can be interleaved as long as they don't touch the same rows. There are some query types that do lock the entire table (such as ALTER table ...) but if you were running these frequently they would have caused troubles long before mysqldump did.

  • A mysqldump can also cause a lot more disk access than usual. It is effectively reading in your entire database and writing it back out again, probably to the same physical disk. Reading from and writing to the same physical disk is going to cause extra latency (unless they are SSDs) because the parts of the disk that you are reading from and writing to have to be different and hence there will be more seeks, which take time. This will raise the load average on your box and will cause any other operations that require disk access to go slower.

  • Most databases store more data than they have RAM. Your mysqldump may well have flushed your filesystem cache and filled it with mostly useless data, meaning that other disk access requests will have to come from the actual disks (which have excess latency at the time) rather than coming from the much faster cache. It may have also flushed caches within MySQL, depending on the limits you have set up for each of them.

  • Depending on your MySQL config, the mysqldump that touches every table may have cause MySQL to use more RAM than you have available. This would cause some of your active RAM to be swapped out to disk, further increasing the disk utilisation and having an enormous impact on performance.

Run mysqltuner.pl on your system to help tune your config and consider switching to InnoDB rather than MyISAM if you are currently using MyISAM. Try streaming your backups straight off to your backup server rather than writing them to the same disk. (This can be achieved many ways but this works: mysqldump [OPTIONS] | gzip | ssh "cat - > database_backup.$(date +%F).sql.gz" <BACKUPHOST>) If (or once) you are on InnoDB, you can use Percona's XtraBackup as a better backup tool that is roughly as efficient as a pure file copy and can be used to initialise replication. XtraBackup can stream directly to another server as well.

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.