DBA > Articles

Writing a Custom Storage Engine

By:
To read more DBA articles, visit http://dba.fyicenter.com/article/

With MySQL 5.1, MySQL AB has introduced a pluggable storage engine architecture that makes it possible to create new storage engines and add them to a running MySQL server without recompiling the server itself.

This architecture makes it easier to develop new storage engines for MySQL and deploy them.

This chapter is intended as a guide to assist you in developing a storage engine for the new pluggable storage engine architecture.

The storage engines manage data storage and index management for MySQL. The MySQL server communicates with the storage engines through a defined API.

Each storage engine is a class with each instance of the class communicating with the MySQL server through a special handler interface.

Handlers are instanced on the basis of one handler for each thread that needs to work with a specific table. For example: If three connections all start working with the same table, three handler instances will need to be created.

Once a handler instance is created, the MySQL server issues commands to the handler to perform data storage and retrieval tasks such as opening a table, manipulating rows, and managing indexes.

Custom storage engines can be built in a progressive manner: Developers can start with a read-only storage engine and later add support for INSERT, UPDATE, and DELETE operations, and even later add support for indexing, transactions, and other advanced operations.

reating Storage Engine Source Files
The easiest way to implement a new storage engine is to begin by copying and modifying the EXAMPLE storage engine. The files ha_example.cc and ha_example.h can be found in the storage/example directory of the MySQL 5.1 source tree. For instructions on how to obtain the 5.1 source tree, see MySQL Installation Using a Source Distribution.

When copying the files, change the names from ha_example.cc and ha_example.h to something appropriate to your storage engine, such as ha_foo.cc and ha_foo.h.

After you have copied and renamed the files you must replace all instances of EXAMPLE and example with the name of your storage engine. If you are familiar with sed, these steps can be done automatically (in this example, the name of your storage engine would be FOO):

sed s/EXAMPLE/FOO/g ha_example.h | sed s/example/foo/g > ha_foo.h
sed s/EXAMPLE/FOO/g ha_example.cc | sed s/example/foo/g > ha_foo.cc

[edit] Adding Engine Specific Variables and Parameters

For more information on adding system variables, status variables, and options supported on the command line or configuration file, see Specifying_mysqld_Variables_within_a_Plugin
[edit] Creating the handlerton

The handlerton (short for handler singleton) defines the storage engine and contains method pointers to those methods that apply to the storage engine as a whole, as opposed to methods that work on a per-table basis. Some examples of such methods include transaction methods to handle commits and rollbacks.
Here's an example from the EXAMPLE storage engine:
handlerton example_hton= {
"EXAMPLE",
SHOW_OPTION_YES,
"Example storage engine",
DB_TYPE_EXAMPLE_DB,
NULL, /* Initialize */
0, /* slot */
0, /* savepoint size. */
NULL, /* close_connection */
NULL, /* savepoint */
NULL, /* rollback to savepoint */
NULL, /* release savepoint */
NULL, /* commit */
NULL, /* rollback */
NULL, /* prepare */
NULL, /* recover */
NULL, /* commit_by_xid */
NULL, /* rollback_by_xid */
NULL, /* create_cursor_read_view */
NULL, /* set_cursor_read_view */
NULL, /* close_cursor_read_view */
example_create_handler, /* Create a new handler */
NULL, /* Drop a database */
NULL, /* Panic call */
NULL, /* Release temporary latches */
NULL, /* Update Statistics */
NULL, /* Start Consistent Snapshot */
NULL, /* Flush logs */
NULL, /* Show status */
NULL, /* Replication Report Sent Binlog */
HTON_CAN_RECREATE
};

This is the definition of the handlerton from handler.h:
typedef struct
{
const char *name;
SHOW_COMP_OPTION state;
const char *comment;
enum db_type db_type;
bool (*init)();
uint slot;
uint savepoint_offset;
int (*close_connection)(THD *thd);
int (*savepoint_set)(THD *thd, void *sv);
int (*savepoint_rollback)(THD *thd, void *sv);
int (*savepoint_release)(THD *thd, void *sv);
int (*commit)(THD *thd, bool all);
int (*rollback)(THD *thd, bool all);
int (*prepare)(THD *thd, bool all);
int (*recover)(XID *xid_list, uint len);
int (*commit_by_xid)(XID *xid);
int (*rollback_by_xid)(XID *xid);
void *(*create_cursor_read_view)();
void (*set_cursor_read_view)(void *);
void (*close_cursor_read_view)(void *);
handler *(*create)(TABLE *table);
void (*drop_database)(char* path);
int (*panic)(enum ha_panic_function flag);
int (*release_temporary_latches)(THD *thd);
int (*update_statistics)();
int (*start_consistent_snapshot)(THD *thd);
bool (*flush_logs)();
bool (*show_status)(THD *thd, stat_print_fn *print, enum ha_stat_type stat);
int (*repl_report_sent_binlog)(THD *thd, char *log_file_name, my_off_t end_offset);
uint32 flags;
} handlerton;

