03 June 2011

How to Parse the Most Selected Tables From MySQL General Query Log in One Line

Problem: Pin down redundant queries by finding out which tables are being selected from the most.

I like general query log parsing tools, such as mk-query-digest or mysqlsla.

However, there are times when I am on a server as a dba with limited access and can only read files in /tmp.  And other times I just need to get something done quickly.

So what did I do to find out the most selected tables from a database?

1. Make sure /tmp has enough space to do what I'm about to do
2. Turn on the general query log with the file at /tmp/mysql_general.log.  This is usually /var/log/mysqllog/mysql_general or similar, but I need access to this log without waking up the sysadmin.
3. Turn off the general query log before /tmp fills up (for some people this might be just minutes)
4. Parse using this nifty set of commands:
grep -i "SELECT " /tmp/mysql_general.log | grep -io "SELECT .*" | sed 's|\(FROM [^ ]*\) .*|\1|' | sort | uniq -c | sort -nr | head -100

That's all!

As my mother would say, "Try it... You'll like it."