The MySQL MyISAM and InnoDB engines and a grocery checkout

http://blog.scoutapp.com/articles/2009/10/09/choosing-between-the-mysql-myisam-and-innodb-table-types

by itsderek23 • 17 years ago

23 19 17 years ago

blog.scoutapp.com

There's plenty of discussions centered on the choice between the MySQL MyISAM and InnoDB engines. I've illustrated a case that has bitten me in the past.

idlewords4• 17 years ago

The analogy is helpful, but leaves some things out. In one of the grocery stores, you'll find that if the lights flicker off and for just a second, half the products on the shelves may end up unusable. And if your store gets past a certain size, a sinkhole may appear and suck most of your inventory underground with no warning.

The only reason I can think of for using MyISAM tables in a web app is the terrible default InnoDB settings in MySQL. Users unfamiliar with InnoDB may not realize that the awful performance is an artifact of bad configuration, and easily fixed.

bhseo• 17 years ago

Even after you've spent days tweaking the performance, you are still left with a fundamentally slower engine.

munctional• 17 years ago

I'd rather have a consistent database than a fast one!

towndrunk2• 17 years ago

Can you provide some info or a link on this config change.

Thanks!

idlewords4• 17 years ago

This is handy: http://www.mysqlperformanceblog.com/2007/11/01/innodb-perfor...

Lots of good InnoDB-related posts on their site.

modoc2• 17 years ago

I'm still not sure why folks still use MyISAM. I'm sure there may be some edge case where it makes more sense, but in generally InnoDB seems like a better choice (not just regarding row level locking). Or postgres:)

DonnyK• 17 years ago

I've yet to find a good comparison of the two.

In fact, from what I've seen it seems better to use MyISAM when you don't need support for ACID transactions.

idlewords4• 17 years ago

I don't agree. MyISAM tables are easy to trash, and the table locking on update can bite you. There's no real disadvantage to using InnoDB other than having to learn to tune it a litte bit, and your data will be much safer.

modoc2• 17 years ago

Googling "myisam vs innodb" gives a lot of pretty informative results, including (http://tag1consulting.com/MySQL_Engines_MyISAM_vs_InnoDB).

The big turn-off for me with MyISAM is that I can run a script to create some tables and setup for FK dependancies, and it will happily run it without errors, but won't enforce the constraints I created. Terrible behavior. I need foreign keys, transactions, row locking, etc...

Of course I now happily use PostgreSQL:)

lucumo• 17 years ago

That's my experience too. We have a few databases of about 500MB-1000MB each. They are running on MyISAM and don't use transactions, foreign keys, constraints or really any kind of database feature other than indexes.

I spent some time optimising it, not a lot of time, maybe a few hours over the course of a year. And even then mostly due to changing machines. It was quite easy to do, reading the manual page and increasing some buffers and so on. Nothing too complex.

We were running into problems in terms of server capacity. On the advise of a friend and just about every comparison on the Internet (notable exception there is the MySQL manual, BTW), we thought we'd give InnoDB a try. I read up on some of the configuration parameters, set some things to values that seemed sane and did a conversion.

Converting from MyISAM to InnoDB took over one hour for one database of about 800M. The server nearly died from that experiment. (Even though only one/tenth of the databases were switched over to InnoDB. The rest was still on MyISAM.) After a few hours to make sure it wasn't an accident, we switched back to MyISAM.

The most concise summary of the performance difference is probably given by comparing the conversion times. From MyISAM to InnoDB took over one hour (1 hour, 8 minutes, 10.74 seconds to be exact). From InnoDB to MyISAM took 2 minutes and 15.35 seconds.

The results were pretty terrible. Ever since I'm convinced InnoDB does not work well with our environment and application.

buro9• 17 years ago

Fulltext indexes are available on MyISAM.

AdamN• 17 years ago

Man this is annoying:

1. The MyISAM/InnoDB debate is so stale it stinks. If you're using MySQL, you should be using InnoDB unless you know exactly why you should use MyISAM. None of these issues are relevant until you have alot of concurrency and uptime/transactions/integrity are radically more important than potentially faster SELECT statements on MyISAM.

2. Did HN really just put this flaccid article to the top of the list? Aren't we more technical than this?

spudlyo2• 17 years ago

I'd vote it down if I could, it's far too fluffy and short for my tastes. The only reason I could think of using MyISAM is if you need features that only it supports, like compression, MRG_MyISAM tables, or full text search.

Having said that, the newer InnoDB plugins support compression, with partitioning you don't need MRG_MyISAM, and you can use something like Sphinx for full text searching, which integrates nicely into MySQL.

The MySQL MyISAM and InnoDB engines and a grocery checkout

Related Stories