How can I track down the cause of excessive database queries?

Requested by Dave_L and Answered by Mamba on 2007/1/19 9:55:07

How can I track down the cause of excessive database queries?

--- Original details submitted by David_L on 2005/4/19 0:01:56 Here's a hack you can use to log all database queries, along with some information that identifies the user. You'll need to change "/example/path" to specify the path to a writable directory for the log files. This directory should be protected from viewing from a web browser. A new log file is created each hour. The log files may consume a lot of disk space, so monitor them. class/database/mysqldatabase.php (function queryF in class XoopsMySQLDatabase)

#*#DEBUG# - start
$logdir  = '/example/path'; ### path to writable directory ###
$logext  = 'log'; // log file extension
$now     = time();
$ymdhms  = date('Y-m-d H:i:s', $now);
$ymdh    = date('Y-m-d-H', $now);
$sql_q   = addcslashes($sql, "rnt");
$logfile = "$logdir/$ymdh.$logext";
error_log("[$ymdhms] [{$_SERVER["REQUEST_METHOD"]}] [{$_SERVER['REMOTE_ADDR']}] [{$_SERVER['HTTP_USER_AGENT']}] [{$_SERVER["SCRIPT_NAME"]}] [$sql_q]n", 3, $logfile);
chmod($logfile, 0666);
#*#DEBUG# - end
$result =& mysql_query($sql, $this->conn);
--- Additional details submitted by Madfish You can also find the number of database queries for page loads by turning on debug mode (in admin -> system -> preferences -> general). This will display the number of SQL queries at the bottom of each page, and you can click on the link to get a list of what all the queries were, which will help identify the problem areas. Turning them off and reloading the page will allow improvements (or not!) to be assessed.

This Q&A was found on XOOPS Web Application System : https://xoops.org/modules/smartfaq/faq.php?faqid=462