MyISAM vs InnoDB

August 22, 2010 at 7:48 pm · Filed under Tech

Lucky asked me the other day if there was any reason to use MyISAM instead of InnoDB for a MySQL database. Scum thought my response was useful, so I’m posting it in case it would be useful for anyone else:

From what I understand, there are still several advantages to MyISAM. If your database load is primarily read or primarily write activity, MyISAM is better. For mixed loads, InnoDB wins. Also, InnoDB comes with a lot of overhead of journaling and stuff to make it a “real” database. MyISAM is pretty simple. If you don’t need all those features, you might be better off with MyISAM.

In InnoDB’s favor:

Obviously if you need anything along the lines of transactions, foreign key constraints, or some general feeling of safety that your data will actually survive after something goes wrong, InnoDB is the answer.

MyISAM has no cache for data. Some or all of its index can get cached, but data retrieval always results in a disk hit. The kernel can help, but it’s still less than ideal. InnoDB uses the same structure to store the index and the data, so make your buffer pool size large enough, and the whole thing can be in RAM.

MyISAM tables get fragmented easily. They have a pretty boring mechanism of marking rows as deleted then filling them back in with new data, fragmenting as needed. It will stay that way until you do an OPTIMIZE TABLE. I don’t remember exactly how things work with InnoDB, but the problem isn’t nearly as bad, and you don’t need to do periodic table optimizations.

InnoDB has row-level locks, MyISAM has to lock the entire table whenever an update occurs. Obviously this is important for a database with mixed read/write activity because you can update part of a table while letting things continue on the rest of the table. There are several caveats to this rule. InnoDB locks have lots of subtleties and you’ll probably end up locking more of the table than you thought you were. Also, MyISAM does support a mode where a table can have INSERT and SELECT statements occurring concurrently, but you need to have very few UPDATE/DELETE statements for it to work.

That’s what I can think of off the top of my head. In general, I’d say go with InnoDB unless you have a good reason. It’ll give you a “real” database. If you don’t need a relational database (which I’m increasingly convinced is the case), use MongoDB or something. As always, the final answer depends on the characteristics of the particular application: benchmarks are your friend. Oh, and MySQL Performance Blog is an excellent resource for everything on the topic of MySQL tuning.

Comments (4)

The Suburbs Review

August 8, 2010 at 7:22 pm · Filed under Life

Last week, Rebecca asked me if I’d heard the new Arcade Fire album, The Suburbs. My reply ended up being more or less a full review, so here are my thoughts on the album:

It’s pretty great, but at this point, I wouldn’t rank it as high as their previous two albums. While they maintain their trademark epic soundscape, the music itself doesn’t sound quite as epic to me. “Neon Bible” has several multi-part songs and lots of cool things with harmonies, counter-melodies, time signature changes, creative instrumentation (boys choir, mens choir, pipe organ(!), bells, etc.), and dark lyrics reflecting on such weighty topics as postmodernism and theology.

On the other hand, the topic at hand in the album–the suburbs–doesn’t really lend itself to those kinds things. Do you really need a pipe organ and a boys choir to bemoan the fact that the house you grew up in looked like all the other ones on your block? Besides, their instrumentation does evoke a bit of a 90s vibe, which makes sense given that Win and Regine are in their early 30s.

And the album certainly has its moments. Who would have thought endlessly repeating the word “rococo” would be so interesting? There’s also the always-eloquent biting cultural commentary: “They heard me singing and they told me to stop. Quit these pretentious things and just punch the clock” (Sprawl II [Mountains Beyond Mountains]) and “In line for a number, but you don’t understand. Like a modern man” (Modern Man). And I particularly enjoyed “We Used to Wait,” their lament over the transience of modern communication and our obsession with instant gratification.

So should you buy it? Yes. But also get “Neon Bible” and “Funeral.” :-)

Comments

Her name is Yoshimi

August 1, 2010 at 5:48 pm · Filed under Life

She’s a black belt in Karate. :-)

I adopted her from from the friendly folks at Humanimal Connection a few weeks ago. It’s nice to finally have a cat. Even if she does think play time comes well before my alarm clock goes off…

The best part is that I didn’t even make up her name. It came from her previous owners. Clearly it was meant to be.

Comments (2)