Re: MySQL Proxy: Scenarios, Use Cases

[Simon Mudd]

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.