pero on anything

Recently I added the first code to support multiple MySQL backends in HSCALE (see svn.hscale.org). As a “side note” I have to thank Giuseppe Maxia for MySQL Sandbox which made multi server testing a bliss!

While coding this I started to feel that writing HSCALE on top of MySQL Proxy is no more as easy and clean as it started out to be. And finally I reached the point where I have to say:

HSCALE (and maybe other advanced multi-server applications) and (current) MySQL Proxy don’t fit very well.

Before I go into details please let me make clear that this is mostly due to the specific nature of MySQL Proxy being a connection and protocol based proxy. MySQL Proxy is great and there are a lot of cool applications that fit perfectly with it.

Aside from some other minor glitches (like missing tokens in SQL tokenizer) the biggest show stopper is:

Handling of multiple backends

The biggest problem I had to face is the handling of backend connections in MySQL Proxy. What I would need for HSCALE are dedicated connections to every backend for every proxy connection. So if a client connects to the proxy, a connection to each backend is opened and attached to this particular client connection. This way I easily could maintain the state of the connection by sending commands like SET variable or USE database to all backends.
Dedicated connections are vital for XA or transactions in general, too.

The way the proxy handles backend connections right now is somewhat cumbersome (See an example.). The way you have to maintain your own connection pool is hard to understand and uses too much “magic” in my opinion. And the pool only grows with the number of connections made to the proxy. People are having problems with this approach (read the MySQL Proxy forum, for instance this thread).

The current experimental multi-server code in HSCALE uses this connection pooling technique since it is the only way to establish connections to other backend servers.

So what can we do now to implement multi-server support in HSCALE?

1. Wait until MySQL Proxy supports dedicated backend connections This would be the easiest and most elegant solution. But it could be that Jan Kneschke and the other developers decide that this is not what they intended to do with the proxy. And this would be totally ok from their point of view! Even if they decide that this is a cool feature it could take a long while until it is implemented. (Hint: I would gladly help out here ).
2. Work around this using luasql While possible this solution would not be preferable since we are using two technologies for the same thing.
3. Switch to another proxy There are quite a few. This would be no fun and I did not take a look at them all to see if it is even possible. It would be complete rewrite though.
4. Fork MySQL Proxy or implement a plugin The new plugin technology could be used to implement the multi-backend connection stuff myself. As a last resort, I could fork the whole project (like Spock Proxy did, see below). This would still mean a lot of work though and loss of the MySQL efforts put into the proxy.
5. Use other sharding technologies and eventually abandon HSCALE As an example Spock Proxy, a fork of MySQL Proxy does a great deal of what we intend to do with HSCALE.
6. Re-implement HSCALE as a JDBC driver Since our applications are written in Java exclusively we could do that.

Of course I would love to continue HSCALE as a MySQL Proxy application! The main reasons are the efforts MySQL is putting into MySQL Proxy, the extensibility we gain combining multiple LUA scripts (there are more things we do with the proxy apart from HSCALE) and last but not least the community echo HSCALE already received.

As a next step I will take a deeper look into the proxy code and evaluate the efforts needed to add dedicated connections.

This entry was posted on Tuesday, August 26th, 2008 at 1:07 am and is filed under hscale, mysql, mysql-proxy. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.