1

If you enable the line

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

in

/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?

Improve this question

2 Answers 2

Reset to default
2

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.

Improve this answer
7
  • On a server with 250 hosts you cannot be familiar with all scripts
    – rubo77
    May 8, 2013 at 5:11
  • You have the username! So you know whose script it is.
    – Michael Hampton
    May 8, 2013 at 5:12
  • Sure, I could find out for each entry with some work, but I need a solution to analyse all slow queries and find out the worst scripts to contact only that customer. besides: multiple slow queries inside the same script don't add up in time.
    – rubo77
    May 8, 2013 at 5:14
  • A simple shell script could tell you which users are logging the most slow queries. Do you have a system administrator? If not, you probably should hire one.
    – Michael Hampton
    May 8, 2013 at 5:16
  • 1
    I'm sorry you didn't like the answer, but the pointless downvote was just rude.
    – Michael Hampton
    May 8, 2013 at 5:33
0

You can append a comment to your queries and this will do the job:

SELECT * FROM table # php file ./path/to/file.php
Improve this answer
2
  • this is a nice Idea, but it will only help to some scripts, that you created yourself. Here I search for a solution to find out which of my 250 domains on the server causes the problem in the slow-log
    – rubo77
    May 30, 2023 at 9:30
  • @rubo77 if you are using any query builder it should be quite easy to add this comment for all the queries. Also Yii2 solves this issue in another way: github.com/yiisoft/yii2/blob/master/framework/db/…
    – Oleg
    May 31, 2023 at 12:56

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .