MySQL Forums :: Router & 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

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' {

pool 'forum' {

(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.

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,163403,163403#msg-163403 )

Options: ReplyQuote

Subject Views Written By Posted
MySQL Proxy: Scenarios, Use Cases 20821 Edwin DeSouza 07/09/2007 11:35PM
Re: MySQL Proxy: Scenarios 7648 Rick Blundell 07/10/2007 12:01AM
Re: MySQL Proxy: Scenarios 7709 Gerry Reno 07/10/2007 11:21AM
Re: MySQL Proxy: Scenarios 5308 Edwin DeSouza 07/10/2007 12:05PM
Re: MySQL Proxy: Scenarios 5384 Gerry Reno 07/10/2007 12:17PM
Re: MySQL Proxy: Scenarios 4872 Edwin DeSouza 07/10/2007 02:37PM
Re: MySQL Proxy: Scenarios 4743 Gerry Reno 07/10/2007 04:07PM
Re: MySQL Proxy: Scenarios 4328 Jason Burnett 02/21/2008 11:26PM
Re: MySQL Proxy: Scenarios 4708 Edwin DeSouza 07/10/2007 11:55AM
Re: MySQL Proxy: Scenarios, Use Cases 7606 Reinis Rozitis 07/20/2007 03:58AM
Re: MySQL Proxy: Scenarios, Use Cases 4665 Jonathon Suggs 07/30/2007 01:21PM
Re: MySQL Proxy: Scenarios, Use Cases 4692 Reinis Rozitis 08/01/2007 05:58AM
Re: MySQL Proxy: Scenarios, Use Cases 4294 Jan Kneschke 08/01/2007 06:03AM
Re: MySQL Proxy: Scenarios, Use Cases 3983 Edwin DeSouza 02/13/2008 02:04PM
Re: MySQL Proxy: Scenarios, Use Cases 3938 Pete Clapham 04/08/2008 04:25AM
Re: MySQL Proxy: Scenarios, Use Cases 4898 Clint Alexander 07/28/2008 09:32PM
Re: MySQL Proxy: Scenarios, Use Cases 3541 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.