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

Advanced Search

Re: MySQL Proxy: Scenarios, Use Cases
Posted by: Simon Mudd ()
Date: September 24, 2008 02:44AM

One thing I've not seen commented and perhaps is needed is that for r/w splitting you may have issues with a single write connection to the master.

1. The proxy will need to be aware of multi-statement transactions as if it receives more than one of these at a time the single connection to the master will receive incorrect SQL or it will behave incorrectly.

So if you have 2 threads trying to talk at once to the master

Thread 1
UPDATE some_table ...
DELETE FROM some_table
INSERT INTO some_table

Thread 2
DELETE FROM some_table
UPDATE some_table
UPDATE some_table

If the commands are mixed and sent over a single connection they will not produce the expected results.

This leads to

2. Make a pool of write connections to enhance concurrency.

This depends on 1. above but means that the proxy can send more transactions down to the master. When the pool is full new client connections will have to wait for existing clients to terminate, or the commands can be sent intermingled through an existing connection IFF it's not a multi-statement transaction.

In all cases the real MySQL client must be very careful (or the proxy must try to guarantee this) that the environment is clean for each thread.

Adjusting session local variables to be consistent between successive commands by different mysql client connections may be difficult for the proxy (or overly expensive in terms of resources) but if this is not done then the clients must understand that they are much more limited in what they can do. Perhaps the ideal thing would be to make this configurable at run / or connection time on the proxy.

Options: ReplyQuote

Subject Views Written By Posted
MySQL Proxy: Scenarios, Use Cases 20759 Edwin DeSouza 07/09/2007 11:35PM
Re: MySQL Proxy: Scenarios 7602 Rick Blundell 07/10/2007 12:01AM
Re: MySQL Proxy: Scenarios 7628 Gerry Reno 07/10/2007 11:21AM
Re: MySQL Proxy: Scenarios 5277 Edwin DeSouza 07/10/2007 12:05PM
Re: MySQL Proxy: Scenarios 5236 Gerry Reno 07/10/2007 12:17PM
Re: MySQL Proxy: Scenarios 4842 Edwin DeSouza 07/10/2007 02:37PM
Re: MySQL Proxy: Scenarios 4722 Gerry Reno 07/10/2007 04:07PM
Re: MySQL Proxy: Scenarios 4301 Jason Burnett 02/21/2008 11:26PM
Re: MySQL Proxy: Scenarios 4641 Edwin DeSouza 07/10/2007 11:55AM
Re: MySQL Proxy: Scenarios, Use Cases 7508 Reinis Rozitis 07/20/2007 03:58AM
Re: MySQL Proxy: Scenarios, Use Cases 4643 Jonathon Suggs 07/30/2007 01:21PM
Re: MySQL Proxy: Scenarios, Use Cases 4657 Reinis Rozitis 08/01/2007 05:58AM
Re: MySQL Proxy: Scenarios, Use Cases 4267 Jan Kneschke 08/01/2007 06:03AM
Re: MySQL Proxy: Scenarios, Use Cases 3953 Edwin DeSouza 02/13/2008 02:04PM
Re: MySQL Proxy: Scenarios, Use Cases 3881 Pete Clapham 04/08/2008 04:25AM
Re: MySQL Proxy: Scenarios, Use Cases 4868 Clint Alexander 07/28/2008 09:32PM
Re: MySQL Proxy: Scenarios, Use Cases 3514 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.