Mysql – thesqldump from AWS RDS slowing down


Im taking a db backup from AWS RDS (MySQL) slave from an EC2 (m5.large – 2c/8GB) in same AZ using –

mysqldump -hxx -u xx -p --extended-insert  --single-transaction  --max_allowed_packet=1G db > db.sql

The backup dies at around 60-70 minutes with error : mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table xx at row: 1743503

  1. The error row changes but the table is the same at which mysqldump errors out
  2. Table has 4M records and size is around 1.5 GB
  3. max_allowed_packet on source is 4MB (although this is inconsequential I believe)

So I tried with single table backup with pv enabled :

mysqldump -hxx -u xx -p --extended-insert  --single-transaction  --max_allowed_packet=1G db tbl_xx | pv > db_tbl_xx.sql

The pv stream was interesting as I saw ~10 MiB/s speed for sometime, then froze to 0 B/s for a few seconds. When it resumed, it hovered around 700-800 KiB/s for the rest of the time till the backup finished (1.79GB – 35 minutes). Only in case of entire DB, the backup never finished but died throwing the "Error 2013".

Interestingly, a backup from SQLyog GUI for the same table finished in around 5 minutes over the internet.

I'm unsure if the mysqldump parameters are not supporting large backups or some network level configuration is the problem here.
Some help with pointers to check on either side will be great, thanks!

Best Answer

On a re:Invent conference I watched once, an AWS engineer suggested when we are going to use high IOPS processes, to increase the instance type. My suggestion would be to change your instance type to db.m5.4xlarge, wait 20 minutes and then try again the dump.

Chances are that it will work like a charm!