mysql issue

Steve Meyers steve-plug at spwiz.com
Wed Feb 8 16:16:44 MST 2012


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


More information about the PLUG mailing list