Jul 12 2008

PHP and MySQL Query Profiler

Published by Narcis Radu at 2:25 am under Web Development

This is not really something new, but I had to use it few days ago and I decided to write my experience with the new Query Profiler from MySQL. Jeremy Cole from Proven Scaling did a great job and since MySQL Community Server 5.0.37 we have now a built-in tool for performance analysis.

I wrote a very simple class to help me identify slow queries:

5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

 
class Profiler_MySQL
{
	private $profiles_data;
 
	public function __construct()
	{
		if(!mysql_query('set profiling=1'))
		{
			die('Unable to set profiler!');
		}
	}
 
	public function get_profiles()
	{
		$this->profiles_data .= '<table cellpadding="3" cellspacing="1" border="1">';
		$this->profiles_data .= '<tr><td>Duration</td><td>Query</td></tr>';
 
		$result = mysql_query('show profiles');
		while($profile = mysql_fetch_assoc($result))
		{
			$this->profiles_data .= '<tr><td>'.$profile['Duration'].'</td>';
			$this->profiles_data .= '<td>'.$profile['Query'].'</td></tr>';
		}
 
		$this->profiles_data .= '</table>';
 
		return true;
	}
 
	public function show_profiles()
	{
		$this->get_profiles();
		return $this->profiles_data;
	}
}

Things worked pretty well but I found that queries are truncated in the result. If you have similar queries, it becomes complicated to identify the slow one, especially in large applications. This class will be updated soon and hopefully I will integrate it with another debugging class for PHP code allowing full profiling of PHP applications.

Comments RSS

Leave a Reply

Download Day - English