Profiling MySQL

To analyze the db server usage in a complex PHP application, the first step is to profile the db server.
There are lots of tools to profile, but I think it's very easy to make a customized code to save the data really needed.
The idea is save information about some queries in the production environment (about 1% of the queries is usually enough, depending on the traffic).


MySQL profiling
Hoping there is a class used to manage queries (or at least mysqli class), it doesn't take long to replace a function that manages the queries with something similar to the following code (written in a simple way to show the idea):

class DB extends mysqli {
...
function query ($q) {

   $start = microtime(1);
   $this->query($q);
   $wtime = microtime(1) - $start;
   #save 1% of the queries + info
   if ( rand(0,100)<1>query("INSERT DELAYED INTO `ProfileData` (q,wtime,created,...) ($q,$wtime, NOW(), ...) ");
   }

}
...
}

What other info to save ? some ideas:

• client IP
• other $_SERVER info: user-agent, request_method, etc...
• PHP backtrace (to understand which line launched the query)
• web server load
• mysql server load
• ...

How to analyze results making queries on the `ProfileData` table.
example: queries grouping by query and showing the average time of the queries. In this way, you can find what queries are the slowest ones.

-- select the slowest queries (average time) in the last 24 h
-- exclusion of the queries executed only once to exclude missing sql cache
SELECT `q`,AVG(`wtime) as "medium time", COUNT(`id`) as "occurences"
FROM `ProfileData`
WHERE `created` > DATE_ADD(NOW(), INTERVAL -1 DAY)
GROUP BY `q`
HAVING COUNT(`id`) > 2
ORDER BY AVG(`wtime) DESC