Slow write of database using `mysqldump `

Question

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?

EDIT

After these helpful answers and google research showed that the database is most likely fine, I run test by duplicating the db1 database on the server into a test database and then deleting tables one by one to decrease the size. And at around 50MB the writes became instant like the other databases. This leads me to believe that there is some throttling going on in Azure because the database is just fine and we will take it up with their support team. I have also found a lot of posts on google complaining about Azure database speeds in general.

In the meantime, I changed the script to ignore large databases. And we will try to move the databases to a SQL Server provided by Azure or a simple VM with a mysql server on it to see where we can get a better performance.

Answer 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).