How do you use the Federated Storage Engine?

We are looking for community feedback on the use-cases for the Federated Storage Engine in MySQL.

Specifically, I would be interesting to hear which users find that the introduction of multi-source replication meets (or does not meet) their requirements.

For a bit of background:

The Federated Storage Engine was introduced in 5.0, but has long been disabled by default. It is useful for ad-hoc queries across MySQL servers, but it misses some of MySQL's newer optimizations, and does not perform as well as we would like. Among it's limitations it also does not support transactions.

By using multi-source replication, many of the downsides mentioned above are negated, since the tables can be made available to query locally. The disadvantage of course, is that there is a need to provision more storage (however, given the performance limitations of federated it is not certain it could fill this use-case either).

So please leave a comment, or get in touch. I would love to hear how you are using the Federated Engine, and what you plan to use multi-source replication for.

Published by

morgo

I joined MySQL AB in 2006, left, and am now back at Oracle working on the MySQL team. I've also worked at Percona and InPowered.

  • FlR

    We use it for replicate the triggers actions from another database that is not replicated.

    • http://www.tocker.ca/ Morgan Tocker

      Interesting use case. Thanks!

      One question though: is this because you are using statement based replication / would this requirement be removed if you had row-based, where the triggers executed on the master?

      • Justin Swanhart

        I think multi-source replication with server-side replication filters would probably fit this use case.

      • Justin Swanhart

        That is an important missing feature that should not be overlooked - server side replication filtering. Right now, you have to download all the binary log to get only one table, or log only a subset of schema to the binlog on the server.

      • FlR

        Statement replication.
        I'll try to switch to row-based next week

  • Henk Jan Agteresch

    I had a customer using Federated Engine so he needed to maintain only one zipcode database on one cluster while using it from several other mysql clusters.

    After upgrading to mysql 5.5 mysqldump on the servers using federated engine failed, tracing back to federated engine usage.

    We changed to nightly dumps/loads for the specified database.

    Multi-source replication looks promising for my use case.

  • Shlomi Noach

    We use some FEDERATED tables on our DWH to read data from our OLTP slaves when generating reports. Some of these tables can change frequently and are just a bother to transport to DWH on hourly/daily basis.
    This is far from perfect, and we are considering a workaround we're developing.

    We have some established experience with cheating FEDERATED to do our biddings even when it decides to play dumb; by carefully playing with index definitions you can popagate query conditions which are otherwise not passed to the underlying server.

    I think FEDERATED has a poor implementation, but the need is sound. I'm sad that all these years and no real solution is found. FEDERATED-X did not live up to expectations.