MySQL Forums
Forum List  »  Federated Storage Engine

Using Federated for a performance gain?
Posted by: Woody Hunt
Date: September 20, 2007 10:34AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Using Federated for a performance gain?
6455
September 20, 2007 10:34AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.