The first main difference is that InnoDB has transactional
capabilities (all-or-nothing) and MyISAM not yet. If you need to use
COMMIT on some statements, you need to use InnoDB. The second point is
the locking of MyISAM, which is on table-level. If your environment is
mixed with read/write concurrency, this is not good, because
table-level locking is exactly what you read. InnoDB has unlimited
If you want to use MyISAM, read about the locking. By default, write
requests take priority over read requests. But you can change the
priority of your requests. For example, you can use the modifier
LOW_PRIORITY with statements that update update tables like INSERT or
UPDATE. But take care, because if read statements keep arriving, it is
possible for a low-priority write request never to be performed.
The last tip: even MyISAM with table-level locking, it support
concurrent inserts. If you have many UPDATE statements and/or DELETE,
you have holes in your tables, and this disable concurrent inserts. Or
you optimize table (to defragment) or change the concurrent_insert
system variable to 2, to force concurrent inserts, or both. But, read
about this too.
There are many differences about this two storage engines, and I hope
I have helped you.
2010/9/16 monloi perez <mlp_folder@stripped>:
> Any idea on this?
> From: Michael Satterwhite <michael@stripped>
> To: mysql@stripped
> Sent: Mon, September 13, 2010 1:07:38 AM
> Subject: Re: skip locking
> On Sunday, September 12, 2010 09:32:12 am monloi perez wrote:
>> Hi All,
>> Sorry if I posted on the wrong list.
>> I've had this issue with my previous server already, seems like
>> skip-locking configuration does not seem to work on myisam tables.
>> Also what is the difference between myisam and innodb tables. The reason I
>> wanted to know is that I think skip-locking works better in InnoDB. Or
>> perhaps the right question is when do I choose myISAM and when do I choose
>> InnoDB. My current server is running on an 8-core cpu with 32G of memory
>> and only running CMS or news based web applications. What do you suggest
>> that we use for the storage engine?
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1