mysql-proxy-discuss team mailing list archive

[Kay Röpke <Kay.Roepke@xxxxxxx>]

Hi!

On Jan 19, 2009, at 4:54 PM, nick loeve wrote:

> 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:
>
> [snip]
>
> 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'?

There is no "backlog" yet, so currently you have to have a free  
connection for each incoming connection, yes,
since we cannot multiplex multiple queries onto one mysqld thread.
IOW, each incoming connection will allocate a new or reuse an idle  
connection from the proxy to the server, up to the thread limit of the  
server (which is probably when you start seeing the connection lost  
errors).

> 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 chance you could share snippets of that script? Makes it a bit  
easier to discuss.
also, which version of proxy are you using?

regarding your follow-up:
> (critical) proxy-plugin.c.1087: I have no server backend, closing  
> connection
> (critical) network-mysqld.c.1181: plugin_call(CON_STATE_READ_QUERY)  
> failed
>
> Yet i do not define a read_query_result in my lua script (and so I do
> not play with backends/connections/proxy.* return status etc), which
> leads me to ask if you need a connection available in the pool for a
> back-end for every query coming in the front-end.

the last message is not from the "proxy" itself, but the chassis which  
is handling the low-level network stuff.
the CON_STATE_READ_QUERY is the state in the protocol, there doesn't  
necessarily has to be a lua callback for it. it's merely the place  
where proxy does the default behavior.

cheers,
-k
--
Kay Roepke
Software Engineer, MySQL Enterprise Tools

Sun Microsystems GmbH    Sonnenallee 1, DE-85551 Kirchheim-Heimstetten
Geschaeftsfuehrer: Thomas Schroeder, Wolfang Engels, Dr. Roland Boemer
Vorsitz d. Aufs.rat.: Martin Haering                    HRB MUC 161028