So I was doing some MySQL database optimization tests this morning when I got my database engines mixed up. Thats when I stumbled on something horrible….InnoDb is really slow when it comes to inserts! Like really slow.
I performed 10,000 inserts on MyIsam and it executed in about 1/10th of a second. The same inserts on an InnoDb table took over 21 seconds! I was seriously dumbfounded by this. I knew MyIsam would be faster, but I figured it would be only nominally faster.
Out of curiosity I did a single insert with MyIsam which executed in 0.000762939453125 seconds whereas the same insert in InnoDb executed in 0.032792091369629 seconds. I must say this is a bummer for anyone using InnoDb in a high-insert environment. I’ll have to see what the differences are on updates, selects, deletes, and joins.
Related posts:
Do you have tons of indexes or foreign keys in your tables? The difference seems to big..
I was a little suprised myself. This was an empty table actually with only 3 columns: a primary key set to auto increment, a varchar, and date_time. Try it for yourself.
I am experiencing the same symptoms,
I have no indexes or foreign keys one table, one column. if i use InnoBb 1000 inserts takes around 10-20 seconds MyIsam < 1 second
server is 4 core @ 2.0Ghz with 2818176KB free
any suggestions how to fix this?
Nick, thanks for the response. Unfortunately I haven’t looked into “fixing” this issue. It is known that MyISAM is faster than InnoDb, but this is the trade-off you agree to when you want to gain referential integrity and normalized data at the database layer. If this is a high availability production server I would look into some books on the subject. You can also check out some of the posts over at http://www.mysqlperformanceblog.com/. The folks over there have lots of great stuff.
I found that if I set AutoCommit=0 before the inserts, then commit after Inserts i can cut off a lot of time. still not as fast but around 2 seconds.
doesn’t work if the call is to a stored procedure(which does the insert).
On my development machine i’m not experiencing this at all. (i dont need to add the autocommit = 0 and i still see < 1 second performance)
the production machine is a VPS, so im suspecting that disk usage may be the primary culprit and someone else has a high disk usage application.
Thanks for the link.
Good to know Nick, thanks for the contribution. Makes sense after reading http://dev.mysql.com/doc/refman/5.0/en/commit.html.