CODEWITHSUNDEEP
performancebackupmysqldatabase-performance
Martin Krung
Martin Krung

Reputation: 1127

mysqldump with single tables much slower than with --all-databases

I use two different ways to backup my mysql database. mysqldump with --all-databases is much faster and has a far better performance than a loop with to dump every database in a single file. Why? And how to speed up performance for the looped version

enter image description here

/usr/bin/mysqldump --single-transaction --all-databases  | gzip > /backup/all_databases.sql.gz

and this loop over 65 databases even with nice:

nice -n 19 mysqldump --defaults-extra-file="/etc/mysql/conf.d/mysqldump.cnf" --databases -c xxx -q > /backup/mysql/xxx_08.sql
nice -n 19 mysqldump --defaults-extra-file="/etc/mysql/conf.d/mysqldump.cnf" --databases -c dj-xxx -q > /backup/mysql/dj-xxx_08.sql
nice -n 19 mysqldump --defaults-extra-file="/etc/mysql/conf.d/mysqldump.cnf" --databases -c dj-xxx-p -q > /backup/mysql/dj-xxx-p_08.sql
nice -n 19 mysqldump --defaults-extra-file="/etc/mysql/conf.d/mysqldump.cnf" --databases -c dj-foo -q > /backup/mysql/dj-foo_08.sql

mysqldump.cnf is only used for the authentication, there are no additional options there.

Upvotes: 0

Views: 1152

Answers (1)

Jürgen Steinblock
Jürgen Steinblock

Reputation: 31723

There are many differences.

  1. In A you are writing to gzip which compresses the data before writing to disk. B writes plain sql files which can be 5-10 times bigger (results from my database). If your performance is disk bound this could be the solution

  2. -c = "full inserts" is not specified in A

  3. -q is not specified in A

  4. for large databases INFORMATION_SCHEMA queries can be a pain with mysql (try executing SELECT * FROM information_schema.columns. For B every dump has to do these queries while A has to do this only once.

Upvotes: 2

Related Questions