InnoDB Performance Tuning

InnoDB becomes the default storage starting from MySQL 5.5. InnoDB is a transaction, ACID complaint MySQL storage engine (including commit, rollback and crash recovery capabilities, and offers row level locking.

I worked on data migration of millions records from Vignette VCM to Drupal 6. Drupal migrate module is not stable and does not perform well on large data sets. So I rewrote migration using raw SQL queries and modify my.cnf settings. Here some useful settings:

innodb_flush_log_at_trx_commit=1 
Set to 1 if database transaction is more important than performance. Set to 2 if performance is more important than transaction.

innodb_log_file_size=1024M 
Set the log file size to about 25% of the buffer pool size.

innodb_file_per_table 
Increase performance in optimize, backup, restore, compress and truncating a table

innodb_buffer_pool_size=4G 
Default is 128M. 70-80% of memory for dedicated 64bit MySQL server.

Disable AUTOCOMMIT
When you need to INSERT lots of data, first call SET AUTOCOMMIT = 0;then execute INSERT statements followung by a manual COMMIT;


Useful Links:
InnoDB Performance Tuning

Recommended my.cnf Settings for InnoDB