mysql slow / queries / sample

mysql slow / queries / sample

  • Written by
    Walter Doekes
  • Published on

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' ; 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: (view)

Back to overview Newer post: mysql replication / relay log pos Older post: postgres / alter column / look closer