MySQL Conf 2008 – MySQL Proxy (Day 4)

Author Michael Tougeron on April 21, 2008

Posted under Events, mysql and tagged with , , ,

MySQL Proxy is one of the new products that MySQL released this year that has some real promise. In the MySQL Proxy, the Friendly Man in the Middle session Jan Kneschke went over the existing and planned features of the proxy.

MySQL Proxy has a c-based event driven core that has command line support, event handling and a Lua-based scripting layer. The proxy is loading into MySQL via the plugin interface. With the community edition, you are responsible for writing your own scripts. But with MySQL Enterprise, it will come with several pre-built scripts that add some enhanced functionality.

The most basic use of MySQL Proxy is to rewrite or alter your queries. If you have a costly query that is killing your db, but you’re not sure where it is coming from, you can write a Lua script to either reject the query or to alter it and make it more manageable. It stops the dba from having to wait for a developer to update code and/or deploy the fix.

Another trick you can do with MySQL Proxy is to create new SQL commands. While this is probably not going to be a great idea, you can see a few interesting examples at http://thenoyes.com/littlenoise/?p=63. It is a good way to expand on functionality at the lowest level. Or to lose/forget where you put it when you switch to another db. 😉

One of the features MySQL was thinking of when they built MySQL Proxy is the ability to manage your queries at the database level. Imagine tracking query quotas, by ip, user, db, etc., then rejecting queries automatically if over quota. Or perhaps to simulate a db outage or transaction failure when testing code to ensure properly fail-over or error handling.

One of the default Lua scripts that comes with MySQL Proxy is a basic load balancer for the db. It rotates the connections round-robin betweeen the MySQL servers. Since it is in the database layer, it can be aware of slag lag and adjust the rotation accordingly. Since it is a Lua script, you can customize it to work how you want it to.

In MySQL Enterprise, this script is on steroids and does so much more. The load balancer automatically checks the slave state, including lag, # of connections, last error, etc., and kicks the server from rotation if it finds something “wrong” with the slave. This functionality will be integrated with MySQL Enterprise Monitor so that you will have an easy view into how the load balancer is working.

Also for Enterprise, the MySQL team is working on an automated sharding algorithm to go with MySQL 5.1’s table partitioning. I’m not sure how easy that will be to implement, but if they are able to pull it off, I’ll be mighty happy. At least for basic partitioning or sharding, like with a user table, you could do it through MySQL Proxy without having to worry about missing an update someplace in your code. If you combine this with logging, you can update your code at your leisure and easily find the queries you missed without having to worry about breaking the site.

Other planned improvements include being able to run parallel queries, two-phased commits and syncronyous replication. Since the proxy is not the database engine, it can use multiple cores on a server and assign out queries and other processes in ways that the db engine you are using cannot.

This is still very new technology/product for MySQL, but I’m looking forward to what comes out of it. There is a lot of possibility in it, as well as the potential for bloat. Personally, I think it will end up back as MySQL core so that the engines can take advantage of some of the Proxy abilities.

Posted under Events, mysql

This post was written by Michael Tougeron on April 21, 2008

Tags: , , ,

Leave a Comment

You must be logged in to post a comment.

More Blog Posts

Next Post: