mysql-proxy-discuss team mailing list archive

[nick loeve <trickie@xxxxxxxxx>]

Hi all,

I have been messing with mysqlproxy for a use-case we have at work,
and I'm having some issues, so I'm asking for some advice/feedback on
whether mysqlproxy would be a good choice, and also some internal
implementation explanation.

To start with, some info:

We currently have a db connection pooling implementation for read-only
slaves in our db architectures, and this is working ok for us. The
solution is similar in some respects to mysqlproxy (speaks mysql
protocol etc), but it is hardwired to only proxy queries for read-only
selects. We use this tool to implement 'query funneling' whereby we
can funnel/serialize selects from 1000 frontend connections through
8-12 backend connections (for example). This ensures that we have a
fairly low connection overhead and also that we keep the mysqld
chugging along at a nice pace (we have lots of small queries). Our
connection pooler is on the same host as the mysqld and connections
are made to the pooler via a loadbalancer between it and the webs. The
pooler than connects to the mysqld via a unix socket.

This tool provides a backlog whereby we can have more queries coming
in the front-end than we have back-end connections, and once we have
idle connections in the pool the backlog is cleared. As long as we
tightly tune the amount of queries and front-end/back-end connections
this is working well.

We are currently evaluating whether mysqlproxy would be a good fit for
connection pooling to our write-only masters, and I am trying to
simulate the 'funneling' behaviour I described above. We are looking
to keep the same 'proxy on same host as mysqld' setup as our other
tool, as it fits with our infrastructure control and scripting.

So, my question:

Does mysqlproxy require a backend connection from the connection pool
for every query coming in the front-end or can it also handle a
'backlog'? My lua script to do the query funneling is currently
returning 'Lost connection to MySQL server during query' errors to the
client if I have more clients connected and sending queries than I
have back-end connections open.

Any guidance/feedback is most welcome,

Thanks
-- 
Nick Loeve