Using Federated for a performance gain?

[Woody Hunt]

Hello,

I have been using MySql for several years but I am far from an expert. I also have no experience with the Federated engine, but its very intriguing. I have a situation with a high traffic database and I would like to get some knowledgeable opinions on whether Federated can help.

Here is the situation:

We have a single database with about 12 tables living on a single server. Right now all the tables are InnoDB.

Two of the tables (lets call them ht_1 and ht_2 for simplicity) are large and get a ton of traffic. We have 8 remote servers that constantly update these two tables with new information (UPDATEs, INSERTs and REPLACE INTOs).

Also, ht_1 and ht_2 are joined in almost every query made on the main server. The problem lies in the fact that front-end users get very poor performance (even though the main server has very high-end hardware). When a user makes a single query through the front-end system (SELECT or UPDATE), that query is stuck behind queries from 8 other servers that are constantly banging on those two tables.

My question is, what if we move just ht_1 and ht_2 to a second server? I would have the 8 update servers bang on the second server directly. And then I would set up federated tables on the front-facing server that point to the secondary remote server that contains the two high traffic tables. The determining factor is, if I join a federated table, what resources are used? Do I gain anything with this setup?

I know this model doesn't scale well at all, because if I increase my server load, it still doesn't let me scale past a single server for the high traffic tables. In an ideal world, I would split the high traffic tables among all 8 update servers and only transfer data to the central front-facing server when needed, but thats a LOT bigger project.

If there is some data missing from this equation that is needed to figure out an ideal solution, please let me know! Also, if you think of an alternative (highly-stable) option to what I'm suggesting, I'm all ears!

Thank you all in advance for any insights you can offer!!

Woody