I'm trying to automate a mysql dump of all databases from an Azure Database for MySQL Server. Current size of databases:
mysql> SELECT table_schema "DB Name", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" FROM information_schema.tables GROUP BY table_schema; +--------------------+---------------+ | DB Name | DB Size in MB | +--------------------+---------------+ | db1 | 278.3 | | db2 | 51.8 | | information_schema | 0.2 | | mysql | 8.9 | | performance_schema | 0.0 | | db3 | 43.3 | | sys | 0.0 | +--------------------+---------------+ 7 rows in set (31.80 sec) I have a python script, on a different VM, that calls mysqldump to dump all of these into a file. However, I'm running into an issue with db1. It is being dumped to a file but it is very slow, less than ~4MB in 30min. However db2 and db3 are dumped almost immediately, in seconds.
I have tried all of the following options and combinations to see if the write speed changes, but it doesn't:
--compress --lock-tables (true / false) --skip-lock-tables --max-allowed-packet (512M) --quick --single-transaction --opt I'm currently not even using the script, just running the commands in a shell, with the same result.
mysqldump -h <host> -P <port> -u'<user>' -p'<password>' db1 > db1.sql db1 has ~500 tables.
I understand that it is bigger than db2 and db3 but it's not by that much, and I'm wondering if anyone knows what could be the issue here?
2 Answers
Answers 1
It's possible it's slow on the MySQL Server end, but it seems unlikely. You can open a second shell window, connect to MySQL and use SHOW PROCESSLIST or SHOW ENGINE INNODB STATUS to check for stuck queries or locks.
It's also possible it's having trouble writing the data to db1.sql, if you have very slow storage. But 4MB is 30min. is ridiculous. Make sure you're saving to storage local to the instance you're running mysqldump on. Don't save to remote storage. Also be careful if the storage volume to which you're writing the dump has other heavy I/O traffic saturating it, this could slow down writes.
Another way you can test for slow data writes is to try mysqldump ... > /dev/null and if that is fast, then it's a pretty good clue that the slowness is the fault of the disk writes.
Finally, there's a possibility that the network is causing the slowness. If saving the dump file to /dev/null is still slow, I'd suspect the network.
An answer in https://serverfault.com/questions/233963/mysql-checking-permission-takes-a-long-time suggests that slowness in "checking permissions" might be caused by having too much data in the MySQL grant tables (e.g. mysql.user). If you have thousands of user credentials, this could be the cause. You can try eliminating these entries (and run FLUSH HOSTS afterwards).
Answers 2
Create a backup from your database first. After that, try this:
mysqlcheck More info about this: mysqlcheck
0 comments:
Post a Comment