MySQL Forums
Forum List  »  Router & Proxy

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
--------
BEGIN WORK;
UPDATE some_table ...
...
DELETE FROM some_table
INSERT INTO some_table
COMMIT;

Thread 2
--------
BEGIN WORK;
INSERT ....
DELETE FROM some_table
UPDATE some_table
UPDATE some_table
COMMIT;

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
20852
July 09, 2007 11:35PM
7665
July 10, 2007 12:01AM
7726
July 10, 2007 11:21AM
5319
July 10, 2007 12:05PM
5402
July 10, 2007 12:17PM
4886
July 10, 2007 02:37PM
4761
July 10, 2007 04:07PM
4339
February 21, 2008 11:26PM
4718
July 10, 2007 11:55AM
3994
February 13, 2008 02:04PM
Re: MySQL Proxy: Scenarios, Use Cases
3556
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.