mysql-proxy-discuss team mailing list archive

[nick loeve <trickie@xxxxxxxxx>]

Hi,

I have created an experimental branch of the mysql-proxy code on
launchpad, in order to show what I have done to implement a connection
multiplexer with backlog for mysql-proxy, and hopefully get some
feedback on our implementation/design. We called the plugin 'funnel'.

A little background:

At the company I work at (www.hyves.nl), we have a set of replication
slave architectures that we do the selects from for a high traffic
website. We have a set of front-end loadbalancers that distribute
connections from web servers to a particular slave in the requested
architecture. We have a 'funnel' that the loadbalancer connects to,
which serializes connections and queries into a small number of
backend connections to the mysqld on the same host as the 'funnel'. As
we have a very large number of web servers, we currently have a
front-end/back-end connection and funneling ratio of about 500 to 1.
In order to accomplish the 'funnel' behavior we require a backlog of
pending connections and/or queries.

Our existing solution works, but we are looking at how to get more
performance using a plugin to mysql-proxy. The plugin in the branch I
posted accomplishes the main tasks I described above, but there are
more features that we would like to implement, mainly support for more
statistics via the admin plugin. I had to make a few changes to the
core state-machine that handles the front-end/back-end connection
state in order to achieve the backlog.

Some assumptions:

We have some hardcoded 'assumptions' in the code base, such as only
ever using one backend (as we always have the funnel sitting in front
of a mysqld on the same host) and we have a single user/database for
most of slave architectures, so multi-user and or complex permissions
may not work correctly. I would like to eventually remove these
limitations/assumptions.

We are currently testing our plugin in a live environment, and our
benchmarks are proving that the mysql-proxy design is giving us better
capacity and lower average query times at peak traffic times. Im
particularly interested in the blueprint on launchpad about threaded
I/O. We did have an attempt at adding a thread pool to our plugin in
order to handle some backlog clearing and some I/O, but without large
changes to the main proxy engine we did not succeed in getting stable
enough to really test out in our high traffic environment.

Anyway, if anyone is interested, please take a look, and any feedback
is most welcome,

Cheers



-- 
Nick Loeve