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:
- Manual log rotating (We use it only when there is an emergency)
- 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)
Table of Contents
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