-1

I am using AWS EC2 instance, and my database size is approx 4GB. Using ubuntu OS and mysql database within the instance. Whenever i dump my database the time its dumping, website stops to respond. Time period is about 15 to 20 seconds.

Kindly assist i anything goes better than this backup procedure.

CW User
  • 65
  • 8
  • Show the command you use to dump the database. Or say what tool you are using (MySQL Workbench?). Did you investigate what *options* can be changed? – ToolmakerSteve Oct 25 '19 at 08:30
  • If using mysqldump [See this answer](https://stackoverflow.com/a/12142501/199364). If using MySQL Workbench, go to Advanced options, *uncheck* "lock-tables" [to not block] and *check* "compress" [less network bandwidth used, so backup goes faster]. – ToolmakerSteve Oct 25 '19 at 08:38

3 Answers3

1

I think you forgot to turn off the lock tables option. By default, MySQL sets table locks when doing a data export.
The lock isn't released until the data export is complete, which explains why your website process cannot do anything on the tables for about 15-20 seconds.

If you are taking the database dump through MySQL workbench, go to advanced options and uncheck lock-tables.

Adeel Siddiqui
  • 676
  • 7
  • 16
  • i cant say this, mysql locks one table at a time, and the the page is not dependent on this most of the time.. – CW User Mar 27 '18 at 11:32
0

Please check max_execution_time and memory_limit in your php.ini file. you also can use set_time_limit function.

Meysam GanjAli
  • 9
  • 2
0

obviously, when you are taking back up the database is busy with that and could not listen to other requests you may consider to get something like RDS which will handle the backup jobs for you behind the scene also if you get done with the read replica you can get rid of this timeout issue.

fayis003
  • 680
  • 4
  • 10