asked on
MySQL slow log not logging entries
I have an issue in mariadb running in centos where the slow query log does not log entries until you go in daily, disable and re-enable the slow log.
**You go in today and run SET GLOBAL slow_query_log = 1;
**You check that it is enabled by running SHOW GLOBAL VARIABLES LIKE 'slow_%';
**You check queries are being logged after running the above.
**You come in tomorrow, check the log, no entries
**You repeat the above command again and it is enable but nothing is logged until you SET GLOBAL slow_query_log = 0; and reset it back to SET GLOBAL slow_query_log = 1;
**Now it would log just for that day.
**come the next day, nothing in the slow log. Repeat the above command over again and it would log entries for that day.
IT team are using Ansible and logrotate is in place. Could it be that it is not properly flushing the file after rotation? or Is Ansible changing conf daily? Or is mariadb writing to a stale file descriptor?
***slow log output: FILE
[***********@itv-mysql01 ~]$ cat /etc/logrotate.conf
# see "man logrotate" for details
# rotate log files weekly
weekly
# keep 4 weeks worth of backlogs
rotate 4
# create new (empty) log files after rotating old ones
create
# use date as a suffix of the rotated file
dateext
# uncomment this if you want your log files compressed
#compress
# RPM packages drop log rotation information into this directory
include /etc/logrotate.d
# no packages own wtmp and btmp -- we'll rotate them here
/var/log/wtmp {
monthly
create 0664 root utmp
minsize 1M
rotate 1
}
/var/log/btmp {
missingok
monthly
create 0600 root utmp
rotate 1
}
Please suggestions are welcome to arrive at a permitting solution
ASKER
Ansible is the only thing that comes to mind, and could be responsible.
This should output your current settings for slow loging ...
if you are using logrotate to control the file size, does the logratate include postrotate to create the file owned by the user with the correct permissions?
ASKER
I pasted infor about the logrotate above with the question.
Slow log settings are correct and same with other servers.
While you might be using logrotate the info on what or when it rotates the slow log what happens.
ASKER
its using mysqladmin to flush the log. logrotate itself copies the log file to the archive directory before running the 'mysqladmin flush-error-log flush-engine-log flush-general-log flush-slow-log' on the original log.
ASKER
Is there any logrotate configuration file in /etc/logrotate.d for the mariadb log files?
***yes
*** $ cat /etc/logrotate.d
cat: /etc/logrotate.d: Is a directory
*** $ cd /etc/
*** $ ls -larth
-rw-r--r--. 1 root root 662 Jul 31 2013 logrotate.conf
Is there an archive directory to which logrotate is writing before you run the mysqladmin flush commands?
***logrotate itself copies the log file to the archive directory before running the 'mysqladmin flush-error-log flush-engine-log flush-general-log flush-slow-log' on the original log.
Are there any playbook settings in Ansible explicitly doing the logrotation and log flush in mariadb?
*** According to the IT team, Ansible wont be doing anything daily.
If mariadb is not restarting every day, the configuration of the slow log should not change.
***it's a production server, so mariadb is not restarting at all until a schedule maintenance window.
There is something in the log rotation and post-rotate flush that is resetting the log. Is the file /var/log/mysql/slow_query.
***yes, the file exist daily and it's not removed.
ASKER
I-wx- - - - . 1 mysql mysql 64 Dec 21 08:35 263 -> /var/log/mariab/itv-mysql0
You posted in your original question that you ran the show Variables, was the slow log on or off?
what is the ownership of the slow log file?
i think it should be owned by mysql:mysql as the likely username under whose rights mariadb is running.
What do you run for the log to start being written again on the mariadb?
ASKER
This is weird that the file the file system has the same permissions as the other servers.
I.e. when the logrotate rolls the log and issues relevant parameters to the mariadb, it refreshes and rescinds the slow log settings? The other servers have the slow log parameters in the my.cnf and that is why they are not impacted while this server is?
ASKER
# MySQL settings
ignore_db_dirs = BACKUP
max_connections = 1000
default_storage_engine = InnoDB
transaction-isolation = READ-COMMITTED
event_scheduler = ON
slave_sql_verify_checksum = NONE
sql_mode = "TRADITIONAL"
wait_timeout = 28800
log_output = "FILE"
log_slow_filter = 'filesort,filesort_on_disk
long_query_time = 5
min_examined_row_limit = 0
innodb_flush_log_at_trx_co
skip_name_resolve = 1
tmp_table_size = 64M
max_heap_table_size = 64M
thread_cache_size = 400
thread_pool_size = 64
table_open_cache = 10000
key_buffer_size = 16
join_buffer_size = 128
table_definition_cache = 16384
slow_query_log = 1
slow_query_log_file = "/var/log/mariadb/itv-mysq
log_error = "/var/log/mariadb/itv-mysq
The only possible issue I think deals with whether the connection is really flushed port logrotate.
much depends on the process you have whether you can trigger the rotation of the log out of order without causing issues.
logrotate -f /etc/logrotate.conf
to then see whether the mariadb reattaches to the /var/log/mariadb/itv_mysql
Do you compress slow logs?
see whether the prior log continues getting the updates.
Have a command section in your logrotate to flush the slow logs:
mysql -uUSER -pPASS -e "FLUSH SLOW LOGS"
OR
/usr/bin/mysqladmin flush-logs
ASKER
slow_query_log = 1
long_query_time = 1 (second, or however many seconds you want to have as the minimum slow query duration)
slow_query_log_file = /var/log/mysql/slow_query.
Is it possible that Ansible is removing the slow query log daily?