There are a total of 30 handlerton elements, only a few of which are mandatory (specifically the first four elements and the create() method).
1. The name of the storage engine. This is the name that will be used when creating tables (CREATE TABLE ... ENGINE = FOO;).
2. The value to be displayed in the status field when a user issues the SHOW STORAGE ENGINES command.
3. The storage engine comment, a description of the storage engine displayed when using the SHOW STORAGE ENGINES command.
4. An integer that uniquely identifies the storage engine within the MySQL server. The constants used by the built-in storage engines are defined in the handler.h file. Custom engines should use DB_TYPE_UNKOWN.
5. A method pointer to the storage engine initializer. This method is only called once when the server starts to allow the storage engine class to perform any housekeeping that is necessary before handlers are instanced.
6. The slot. Each storage engine has its own memory area (actually a pointer) in the thd, for storing per-connection information. It is accessed as thd->ha_data[foo_hton.slot]. The slot number is initialized by MySQL after foo_init() is called. For more information on the thd, see #Implementing ROLLBACK.
7. The savepoint offset. To store per-savepoint data the storage engine is provided with an area of a requested size (0, if no savepoint memory is necessary).

The savepoint offset must be initialized statically to the size of the needed memory to store per-savepoint information. After foo_init it is changed to be an offset to the savepoint storage area and need not be used by the storage engine. For more information, see #Specifying the Savepoint Offset.

1. Used by transactional storage engines, clean up any memory allocated in their slot.
2. A method pointer to the handler's savepoint_set() method. This is used to create a savepoint and store it in memory of the requested size.
For more information, see #Implementing the savepoint_set Method.
1. A method pointer to the handler's rollback_to_savepoint() method. This is used to return to a savepoint during a transaction. It's only populated for storage engines that support savepoints.
For more information, see #Implementing the savepoint_rollback() Method.
1. A method pointer to the handler's release_savepoint() method. This is used to release the resources of a savepoint during a transaction. It's optionally populated for storage engines that support savepoints.
For more information, see #Implementing the savepoint_release() Method.
1. A method pointer to the handler's commit() method. This is used to commit a transaction. It's only populated for storage engines that support transactions.
For more information, see #Implementing COMMIT.
1. A method pointer to the handler's rollback() method. This is used to roll back a transaction. It's only populated for storage engines that support transactions.
For more information, see #Implementing ROLLBACK.
1. Required for XA transactional storage engines. Prepare transaction for commit.
2. Required for XA transactional storage engines. Returns a list of transactions that are in the prepared state.
3. Required for XA transactional storage engines. Commit transaction identified by XID.
4. Required for XA transactional storage engines. Rollback transaction identified by XID.
5. Called when a cursor is created to allow the storage engine to create a consistent read view.
6. Called to switch to a specific consistent read view.
7. Called to close a specific read view.
8. MANDATORY - Construct and return a handler instance.
For more information, see #Handling Handler Instantiation.
1. Used if the storage engine needs to perform special steps when a schema is dropped (such as in a storage engine that uses tablespaces).
2. Cleanup method called during server shutdown and crashes.
3. InnoDB-specific method.
4. InnoDB-specific method called at start of SHOW ENGINE InnoDB STATUS.
5. Method called to begin a consistent read.
6. Called to indicate that logs should be flushed to reliable storage.
7. Provides human readable status information on the storage engine for SHOW ENGINE foo STATUS.
8. InnoDB-specific method used for replication.
9. Handlerton flags that indicate the capabilities of the storage engine. Possible values are defined in sql/handler.h and copied here:

Full article...


Other Related Articles

... to read more DBA articles, visit http://dba.fyicenter.com/article/