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:[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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
