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
20893
July 09, 2007 11:35PM
7697
July 10, 2007 12:01AM
7760
July 10, 2007 11:21AM
5356
July 10, 2007 12:05PM
5446
July 10, 2007 12:17PM
4913
July 10, 2007 02:37PM
4922
July 10, 2007 04:07PM
4365
February 21, 2008 11:26PM
4746
July 10, 2007 11:55AM
4020
February 13, 2008 02:04PM
Re: MySQL Proxy: Scenarios, Use Cases
3582
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.