Hacker News Reader

Comment by DonnyK | Hacker News Reader

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.

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.

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:)

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.

Define 'easy to trash'. I've been using MyISAM without problems for years now.

MyISAM is like the ext2 filesystem. You crash and you end up spending a long time doing an fsck, where you have to verify the consistency by walking all the data structures. InnoDB is more like ext3, where you can make things consistent by replaying the changes in the journal. Doing repair on a large MyISAM table can take hours and hours.

I'm curious about this too -- I've used MyISAM for many projects without a problem.

The two ways I've experienced:

1. Yank power cord while db writes are happening.

2. Grow the tables over a couple of GB in size.

The additional storage cost of InnoDB can be quite significant if you have a lot of data. We have about 4 billion rows in MyISAM, we simply wouldn't have space to do that with InnoDB.

Hopefully compressed tables will improve matters.

Reply on news.ycombinator.com