In my day to day activities, I often need to know who’s being especially bad at MySQL on multi-user systems. Sure, running SHOW PROCESSLIST; a few dozen times might give me an idea, but often you just have to start logging and hope for the best.
NO MORE I SAY!
I built a very basic MySQL query parser in Python that takes a MySQL 5.0/5.1 log file (any size, it’s rather memory efficient) and returns the total number of SELECT, UPDATE, and INSERT queries, tallies their Queries Per Second, and sorts by selects to give a final output of just who’s been bad and in what way.
I’ll let the help output do the talking:
redkrieg@h4xs3rv3r [~]$ queryparser.py --help
usage: queryparser.py [options] [filename]
[filename] is optional and defaults to stdin.
options:
-h, --help show this help message and exit
-v, --verbose Print info on stderr (default: True)
-q, --quiet Suppress stderr output
-o FILE, --output=FILE
Write output to FILE (default: stdout)
-r REGEX, --regex=REGEX
Tally arbitrary REGEX string (slow)
Supports arbitrary regex searches and takes a best-guess approach to identifying the appropriate user when it’s not obvious. Without the regex option, it can chew through a 1GB log in about 30 seconds on my VPS while staying in a single execution thread.
Download the latest version here