Look ma! Mysql Federation sucks!

by Harun Yayli on Sunday, January 20th, 2008 at 12:37 am under MySql, Open Source

I was trying to explore alternatives for slave/master architectures and gave a try to federated tables.

No this post is not about Sun buying mySql.


So I compiled a fresh 5.0.51 on two boxes. It’s quite easy to setup, all it takes is the main server and the sub servers to support federated engine.Only trick is to remove fulltext indexes on the federated tables. Leave the main server as-is. On the sub server, create the table with ENGINE=Federated and set the connection to the main server. Make sure you have the user created on tha main server and the password of the user does not contain any special characters. (some reported @ $ characters are problematic).

It works on the first try. Great feature! However, setup is a bit long if you want to federate a whole database with 100 tables in it.

Then you try your first query on a mid sized table with 250K rows.

SELECT * FROM table_t LIMIT 10;
Runs 0.03 sec on main server, takes 8 seconds on the federated server!

then the second:

SELECT count(id) FROM table_t
Runs 0.02 sec on main server, takes 252 seconds on the federated server!

Looks like I’m not the only one complainig about the problem so I didn’t care to fill in a bug report.

More info about federation is here if you are still interested in.

Update:
Giuseppe Maxia contacted me refering his article on federation engine. It’s good read.

Recent Entries

You can follow any responses to this entry through the RSS 2.0 feed. You can skip to the end and leave a response. Pinging is currently not allowed.

4 Responses to “Look ma! Mysql Federation sucks!”

  1. Mark Robson Says:

    No, federation is NOT useless.

    You just need to be very careful using Federated in SELECTs. Actually for doing things like bulk inserts, it’s pretty nifty (particularly with recent changes to use better bulk inserts).

    Suppose you have a farm of servers collecting audit data and want to summarise it regularly to a master reporting server, you can use INSERT … SELECT on the Fed. table and it will do an efficient bulk insert for you. This is likely to be more efficient than taking the data via a client application and issuing the queries yourself.

    Mark

    January 20th, 2008 at 4:54 am
  2. argiope Says:

    Mark,

    I agree.
    Federation is not completely useless. Good usage suggestion. Unfortunately, I feel like federation is not ready yet for prod environments as a roboust engine.

    January 20th, 2008 at 7:12 pm
  3. Sheeri Cabral Says:

    Why do you say MySQL Federation? Did you compare it with Oracle Federation? You do realize that the query time is dependent on network latency and, depending on your setup, can be increased exponentially if DNS reverse lookups are slow? Oracle FEDERATED tables suffer from the same long running time.

    January 23rd, 2008 at 8:08 am
  4. argiope Says:

    Sheeri.
    well you have a point. If Oracle Federation sucks as well, then it’s not mysql faults to not write a better one.
    no need to get sentimental about MySQL. It’s obvious that federation is no good for my situation. I’ve just put some words together to express my frustration on mysql federation, because, now I need to code something to solve my problem.
    If Our 11g was any better I wouldn’t need to use mysql federation.
    I tried Giuseppe Maxia’s idea on universal mysql server (ref: http://dev.mysql.com/tech-resources/articles/dbixmyserver.html) and here I’m frustrated to mysql.
    Idea is nice, mysql was the weakest link in the chain.

    January 24th, 2008 at 10:17 pm

Leave a Reply

authimage