The MySQL Storage Engines Landscape

The MySQL Storage Engines Landscape

By Colin Charles
Date: Saturday, 24 September 2011 14:00
Duration: 45 minutes
Language: English
Tags: engines mysql storage

A unique selling point of MySQL is that is supports multiple storage engines, which basically means users get to use the same top-level SQL interface while storing their data in many different sorts of methods. However as these are benefits they also come with some trade-off's and we discuss some (and point to solutions):

* transaction support - not all storage engines support transactions, and storage engines that do use different locking strategies, so cross-storage engine transactions are always interesting. Do you choose a transactional engine for your workload? When is it right to use a non-transactional engine like MyISAM?
* backup - cross-storage engine backup does not work unless you use OS-level approaches like LVM/ZFS snapshots. In-memory engines will naturally not allow snapshots to work. How do you backup across engines?
* replication for different storage engines differ in the sense that we already write a second binlog, despite transactional engines having their existing one. How do you replicate when you have a mix of engines?
* how do you monitor when you have several engines? What resources do you allocate to each in the configuration?
* how does the optimiser deal with all the different storage engines?

Today in MySQL 5.5 and greater, InnoDB is the default storage engine. It has spawned two large forks - XtraDB and HailDB (for Drizzle). Previously, MyISAM was the default storage engine. MySQL by default ships with about a dozen engines, and other branches like MariaDB ship with close to twenty.

Naturally we'll cover cool tricks you can do with storage engines. For example, how you can make good use of the Spider storage engine for vertical partitioning? When do you use the Archive storage engine to store log tables? When do you use the Federated tables to get different views or execute remote commands? How do you use the Blackhole engine for replication relay despite an engine that really is the equivalent of /dev/null in Unix?

We will go through the entire landscape, including the commercial landscape, and show you what engine is correct for your use-case. If you're a developer, you will benefit from learning about the extended storage engine API, in MariaDB which for starters supports an extended CREATE TABLE functionality.