Improving the Performance of BigDump

BigDump its my favorite way to move a Drupal Database from one server to another. Lately I had a "feeling" that this migration was becoming slower and slower. I really dont know if it is because of the way Drupal7 store the data, or because the new WAMP versions just come with a bad default configuration.

Anyway I was decided to confront this problem and find some interesting things that we can use to improve BigDump. So I made some experiments in my CoreI5 with 8GB of Ram and here is what i found:

About Drupal 7 and WAMP Mysql

Well this entry is mainly for Drupal7 users with Mysql as their main database in a WAMP server, I guess a lot of people have that configuration as a developing server. well seems like Drupal7 change their storage engine, instead of MyISAM, the default storage engine of Drupal6, its now use InnoDB. There are a lot of opinions about what is better but Seems like InnoDB can get you more performance if you give it an adequate quantity of RAM memory.  Now a simple site in Drupal, lets say a Blog, will usually weight between 5 to 10 MB in database data. That quantity of data doesn't work with the default configuration  of a Mysql WAMP installation, you will surely have a Server Gone Away error or Time Limit error, that's why we need to use BigDump for such quantity of data.

First: Configuring Mysql

Ok open your my.ini (in Linux it should be my.cfg but I not quite sure where it is)  file to start modifying it, Don't know where to find it? Follow this simple image and do it baby:

After some trial an error messing up this file , here are some configuration that you should try in order of importance:

• innodb_flush_log_at_trx_commit = 2, by default it is set on 1, putting it on 2 means that your log will be stored temporaly in RAM instead of saving it on the HD every time you do an insert delete or update. This configuration have a side effect that it may corrupt the Log if crazy stuff happens to your RAM but you will not loose too much if your server is not your web hosting. Changing this variable makes my BigDump script run from 190 seconds to only 90 seconds, almost doubling the speed of this. If by any reason you cannot change this variable, maybe you can put your SQL exported script inside a transaction:

This is the export tool of PhpMyAdmin, check that option in Green and all your Data will be inside a Transaction

• innodb_buffer_pool_size = 1024M, Ok I put 1024MB of RAM but you can put more according to the quantity of RAM in your rig. The default I think it is 1M or 16M, just changing this variable improve the speed from 90 seconds to only 50! 
• Moar!!!: This blog gave me some more good ideas about other variables that we can tweak, but just with these two you can get an improvement of quad the speed.

Dont forget to remove the # on the start of the line, # is used for code comments. Yes you know that, but you already forget to delete it, check it again ;)

Second: Configuring PHP

This is optional really, but giving more memory to PHP improve the efficiency of the BigDump Script in 2 to 3 seconds, its not really too much but it will really help when you have Drupal installed

• memory_limit 1024M :Play with it if you have more RAM, but 1024 worked fine for me.

 

PHP.ini after modifying it.

So to recap, change those 3 values showed to you and your BigDump script will fly!. Have fun

THIRD: Host File (update Jul2014):

After a hard drive format on my main disk, I installed again WAMP, and discovered that is was slow as hell, in a CoreI7 machine with 16GB of RAM, so I follow some advice to improve the general Wamp performance. One of those tricks is to modify the hosts file of windows, configure my.ini (of mysql) to listen to 127.0.0.1 and to disable IP6. Please take a look at those tricks here.

FOUR: Mysql Bind parameter (update Jul2014):

In  my.ini use the following under the [wampmysqld] tag.

bind-address = ::

Find this information in stack overflow