MySQL supports several different types of Table Engines also known as "Table Types". A database can have its tables being a mix of different table engine types or all of the same type. Here is more information on each of the different types of table engines that MySQL offers:
The two most commonly used on most Cloud Sites MySQL servers use Innodb and MyISAM engines.
The purpose of this document is to briefly cover the two types and identify which ones are more recommended under what circumstances in the Cloud Sites environment. Please note that the purpose of this document is however not to go over a performance comparison of each of the two engine types as far as comparing via running specific sql test benchmarks, which if you are interested are well done on the two links below:
MyISAM is the default table engine type for MySQL 5.0 but the Cloud Sites environment defaults the storage engine to Innodb. In other words Cloud Sites is partial to Innodb if you do not explicity specify your engine type in your table DDL. We have also tuned the database servers to generally perform best with using the Innodb Engine type.
|Not *ACID compliant and non-transactional||*ACID compliant and hence fully transactional with ROLLBACK and COMMIT and support for Foreign Keys|
|MySQL 5.0 Default Engine||Rackspace Cloud Default Engine|
|Offers Compression||Offers Compression|
|Requires full repair/rebuild of indexes/tables||Auto recovery from crash via replay of logs|
|Changed Db pages written to disk instantly||Dirty pages converted from random to sequential before commit and flush to disk|
|No ordering in storage of data||Row data stored in pages in PK order|
|Table level locking||Row level locking|
If you need to see further details on each of the two engine types, please refer to the following MySQL documentations:
There can be several other reasons that fit your requirement for choosing the MyISAM engine. For example reads can be faster on MyISAM vs Innodb despite what the general claims on the above two links when MyISAM table has fixed (not dynamic) row size i.e. when it uses more CHARs for example versus VARCHARs. Still there could be other reasons besides this why you choose or have chosen MyISAM over Innodb. Another reason why you may have chosen MyISAM over Innodb is perhaps due to the fact that Innodb must perform additional checks owing to its ACID compliant nature - so for example a FK check needs to be checked which could potentially cause an operational overhead. Unless you have benchmarked this to be the case, I would not recommend you believing this to be the case as default as per the links above, you may find out otherwise. But in our experience the most pressing requirement comes if you require full text indexing type search capabilities, then that is one main reason that makes MyISAM more desirable to use.
So to summarize, the queries that are victims of lock escalations under heavy but slow reads would do much better as a table converted to Innodb.
You do so by simply issuing the "ALTER TABLE" DDL statement:
ALTER TABLE <table-name> ENGINE=INNODB;
Below is a step by step process for altering a table in PHPMyAdmin:
Note: A MyISAM table that is using FULL TEXT Indexing can not be converted to an Innodb Table Engine type.
© 2011-2013 Rackspace US, Inc.
Except where otherwise noted, content on this site is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported License