Strategy for setting up a 60gb MySQL database from a dump?

Posted on

Question :

I have been trying to set up a 60gb MySQL database from a dump. I loaded it into MySQL Workbench on my standard-issue dual-core 2.4 Ghz, 4gb RAM, 2011 Macbook Pro. After roughly 24h, it had only been able to load a few percent. During other attempts MySQL Workbench crashed completely. Doing the same thing on the command line generates roughly the same results.

What is a viable strategy for loading a 60gb MySQL database? Do I need to buy a very powerful computer? How powerful? Is there a way of loading it onto an Amazon Web Services instance that is feasible?

Answer :

To speed up load time of InnoDB you can tune some variables.

  1. innodb_buffer_pool_size. Kind of the most important one. Allocate as much as your system allows, but make sure MySQL doesn’t use swap. 4GB RAM is desperately little, try buffer pool around 2G. If you can install more memory that will be more helpful than anything below.

  2. innodb_log_file_size. The larger REDO log the less random IO InnoDB does. It doesn’t make to make it larger than the buffer pool. So, 512M – 1G should be OK.

  3. Reduce InnoDB durability with innodb_flush_log_at_trx_commit=0

  4. A bit risky, but you can also disable innodb_doublewrite

  5. Make sure you disable all unnecessary logs (binlog, slow log, general log).

All recommendations for the load process only. For normal operation configuration must be different.

Leave a Reply

Your email address will not be published. Required fields are marked *