comprehensive documentation repository

MySQL 5.5 Reference Manual

13.4.2. The Common Database Server Layer

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:

Concurrency: Some applications have
more granular lock requirements (such as row-level locks)
than others. Choosing the right locking strategy can reduce
overhead and therefore improve overall performance. This
area also includes support for capabilities such as
multi-version concurrency control or “snapshot”
read.

Transaction Support: Not every
application needs transactions, but for those that do, there
are very well defined requirements such as ACID compliance
and more.

Physical Storage: This involves
everything from the overall page size for tables and indexes
as well as the format used for storing data to physical
disk.

Index Support: Different application
scenarios tend to benefit from different index strategies.
Each storage engine generally has its own indexing methods,
although some (such as B-tree indexes) are common to nearly
all engines.

Memory Caches: Different applications
respond better to some memory caching strategies than
others, so although some memory caches are common to all
storage engines (such as those used for user connections or
MySQL's high-speed Query Cache), others are uniquely defined
only when a particular storage engine is put in play.

Miscellaneous Target Features: This may
include support for geospatial operations, security
restrictions for certain data manipulation operations, and
other similar features.

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.