InnoDB: Pros and Cons

InnoDB is a storage engine for mysql. It is supported in all releases of mysql and its edge over the default storage engine is that it supports ACID (Automactiy, Consistency, Intergrity, Durability). Mysql is ready to support InnoDB by default and there is no change needed in configuration. If you need optimize your innodb configuration, open /etc/my.cnf file and put the optimization parameters there. Make sure to restart mysql whenever you make changes to my.cnf

Lets discuss its some pros and cons. InnoDB tables normally recovers from crash automatically, but MyISAM tables usually needs to be repaired. InnoDB stores data according to the order of primary key while in default mechanism, MyISAM,  is saved data in the order in which it is entered. MyISAM provides data compression facility but InnoDB does not yet supports it. Here is list of parameters which should be set in my.cnf if you are using InnoDB.

  • innodb_data_home_dir
  • innodb_data_file_path
  • set-variable = innodb_buffer_pool_size
  • set-variable = innodb_additional_mem_pool_size
  • innodb_flush_log_at_trx_commit

How To change MyISAM Tables To InnoDB?

There is one simple way to achive it, dump your database, open dump file, change MyISAM to InnoDB and then restore the dump. Thats all, Cheers!

Tags: , , ,

1,045 Responses Leave a comment

Leave a Reply