Rotating MySQL slow query log

MySQL slow_query_log logrotate 



We often enable MySQL slow_query_log to proactively troubleshoot the performance, There is so lot of information with-in slow_query_log which is enough to address most common (even complex ones occasionally) MySQL performance bottlenecks. What if we haven’t planned for the log lifecycle management of slow query log ? Your log will really grow huge (we keep long_query_time as-low-as 1 second), So we have to plan for slow_query_log rotating to avoid MySQL outage due to “no space left in the disk” to grow further. There are two ways you can do log rotate, I have explained below:

  1. Manual log rotating (We use it only when there is an emergency)
  2. Using logrotate to automatic MySQL log rotation (We love it, MinervaDB is an big fan of MySQL automation & DevOps. but we also recommend to use it very carefully)

Step-by-step manual log rotating of slow_query_log

Step 1 – Get the path of slow_query_log

mysql> show variables like '%slow_query%';
+---------------------+-----------------------------------+
| Variable_name       | Value                             |
+---------------------+-----------------------------------+
| slow_query_log      | ON                                |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+
2 rows in set (0.00 sec)

Step 2 – Disable slow_query_log temporarily

mysql> set global slow_query_log=off;
Query OK, 0 rows affected (0.01 sec)

Step 3 – Flush only slow query logs

mysql> flush slow logs;
Query OK, 0 rows affected (0.00 sec)

Step 4 – Rename the old slow query log and or compress the same

[root@localhost ~]# mv /var/lib/mysql/localhost-slow.log /var/lib/mysql/localhost-slow-$(date +%Y-%m-%d).log

[root@localhost mysql]# ls *.log
localhost-slow-2018-04-18.log
[root@localhost mysql]# gzip -c /var/lib/mysql/localhost-slow-2018-04-18.log > /var/lib/mysql/localhost-slow-2018-04-18.log.gz
[root@localhost mysql]# ls *.gz
localhost-slow-2018-04-18.log.gz
[root@localhost mysql]# 

Step 5 – Re-enable slow_query_log

mysql> 
mysql> set global slow_query_log=on;
Query OK, 0 rows affected (0.00 sec)

Using logrotate  to rotate MySQL slow_query_log 

We at MinervaDB love automation, logrotate make slow_query_log rotation simple , To enable logrotate you can use logrotate config file in  /etc/logrotate.d/ mysql-slow-query:

/var/lib/mysql/localhost-slow.log {
    size 2G
    dateext
    compress
    missingok
    rotate 30
    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 slow_query_log=@sq_log_save;'
    endscript
    rotate 250
}

 

We have explained below logrotate script:

  • size 2G: Rotate the slow query log only if it is more that 2GB.
  • dateext: archive the old slow query log files with a date extension, YYYYMMDD format.
  • compress: compress slow query log files using gzip.
  • delaycompress: Delay the compression of the previous slow query log file until next rotation cycle.
  • missingok: Don’t issue error message, if slow query log file is missing.
  • rotate30: Retain 30 slow query log files before deleting old files.
  • Run prerotate & postrotate  scripts only once, no matter how many slow query logs matches to the wildcard pattern
  • nocopytruncate: Don’t truncate the original slow query log file in place after creating a copy. logrotate use rename() system call to move the file to a new file name and expects its daemon to be signaled to reopen it’s slow query log file.
  • copytruncate: logrotate truncates the original slow query log file in place after creating a copy. We don’t recommend you to use this option because truncation blocks MySQL due to OS serializing the access on inode. This happens more on ext3 file system compared to xfs
  • create 660 mysql mysql: create new slow query log file after rotation which will be owned by mysql with permission mode 660
  • postrotate: this script is executed after slow query log rotation is done

 

About MinervaDB Corporation 88 Articles
Independent and vendor neutral consulting, support, remote DBA services and training for MySQL, MariaDB, Percona Server, PostgreSQL and ClickHouse with core expertize in performance, scalability and high availability . We are an virtual corporation, all of us work from home on multiple timezones and stay connected via Email, Skype, Google Hangouts, Phone and IRC supporting over 250 customers worldwide
UA-155183614-1