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
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?
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.
This work is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.