myprofiler

README

myprofiler

myprofiler is statistical profiler for MySQL.

It watches SHOW FULL PROCESSLIST periodically, and count each query.

Install

Download archive and put it on directory in your PATH.

wget https://github.com/KLab/myprofiler/releases/download/0.2/myprofiler.linux_amd64.tar.gz
tar xf myprofiler.linux_amd64.tar.gz
mv myprofiler ~/bin

Options

Usage of ./myprofiler:
  -delay=1: (int) Show summary for each `delay` samples. -interval=0.1 -delay=30 shows summary for every 3sec
  -dump="": Write raw queries to this file
  -host="localhost": Host of database
  -interval=1: (float) Sampling interval
  -password="": Password
  -port=3306: Port
  -last=0: (int) Last N samples are summarized. 0 means summarize all samples
  -top=10: (int) Show N most common queries
  -user="": User

Profiling

myprofiler -host=db1234 -user=dbuser -password=dbpass -interval=0.2 -delay=10 -top=30

You should use user having 'PROCESS' privilege, or same user to your app. See document for "SHOW FULL PROCESSLIST".

myprofiler transform query like mysqldumpslow. For example, "SELECT * FROM user WHERE id=42" is transformed into "SELECT * FROM user WHERE id=N". myprofiler counts transformed quries and show top N (specified by -top) queries.

myprofiler sleeps seconds specified by -interval for each sample. 0.2 (up to 5 queries/sec) may be low enough for your production DB.

You can get raw query by -dump=rawquery.txt option. It is useful when you want to EXPLAIN heavy queries.

Monitoring (long profiling)

You can use log rotation tool like rotatelogs or multilog.

myprofiler -host=db1234 -user=dbuser -password=dbpass -last=60 -delay=30 | rotatelogs logs/myprofiler.%Y%m%d 86400

-last=60 means myprofiler only summarize last 60 samples. Since interval is 1 sec by default, this command shows top queries in minute.