« 21,052 Queries per Second on AMD64 MySQL | Main | INFORMATION_SCHEMA in MySQL »

January 24, 2005

MySQL Adds Federated Storage Engine

This is cool, MySQL has added (scheduled for release with 5.0.3) a new storage engine, called the federated storage engine. The jist, federated tables reside on a remote machine.

SQL statements are issued using the local client, as you would with any query. However, when MySQL gets the data, rather than pulling it from the local filesystem, it contacts another MySQL server and pulls the data from that machine.

The MySQL docs go into detail on how to do it. Essentially, you create a table on one machine, and then on another machine you create a identical table, but indicate the table type is federated. In the comment (that seems like a hack) of the federated table, you specify information on where the data really lives:

mysql://root@remote_host:9306/federated/test_table'
No doubt there are performance issues to consider when using this kind of a storage engine, but it seems like this is going to open up a lot of options for breaking up storage and processing. Surely someone out there has been praying for the ability to have a dedicated server just to handle their lone X table.

There are a few interesting notes in the limitations section of the documentation:
- federated tables work with MySQL now, but may work with other vendors in the future
- little awareness of the real table (no query cache, alter, update)

Will be interesting to see what comes of this, I'm sure the 2005 MySQL Conference will include some conversation.

Posted by mike at January 24, 2005 9:17 PM