MySQL Forums
Forum List  »  Router & Proxy

Re: MySQL Proxy: Scenarios, Use Cases
Posted by: Reinis Rozitis
Date: July 20, 2007 03:58AM

The features / solution we want to archieve is something like this.

To set up a MySQL DB server farm which from the client side (web/php aplication) looks and can be used as one but the content would be split among bunch of servers without the need to build up a real cluster.

In the simplest case it can be imagined also just by 2 (or more) different MySQL servers linked together with federated tables (eg one server has each other table linked) without the date really being replicated.


mysql-proxy could be the tool which based on SQL queries could transparently manage which request goes to which server.

Lets say we have a huge site and 2 site sections are 'forum' and 'gallery'. Because of the load and data amount each section resides on for example one master server and 2-3 slaves and so on.

By having the full connection logic on client/aplication side is getting pretty heavy when there are many logical parts / db pools of the website and many servers. Like you need a seperate connection for each DB server and you have to manage to let the slaves run SELECTs and rest goes to masters.


Queries could be built pretty simple by providing the pool within /* */ for

example:
SELECT /*gallery*/ * FROM pictures WHERE ..

based on the /*gallery*/ mysql-proxy would know it need to change the connection to gallery pool SLAVE server.

It needs a bit advanced config (for example):

pool 'gallery' {
master 192.162.0.1;
slave 192.162.0.2, 192.162.0.3;
}

pool 'forum' {
master 192.162.0.6;
slave 192.162.0.7, 192.162.0.8;
}

(or possibility to implement some loadbalacing within the LUA scripts - features to check if the backend is alive before redirecting the query to the sever and not overloaded).


The advantages of such solution would be:

1. Developers/aplication does not need to manage/know the connections and logic behind real server structure/cascades. All is needed is a virtual name of pool which the aplication has all the data on.

2. Apllication/developers doesnt need to change anything in their code/config when the server structure is changed (new servers added/removed, whole pool moved to another place).

3. Loadbalancing and master/slave switching is transparent.

Drawbacks:
Only drawback I can imagine is you can't link tables/data from tables which physycally reside on different servers (but then there is always the FEDERATED table or partial replication workarround).




At some point it will become possible if you could switch backend (proxy_connection_set) within the read_query stage ( another thread http://forums.mysql.com/read.php?146,163403,163403#msg-163403 )

Options: ReplyQuote


Subject
Views
Written By
Posted
21458
July 09, 2007 11:35PM
7973
July 10, 2007 12:01AM
8097
July 10, 2007 11:21AM
5710
July 10, 2007 12:05PM
5802
July 10, 2007 12:17PM
5193
July 10, 2007 02:37PM
5176
July 10, 2007 04:07PM
4637
February 21, 2008 11:26PM
5026
July 10, 2007 11:55AM
Re: MySQL Proxy: Scenarios, Use Cases
8224
July 20, 2007 03:58AM
4255
February 13, 2008 02:04PM
3839
September 24, 2008 02:44AM


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.