sjon.blog

Since I am always interested in the performance of the applications I write I always try to trace average time requests take wile developing. When applications are in production however; I do not have time to monitor them and assume reasonable performance. The same goes for debugging; I hope to try out all posible requests and check the responses. However, sometimes I just need some statistics of applications already running on my server; or even from applications I never even wrote; such as Roundcube, or PHPMyAdmin.

I have implemented a simple script which allows me to do all these sort of tracing. So, download it here, and add this to a .htaccess

php_value auto_prepend_file '/location/of/file/php_access_logging.inc.php'

Don’t forget to create the table as well:

CREATE TABLE php_logging (
  `date` timestamp NOT NULL default CURRENT_TIMESTAMP,
  ip varchar(15) NOT NULL default '',
  host tinytext ,
  browser tinytext NOT NULL,
  referer tinytext,
  site tinytext,
  url tinytext,
  request text,
  user_unique varchar(32) NOT NULL default '',
  duration int(10) NOT NULL default '0',
  PRIMARY KEY  (`date`,ip, url),
  KEY user_unique (user_unique)
)

I will post some usefull queries as well:

browser_usage

SELECT COUNT(*) as c ,browser FROM `php_logging` GROUP BY browser ORDER BY c DESC

referers

SELECT COUNT(*) as count, referer FROM `php_logging` GROUP BY referer ORDER BY count DESC

unique_users

SELECT COUNT(*) AS count, host, user_unique FROM `php_logging` GROUP BY user_unique ORDER BY count DESC

slowest_urls

SELECT COUNT(*), site, url, duration FROM `php_logging` GROUP BY url ORDER BY duration DESC

average_duration_per_site

SELECT COUNT(*), site, AVG(duration) FROM `php_logging` GROUP BY site

Hope you enjoy it!

This entry was posted on Tuesday, April 25th, 2006 at 1950 and is filed under PHP [Hypertext Preprocessor], MySQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.