Bookmark and Share

Adding script name on SQL queries

Posted: Tuesday, February 10th, 2009 at 10:45 pmUpdated: Wednesday, February 11th, 2009 at 10:37 am

Have you ever done SHOW FULL PROCESSLIST on MySQL and found that there are some queries that has been running for a long time? Then you tried to find out from where the query was initiated so that you can debug those script only to find out that there are many similar queries from different scripts.

Well, if you’re working on a relatively small source code, it may not be a big deal. However, if you have a large code base, trying to debug such condition above is kinda daunting, especially if you want to find it quickly. My personal experience is that I often found a few files that may be executing similar queries and thus, I could not find out for sure from which of the scripts the slow query was initiated.

What are we working towards

From the preface above, I need a way to easily find out which script responsible in executing the SQL query, preferably something I can find out easily from executing SHOW FULL PROCESSLIST on MySQL.

Since I’m working mostly on PHP, my friend Justin Swanhart had a simple, yet brilliant idea on utilizing PHP’s debug_backtrace() function to automatically do exactly what I need. Armed with his idea, here’s what I did.

Modify query header on your db library

First of all, if you’re not using any database abstraction layer, you’re out of luck. There’s no easy way to add script name on each of mysql_query() calls. If you fall into this category, do consider (I mean really consider) of rewriting your code to use one of the available database abstraction layer.

Ok having that out of the way, here’s the base PHP code to generate the query header you can append to all queries to be executed.

// Pardon my use of /\n. It looks like my plugin to display
// sourcecode has a bug that makes backslash n turned
// into n without backslash. When you copy this code,
// make sure to change /\n to the correct new line char.
$query_header = "/* /\n Call Trace:/\n";
$script_trace = debug_backtrace();
while ($script_data = array_pop($script_trace)) {
	$query_header .= $script_data['file'];
	$query_header .= ' on line ';
	$query_header .= $script_data['line'] . "/\n";
}
$query_header .= "*//\n";

If you just want the original script that initiates the query, simply get the first array_pop() and include only the first script name as the query header. Now that we have the header that we want, we can simply prepend $query_header variable into all SQL queries to be executed.

Justin also advised to "[r]emember not to put anything in the query comments that will invalidate the query cache, such as a timestamp. The query cache in [MySQL] 5.1+ is better about ignoring comments so it shouldn’t matter so much if you are using a newer version.". Here’s what he means. Most modern DBMS have query cache. How it works, in short, is if you execute one query then execute it again within a short period of time, the DBMS doesn’t actually do the work again to get the data. Rather, it’ll just get the data from its cache; thus returns the subsequent query faster than the first one. Now what happens if you add a timestamp or something that will always change as part of query header, the DBMS will always think that it’s a diffent query and thus you pretty much nullify DBMS’ query cache.

If you’re (like me, still love) using PHPLib

I use PHPLib only for their database abstraction stuff. It’s one of the oldest database abstraction library in PHP I believe. I’ve been using it since 2000 and it serves me fine all these years. In PHPLib, you normally create your own abstraction class extending DB_Sql. In that case, simply override query() function. Here’s a sample code:

require_once("db_mysql.inc"); // or db_pgsql.inc or other.
class DB_Example extends DB_Sql {
	var $Host     = "localhost";
	var $Database = "example_database";
	var $User     = "example_user";
	var $Password = "";

	function query($query_string) {
		// Note the next line should only have 1 empty. It's
		// another bug I found on sourcecode WP plugin.
		if (!empty($query_string)) {
			$query_header = "/*/\nCall Trace:/\n";
			$script_trace = debug_backtrace();
			while ($script_data = array_pop($script_trace)) {
				$query_header .= $script_data['file'];
				$query_header .= ' on line ';
				$query_header .= $script_data['line'] . "/\n";
			}
			$query_header .= "*//\n";

			$query_string = $query_header . $query_string;
		}

		return parent::query($query_string);
	}
}

If you’re using PDO, PEAR DB, MDB, or MDB2, the mod is pretty much the same as my example for PHPLib above. Basically, override the query() function, construct the query header and just before sending it to DBMS server. And as always, I welcome comments / questions / critics that will help me and other readers understand better.

One Response to “Adding script name on SQL queries”

  1. Justin Swanhart Says:

    Great article. Thanks for sharing.

Leave a Reply