Skip to Main Content

Mysql daemon not releasing deleted temp file

3837224May 2 2019 — edited Aug 21 2019

Dear all, I'm having a severe issue with MySQL server 8.0. The mysqld process doesn't release lock on deleted temp table, consuming a lot of disk space. This is a partail output of lsof command: mysqld 13831 13850 mysql *592u REG 253,0 1048584 34759935 /tmp/mysql_temptable.ol3xZW (deleted) mysqld 13831 13850 mysql *593u REG 253,0 1048584 34760055 /tmp/mysql_temptable.CpaEXB (deleted) mysqld 13831 13850 mysql *594u REG 253,0 1048584 34759954 /tmp/mysql_temptable.LkmsvB (deleted) mysqld 13831 13850 mysql *595u REG 253,0 1048584 34759944 /tmp/mysql_temptable.SLnDoR (deleted) mysqld 13831 13850 mysql *596u REG 253,0 1048584 34759929 /tmp/mysql_temptable.2TozJv (deleted) mysqld 13831 13850 mysql *597u REG 253,0 1048584 34759946 /tmp/mysql_temptable.pkVIfG (deleted) mysqld 13831 13850 mysql *598u REG 253,0 1048584 34760014 /tmp/mysql_temptable.ikVMIA (deleted) mysqld 13831 13850 mysql *599u REG 253,0 1048584 34761022 /tmp/mysql_temptable.kTfeeY (deleted) mysqld 13831 13850 mysql *600u REG 253,0 1048584 34759906 /tmp/mysql_temptable.K7EfKt (deleted) mysqld 13831 13850 mysql *601u REG 253,0 1048584 34759953 /tmp/mysql_temptable.k5pCqB (deleted) mysqld 13831 13850 mysql *602u REG 253,0 1048584 34759934 /tmp/mysql_temptable.F54V80 (deleted) mysqld 13831 13850 mysql *603u REG 253,0 1048584 34759942 /tmp/mysql_temptable.3xlL2B (deleted) mysqld 13831 13850 mysql *604u REG 253,0 1048584 34760074 /tmp/mysql_temptable.iK2iJj (deleted) mysqld 13831 13850 mysql *605u REG 253,0 1048584 34759943 /tmp/mysql_temptable.lYxfQS (deleted) mysqld 13831 13850 mysql *606u REG 253,0 1048584 34759959 /tmp/mysql_temptable.Sn5PFx (deleted) mysqld 13831 13850 mysql *607u REG 253,0 1048584 34759941 /tmp/mysql_temptable.A8z4bn (deleted) mysqld 13831 13850 mysql *608u REG 253,0 1048584 34759921 /tmp/mysql_temptable.jgBJOb (deleted) mysqld 13831 13850 mysql *609u REG 253,0 1048584 34759907 /tmp/mysql_temptable.92quX4 (deleted) mysqld 13831 13850 mysql *610u REG 253,0 1048584 34759908 /tmp/mysql_temptable.v3h9aG (deleted) mysqld 13831 13850 mysql *611u REG 253,0 1048584 34760039 /tmp/mysql_temptable.0ZNMEs (deleted) mysqld 13831 13850 mysql *612u REG 253,0 1048584 34760054 /tmp/mysql_temptable.Biv5Jj (deleted) When the process is restarted, the locked space is released and everything go back to normal: File system                      Dim. Usati Dispon. Uso% Montato su /dev/mapper/rhel-root              76G  61G    15G  81% / devtmpfs                          7,8G    0    7,8G  0% /dev tmpfs                            7,8G    0    7,8G  0% /dev/shm tmpfs                            7,8G  409M    7,4G  6% /run tmpfs                            7,8G    0    7,8G  0% /sys/fs/cgroup /dev/sda1                        1014M  228M    787M  23% /boot 192.168.4.64:/volume1/FILESERVER  17T  6,1T    11T  38% /mnt/fileserver tmpfs                            1,6G    0    1,6G  0% /run/user/0 [root@mysql-cluster-2 ~]# systemctl restart mysqld [root@mysql-cluster-2 ~]# df -h File system                      Dim. Usati Dispon. Uso% Montato su /dev/mapper/rhel-root              76G  17G    59G  23% / devtmpfs                          7,8G    0    7,8G  0% /dev tmpfs                            7,8G    0    7,8G  0% /dev/shm tmpfs                            7,8G  409M    7,4G  6% /run tmpfs                            7,8G    0    7,8G  0% /sys/fs/cgroup /dev/sda1                        1014M  228M    787M  23% /boot 192.168.4.64:/volume1/FILESERVER  17T  6,1T    11T  38% /mnt/fileserver tmpfs                            1,6G    0    1,6G  0% /run/user/0 [root@mysql-cluster-2 ~]# Could you please help me in understanding how to solve this issue? May I missing something in the configuration? this is the my.cnf: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid #GENERAL port = 3306 report-port = 3306 max-connections = 400 max-allowed-packet = 32M default-authentication-plugin = mysql_native_password #collation-server = utf8_unicode_ci #character-set-server = utf8 sql-mode = NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION tmp-table-size = 32M max-heap-table-size = 32M datadir=/var/lib/mysql log-error-verbosity=3 #INNODB innodb-buffer-pool-size = 12G innodb-buffer-pool-instances = 12 innodb-flush-method = O_DIRECT innodb-log-file-size = 1024M innodb-log-buffer-size = 64M innodb-data-file-path=ibdata1:12M:autoextend innodb-log-files-in-group=2 innodb-data-home-dir=/var/lib/mysql/ innodb-log-group-home-dir=/var/lib/mysql/ #innodb-flush-log-at-trx-commit = 2 #innodb_force_recovery = 1 #REPLICATION server-id = 81 master-info-repository=TABLE relay-log-info-repository=TABLE log-bin = mysql-bin.log max-binlog-size = 500M #sync-binlog = 1000 binlog-expire-logs-seconds = 259200 binlog-format = ROW binlog-checksum = NONE enforce-gtid-consistency = ON gtid-mode = ON log-slave-updates = ON super-read-only = ON transaction-write-set-extraction = XXHASH64 #FIREWALL loose-mysql_firewall_mode=ON loose-mysql_firewall_trace=ON collation-server = utf8_general_ci character-set-server = utf8 [client] default-character-set=utf8 [mysql] default-character-set=utf8 Thanks in advance for your kind help Best regards, Andrea

Comments
Post Details
Added on May 2 2019
4 comments
3,492 views