0

MySQL's disk space usage increases as time goes on, but drops all at once when I restart MySQL. After much investigation I think I have found the cause of the problem, it seems that MySQL is writing to a file even though it is marked as deleted. Do you know how I can avoid this kind of problem?

MySQL version : 8.0.21 OS: CentOS 8

Command used for: lsof -u mysql | grep REG | grep /home/mysql/log/query.log

Return: mysqld 1387561 mysql 36w REG 253,0 8301798945 17011295 /home/mysql/log/query.log-20230110 (deleted)

0

1 Answer 1

0

That's a feature of Unix.

  • The shell command rm (and the C function unlink()) deletes a file from the directory it is in. But it does not remove it from disk until the file is closed.

  • /tmp/ is cleared after any reboot.

  • Some products deliberately open a file for writing and then immediately unlink() it. Then they write (and read) it. This action, together with the items above provides a way to create a temp file that is guaranteed to go away even if things crash.

  • mv renames a file by adding a link to it, then unlinking the original file. (There are more details.)

  • MySQL's FLUSH LOGS "closes" all logs and reopens them. So, if mv or rm was done in the meanwhile, MySQL will now actually be writing the correctly named file.

MySQL has several log files. In some installations, some of them are automatically 'rotated' by utilities driven by cron. This rotation may perform a mv to give it a new name or simply rm it. Either way, mysqld is oblivious to it since it still has the file open.

binlog

The binary log is where MySQL writes for replication and/or for backup. After a set amount of space (default: 100MB), it will close the file and open a new file with a higher sequence number.

Meanwhile, it may also "expire" old binlog file(s) based on another turnable. That is, some old file(s) will be deleted.

I like to set the expiration to 7 days. SHOW VARIABLES LIKE '%expire_logs%'; to see what your system is doing.

If you are seeing a drop of about 100MB, I would check the binlogs.

general log

The "general log" fills up disk rapidly because it records every query performed. There is no auto-rotate or auto-purge. SHOW VARIABLES LIKE '%general%';

Are you asking out of curiosity? Or is disk space a problem?

2
  • Thanks for these explanations on how Linux works with deleted files. I will try to find a solution to make a log rotate on the querylog file
    – rhiezcnl
    Commented Jan 11, 2023 at 8:54
  • @rhiezcnl - There are different techniques, based on whether you want to (1) immediately toss (rm+flush) (2) keep just one backup (rm,mv,flush) or (3) keep lots of backups (mv,flush)
    – Rick James
    Commented Jan 11, 2023 at 17:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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