Skip to Main Content
Forums

MySQL Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

This post has been answered by 3412947 on May 22 2019
Jump to Answer

Comments

If you shutdown mysqld, are the files freed?

3412947
Answer

I am experiencing the same problem and spent the majority of my work day yesterday researching it.  I believe you are hitting a known bug that affected MySQL 5.7.22 - 5.7.25 and 8.0.14 - 8.0.15.  It has been fixed in 5.7.26 and 8.0.16.  Oracle BUG id is 28039829 "File handles are leaked for SELECT queries involving complex joins."  The way MySQL uses on-disk internal temp tables (those produced during a query, not the explicit temporary tables that a user can create), to not leave any temp files behind in case of a server crash, by design MySQL creates the tmp file, and then immediately unlinks it (so it appears to the OS as deleted), but MySQL keeps the descriptor open to the file. You can see this under /proc/{mysqld pid}/fd.  When these work normally, once the thread is done with the temp table, it releases the descriptor and the space and open file are released.   But due to a mistake in file handles that was introduced, it was possible that the descriptors were not being freed.  If you upgrade to the latest release, the problem should go away.  In the meantime, you can workaround (sort of), by setting your max open files higher and giving your tmp location some extra space, and even then you may have to periodically bounce mysqld to reclaim the fds and space.  The bug report has a partial workaround on 8.0.14 & 8.0.15 suggested to set loose_internal_tmp_mem_storage_engine=MEMORY.  For my servers, I run the Percona Server fork, and they have not released their 8.0.16 port (yet as of today), and I eagerly await it.

Marked as Answer by 3837224 · Sep 27 2020
1 person found this helpful
3837224

Yes, I confirm that with upgrade to 8.0.16 the bug has been fixed.

1 person found this helpful
Andy_C

We're having the same problem (8.0.15) - it's a show stopper for us but we have a very extended upgrade time-line so we need a work-around (or work-arounds) that will keep this from occurring until we're able to upgrade (to 8.0.17).

One obvious work-around is to stop and restart mysqld (say weekly) - however, that's kind-of a big deal timing-wise and the problem could still occur between restarts.

In terms of the broken sym-links - does anybody know if it's safe to remove them? That would be an easy, safe, and reliable work-around. I'm thinking that since the files are no longer needed (they were temp files) and are no longer on disk and mysql was supposed to have removed the sym-links anyway it would be ok to run a script periodically to remove them. However, I don't know if mysql would 'object' to them being removed by a non-mysqld process.

Thank you!

Andy

1 person found this helpful
1 - 4

Post Details

Added on May 2 2019
#mysql-faqs
4 comments
4,948 views