This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.
Remember that mysqldump exports the data using SELECT command and all the rows needs to be read and export to the dump file.
It is key to review the CPU, Storage and Memory usage during the dumping process. In this case, I found that our customer reached the maximum capacity of Memory.
Some best practices and recomendations based on this troubleshooting process:
- Run mysqldump in the same region of the Azure Database for MySQL. It is very important:
- To save money because we could have some additional cost reaching the default limit downloading data from Azure to OnPremise
- To reduce the networking latency.
- If possible, enable the accelerated networking in the computer that will run mysqldump.
- Review if you have any table with a large number of rows or with blob fields. I suggested to run the following SQL Command: SELECT * FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql','performance_schema'). During the troubleshooting steps we found that during the mysqldump execution the Azure Database for MySQL reached the 80%-90% of the total memory of the instance. And it was needed to scale up the MySQL Instance to a higher database tier to complete the execution of mysqldump process.
- Instead to export of the databases at the same time, try to export per database per mysqldump execution if needed.
- Reduce to the maximum the limit active transactions in order to avoid many blockings.
- Include the following command line, mysqldump --host servername.mysql.database.azure.com --user username@servername -p –quick –compress --verbose --databases databasename > bkpdb.sql, with the following goal, based on the URL: https://dev.mysql.com/doc/refman/5.6/en/mysqldump.html#mysqldump-performance :
- Know what is the table and database that is exporting mysqldump and you have the issue.
- To have a verbose about the lines to be executed.
- Compress the communication between server and client
- Export the data in quicker mode
- During my test, if you have any issue exporting column statistics details (you may have an error message), please, add in the command line --column-statistics=0).
Enjoy!