KLab/myprofiler

Name: myprofiler

Owner: KLab Inc.

Description: Sampling profiler for MySQL

Created: 2011-12-15 12:01:08.0

Updated: 2017-10-22 16:38:23.0

Pushed: 2016-09-13 12:19:11.0

Homepage: http://dsas.blog.klab.org/archives/52024948.html

Size: 27

Language: Python

GitHub Committers

User	Most Recent Commit	# Commits
Yasui	2012-07-10 10:37:47.0	2
INADA Naoki	2016-09-13 12:16:08.0	43

Other Committers

User	Email	Most Recent Commit	# Commits
INADA Naoki	inada-n@klab.jp	2012-07-14 07:49:03.0	1
makiuchi-d	makiuchi-d@klab.jp	2012-07-13 03:21:07.0	2

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.

 https://github.com/KLab/myprofiler/releases/download/0.1/myprofiler.linux_amd64.tar.gz
xf myprofiler.linux_amd64.tar.gz
yprofiler ~/bin

Options

e of ./myprofiler:
elay=1: (int) Show summary for each `delay` samples. -interval=0.1 -delay=30 shows summary for every 3sec
ump="": Write raw queries to this file
ost="localhost": Host of database
nterval=1: (float) Sampling interval
assword="": Password
ort=3306: Port
ast=0: (int) Last N samples are summarized. 0 means summarize all samples
op=10: (int) Show N most common queries
ser="": User

Profiling

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

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