RE: Best update strategy with MyISAM

[mos]

At 11:18 AM 3/2/2007, Gary W. Smith wrote:
>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?


Thanks Gary. I should have also mentioned that this will be a web 
application and the writes will be batched (accumulated in memory and 
written out in one or two SQL operations). So the 100 updates will be 100 
rows being updated by one or two SQL commands, not 100 users doing updates 
simultaneously.

The reason for my original question was some databases do allow for 
simultaneous reads and writes without blocking each other (and still uses 
page locks-the writes don't block the reads). I was hoping MySQL's MyISAM 
had such an option without having to resort to InnoDb. I've never gotten 
any kind of speed out of InnoDb and would like to avoid it if possible. I 
was hoping Raven out be out by now but that may be a couple of years away 
before it in production.

The only other solution would be to use MySQL Cluster but that of course 
requires a lot more hardware. Eventually I may need a cluster but I was 
hoping to avoid the startup costs. I assume of course that the cluster will 
have fast row locking so writes won't block reads and select statements 
will run much faster than InnoDb. :)

Mike




>****** 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
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:    http://lists.mysql.com/mysql?unsub=1