In previous weeks, we’ve covered some debugging tools for both MySQL and PHP that can be very helpful in troubleshooting problems with your LAMP-based web applications. However, there is a little known feature in the Varien library installed with Magento that will automagically log database queries to a file. This feature not only provides a great deal of insight into the behind-the-scenes functionality of Magento, it also helps you to debug slow-running queries and other Model-based performance problems.
To enable this feature, open the file lib/Varien/Db/Adapter/Pdo/Mysql.php in your favorite text editor. Down around line 86, you’ll see the following class variables:
[code language=”php”] /**
* Write SQL debug data to file
*
* @var bool
*/
protected $_debug = false;
/**
* Minimum query duration time to be logged
*
* @var unknown_type
*/
protected $_logQueryTime = 0.05;
/**
* Log all queries (ignored minimum query duration time)
*
* @var bool
*/
protected $_logAllQueries = false;
/**
* Add to log call stack data (backtrace)
*
* @var bool
*/
protected $_logCallStack = false;
/**
* Path to SQL debug data log
*
* @var string
*/
protected $_debugFile = ‘var/debug/sql.txt’;[/code]
The documentation does a good job of explaining what these variables do, but I’ll go through them anyway.
[code language=”php”]protected $_debug = false;[/code]
– This is the switch that turns SQL debugging on or off. Set it to “true” to enable.
[code language=”php”]protected $_logQueryTime = 0.05;[/code]
– This sets the minimum time in seconds that will determine which queries get logged. This is useful for finding slow queries that could be impacting your store’s performance. 0.05 seconds is a bit low. I recommend setting it to 1.0 seconds if you want to find the really slow queries.
[code language=”php”]protected $_logAllQueries = false;[/code]
– This variable tells Magento to ignore the previous option and just log everything. Magento does a lot of database work, so turning this option on will generate a great deal of data. If you just want to get a general sense of the database operations going on when loading a specific page, you can enable this option by setting it to “true”.
[code language=”php”]protected $_logCallStack = false;[/code]
– This option is helpful when debugging custom and third-party modules because in addition to the queries, it logs a backtrace of the methods that called the query. Again, if you’ve turned on the option to log all queries, this option will produce even more data to sift through.
[code language=”php”]protected $_debugFile = ‘var/debug/sql.txt’;[/code]
– Finally, you have the option to set the location of the debug file, which is set to (your Magento root)/var/debug/sql.txt by default.
If you decide to enable this feature to debug your Magento store, make sure that you limit access your website by editing the appropriate section of your .htaccess file. Otherwise you will get a mix of queries from users browsing your site.
[code]############################################
## By default allow all access
#Order allow,deny
#Allow from all
Order deny,allow
Deny from all
Allow from YOUR.IP.ADDR.ESS[/code]
Also, make sure you remember to turn debugging off once you’re finished troubleshooting, or else the size of this log file will become a problem in itself. Hopefully this will help you to figure out that elusive bit of database latency slowing down your site. Happy hunting!
