With MySQL 5.1, MySQL AB has introduced a new pluggable storage engine architecture that allows storage engines to be loaded into a running MySQL server.

This chapter describes the pluggable storage engine architecture and provides an overview of the various storage engines provided with MySQL along with instructions on how to assign storage engines to tables and plug and unplug storage engines.

The MySQL pluggable storage engine architecture allows a database professional to select a specialized storage engine for a particular application need while being completely shielded from the need to manage any specific application coding requirements. The MySQL server architecture isolates the application programmer and DBA from all of the low-level implementation details at the storage level providing a consistent and easy application model and API. So while there are different capabilities across different storage engines, the application is shielded from these.

Graphically depicted, the MySQL pluggable storage engine architecture looks like the following:

Figure 14.1. The MySQL pluggable storage engine architecture

The pluggable storage engine architecture provides a standard set of management and support services that are common among all underlying storage engines. The storage engines themselves are the components of the database server that actually perform actions on the underlying data that is maintained at the physical server level.

This efficient and modular architecture provides huge benefits for those wishing to specifically target a particular application need – such as data warehousing, transaction processing, high availability situations, etc. – while enjoying the advantage of utilizing a set of interfaces and services that are independent of any one storage engine.

The application programmer and DBA interact with the MySQL database through Connector APIs and service layers that are above the storage engines. If application changes bring about requirements that demand the underlying storage engine change, or that one or more additional storage engines be added to support new needs, no significant coding or process changes are required to make things work. The MySQL server architecture shields the application from the underlying complexity of the storage engine by presenting a consistent and easy to use API that applies across storage engines.

A MySQL pluggable storage engine is the component in the MySQL database server that is responsible for performing the actual data I/O operations for a database as well as enabling and enforcing certain feature sets that target a specific application need. A major benefit of using specific storage engines is that you are only delivered the features needed for a particular application, and therefore you have less system overhead in the database, with the end result being more efficient and higher database performance. This is one of the reasons that MySQL has always been known to have such high performance, matching or beating proprietary monolithic databases in industry standard benchmarks.

From a technical perspective, what are some of the unique supporting infrastructure components that are in a storage engine? Some of the key differentiations include:

Each set of the pluggable storage engine infrastructure components are designed to offer a selective set of benefits for a particular application. Conversely, avoiding a set of component features helps steer clear of unnecessary overhead. So it stands to reason that understanding a particular application’s set of requirements and selecting the proper MySQL storage engine can have a dramatic impact on overall system efficiency and performance.

The various storage engines provided with MySQL are designed with different use-cases in mind. In order to use the pluggable storage architecture effectively, it is good to have an idea of the benefits and drawbacks of the various storage engines.

The following table provides an overview of the storage engines provided with MySQL:

Figure 14.2. Storage engine comparison

The following storage engines are the most commonly used:

It is important to remember that you are not restricted to using the same storage engine for an entire server or schema: you can use a different storage engine for each table in your schema.

For detailed information on the storage engines included with MySQL, see Section 14.4, “Choosing a Storage Engine”.

Storage engines can be designated either when creating new tables or through the use of an ALTER TABLE statement.

To specify a storage engine when creating a table, use the ENGINE parameter:

CREATE TABLE engineTest(
id INT
) ENGINE = MyISAM;

To change the storage engine of an existing table, use the ALTER TABLE statement:

ALTER TABLE engineTest ENGINE = ARCHIVE;

Transactions are supported by the following storage engines:

Before a storage engine can be used, the storage engine plugin must be loaded into mysql using the INSTALL PLUGIN statement. For example, to load the example engine you would first load the ha_example.so module:

INSTALL PLUGIN ha_example SONAME 'ha_example.so';

The .so file must be located in the MySQL server library directory (typically installdir/lib).

To unplug a storage engine, use the UNINSTALL PLUGIN statement:

UNINSTALL PLUGIN ha_example;

If you unplug a storage engine that is being used by existing tables, those tables will not be accessible. Ensure that there are no tables using a storage engine before you unplug the storage engine.

In order to install a pluggable storage engine, the plugin file must be located in the appropriate MySQL library directory, and the user issuing the INSTALL PLUGIN statement must have the SUPER privilege.