MockLoad on Launchpad - MySQL Proxy

Several months ago, I started a little project at work, called Mockload. It started as a fun way of using the MySQL Proxy, to test our monitoring agent, as well as the rules engine and graphs on the Service Manager.

Why?
I needed a tool that would be easy to use, and improve over time. And that it would allow our QA team to send custom values to the service manager. The goal was to pretend having some very busy MySQL servers.

And what better tool, than the MySQL Proxy itself to pretend being a busy MySQL Server!
The way our agent collects the MySQL related data, is by issuing different sql queries. So, I thought that I could have a MySQL proxy instance in between our agent, and the MySQL server we were monitoring.
This proxy would then intercept the queries that our agent was collecting, and it would return some custom values.

Writing Lua scripts
I started by looking at all the queries that our agent sends to the MySQL server it was monitoring, once I got the list, I went ahead, and looked at what values our graphs use. And finally I looked at the values our rules engine uses.

I then started by mocking just two queries, SHOW GLOBAL STATUS and SHOW GLOBAL VARIABLES.

What I do is I replace the values from a normal SHOW GLOBAL STATUS query, by Lua variables. And I manipulate those Lua variables to trigger alerts and to produce interesting graphs.

In the utils.lua script, I have this function

function increase_val(incoming_val, method, increaser)
  if (method == "multiply") then
    incoming_val = proxy.global.round( incoming_val * 1.1 , 0)
  elseif (method == "add") then
    incoming_val = incoming_val + increaser
  end
  return incoming_val
end

Pretty simple, this allows me to increase specific counters by just adding to the original value or by multiplying the original value by 1.1.

Replication.
But the fun does not stop there, with this script, I also mock a replication setup where the slaves show some serious replication lag.



This was actually pretty neat to implement, I had to make our agent go through the proxy to query the slave servers, so that the master and the slave would report the same binlog names, etc.

Oh, and for the most part, the proxy reports having 700 binlogs, 700 users without password, 700 users with root access to the server. These are all values that are supposed to put stress on our monitoring system.

The other challenge I had with simulating replication was how to tell each proxy instance, that the backend was a master or a slave server.
My first idea was to have two different Lua scripts, but that just did not look clean, I then thought of sending a custom query through each proxy, to tell it what the backend was. But again, I wasn't happy with neither approach. So I finally decided to do some query injection.

Master or Slave?
Whenever our agent sends a query SHOW MASTER STATUS, the lua script injects a SHOW SLAVE STATUS query to the queue. If the proxy finds an empty resultset for the slave status, it assumes that the backend server is a master. While this is not a foolproof method, it works fairly well for now.

You can see the replication mocking on the mockload.lua file.

Graphs?
These are just some of the graph our service manager produces when we use MockLoad:




Controlling the counters.
To prevent the counters from increasing without limit, I use a global variable that sets the maximum value any counter could get. Once any counter reaches that value, all counters are reset to their initial values.

Source code?
You can find the scripts that make up MockLoad on Launchpad. You may ask why I'm hosting them there. The answer is that I still have a long way to go with MockLoad, and I thought that some people would benefit from seeing how this was implemented. Who knows, someone may be able to use it as is, or without many changes.

How do I use it?
You need at least one MySQL server and a Proxy. You tell the proxy where the lua modules are by doing this:

Assuming the main file is
/usr/local/scripts/mockload.lua
and the modules are in

/usr/local/scripts/mockload/

$ export LUA_PATH=/usr/local/scripts/?.lua
$ ./sbin/mysql-proxy --defaults-file=/path/to/ini/file/proxy.ini

You can then send a query like SHOW GLOBAL STATUS through the proxy port 4040, and each time you send that query, you will notice some counters returning increased values (more than normal for an idle server :) )

I hope you all enjoy it and as always, feedback is welcome.

MySQL Proxy => proxydb + replication

A couple of weeks ago I wrote a lua script to use with the MySQL Proxy that transforms the Proxy into a key=>value lookup dictionary.

But I couldn't just stop there. So I decided to add replication to it :).


The basic idea is that you have one proxy that acts like a master, it can handle all write/read operations. Once it receives a write query, it will send that query to the slave proxy instances, and after the last slave gets the query, the master will return a confirmation to the mysql client.
And of course, you send your read queries to the slave proxy instances.

