Add causing php-script to mysql-slow.log

rubo77 asked:

If you enable the line

log_slow_queries = /var/log/mysql/mysql-slow.log


/etc/my.cnf or /etc/mysql/my.cnf

then that will log all queries, that take longer than the setting in long_query_time.

On a server with many websites hosted you’ll get entries, that show, which host caused which time in the form like:

# Time: 130508  0:04:07
# User@Host: dbuser[dbhost] @ localhost []
# Query_time: 9.354343  Lock_time: 0.000094 Rows_sent: 0  Rows_examined: 1
use database_name;
SET timestamp=1367964247;
UPDATE some_table SET something='some data'

but the most important information is missing: which script called that query? And multiple slow queries inside the same script don’t add up in time, so it would be handsome to have an analysis, that finds out the script-name automatically.

Is it possible to add the PHP-script name to that log?

Or maybe somehow combine the different logs somehow in an analysis script, that finds out, which script was called during that time the slow query was called?

My answer:

There’s no way for MySQL to know what PHP script was running. All it knows is it got a connection from username, password, host, and was asked for some query. So it logs what it has available.

If you’re the developer, you should hopefully be familiar enough with your application to locate the code making the query within a few seconds. If you aren’t the developer, or aren’t familiar with the application, try grep. Or contact the developer who is familiar with it.

View the full question and answer on Server Fault.

Creative Commons License
This work is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.