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 profilingHoping 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
