MySQL Pluggable Storage Engine Architecture

MySQL is very different from other databases, in that it's storage engine is pluggable, by pluggable I mean is that the MySQL Instance core code is separate from the storage engine, which means that we can choose a pluggable storage engine that fits as per the application.

MySQL has many storage engines, here is a list of few common engines:

Storage Engine	Transactional Support	Locking Level	Online Backup	Version(s) available
InnoDB (default)	Yes	Row	Yes	5.1, 5.5, 5.6
MyISAM/Merge	No	Table	No	5.1, 5.5, 5.6
Memory	No	Table	No	5.1, 5.5, 5.6
Marta	Yes	Row	No	5.1, 5.5, 5.6
Falcon	Yes	Row	Yes	5.6
PBXT	Yes	Row	Yes	5.1, 5.5, 5.6
FEDERATED	No	n/a	n/a	5.1, 5.5, 5.6
NDB	Yes	Row	Yes	5.1 and in MySQL Cluster
Archive	No	Row	No	5.1, 5.5, 5.6
CSV	No	Table	No	5.1, 5.5, 5.6

The storage engines can vastly improve a database performance when using the correct one, below is a list of parameters that allow you to decide to change from the default InnoDB storage engine:

- Transactional Support - If you application does not require transactional support then you can reduce the resources by using a non-transaction engine.

- Table-Level features - If you need specific features for an example the checksum feature then you would have to use the MyISAM engine.

- Locking - Depending on your application you may want row or table locking.

- Indexing - Different storage engines use different indexing strategies that meet different application types, OLAP, Data Warehouse, etc.

- Foreign Keys - A number of storage engines do not support foreign keys, so double check when you select a particular engine.

- Buffering - Some engines use buffering others don't, for example the MyISAM engine does not buffer data but supports multiple buffers for indexes.

- File Storage - Some engines store their data/indexes in self-contained files, others use centralized metadata this has a impact if you want to move the data files to another server.

- Backup - some engines allow you to perform hot backups, others you need to take the database down before performing a backup.

Although the idea is great sometimes you can get too bogged down on what engine to choose, hence that's why most mysql storage engines will be either be MyISAM or InnoDB. Today we will be covering only MyISAM, InnoDB, MEMORY and CSV in depth, and rest you can read from the MySQL documentation.

MyISAM was the default engine until recently, this engine has been used since MySQL version 3.2, it is a non-transactional engine and does not implement any additional locking mechanisms, this may cause problems when you have a large number of concurrent writes to a table. If your application has a lot of write activity the writes will end up blocking the reads which will cause performance problems, thus you should use either a InnoDB or Falcon engine.