0

I am running a MySql 8.0.29 server serving as a replica for another server on an ubuntu 20.04.4 LTS box.

'slow_query_log','ON'
'slow_query_log_file','/var/log/mysql/mysql-slow.log'
'long_query_time', '10.000000'

I ran a query that took approx. 20s, however, the query doesn't show up in mysql-slow.log. The file is empty (size 0).

Any ideas where I can look to find out why the query is not recorded in the slow log? I checked the error logs, and syslog and there is nothing related to this. File permissions are ok as well.

Thanks

Update: it worked for a while after flushing the slow log, then it lost it again. Not sure what it is...

2 Answers 2

0

I ran FLUSH SLOW LOGS and now it got its brains back. The slow queries are logged again. I still have to investigate what could have caused mysql not to update the file.

Today I checked the logs, and they were empty again. I think it's logrotate that interferes with the logging. I found this article and they suggest flushing the log:

https://oguya.ch/posts/2016-04-13-safely-rotating-mysql-slow-logs/

/var/lib/mysql/mysql-slow.log {
    size 1G
    dateext
    compress
    missingok
    rotate 20
    notifempty
    delaycompress
    sharedscripts
    nocopytruncate
    create 660 mysql mysql
    postrotate
        /usr/bin/mysql -e 'select @@global.slow_query_log into @sq_log_save; set global slow_query_log=off; select sleep(5); FLUSH SLOW LOGS; select sleep(10); set global [email protected]_log_save;'
    endscript
    rotate 150
}

My configuration is:

/var/log/mysql/mysql-error.log /var/log/mysql/mysql.log /var/log/mysql/mysql-slow.log {
    daily
    rotate 7
    missingok
    create 644 mysql adm
    Compress
}
3
  • That would be because of this answer, mysql is still writing to the slow log file, but its now moved. So you'll see it writing to a deleted file because it didn't get notified by FLUSH SLOW LOGS to reopen the file. The other aspects of the postrotate script are unnecesary.
    – danblack
    Jun 21 at 5:28
  • Are you referring to that what these guys write about? percona.com/blog/2014/11/12/…
    – costa
    Jun 21 at 22:23
  • Use pt-query-digest or mysqlslow -s t to summarize the slowlog.
    – Rick James
    Jun 22 at 1:14
0

What values do you have for these?

log_output = FILE
slow_query_log = ON
slow_query_log_file = (fullpath to some file)
long_query_time = 1
log_slow_admin_statements = ON
log_queries_not_using_indexes = OFF
log_slow_slave_statements = ON    
log_slow_replica_statements = ON
log_slow_extra = ON

(Some may be missing from your version; that is OK.)

1
  • I don't have all the flags flipped like yours but the important ones are. The first 4 are similar (path and long_query_time different) + I set log_slow_extra = ON. The others are off.
    – costa
    Jun 21 at 22:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.