The MySQL Storage Engines LandscapeColin Charles
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):
* storage engines that support transactions 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? * 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. What about 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.
Cool tricks covered: 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?