FEDERATED Storage Engine

Users and uses of

[brotherhood of LAN]

I have a 'main' database server and half a dozen 'gateway' servers that perform distributed tasks for the main DB, that in random places on the web.

They're all set up to allow access from the main database's IP address.

FEDERATED storage engines [dev.mysql.com] looks like another approach that may be useful to me.

Has anyone any insight or active experience of using this storage engine? Thoughts appreciated.

[da_pinky]

i tried it a while ago. my project had to access the phpBB forum database on another server so people could reuse the forum account on another website, and i used federated storage engine for that.
i would not really recommend it for live systems, just because every access to to the federated table added 0.5 seconds to the query execution time, and that was between 2 servers on the same network on a 1GBit lan. i could not really find any good tutorials how to tune this storage engine. because i only need read access to the forum db and it does not have to be synchronized in real time, i set up a replication so the forum server pushes the changes to the other database server - queries are just much faster if you just access local tables.

[brotherhood of LAN]

Thanks for sharing da_pinky,

Agreed on the tutorials, outside of the mysql documentation, I found an O'Reilly article on them fairly useful and insightful.

It looks like using them requires very careful planning of queries, or you can end up doing a full table scan (and sending the full table across the network).

I had plans for using federated with partitions but it appears that there are quite a few bugs with that combo just now. Partitions would be paired with the various remote servers being used...partitioning the federated table as well as the remote tables could lead to some very fancy / large / optimised setups, like a DNS type lookup, or a product comparison DB.

They could be a very useful and powerful part of mysql in the future.