Mike, Below is an message written a few months back tailing a discussion on why MyISAM is and is not faster than InnoDB. Basically my understanding is that if you have multiple simultaneous updates/inserts, stay away from MyISAM.
We switched from MyISAM to InnoDB because of the concurrency issue. Sometimes we might have upwards of 500 inserts/s (though the current configuration is causing a backlog and delays). When these updates were done via MyISAM it was really slow. With InnoDB it was much faster (by a factor I believe). Also, I have yet to find any documentation to say that if InnoDB doesn't fit into ram you will see a performance penalty (then again, I haven't been looking around that much for it). Can anyone else chime in on this? ****** Quoting: From: Axel Schwenke Date: Sunday, October 22, 2006 There are *no* transactions with MyISAM. If you need transactions, go InnoDB. MyISAM always locks whole tables. That is: reads and writes are mutually exclusive. Concurrent reads are fine, concurrent writes are sometimes possible. If you expect concurrent reads and writes on your tables -> go InnoDB. OTOH: MyISAM table locks are really, really fast. As long as you do not need hundreds of writes per second (along with reads on the same table) you can still use MyISAM. Reads and writes will be serialized internally, but you won't notice. Again: performance isn't the only - not even the most important - criteria for chosing a storage engine.XL -- Axel Schwenke, Senior Software Developer, MySQL AB > -----Original Message----- > From: mos [mailto:[EMAIL PROTECTED] > Sent: Friday, March 02, 2007 7:39 AM > To: mysql@lists.mysql.com > Subject: Best update strategy with MyISAM > > I will have an application that will have 1000 20 row selects executed > every second. The application will also be updating individual rows > from > this table at a rate of 100 rows/second. I prefer to use MyISAM instead > of > InnoDb because eventually the table will exceed the amount of memory on > the > machine and InnoDb is too slow if the table doesn't fit into memory. > > With MyISAM: > 1) Will the updates block the Select statements from executing? If so, > is > there a way around it? I don't want to stop the Select's from executing > by > having them wait for an update lock to complete. > 2) Is it better to update a secondary table and then use a Select with > a > join to display the results? Or will it matter? > > TIA > > Mike > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
