Another MySQL Proxy Tutorial

Since MySQL Proxy 0.7.0 is soon to be released, I thought another brief tutorial would be helpful. Today we ran across a good use case, and so I wanted to pass this along. If you’ve not used Proxy yet, this is a great opportunity to get your feet wet with it and some Lua.

When queries are routed though MySQL Proxy from various servers, the MySQL Server only sees that the query came from the machine Proxy is running on.

So when you are viewing output of SHOW PROCESSLIST, you have no way of telling what server a particular query originated from.

However, this could be very useful information to have, especially to determine which server a particular long-running query is coming from at that particular moment in time.

Output not using Proxy:

mysql> show processlist;
+----+------+-----------------+------+---------+------+-------+------------------+
| Id | User | Host            | db   | Command | Time | State | Info             |
+----+------+-----------------+------+---------+------+-------+------------------+
| 11 | root | localhost:61252 | test | Query   |    0 | NULL  | show processlist |
| 12 | root | 10.1.10.1:61267 | NULL | Query   |   17 | init  | select sleep(30) |
| 13 | root | 10.1.10.2:62047 | NULL | Query   |   18 | init  | select sleep(30) |
| 14 | root | 10.1.10.3:62050 | NULL | Query   |   19 | init  | select sleep(30) |
| 15 | root | 10.1.10.4:62052 | NULL | Query   |   20 | init  | select sleep(30) |
+----+------+-----------------+------+---------+------+-------+------------------+
5 rows in set (0.00 sec)

In the above, you can easily see the IP address of the server the query originated from.

However, now see the output when using Proxy:

mysql> show processlist;
+----+------+-----------------+------+---------+------+-------+------------------+
| Id | User | Host            | db   | Command | Time | State | Info             |
+----+------+-----------------+------+---------+------+-------+------------------+
| 11 | root | localhost:62255 | test | Query   |    0 | NULL  | show processlist |
| 12 | root | localhost:62271 | NULL | Query   |   17 | init  | select sleep(30) |
| 13 | root | localhost:63038 | NULL | Query   |   18 | init  | select sleep(30) |
| 14 | root | localhost:63042 | NULL | Query   |   19 | init  | select sleep(30) |
| 15 | root | localhost:63043 | NULL | Query   |   20 | init  | select sleep(30) |
+----+------+-----------------+------+---------+------+-------+------------------+
5 rows in set (0.00 sec)

Here, every query shows as originating from ‘localhost’. This is accurate, but in this example, we’d like to know the originating server.

So, how do we accomplish this?

A good work-around to this problem is to append the originating host to the query in the form of a query comment. In this way, the comment will get passed through, and will appear in query itself, under the “Info” column of the output. This will also incur very little overhead, as we’re only appending a known value to the query, and then the MySQL parser handles it as it would any other query (which is to execute it while ignoring the comment).

Now, how do we accomplish this with Lua and Proxy?

I started using one of the included sample scripts – tutorial-rewrite.lua.

I chose this file because it already has the stub code included for both the following function:

function read_query( packet )

It is in this function where the query is read, and before it is sent to the MySQL Server. So this is where we can modify the query to append this information.

Here is the resulting function read_query():

function read_query( packet )
  if string.byte(packet) == proxy.COM_QUERY then
    local query = "/* " .. proxy.connection.client["address"] .. " */ " .. string.sub(packet, 2)
    print("we got a normal query: " .. query)
    proxy.queries:append(1, string.char(proxy.COM_QUERY) .. query )
    return proxy.PROXY_SEND_QUERY
  end
end

The first 3 lines are already in that file. However, I did modify the 3rd line to include the comment with the originating server IP.

Here is the new 3rd line:

local query = "/* " .. proxy.connection.client["address"] .. " */ " .. string.sub(packet, 2)

This defines “query” as a local variable, and sets it equal to the comment concatenated with the actual query.

You can see it starts by appending the comment, which is of the form: /* comment */, where comment is the IP address of the server the query came from.

Note that “..” is used to concatenate two strings in Lua.

The IP address can be found in an existing variable, which is:

proxy.connection.client["address"]

And the query itself can be found in an existing variable, which is:

string.sub(packet, 2)

So as long as we’re within function read_query(), then we have access to these variables, and they’ll be properly assigned.

The next statement is the print() statement, which is originally from the sample script, and I left it in for debugging purposes:

print("we got a normal query: " .. query)

And then you just need the following two statements:

proxy.queries:append(1, string.char(proxy.COM_QUERY) .. query )
return proxy.PROXY_SEND_QUERY

The former appends the new query to the query stack to be executed. This must be done so the changes you make to the variable “query” are actually reflected in the packet that gets sent to the server. And in the latter, PROXY_SEND_QUERY is called, which sends the packet (with the modified query) to the MySQL Server to be executed.

So, now we just need to save this function to a file, start up proxy with this script, and then issue a SHOW PROCESSLIST.

I’ll save the above function to a file named ‘retain-hosts.lua’.

In Window #1:

cd "../../Program Files/MySQL/mysql-proxy-0.6.0"
mysql-proxy --proxy-lua-script="C:Program FilesMySQLmysql-proxy-0.6.0retain-hosts.lua"

In Window #2:

mysql -u root -pmysql -h 127.0.0.1 -P 4040 --comments

Note you must pass the –comments option to the MySQL Client, or the comments will get stripped out. This is at least necessary for testing, or if actually passing queries through the command line interface.

