Mysql: Insert performance INNODB vs MYISAM

Question

I am inserting into a simple small table with 5 attributes and 1000 rows.

I observed when the engine is INNODB, each insert is taking 0.03 - 0.05 seconds. I changed the engine to MYISAM, then the inserts are faster. it is taking 0.001 - 0.003.

What is the problem. innodb_flush_log_trx_commit = 1 by default. I was this setting as it is. Here are my innodb setting.

innodb_log_buffer_size : 1MB
innodb_log_file_size   : 5MB
innodb_buffer_pool_size: 8MB
innodb_flush_log_trx_commit = 1

I could not able to figure out what went wrong with this. Thanks in advance. Regards, UDAY

Answer 1

innodb_flush_log_at_trx_commit = 1 means that each transaction is written to log buffer.

Set it to 0 to get better performance, or even better try doing all inserts within one transaction (don't forget commiting in the end).

You can look at http://dev.mysql.com/doc/refman/4.1/en/innodb-parameters.html for more details on innodb_flush_log_at_trx_commit and other variables