Skip navigation links

MySQL Forums :: Proxy :: MySQL Proxy: Scenarios, Use Cases


Advanced Search

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
MySQL Proxy: Scenarios, Use Cases 19560 Edwin DeSouza 07/09/2007 11:35PM
Re: MySQL Proxy: Scenarios 7113 Rick Blundell 07/10/2007 12:01AM
Re: MySQL Proxy: Scenarios 7179 Gerry Reno 07/10/2007 11:21AM
Re: MySQL Proxy: Scenarios 4806 Edwin DeSouza 07/10/2007 12:05PM
Re: MySQL Proxy: Scenarios 4782 Gerry Reno 07/10/2007 12:17PM
Re: MySQL Proxy: Scenarios 4402 Edwin DeSouza 07/10/2007 02:37PM
Re: MySQL Proxy: Scenarios 4293 Gerry Reno 07/10/2007 04:07PM
Re: MySQL Proxy: Scenarios 3792 Jason Burnett 02/21/2008 11:26PM
Re: MySQL Proxy: Scenarios 4217 Edwin DeSouza 07/10/2007 11:55AM
Re: MySQL Proxy: Scenarios, Use Cases 6914 Reinis Rozitis 07/20/2007 03:58AM
Re: MySQL Proxy: Scenarios, Use Cases 4168 Jonathon Suggs 07/30/2007 01:21PM
Re: MySQL Proxy: Scenarios, Use Cases 4168 Reinis Rozitis 08/01/2007 05:58AM
Re: MySQL Proxy: Scenarios, Use Cases 3764 Jan Kneschke 08/01/2007 06:03AM
Re: MySQL Proxy: Scenarios, Use Cases 3459 Edwin DeSouza 02/13/2008 02:04PM
Re: MySQL Proxy: Scenarios, Use Cases 3391 Pete Clapham 04/08/2008 04:25AM
Re: MySQL Proxy: Scenarios, Use Cases 4353 Clint Alexander 07/28/2008 09:32PM
Re: MySQL Proxy: Scenarios, Use Cases 3058 Simon Mudd 09/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.