MySQL MyISAM vs. InnoDB: Batch Insert Senario

Recently the company hired a Business Analyst, and has requested some reports which cause Heavy Disk Queue to the Master Database (MSSQL). :(

Receiving lots of complains from users that they can’t perform their duties in the management dashboards. (Disk Queue is over 100, where as normally is only 0.2 to 2 max during peak hours).

Had a discussion with the “General”, he explain that there should be a separate DB just for reporting purpose. If purchasing a new MSSQL licence is required, he won’t be up against it.

The “General” is a open source fan and purchasing a new MSSQL licence will require some cost, and going through the process of purchasing and approval from the finance guys will take ages.

Oh well, since we’ve secured a MySQL server to store none critical data. What the hack… lets just use it as our reporting DB.

Created a batch app to exporting from MSSQL to MySQL, what this batch app do is:

  1. Selecting the data by year from MSSQL.
  2. Insert all the data to MySQL.

Next creating the DB and clone/convert table to MySQL version. Then start up the batch app and start the exporting.

At this moment, the table was created using InnoDB Engine. Picked it cause it seems to be the favorites among many “MyISAM vs InnoDB” discussion.

Server specs: 4 core 2.27 Xeon with 2GB RAM
Total Number of records: 28833017

Results: InnoDB took 5 hours++ to finish the batch app.

Investigations
It was way too long, so I look for my best friend (Google), and ask for help, found the follow post which immediately enlightened me:

  1. Choosing mySQL Database Engine: myISAM or InnoDB?
  2. Difference Between InnoDB and MyISAM
  3. Dave’s MySQL: MyISAM vs. InnoDB

Next, check with the other guys and found out that a team tried changing MyISAM to InnoDB, and later found out it was a big mistake and change back to MyISAM.

Well, clearly enough that I got my solution, changed the table’s engine to MyISAM. And fire up the batch app.

Results: 3 hours 10mins to complete the batch app.

Field Reports: Why MyISAM wins in this senario?

The batch app runs daily and import the latest data to the Reporting DB, so Insert/Update actions is key.

I wonder what magic will happen if using MyISAM on Master DB and InnoDB on the Slave DB? Well, will find out if given the chance to do this.

I’m a newbie in MySQL, so if you had spent your valuable time and read until this line, please do share your experience or your findings on MyISAM & InnoDB mixing. :)