Now in Window #2, issue your SHOW PROCESSLIST (note I’ve thrown a few queries at it from some other connections):

mysql> show processlist;
+----+------+-----------------+------+---------+------+-------+----------------------------------------+
| Id | User | Host            | db   | Command | Time | State | Info                                   |
+----+------+-----------------+------+---------+------+-------+----------------------------------------+
| 11 | root | localhost:62252 | test | Query   |    0 | NULL  | /* 127.0.0.1:11251 */ show processlist |
| 12 | root | localhost:62267 | NULL | Query   |   17 | init  | /* 10.1.10.1:15091 */ select sleep(30) |
| 13 | root | localhost:63047 | NULL | Query   |   18 | init  | /* 10.1.10.2:18166 */ select sleep(30) |
| 14 | root | localhost:63050 | NULL | Query   |   19 | init  | /* 10.1.10.3:18934 */ select sleep(30) |
| 15 | root | localhost:63052 | NULL | Query   |   20 | init  | /* 10.1.10.4:19446 */ select sleep(30) |
+----+------+-----------------+------+---------+------+-------+----------------------------------------+
5 rows in set (0.00 sec)

Voila! Now you can see in the IP address of the server the query originates from in the actual query itself, as a comment.

For more information on MySQL Proxy, please see the following links:

http://dev.mysql.com/downloads/mysql-proxy/index.html
http://forge.mysql.com/wiki/MySQL_Proxy
http://forge.mysql.com/wiki/MySQL_Proxy_Cookbook
http://jan.kneschke.de/projects/mysql/mysql-proxy/
http://datacharmer.blogspot.com/

Tags: , , , , , , ,

10 Responses to “Another MySQL Proxy Tutorial”

  1. Renaud Drousies says:

    This is a really great tip but it should be used with caution as the query cache will rarely be hit, I don’t think the comments are stripped before getting in the cache. (but I might be wrong)

  2. Renaud – nope, you’re right, it won’t hit query cache. Stripping comments before checking in the query cache wouldn’t work. i.e.

    SELECT * from my_table WHERE value = ‘this is /* a comment */ here’

    (You really need a parser to determine this)

    But the query cache doesn’t work for everyone. I use a similar technique to Chris’ tip to write either the function name or request_uri that required the query in a comment. i.e.

    /* mypage.php?arg1=321312 */ SELECT * FROM my_table WHERE …;

    That way this information can make my slow query log as well.

  3. Kay Roepke says:

    Hey,

    please also see my little response to it :)
    http://blogs.sun.com/kay/entry/query_cache_and_comments

    In a nutshell: It totally depends on the version you are using.
    Very nice, btw, and great to see you taking up blogging :)

  4. Baron says:

    Actually a query that has a comment in front will still use the query cache. But since there’s a DIFFERENT comment for each host, queries coming from each host will NOT be able to share the query cache amongst themselves. I’ll be able to get hits from the cache for my requests, but Morgan won’t get them when he runs the “same” query, because it’s not the same anymore after Proxy rewrites it. So Morgan and I will cause cache duplication.

  5. Renaud, Morgan, Kay, and Baron – Thanks for the comments!

    I’ve worked on this a bit further, and have found one tweak that will help with the query cache.

    This is only a change to the 3rd line (the declaration and definition of the variable “query”).

    Here is the new line of code:

    local query = “/* ” .. string.gsub(proxy.connection.client["address"],”:%d+”,” “) .. “*/ ” .. string.sub(packet, 2)

    The “gsub” call is what’s new.

    Basically, this strips off the “:” and the trailing id. Therefore only returning the IP address. This will help a lot for the query cache.

    Now, at least all queries that originate from the same server can use the query cache. Note queries that are the same, but originate from different IP addresses will result in 2 entries into the query cache (assuming it is enabled).

    So instead of seeing queries like the following:

    /* 127.0.0.1:11251 */ show processlist
    /* 10.1.10.1:15091 */ select sleep(30)
    /* 10.1.10.2:18166 */ select sleep(30)
    /* 10.1.10.3:18934 */ select sleep(30)
    /* 10.1.10.4:19446 */ select sleep(30)

    They will now appear as:

    /* 127.0.0.1 */ show processlist
    /* 10.1.10.1 */ select sleep(30)
    /* 10.1.10.2 */ select sleep(30)
    /* 10.1.10.3 */ select sleep(30)
    /* 10.1.10.4 */ select sleep(30)

  6. [...] a previous post, Another Proxy Tutorial, I outlined how you could find and display the originating IP address of a particular query from [...]

  7. chris says:

    Note that I have updated this solution by creating a new comand SHOW PROXY PROCESSLIST that shows the originating IP address for every connection (not just active queries). It also does not interfere with the query cache. I’ve detailed how to implement this on the following page:

    http://www.chriscalender.com/?p=41

  8. How do I add this to my RSS reader? Sorry I’m a newbie :(

  9. chris says:

    @Facebook Proxy: You can subscribe to this using the following RSS feed:
    http://www.chriscalender.com/?feed=rss2

  10. [...] Another MySQL Proxy Tutorial Chris on MySQL Posted by root 1 hour 6 minutes ago (http://www.chriscalender.com) You can see it starts by appending the comment which is of the form comment where comment is chris on mysql is proudly powered by wordpress Discuss  |  Bury |  News | Another MySQL Proxy Tutorial Chris on MySQL [...]


Period Panties by Period Panteez Menstrual Underwear Menstruation PMS Panty