An example of MySQL server disk problem
I encountered a disk space related problem in the company this morning. It's typical. Record it. I hope it will be helpful to you.
The business side reported that there was a disk alarm. After logging in to the server, you can see the following scenario:
copy[root@ data1]# df -h /data1 Filesystem Size Used Avail Use% Mounted on /dev/sda8 1.5T 1.3T 140G 91% /data1 [root@ data1]# du -sh * 21M database_exporter 845M dbatemp 16K lost+found 116G mysql3306 6.4G mysql3630 414G mysql5186 10M mysqlbinlog
The following information can be obtained from the figure:
1. The total capacity of the disk is 1.5T, which actually takes up 1.3T
2. The mysql related directories under the / data1 directory add up to less than 600G, while other directories hardly occupy disk space
3. The 1.3T capacity seen by df -h does not match the actual 600G capacity seen by du -sh. it seems that 700G disk capacity has disappeared
Where is the remaining 700G space?
Checked the hidden files on the disk and found no clue. So I thought of a classic case before, that is, under Linux server, it is possible that the disk space is not released after deleting files. Generally, the disk space problem is caused by the non release of file handles.
Here we need to simply say a Linux command, which is lsof command:
In the Linux operating system, everything is a file, and the whole process of lsof command is (list open files), which displays all the open files. Lsof command can not only view the open files and directories of the operating system, but also view the port and other information monitored by the process. The parameters of the commonly used lsof command are as follows:
copy-a Indicates that the relationship between other options is and -c <Process name> Outputs the file opened by the specified process -d <File descriptor> Lists the processes that occupy the file number +d <catalogue> Output directory and open files and directories under the directory(No recursion) +D <catalogue> Recursive output and open files and directories under the directory -i <condition> Output qualified network related files -n Do not resolve host names -p <Process number> Output assignment PID The file opened by the process -P Do not resolve port number -t Output only PID -u Output files opened by the specified user -U Output open UNIX domain socket file -h display help information -v display version information
With this in mind, I use the lsof command to view the contents of the currently open file and filter the delete d files. The results are as follows:
copy[root@ data1]# lsof -n | grep delete COMMAND PID TID USER FD TYPE DEVICE SIZE/OFF NODE NAME cupsd 1225 root 9r REG 8,2 1926 1575211 /etc/passwd+ (deleted) mysqld_sa 9014 root 0u CHR 136,0 0t0 3 /dev/pts/0 (deleted) mysqld 10175 my5186 5u REG 8,8 15513 96731233 /data1/mysql5186/ibdpQaPG (deleted) mysqld 10175 my5186 6u REG 8,8 0 96731972 /data1/mysql5186/ibQg9qu6 (deleted) mysqld 10175 my5186 7u REG 8,8 0 96731979 /data1/mysql5186/ibLLGH9v (deleted) mysqld 10175 my5186 8u REG 8,8 0 96731980 /data1/mysql5186/ibYg7Bln (deleted) mysqld 10175 my5186 13u REG 8,8 0 96731981 /data1/mysql5186/ibm22peP (deleted) mysqld 10175 my5186 19w REG 8,8 786372990960 96731986 /data1/mysql5186/slow.log (deleted) mysqld 10175 1400 my5186 5u REG 8,8 15513 96731233 /data1/mysql5186/ibdpQaPG (deleted) mysqld 10175 1400 my5186 6u REG 8,8 0 96731972 /data1/mysql5186/ibQg9qu6 (deleted) mysqld 10175 1400 my5186 7u REG 8,8 0 96731979 /data1/mysql5186/ibLLGH9v (deleted) mysqld 10175 1400 my5186 8u REG 8,8 0 96731980 /data1/mysql5186/ibYg7Bln (deleted) mysqld 10175 1400 my5186 13u REG 8,8 0 96731981 /data1/mysql5186/ibm22peP (deleted) mysqld 10175 1400 my5186 19w REG 8,8 786372847911 96731986 /data1/mysql5186/slow.log (deleted) mysqld 10175 1401 my5186 5u REG 8,8 15513 96731233 /data1/mysql5186/ibdpQaPG (deleted) mysqld 10175 1401 my5186 6u REG 8,8 0 96731972 /data1/mysql5186/ibQg9qu6 (deleted) mysqld 10175 1401 my5186 7u REG 8,8 0 96731979 /data1/mysql5186/ibLLGH9v (deleted) mysqld 10175 1401 my5186 8u REG 8,8 0 96731980 /data1/mysql5186/ibYg7Bln (deleted) mysqld 10175 1401 my5186 13u REG 8,8 0 96731981 /data1/mysql5186/ibm22peP (deleted) mysqld 10175 1401 my5186 19w REG 8,8 786372854597 96731986 /data1/mysql5186/slow.log (deleted) mysqld 10175 1402 my5186 5u REG 8,8 15513 96731233 /data1/mysql5186/ibdpQaPG (deleted) mysqld 10175 1402 my5186 6u REG 8,8 0 96731972 /data1/mysql5186/ibQg9qu6 (deleted) mysqld 10175 1402 my5186 7u REG 8,8 0 96731979 /data1/mysql5186/ibLLGH9v (deleted) mysqld 10175 1402 my5186 8u REG 8,8 0 96731980 /data1/mysql5186/ibYg7Bln (deleted) mysqld 10175 1402 my5186 13u REG 8,8 0 96731981 /data1/mysql5186/ibm22peP (deleted)
It means that the file handles of linux have not been deleted from the disk, which means that they are not in the free state of the operating system.
As a result, we can see that a MySQL instance holds slow The log file has been deleted, but the handle has not been released. The number displayed in the size field in the red part is 786372854597. This unit is B, which is converted into GB, that is, 786G. This can explain why our disk space is consumed so much. The problem now is to solve the problem that the handle is not released.
Since the held slowlog file has not been released, we can regenerate a new slowlog file. Therefore, regenerate the slowlog file for this MySQL instance and execute the command flush slow logs, as follows:
copysuperdba@[(none)] 17:45:11>flush slow logs; Query OK, 0 rows affected (28.83 sec)
It can be seen that the execution time is still relatively long, lasting for 28s. During this period, I am still worried that the MySQL instance will hang up directly. Fortunately, it doesn't hang up, and everything is normal.
After processing, let's look at the disk space:
copy[root@mis73243 ~]# df -h /data1 Filesystem Size Used Avail Use% Mounted on /dev/sda8 1.5T 571G 874G 40% /data1
You can see that the disk space has been released.
So far, the problem has been solved.
Summary:
1. In Linux, deleting a file does not free space. It is possible that the file handle is not released and continues to occupy disk space
2. You can use the lsof command to view the currently open files. If you want to filter the deleted files, you can use the lsof -n | grep delete command
3. MySQL can use the flush slow logs command to regenerate the slowlog and release the old slowlog handle. If it is other types of logs, you can use the corresponding flush statement, as follows:
FLUSH [NO_WRITE_TO_BINLOG | LOCAL] {
flush_option [, flush_option] ...
| tables_option
}
flush_option: {
BINARY LOGS
| ENGINE LOGS
| ERROR LOGS
| GENERAL LOGS
| HOSTS
| LOGS
| PRIVILEGES
| OPTIMIZER_COSTS
| RELAY LOGS [FOR CHANNEL channel]
| SLOW LOGS
| STATUS
| USER_RESOURCES
}
tables_option: {
TABLES
| TABLES tbl_name [, tbl_name] ...
| TABLES WITH READ LOCK
| TABLES tbl_name [, tbl_name] ... WITH READ LOCK
| TABLES tbl_name [, tbl_name] ... FOR EXPORT
}
For more information about the flush command, please refer to the official MySQL documentation.