Home / server / Questions / 471648
Accepted
Alex
Asked: 2013-01-24 01:25:52 +0800 CST

Making a mysql dump causes web application to hang.

  • 772

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?

mysql

1 Answers

  1. Best Answer

    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.

    • 5