RE: Best update strategy with MyISAM

[Gary W. Smith]

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:mos99@stripped]
> Sent: Friday, March 02, 2007 7:39 AM
> To: mysql@stripped
> 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/mysql?unsub=1