mysql-proxy-discuss team mailing list archive

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

Hi!

On Feb 27, 2009, at 5:45 PM, Boehm, Matthew wrote:

> Do you see any harm in making the table a MySQL MEMORY table? I  
> can't do
> ORDER BY on the internal analyze-queries.lua results table. Or  
> should I
> try and modify the lua so that if I provide an ORDER BY, it sorts the
> in-memory table?


This is something I've thinking about on several occasions, as well,  
for entirely different reasons, though.

For me the reason was/is:
When you use proxy to "overlay" the mysql backend with another  
database or table (like histogram.queries or other data you acquire  
from a non-MySQL resource) the fact that mysql proxy is (mostly)  
transparent is causing interesting problems for the user.
From a client perspective there is no reason to assume you cannot  
join with the "virtual" table, when in fact you can't.
There are at least two ways to deal with this:
1) materialize the table on the backend(s)
2) implement some kind of join logic within proxy

Both suck ;)
Option 1 sucks because you will have to write a lot of data and still  
would need to deal with synchronization and isolation issues. If there  
are transactions involved it just becomes more fugly.
Option 2 sucks because then you duplicate server mechanisms and most  
likely do a poor job at it, too. What's more, you'd need to parse and  
pry apart the queries to figure out what data you have to read from  
the backend, then write an optimizer to tell you what join order and  
algorithm to use and you soon end up with something like MySQL 
+federated. Not a good idea :)
The third option would of course be to implement a storage engine, but  
that's often inconvenient and can severly impact the server stability  
if something goes wrong.

Still, the entire thing has a certain attraction to it, so I'd like to  
find a neat solution that does not involve reimplementing half of a  
server.

The second point: No, we currently don't have a way of establishing a  
connection from within the proxy, although it's pretty high on the  
wishlist, simply because there's a lot of cool things you could do  
with it (including plugging in external authentication via LDAP or  
something among many many others).

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