if you have query logging turned on on your production/test environment [ which is not necessary the case ] you can use mk-query-digest from maatkit toolkit. it'll help you to determine which queries are most frequent/longest taking etc.
Another commercial option is MySQL Query Analyzer which is part of the MySQL Enterprise Monitor. I've found it to be moderately useful in helping to profile oddball queries to figure out ways to improve their performance.
I've used several scripts and other tools which are all great, but i found Jet Profiler really good at giving real time monitoring and visualization of what's going on and how things are changing. The full version costs money but the restricted free version is also useful and gives you a good feel for what the full one can do.
Column Meaning
============ ==========================================================
Rank The query's rank within the entire set of queries analyzed
Query ID The query's fingerprint
Response time The total response time, and percentage of overall total
Calls The number of times this query was executed
R/Call The mean response time per execution
Apdx The Apdex score; see --apdex-threshold for details
V/M The Variance-to-mean ratio of response time
EXPLAIN If --explain was specified, a sparkline; see --explain
Item The distilled query
In the DBA StackExchange I answered MySQL general query log performance effects. In my old post, I suggested using mk-query-digest instead of the general log or slow log. From that post, here is sample output of the query profiling done by mk-query-digest:
Look into enabling the Query Log and the Slow Query Log.
if you have query logging turned on on your production/test environment [ which is not necessary the case ] you can use mk-query-digest from maatkit toolkit. it'll help you to determine which queries are most frequent/longest taking etc.
You can also check out MySQLTuner
Another commercial option is MySQL Query Analyzer which is part of the MySQL Enterprise Monitor. I've found it to be moderately useful in helping to profile oddball queries to figure out ways to improve their performance.
Here is an good article about the profiler of MySQL. Although take a look at the explain statement.
I use this little script. It's always been useful to me, although not anything official.
http://genomewiki.ucsc.edu/index.php/Tuning-primer.sh
I've used several scripts and other tools which are all great, but i found Jet Profiler really good at giving real time monitoring and visualization of what's going on and how things are changing. The full version costs money but the restricted free version is also useful and gives you a good feel for what the full one can do.
See: https://sites.google.com/site/basicsqlmanagment/ Works for me, is not a proxy profiler
I highly recommend the following
From the old MAATKIT Documentation
In the DBA StackExchange I answered MySQL general query log performance effects. In my old post, I suggested using mk-query-digest instead of the general log or slow log. From that post, here is sample output of the query profiling done by mk-query-digest:
Above this output are histograms of these 20 top worst-performing queries
Example of the first entry's histogram