Show me the code.
It is available on the MySQL Forge. And I'd recommend using Oldag's version of the sandbox, which allows you to easily setup different topologies of MySQL servers and Proxies. (It saved me lots of time)

Complex situation, lots of ins, outs and what have you.
(or how does it work?)

It uses the connection pool feature from the MySQL Proxy that is based on the rw-splitting.lua script.
This means that you will need to start a few different connections to the master proxy instance to get the poll going.
I do this by using this simple command line

$ for i in `seq 1 100` ; do ./proxy1/use -e "exit"; done ;

It also makes usage of mocking a mysql server. The script checks if there is a backend defined (proxy-backend-addresses), if there is one, it will tr to open a connection, otherwise, it is the last node on the chain and it will return an OK packet.

Oh, look at the proto.to_challenge_packet({}) function, which is better than working directly with the protocol.

We then have read_query(), which is pretty easy to understand, I tried to add as many comments as I could to the actual code.
We have different functions for each type of query. I think this is a clean way of doing :).
You will also see that each function that handles different query types has a line like this one:

if proxy.global.last_node == 0 then

This tells the proxy that if it is the last node on the chain, it has to return a resultset

read_query_result()
This was fun to work on. Here is where you send the queries to the slave proxies. The proxy loops over the list of backends and appends the same query. Once it reached the last proxy, it resets the current backend server and returns an OK packet to the mysql client.

This implementation could help you duplicate and reroute your queries to a different mysql server. So that you can send them to a server with different values on the my.cnf file, or any other changes that you'd like to test.

I hope you find it useful and feedback is always welcome.

*I got seq for my mac from this post.

MySQL Proxy - is it a MySQL server or what is it?

There are times when you don't need a MySQL server to handle your queries, this could come handy when you are automating tests, or writing interesting Lua scripts.

Yes, you can use the MySQL Proxy for this as well. And have it pretend to be a MySQL server.
Today we'll focus on a simple implementation. We will handle an initial connection, a SHOW DATABASES query and the exit (QUIT) command.

You can see all the hooks that the proxy handles on this Lua script written by Giuseppe

Connecting to a server.

function packet_auth(fields)
    fields = fields or { }
    return "\010" ..             -- proto version
        (fields.version or "5.0.45-proxy") .. -- version
        "\000" ..             -- term-null
        "\255\255\255\255" .. -- thread-id
        "\000\020\000\000" ..
        "\000\000\000\000" .. -- challenge - part I
        "\000" ..             -- filler
        "\001\130" ..         -- server cap (long pass, 4.1 proto)
        "\008" ..             -- charset
        "\002\000" ..         -- status
        ("\000"):rep(26)   -- filler & challenge - part II
end

This function prepares the raw response that the MySQL Proxy will return to all clients connecting through the proxy port (default 4040)

If you are into protocols and what not, you can see the Client - Server protocol on the forge.

Then you need to use this function

function connect_server()
    -- emulate a server
    proxy.response = {
        type = proxy.MYSQLD_PACKET_RAW,
        packets = {
            packet_auth()
        }
    }
    return proxy.PROXY_SEND_RESULT
end

There, you see how we return a RAW packet (proxy.MYSQLD_PACKET_RAW), most of the time you see a proxy.MYSQLD_PACKET_OK
or proxy.MYSQLD_PACKET_ERR type of result. And then you see that
packets gets the result of the packet_auth() function.

Handle the Query types.

function read_query( packet )

    if (string.byte(packet) == proxy.COM_QUIT)
    or (string.byte(packet) == proxy.COM_INIT_DB) then
      -- This gets called when you disconnect from the server
      -- or when you specify a database on connection time
        proxy.response.type = proxy.MYSQLD_PACKET_OK
        return proxy.PROXY_SEND_RESULT
    elseif string.byte(packet) == proxy.COM_QUERY then
        if packet:sub(2) == "SHOW DATABASES" then
            return show_databases()
        else
            -- Unkown query
            return error_result (
            "I haven't learnt how to handle that query"
            , '101'
            , '!101')
        end
    end
end

If the query type is either a COM_QUIT or COM_INIT_DB, you can just send an OK packet, and that will be fine.

Then we check for COM_QUERY, and once we find a query, we try to match the query text. In this example, we only handle SHOW DATABASES (all upper case), but you can handle any other query, even queries that are not real SQL :) .

