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