mysql slow / queries / sample
Sometimes you’re in a situation where you know that a database is more heavily loaded than it should be. Time to figure out which queries are stressing it the most.
The standard thing to do with a MySQL database would be to enable query
logging with general_log_file. Or, to get only slow queries and those
not using indexes, the log_slow_queries.
But, if this is a mission critical and heavily loaded database, adding expensive logging may be just enough to give it that final push to become overloaded.
Perhaps taking just a sample of queries is good enough too. In that case this script is for you:
$ sed -e '/This little/,/^import/!d' mysql-slow-queries-sample.py ; echo ...
# This little script can be used to take a sampling of queries that
# are run often and/or take a lot of time.
#
# Every time you run this, a single 'show processlist' is executed on
# the local mysqld. If any queries are seen, they're cleaned up a bit
# and printed to stdout. Run this as often as you feel comfortable
# with; if run often/long enough you'll get statistically significant
# data about which queries put the most load on your database.
#
# Example output sort:
# $ grep -v '^\[' output.txt | sed -e 's/^[^:]*: //' |
# sed -e 's/[0-9]\{5,\}/<NUMBER>/g' | sort | uniq -c | sort -n |
# tail -n5
#
import datetime, re, subprocess
...
Get the full script here: mysql-slow-queries-sample.py (view)