If the MySQL Proxy got a SHOW DATABASES query, it will call the show_databases() function and return a resultset of 70 databases.

For all other queries, the proxy will return an error message back to the mysql client.

Send a result back to the client.

function show_databases()
    proxy.response.type = proxy.MYSQLD_PACKET_OK
    proxy.response.resultset = {
        fields = {
            { type = proxy.MYSQL_TYPE_STRING, name = "Database", },
        },
            rows = {}
    }
    -- here we add 70 database names to the resulset
    for i = 1, 70 do
        table.insert(
        proxy.response.resultset.rows,
         {"db_" .. i})
    end
    return proxy.PROXY_SEND_RESULT
end

Here we create the table for proxy.response.resultset, note the empty rows table. And then, we use a for loop to add 70 items that will become 70 rows on the resultset.

Full code.

You can find this and many other lua scripts to use with the MySQL Proxy on the MySQL forge site.

How can I use it?
Because this lua script simulates being a MySQL Server, you do not need to specify a proxy-backend-address value, just start the proxy like this:

$ sbin/mysql-proxy --proxy-lua-script=/path/to/server_mock.lua

MySQL Proxy - what if it crashes?

While I hope the MySQL Proxy never crashes, it will happen, there will be some strange (or maybe not so strange) usage or workload and it will die.

To avoid this, you could decide not to use it, or maybe you could use something like Linux HA to have more than one MySQL Proxy running at all times. Or you could use one of the new features that comes with the version 0.7.0.

What is it?
We now have a --keepalive option. As the name indicates, if the mysql proxy process dies/crashes, it will come back up in a few seconds (less than 5 seconds).

How does it work?
If you start the MySQL Proxy with the keepalive option, there will be two processes with the same name. One will be very small, about 600KB. and then you will also see the "real" mysql proxy process. The "angel" process has a lower PID value than the "real" mysql proxy process.

If there is some kind of error that causes the proxy to die, the "angel" process will wait about 3 seconds, and try to restart the proxy process.

You can "force" this behavior by sending a

$ kill -SIGKILL < pid of the proxy >

or

$ kill -SEGV < pid of the proxy >

How do I use it?
Very simple, if you use a configuration file, you need to add a new line with

keepalive = true;

or you can start the mysql proxy like this:

sbin/mysql-proxy --proxy-lua-script=/mysql-lua-scripts/trunk/scripts/proxydb.lua --keepalive

Enjoy! ... And next time I'll write about using GDB to get some debugging information about the crash.

MySQL Proxy - proxydb

What I really like about having Lua and MySQL Proxy together is that it turns out to be very flexible, you can have the proxy do all kinds of things. And the last thing I made the MySQL Proxy do is to act like memcached.

Well, maybe not, but it handles key => value pairs now :P




What does it do?

It handles 5 basic query types:

mysql> INSERT "key" = "value";

Very simple, insert a key => value pair on a proxy.global.db table, if there is already a value for that query, it will overwrite it.

mysql> SELECT "key";

It retrieves the value for the specified key.

mysql> DELETE "key";

Deletes the key => value pair from the lua table.

mysql> SELECT *;

It returns all the key=> value pairs.

mysql> DROP;

It deletes all rows from the table.

Internals.
This time I used the tokenizer to parse the queries, and I'm so glad I did. It is much easier and cleaner that writing regular expressions to match your queries.

I also wrote different functions to handle each statement. Which I call based on the tokens I find.

Looking at the script, you will find different ways of returning results back to the mysql client. Something that took me a while to figure out was, how to return an empty resulset, but with a number of affected rows, like what you get from an INSERT statement.

This is the answer:

function add( key, value )
   -- We add the key value pair to the global table
   proxy.global.db[key] = value
   -- we return a nice "affected rows count"
   proxy.response.type = proxy.MYSQLD_PACKET_OK
   proxy.response.affected_rows = 1
   proxy.response.insert_id = 0
   return proxy.PROXY_SEND_RESULT
end

If you call this function, you need to do so like this:

return add(tokens[i + 1]["text"], tokens[i + 3]["text"])

and not just like this:

add(tokens[i + 1]["text"], tokens[i + 3]["text"])

* Note the "return" there. Otherwise, you will not get the custom resulset on the client.

I also added the connect_server() function, which allows you to run the mysql proxy without any mysql server on the backend.

How do I use it?
Get the code from the MySQL Forge site, and save it as proxydb.lua
Then, start the MySQL Proxy like this:

./bin/mysql-proxy  --proxy-lua-script=/src/mysql-lua-scripts/trunk/scripts/proxydb.lua

You can then connect to the port 4040 using your mysql client and start sending queries. If you use any of the queries I showed before, the proxydb.lua script will handle them.

Numbers?
Yesterday I run some tests and I found that

• Having 3,908,492 entries
• Key length was about 11 characters
• Value length was about 17 characters
• MySQL Proxy process used 1.5GB of RAM

Next?
Who knows, maybe someone will use this script and let me know how it runs on a busy setup :).

How do I use the MySQL Proxy Admin plugin?

We have an Admin plugin for the MySQL Proxy, but people started asking how to use it. I only found one example, thanks to Giuseppe, but people wanted more.

While the admin plugin is somehow limited, it already provides some nice features. One of the use cases is to give access to information to only authorized users. The Admin plugin uses its own username and password to authenticate users. This is not related to any user on your MySQL server.

The example.

I went ahead and put two scripts together in about an hour. They are basic, but should give you more of an idea of what you can do.

Number of queries processed.

Connecting to the admin port 4041, you can execute this query

mysql> show querycounter;
+---------------+
| query_counter |
+---------------+
|            15 |
+---------------+
1 row in set (0.00 sec)

And you can see how many queries went through the proxy (does not count the queries to the admin plugin)


Backends list.

You can see some information about the backends. Type 1 means master and type 2 means slave

mysql> SELECT * FROM backends;
+-------------+-----------------+-------+------+
| backend_ndx | address         | state | type |
+-------------+-----------------+-------+------+
|           1 | 127.0.0.1:22547 | 1     | 1    |
|           2 | 127.0.0.1:22548 | 0     | 2    |
|           3 | 127.0.0.1:22549 | 0     | 2    |
+-------------+-----------------+-------+------+
3 rows in set (0.00 sec)

Client connections list

mysql> SHOW PROXY PROCESSLIST;
+------+--------------------+--------------------------+
| Id   | IP Address         | Time                     |
+------+--------------------+--------------------------+
| 249  | 127.0.0.1:53830    | Fri May  1 00:13:28 2009 |
| 248  | 127.0.0.1:53827    | Fri May  1 00:13:27 2009 |
| 247  | 127.0.0.1:53825    | Fri May  1 00:13:26 2009 |
| 250  | 192.168.0.99:53836 | Fri May  1 00:13:38 2009 |
+------+--------------------+--------------------------+
4 rows in set (0.00 sec)

The way it works is basically by collecting data using the proxy plugin, making that information available using proxy.global.* variables, and then the admin plugin reads those global variables and returns the data on a pretty format :)

You can start the proxy with a command like this:

./sbin/mysql-proxy --admin-lua-script=/Users/wizard/etools/mysql-lua-scripts/trunk/scripts/admin-1.lua \
--proxy-backendddresses=127.0.0.1:22547 \
--proxy-read-only-backend-addresses=127.0.0.1:22548 \
--proxy-read-only-backend-addresses=127.0.0.1:22549 \
--proxy-lua-script=/Users/wizard/etools/mysql-lua-scripts/trunk/scripts/reporter.lua \

--plugins=admin

Where are the Lua scripts?

You can find them on the MySQL Forge site here and here.

And remember that the default username and password for the admin plugin is root / secret, which you can change by using

--admin-username=proxy \
--admin-password=guesswhat

Enjoy!

A feature, a bug, a new feature and a bug fix

After I wrote about reading a master.info file using the MySQL Proxy, I went ahead and added the missing piece. Creating a master.info file using the MySQL Proxy.

A bug?
As I went back to lib/mysql-proto.c looking for a function that I could duplicate and modify to add the to_masterinfo_string() function, I realized that I missed a few master_ssl_* fields. It turned out that I was not exporting all the fields from the master.info file.

This time, the bug fix was easy enough. After modifying the test case to account for the missing fields, I added a few lines of code and I was ready to add the new feature.

New feature
You can now use the function to_masterinfo_string(), which takes as a parameter a table. You can create one using from_masterinfo_string()

You could read this example to get an idea of how it could be used.

Next?
I'll continue my work on Mock Load(*) and wait for the merge proposal to be accepted.

(*)More about what it does once I finish some legal paperwork here at Sun.