MySQL Exotic Storage Engines

April 8, 2010

Introduction

MySQL has an interesting architecture that sets it apart from some other enterprise database systems.  It allows you to plug in different modules to handle storage.  What that means to end users is that it is quite flexible, offering an interesting array of different storage engines with different features, strengths, and tradeoffs.  

In Survey of MySQL Storage Engines, we discussed some of the more common storage engines, MyISAM the default, InnoDB, Archive, Merge, Memory, CSV and NDB.  This time we'll cover some of the newest and more exotic storage engines, and even some that are still in development.

Blackhole

This storage engine acts like Unix's tried and true /dev/null.  At first glance, /dev/null seems kind of useless; why would I need that, you might think.  However, it turns out to be quite useful, and can be used for instance by scripts to suppress the output of stdout or stderr.  It can also be used to overwrite a file with nothing, i.e. delete contents but keep the file in place.  If you have a program that requires a file to store information that for some reason you don't want stored, for instance your friendly neighborhood browser, you can use /dev/null as a creative trick to send such output into a virtual blackhole.

In this way, applications that store their information in tables can use the blackhole storage engine if they want that information to disappear automatically.  Create a view for the application to use, and then point the view at a real table for storage, or a blackhole table when you don't want the information to be persistent.  Privacy related applications come to mind.

Federated

In Oracle, and other enterprise databases, there is the notion of database links.  They allow you to make a connection to an external database and query it's tables.   Federated storage engine provides some of this functionality in MySQL.

Federated storage engine is a bit like creating a view on a remote table.  So, indexes are created on that remote table for instance, and you'd have to go and create one for each of the tables in the remote database that you want to access.  This also means the remote table has to exist before you create the federated table, and the remote server must also be a MySQL server.

Maria

MariaDB is a community distribution of MySQL with the Maria storage engine as the default storage engine.  However, you can also get the Maria storage engine for a regular MySQL community edition database.

The project's goals are to provide a replacement for the long used MyISAM storage engine, but with more crash protection.  They also intend to add transactional support in a future release.  It is still in beta.

XtraDB

XtraDB is based off of InnoDB, and pulled together a lot of the patches and fixes for InnoDB that have been out in the wild  

XtraDB also adds some new functionality to the information_schema data dictionary, adds additional metrics, and ways of controlling  and configuring your storage engines memory usage.

Falcon

This storage engine, like a few others, is a work in progress.  The goal is to be a better transactional storage engine, improving on some of the problems with InnoDB such as scalability, and memory allocation.    It is still in alpha.

PrimeBase XT

Also known as PBXT, this storage engine also has transactional applications in its crosshairs.  However, it attempts to keep some aspects of MyISAM for reasons related to performance.  One of those is how it manages data that has been committed.  In an ACID compliant database, the D stands for durability.  That means when you commit, you know your data has already been written to disk.  Therefore, if you crash right after a commit, your data is guaranteed to be there.  PBXT does not provide this durability. so with crashes your mileage may vary.    It is still in development, so keep an eye on this one.

AWSS3

As the name implies, this storage engine is used for accessing data in Amazon Web Services S3.  S3 exposes internet based storage through a web services interface, so your applications can store and share files as needed.  This storage engine exposes the S3 environment as tables, columns and rows of data in your MySQL database. 

mdbtools

This is an interesting storage engine.  Want to move your data from Microsoft Access, but find import/export too crude.  You can use this storage engine to access the Access mdb files directly, manipulate the data using SQL, and move it into new tables in your MySQL database.  Not surprisingly, this storage engine is read-only.

Ritmark FS (like Oracle)

This is another storage engine, which provides functionality not unlike Oracle's external tables allowing you to use SQL to access files in the filesystem.  Ritmark also has an FTP like tool to upload files.  You would then select and view the contents of those within your application via SQL to find the specific file or content your application requires.

Q4M

This storage engine provides messaging queuing facility in MySQL.  Create a table to create a queue, insert rows to send messages, and receive messages one by one using your SQL where clause, and the queue_wait ('my_queue_table') call.   Prioritization is being added to the feature set as well as relaying between different MySQL instances.  That latter feature is sure to be a useful one.

Conclusions

The MySQL architecture offers a rich selection of storage engines with different characteristics, strengths, and weaknesses.   I hope that this more exotic list of storage engines has piqued your interest of what's happening on the horizon with new development in the MySQL storage engine space.

Additional Resources

MySQL Chapter 13. Storage Engines
MySQL Pluggable Storage Engine Architecture
LinuxPlanet MySQL Storage Engines

» See All Articles by Columnist Sean Hull








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers