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
20853
July 09, 2007 11:35PM
7665
July 10, 2007 12:01AM
7727
July 10, 2007 11:21AM
5321
July 10, 2007 12:05PM
5402
July 10, 2007 12:17PM
4887
July 10, 2007 02:37PM
4762
July 10, 2007 04:07PM
4341
February 21, 2008 11:26PM
4718
July 10, 2007 11:55AM
Re: MySQL Proxy: Scenarios, Use Cases
7629
July 20, 2007 03:58AM
3994
February 13, 2008 02:04PM
3557
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.