Wednesday, March 16, 2011

MySQL Slow Query Log Analyzer

I found myprofi suitable for my requirement:

Download myprofi in /opt



cd myprofi

php parser.php

OUTPUT will be like :

MyProfi: mysql log profiler and analyzer

Usage: php parser.php [OPTIONS] INPUTFILE

-top N
Output only N top queries
-type "query types"
Ouput only statistics for the queries of given query types.
Query types are comma separated words that queries may begin with
Output one sample query per each query pattern to be able to use it
with EXPLAIN query to analyze its performance
Consideres an input file to be in csv format
Note, that if the input file extension is .csv, it is also considered as csv
Treats an input file as a slow query log
Sort output statistics by given CRITERIA.
Works only for slow query log format.
Possible values of CRITERIA: qt_total | qt_avg | qt_max | lt_total | lt_avg | lt_max | rs_total
rs_avg | rs_max | re_total | re_avg | re_max,
where two-letter prefix stands for "Query time", "Lock time", "Rows sent", "Rows executed"
values taken from data provided by sloq query log respectively.
Suffix after _ character tells MyProfi to take total, maximum or average
calculated values.

php parser.php -csv -top 10 -type "SELECT, UPDATE" /va/lib/mysql/slow-query-file.log

No comments: