Monday, October 06, 2008

Is the Federated engine useful to you?


Philadelphia - July 2008


Followers of my blogs and talks know that I am partial to the Federated engine. Not much as a normal way of storing data, but more as an auxiliary device to create innovative schemes.
A few years ago I wrote an article, Federated: the missing manual where I listed most of the gotchas that you can meet when using the Federated engine. This article alone would be enough to discourage you from using Federated in production. If you attempt to use a Federated table like a normal one, your server will suffer, and possibly crash.

Add the sad fact that the Federated engine is riddled with bugs, and you will agree with the decision of disabling the engine by default in MySQL 5.1.
Indeed, using federated tables as normal ones is a recipe for disaster. Yet, a measured usage of Federated tables can greatly improve your database usage experience.
For example, one of my former customers has had a Federated table in production since 2005. The production database is 4.1. A data warehouse database used 5.0, with a Federated table linking to a production table that grows very quickly. Using a cron job, the federated table copies data regularly from the production table to an archive table, and deletes the records older than three months.
Another usage that I have been experimenting with for a few years is using Federated tables as monitoring devices, with small records being passed around between servers. You can use this scheme to get a quick statistics of what another server is doing, for example by linking the federated table to a well tuned view. Or you can use small federated tables to check the health of a master from a slave and vice versa. I used this system for my MySQL Replication Monitor.
In short, Federated is not to be discarded, but it must be used with extreme caution.
The best course of action would be refactoring the whole engine with a more robust architecture. Until that happen, caution and planning would suffice.

3 comments:

Anonymous said...

Hi!

Patrick has now been allowed to push any fixes for Federated for a couple of years now. It is not too surprising that it has ended up in the state that it is in.

I'm hoping to see him push his new FederatedX work into Drizzle so that we can have a working engine again.

-Brian

Brian Moon said...

We use a federated table to allow us to join a table on another server. The application in question stores millions of rows of data that is indexed using Sphinx. We just allocated on server to process huge product files, store them and index them. So, we added a federated table to this database to allow us to only query the one sever from our front end applicaitons.This saves us making a second connection to the main DB server. We only use it in a read-only context. We don't update via the Federated table.

Antony said...

Don't forget, there is FederatedX, which fixes many issues which exist with the Federated storage engine which currently ships with MySQL.
Patrick's repository is currently public at
http://freshmeat.net/projects/federatedx/

Among one of the more interesting features of the fork is that it has some support for transactions, although 2 phase commit is not implemented yet, but remote transactional tables can be used as you would expect.

Check it out =)