XOOPS FAQ - Dealing with error messages, faults and debugging. - 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.