I will admit in advance, this post is based on only a few days of working with MySQL Proxy.  I’ve emailed the author a couple of days ago, but not heard back yet, and I’m stumped as to why this isn’t working as advertised.  Theoretically it supports read/write splitting of queries to different backends, but when put to the test it seems that there are problems with connection pooling.  I.e., the second it tries to reuse a connection, that connection terminates and the client gets an error.

The concept of a MySQL Proxy is quite interesting, however, especially from the perspective of being in the IT team and having the quickest way to solving problems sometimes not involve fixing the app.  Let’s talk about a particular issue, the one thing I am presently working on solving: put a Bugzilla instance in Europe to provide a much better experience for people who need to use Bugzilla and are thousands of miles from the main instance in California.

The first step was to setup a mini-cluster in our datacenter in Amsterdam.  Matthew did most of this work, and then handed the project off to me after the machines were generally setup.  I took over and did some work to get the machines into monitoring and our configuration management system.  The basic setup is that we have a database slave running MySQL and replicating from the main database in the US.  A few webservers are then setup with this database as the Bugzilla “shadow” database, i.e., a read-only slave.

The problem is that Bugzilla doesn’t really use the shadow role very often.  Most of the queries, even reads, are sent over to the master database in the US.  This causes really problematic latency issues as it sends a dozen queries to build a page.  If each one takes ~170ms one-way to pass data, then we easily see page load times of 5-10 seconds or more depending on how many queries are done to generate the page.  This makes the site nigh unusable.

The typical method of fixing this involves changing the application to support read-only slaves.  While that’s certainly an option and one that should definitely be done, for various reasons it’s not the most attractive option in this case.  Enter MySQL Proxy.  With the promise of read/write splitting, it seems like it would be a great drop-in solution for solving this problem.

Theoretically, MySQL Proxy is setup on the database slave in the Netherlands.  It’s configured to point to the US master as the writable database and the local slave as the read-only database.  The application (Bugzilla-EU in this case) is then configured to use the Proxy address as the database.  In theory, with this done, we then will solve the latency problem by sending read queries locally and write queries across the pond.  Hurrah!

The problem comes down to this: the software doesn’t work (version 0.6.1) in this use case.  I spent some time trying to debug it, going so far as to step through the core C code to try to determine how it does what it does.  I hit a roadblock of time to invest, unfortunately, and decided that I couldn’t justify spending another day monkeying with the bits to find the problem.  I’ve posted on the MySQL Support Forum for MySQL Proxy and hope to get a response at some point, as this would be great software to use for this problem.

Well, fingers crossed!