2

We have a large php application and trying to debugging slow sql queries, we'd like to have the ability to auto comment what file actually called the sql query, something like:

"select /* filename.php line 234*/ `ID`,`Email`, `NickName`, `AlertBusiness`,`City` from People where ?=? and limit ?,?"

where the /* filename.php line 234*/ is inserted dynamically. Probably

In php there is a debug_backtrace, http://php.net/manual/en/function.debug-print-backtrace.php

Which could do it manually: 

<?php

$query = sprintf("INSERT INTO EventLog (Trace) VALUES ('%s')",
    mysql_real_escape_string(join("\n", debug_backtrace())) );
mysql_query($query);

?>

But was hoping for something more central like in a db driver.

MediaWiki has a similar feature. http://www.mediawiki.org/wiki/Manual:How_to_debug

You can also enable backtrace on SQL error by setting $wgShowDBErrorBacktrace:

$wgShowDBErrorBacktrace = true;"

the PDO driver has a dump statement, but that's only the query, not who called it http://php.net/manual/en/pdostatement.debugdumpparams.php

How do you best debug sql in context of the php page running it?

TroyWorks
  • 411
  • 8
  • 16
  • _“MediaWiki has a similar feature”_ – pretty sure they implemented that “manually” as well, like in your first as example … – CBroe Aug 08 '14 at 16:40
  • Have a look at this Q&A http://stackoverflow.com/q/4777517/ – Funk Forty Niner Aug 08 '14 at 16:40
  • You could write your own database class that calls PDO, after instrumenting the queries. – Barmar Aug 08 '14 at 16:41
  • 1
    You can register for free with New Relic and do an easy setup on your PHP-enabled web server. Once setup, you log in to New Relic, wait for some requests, and you will be able to see where everything slows down using their interface. New Relic plugs in directly to the PHP software, so there's no code to edit or PDO/DB driver to change. – Carl Bennett Aug 16 '14 at 14:36

1 Answers1

0

It maybe too simple but you can turn on slow query log in MySQL and find the top 10-25 slow queries.

After you have your list of slow queries you can use Percona Toolkit to analyze them.

I would use ack/ack-grep to find the culprit and where it lives. If you have a large number of files making calls directly to MySQL it will be painful to go in and route them all through a method that appends/attaches a MySQL comment to the query and might not be the most efficient.

If you have multiple apps connecting to the same database it's best to separate the users as well so you can determine which users/applications are eating up database resources.

bedlam
  • 357
  • 3
  • 9