With MySQL 5.1, MySQL AB has introduced a new pluggable storage engine architecture that allows storage engines to be loaded into and unloaded from 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. Thus, although there are different capabilities across different storage engines, the application is shielded from these differences.

The MySQL pluggable storage engine architecture has the structure shown in the following figure:

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, or high availability situations — 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 feature 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 reduce unnecessary overhead. 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. 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 some storage engines provided with MySQL:

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 Chapter 15, Storage Engines and Table Types.

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 table option:

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;

The following storage engines support transactions:

Before a storage engine can be used, the storage engine plugin shared library must be loaded into MySQL using the INSTALL PLUGIN statement. For example, if the EXAMPLE engine plugin is named ha_example and the shared library is named ha_example.so, you load it with the following statement:

INSTALL PLUGIN ha_example SONAME 'ha_example.so';

The shared library must be located in the MySQL server plugin directory, the location of which is given by the plugin_dir system variable.

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

UNINSTALL PLUGIN ha_example;

If you unplug a storage engine that is needed by existing tables, those tables become accessible, but will still be present on disk (where applicable). Ensure that there are no tables using a storage engine before you unplug the storage engine.

To install a pluggable storage engine, the plugin file must be located in the MySQL plugin directory, and the user issuing the INSTALL PLUGIN statement must have INSERT privileges for the mysql.plugin table.