Sorting by stat numbers (v.0.16)Support for sorting by total, average or maximum numbers for slow queries. Sun, 28 Jan 2007 11:16:44 GMTNew release 0.15betaImplemented support for slow query log format. Now you can extract the most frequent queries from MySQL slow query log. Fri, 05 Jan 2007 00:23:52 GMTNew features implementedNow it's possible to output the statistics only for given types of queries like SELECT, INSERT, UPDATE or whatever else. New option -type is implemented it takes a comma separated list of query types as a parameter.

Another option is -top N, which allows to output only first N top queries, as usually these are the most intresting for analyzing.

Considering NULL values as variable and normalyzig them as regular variable data by replacing with {}.

Shorten query patterns by groupping multiple insert values groups together.
So "INSERT INTO T1 VALUES(1),(2),(3);"
becomes
"insert into t1 values ({});" Fri, 24 Nov 2006 23:42:56 GMTNew version supports csv formatted query logAs mysql server 5.1 stores query log into system table of csv format, MyProfi is now able to parse this format and output the statistics for it as well. Sun, 12 Nov 2006 13:21:41 GMTUsable version releasedStill no documentation, but added some output, describing how to use it in command line.

NB! Problems may occure with php5.1.6 on certain very long queries, php crashes on preg_replace call. It's a PCRE extension bug, can do nothing to fix it on my side. No problems observed with php5.2RC6 though. Mon, 23 Oct 2006 08:22:32 GMTMyProfi first releaseFinally publically available the very first release. It's raw and uncommented. No manual, no examples, no nothing. Check the code itself. But will fix it soon. Fri, 13 Oct 2006 10:19:58 GMT

What is

MyProfi is a command line tool that parses mysql query log and outputs statistics of most frequently used queries, sorting them by number of times they appear in a log file. The similar queries are groupped together. Thus, select id from user where login="admin" and select id from user where login='root' become select id from user where login={} - the variable parts of queries are replaced with {} markers, this make statistics analysis as easy as revewing the top of most frequently occured queries' patterns.

What for

When developing an application one can hardly predict, which of the sql queries will run the most often, and which less often. The most popular queries are to be the first candidates for optimisation, fine tuning and polishing. By optimizing the most frequenly run queries the developer may achieve significant performance gain for overall system. By reviewing the statistics of MyProfi output it's becomes too easy to extract those queries to start optimizing db by adding indexes, reconsidering database structure, etc.

Where from

You may download latest stable version from project's sourceforge page

If interested in the development process or in raw version testing, the cvs repository is open for anonymous readonly access cvs -d:pserver:anonymous@myprofi.cvs.sourceforge.net:/cvsroot/myprofi myprofi

What needs

  • PHP 5.x (sorry for all you oldschool PHP 4 lovers)
  • Any platform that supports php command line version running on it