Sunday, July 26, 2009

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.

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 = incoming_val * 1.1 , 0)
elseif (method == "add") then
incoming_val = incoming_val + increaser
return incoming_val
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.

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.

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
and the modules are in


$ 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.

Vote on Planet MySQL