In this tutorial, we will take an in-depth look at MySQL's "Pluggable Storage Engine" architecture. Understanding the features and trade-offs in each engine allows developers to optimise their applications by making appropriate choices and tuning the MySQL server appropriately for their needs.

For example, logging of page clicks on a web site places completely different requirements on a database from say tracking customers and sales. Functionally, either can be done using generic solutions. But by utilising specific features available in specialised storage engines, extraordinary performance improvements can be attained.

This becomes particularly relevant when there are specific speed and scalability requirements for an application. Yahoo! uses the ARCHIVE storage engine to deal efficiently with the massive amounts of user traffic information that is continually generated. A general purpose storage system would simply not do.

In MySQL, the storage engine can be selected on a per-table basis. This means that different engines can be used from within a single application, as appropriate for the application's needs. In many cases, the application need not even be aware which engine is used.

In this tutorial, the different available storage engines will be compared. Also, the fundamentals of adding new storage engines will be discussed.

Overview of the MySQL Pluggable Storage Engine Architecture:

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 encapsulates 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.

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.

Currently Available Storage Engines:

• MyISAM - the storage engine that is used the most in Web, data warehousing, and other application environments.
• InnoDB - used for transaction processing applications, and sports a number of features including ACID transaction support.
• BDB - an alternative transaction engine to InnoDB that supports COMMIT, ROLLBACK, and other transactional features.
• Memory - stores all data in RAM for extremely fast access in environments that require quick look ups of reference and other like data.
• Merge - allows a MySQL DBA or developer to logically group together a series of identical MyISAM tables and reference them as one object. Good for VLDB environments like data warehousing.
• Archive - provides the perfect solution for storing and retrieving large amounts of seldom-referenced historical, archived, or security audit information.
• Federated - offers the ability to link together separate MySQL servers to create one logical database from many physical servers. Very good for distributed or data mart environments.
• Cluster/NDB - the Clustered database engine of MySQL that is particularly suited for applications with high performance lookup needs that also require the highest possible degree of uptime and availability.
• Other - other storage engines include CSV (references comma-separated files as database tables), Blackhole (for temporarily disabling application input to the database) and an Example engine that helps jump start the process of creating custom pluggable storage engines.

While the above brief descriptions will give you a general idea of what type of application might benefit from a particular storage engine, a more detailed look at various common database tasks and needs across the various engines may help delineate the differences a little more.

Of course, you can use multiple storage engines in a single application; you are not limited to using only one storage engine in a particular database. So, you can easily mix and match storage engines for the given application need. This is often the best way to achieve optimal performance for truly demanding applications: use the right storage engine for the right job.