By transparently sitting between client and server on each request, MySQL Proxy offers many possibilities for query manipulation.
Many are explored in the cookbook, and they even include a histogram recipe. Still, I wanted to learn more about the proxy while working on a script that would let me get some stats on the queries executed against a server (or group of servers).
We'll, now on to it.
Here's my super simple proxy script. It consists of a global variable and a line that spits out all the vars I want, separated by '||||'. I choose that separator since it's unlikely to happen in a real query and hence it won't cause me to loose much data while doing load data infile later. That's my scientific approach, and considering this aren't medical records, it's good enough for me.
I can get away with using a global var because the proxy fires up a new instance of the lua script for every new client connection. At least that's what I've been able to find out so far, and my empirical data has confirmed this. If source code inspection later rejects this finding, I'll have to find a better (probably more complex) way to achieve the same goal.
Ok, so with this part covered, we need to run the proxy, and then run some queries against it.
Notice that 4040 is the default port for the proxy, but you could change it into 3306, and move mysql into another port.
We need to load that into mysql.
echo "You'll be asked for MySQL root user's password"
And then run some queries against the analysis tables.
Top 10 queries, ordered by their text, and then the time it took them to get back. You can infer, by the query issue time, if the query cache was in use, and then, if it was useful. You'll be surprised that for large datasets, while the load is taken off the server by using the cache, the client doesn't perceive such a big improvement because it still takes a lot of time for the resultset to go back. Therefore he/she will still complain. Sometimes a lot of effort is put into optimizing server performance, and the way this server is accessed is totally neglected!
Well, there you go. Have fun and find out what kind of usage your application is giving to your server.