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

Rheumy

You should try posting this on dba.stackexchange.com or serverfault.com – Blair

innodb flush method seems on of the reasons. by changing the flush method to O_DIRECT/O_DSYNC i got 5 times better performance. – Rheumy

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

Privateer

Hi Argeman, Here I am concerned about why with this variable set to 1, it is showing some very very low through puts like 120K inserts per hour. Keeping a side changing this value to 2, what might be the reason for this worse performance with this value set to 1. – Rheumy

The reason is that each transaction is written to the log buffer. If you now do inserts without manually putting them into one transaction, each is performed as a transaction, resulting in a write to disk. A write to disk takes about 10-50ms on a normal rotating disk... That might be better if you have battery backed cache on a raid controller or a SSD however – Privateer

Do u really mean it like single transaction on a comodity server will take 10 - 50 milli seconds. I don't think so. No one can accept that. I have this performance not on all the servers only on a few servers. – Rheumy

This problem is not specific to MySQL, every program that wants to store a transaction permanently will have that behaviour. Otherwise a transaction can finish and is not saved to disc! And remember, you can pack as many commands as you wish into a single transaction, so this problem can be simply avoided. And you can of course use better hardware or just switch the mentioned flag to O_DIRECT/O_DSYNC... – Privateer

Ya... I did some test on this. a week back. I found it like 50% improvement after setting the flush method to O_DIRECT/O_SYNC. – Rheumy

Did you try setting it to 0? My experience is that this is giving you comparable performance to MyISAM. – Privateer

I set it to 2 the performance is similar to what I see with MYISAM engine. But I want the equally better performance with this set to 2. – Rheumy

In this simple case, MYISAM will not be slower than INNODB. If you start inserting and updating with 2 or more clients at the same time, you have better bets that innodb outperforms myisam – Privateer

"innodb_flush_log_at_trx_commit = 0" doubled my insert performance compared default 1. – Ascender