From: Date: March 2 2007 6:18pm
Subject: RE: Best update strategy with MyISAM
List-Archive: http://lists.mysql.com/mysql/205346
Message-Id: <57F9959B46E0FA4D8BA88AEDFBE5829024F129@pxtbenexd01.pxt.primeexalia.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Mike,=20

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. =20

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?=20


****** Quoting:

From: Axel Schwenke Date:   Sunday, October 22, 2006
=20
There are *no* transactions with MyISAM. If you need transactions,=20
go InnoDB.=20

MyISAM always locks whole tables. That is: reads and writes are=20
mutually exclusive. Concurrent reads are fine, concurrent writes=20
are sometimes possible. If you expect concurrent reads and writes=20
on your tables -> go InnoDB. OTOH: MyISAM table locks are really,=20
really fast. As long as you do not need hundreds of writes per=20
second (along with reads on the same table) you can still use=20
MyISAM. Reads and writes will be serialized internally, but you=20
won't notice.=20

Again: performance isn't the only - not even the most important -=20
criteria for chosing a storage engine.XL=20
--=20
Axel Schwenke, Senior Software Developer, MySQL AB=20


> -----Original Message-----
> From: mos [mailto:mos99@stripped]
> Sent: Friday, March 02, 2007 7:39 AM
> To: mysql@stripped
> Subject: Best update strategy with MyISAM
>=20
> 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.
>=20
> 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?
>=20
> TIA
>=20
> Mike
>=20
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=3Dgary@stripped