Jun 12 2010
MySQL InnoDb inserts are slow, really slow!
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.
Chris Nizzardini has been developing web applications since 2006. He lives and works in beautiful Salt Lake City, Utah. If you’re interested in hiring me for contract work please visit IO Spring LLC.
Night Harvast Wine, it blows. Use MySQL HAVING to Get Past Error 1111 Invalid Use of Group Function
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.
Chris,
InnoDB is a pretty good engine. However, it highly relies on being ‘tuned’. One thing is that if your inserts are not in the order of increasing primary keys, innoDB can take a bit longer than MyISAM. This can easily be overcome by setting a higher innodb_buffer_pool_size. My suggestion is to set it at 60-70% of your total RAM.
I am running 4 such servers in production now, inserting about 3.5 million rows a minute. They already have close to 3 Terabytes. InnoDB it had to be , because of the highly concurrent inserts.
There are further ways to speed up inserts. And I’ve benchmarked some.