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
