Forum Stats

  • 3,769,367 Users
  • 2,252,958 Discussions
  • 7,875,006 Comments

Discussions

Mysql daemon not releasing deleted temp file

3837224
3837224 Member Posts: 6
edited Aug 21, 2019 9:28AM in MySQL Community Space

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 [[email protected] ~]# systemctl restart mysqld [[email protected] ~]# 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 [[email protected] ~]# 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

Tagged:
3837224Dave Stokes-MySQL Community Team-OracleAndy_Cee5f1ff4-5015-4785-b316-72d265bcf850

Best Answer

  • 3412947
    3412947 Member Posts: 1
    edited May 22, 2019 11:44AM Accepted 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.

    38372243837224

Answers

  • Dave Stokes-MySQL Community Team-Oracle
    Dave Stokes-MySQL Community Team-Oracle MySQL Community Manager TexasMember Posts: 366 Employee
    edited May 4, 2019 10:38AM

    If you shutdown mysqld, are the files freed?

    Dave Stokes

    MySQL Community Manager

  • 3412947
    3412947 Member Posts: 1
    edited May 22, 2019 11:44AM Accepted 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.

    38372243837224
  • 3837224
    3837224 Member Posts: 6
    edited May 22, 2019 11:52AM

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

    Dave Stokes-MySQL Community Team-Oracle
  • Andy_C
    Andy_C Member Posts: 4
    edited Aug 21, 2019 9:28AM

    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

    ee5f1ff4-5015-4785-b316-72d265bcf850