Compare MyISAM and InnoDB MariaDB Databases in Cloud Sites

The two most common table engines on Cloud Sites MariaDB servers are InnoDB and MyISAM. This article briefly describes these engines and recommends their usage under certain circumstances in the Cloud Sites environment, without comparing their performance through SQL benchmarks.

The two most commonly used table engines on Cloud Sites MariaDB servers are Innodb and MyISAM. The purpose of this article is to briefly describe these two types and identify which ones are recommended under certain circumstances in the Cloud Sites environment. This article does not compare the performance of the two engine types via running specific SQL test benchmarks.

MariaDB supports several different types of table engines, also known as table types. A database’s tables can be a mix of different table engine types or all the same type. The MariaDB documentation provides more information about each of the types of table engines that MariaDB offers.

XtraDB is the default table engine type for MariaDB, but the Cloud Sites environment defaults the storage engine to Innodb. In other words, Cloud Sites is partial to Innodb if you do not explicitly specify your engine type in your table DDL. The database servers have also been tuned to generally perform better when using the Innodb engine type.

Table Engine Comparison

MyISAM

InnoDB

Not ACID-compliant and non-transactional.(ACID = Atomicity, Consistency, Isolation, Durability)
MySQL 5.0 default engine
Offers compression
Requires full repair and rebuild of indexes and tables
Changed database pages written to disk instantly
No ordering in storage of data
Table-level locking

ACID-compliant and hence fully transactional with ROLLBACK and COMMIT and support for Foreign Keys.(ACID = Atomicity, Consistency, Isolation, Durability)
Cloud Sites default engine
Offers compression
Provides automatic recovery from crashes via the replay of logs
Dirty pages converted from random to sequential before commit and flush to disk
Row data stored in pages in PK order
Row-level locking

The conversion of the table engine from MyISAM to InnoDB is most beneficial when:

If you need ACID compliance and need your database to be transactional.
If you are not disproportionately read-only heavy and are doing a mix of reads (not requiring full text indexing) and writes.
If you face frequent table lock escalations for long periods of time.
If a read is slow or hasn’t completed, and a read-write is waiting on the first read to finish. In such cases, the MyISAM table referenced in the read is held in a locked state till the result set is made available to the query. This also causes a rise in the load average on the server and slows your site down. During this time no reads or writes can complete because MyISAM only has table-level locking. The queries that are victims of lock escalations under heavy but slow reads would do much better as a table converted to Innodb.