On 2/8/12 4:02 PM, Steve Alligood wrote: > Why would you run performance critical really large data sets on > spindles? If it is performance critical, put your db on SSDs.
A very good point, but not always economically feasible. > This entire discussion has been from a DBA standpoint. From a SysAdmin > standpoint, splitting out your tables into files makes life MUCH better > when things go south. In fact, MyISAM tables are MUCH better when things > go south, as they are both easier and faster to repair and get back online. Eeek. If you get in that situation, you're much better off if you have a good replication setup, so you can just promote a slave and be done with it. MyISAM is actually potentially a lot worse. If you are using the proper settings in InnoDB for durable storage, it is actually very good at repairing itself, although if your settings aren't right it could take a while. > I have also found that MyISAM is considerably faster for relatively > simple datasets, especially if you are mostly doing inserts and not a > lot of selects. Constraints, checkpoints, row locking, etc, all add > overhead. In fact, if write speed is important, don't even do more > indexes that you specifically need. MyISAM can do read-lock inserts (under certain conditions), so this is probably the area where it shines the most. The point about indexes is especially true -- I've found that many developers like to add an index any time a query is a little slow, and I've seen lots of redundant and useless indexes on tables. > Basically, if you don't specifically need what InnoDB brings to the > party, stick with MyISAM every time and you will have far less problems > in a repair situation, simpler management, and faster writes. As long as your hardware is simple enough, yes that is true. But most people don't realize how much InnoDB brings to the table, especially with today's hardware. Here's a study Oracle did on multi-core performance of InnoDB vs. MyISAM: http://www.oracle.com/partners/en/knowledge-zone/mysql-5-5-innodb-myisam-522945.pdf Steve /* PLUG: http://plug.org, #utah on irc.freenode.net Unsubscribe: http://plug.org/mailman/options/plug Don't fear the penguin. */
