[MDEV-4016] On-disk Temporary Tables Are Leaking Created: 2013-01-09  Updated: 2019-02-20  Resolved: 2013-11-01

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.0
Fix Version/s: 10.0.4

Type: Bug Priority: Major
Reporter: Gordan Bobic Assignee: Unassigned
Resolution: Fixed Votes: 0
Labels: upstream
Environment:

Linux (RHEL6)


 Description   

It would appear that temporary tables that get created on disk are being leaked. A lot of #sql*.ibd files get left behind in /tmp and never get reaped, even though most of them do not have open file handles (checked with lsof).

There are no user-processes running on the server, only replication (from multiple sources) so it is somewhat mysterious that the temp tables are getting create in the first place.

This may be related to MDEV-4015. I'm wondering if it is possible that the reaping function is looking for MyISAM tables (aria is the default engine for temp tables, a compile time option), but since the default storage engine is InnoDB, the tables get created as such, and the clean-up doesn't catch them.

 Comments   
Comment by Elena Stepanova [ 2013-01-09 ]

I can see this too. Regardless the default engine, with innodb_file_per_table=1 and InnoDB as an engine for a temporary table, 2 files per table are created in the tmp folder:

rw-rw--- 1 elenst elenst 8554 2013-01-09 18:22 #sql2ef8_1_0.frm
rw-rw--- 1 elenst elenst 98304 2013-01-09 18:22 #sql2ef8_1_0.ibd

rw-rw--- 1 elenst elenst 8554 2013-01-09 18:22 #sql2ef8_1_0.frm
rw-rw--- 1 elenst elenst 98304 2013-01-09 18:22 #sql2ef8_1_0.ibd
rw-rw--- 1 elenst elenst 8554 2013-01-09 18:22 #sql2ef8_1_1.frm
rw-rw--- 1 elenst elenst 98304 2013-01-09 18:22 #sql2ef8_1_1.ibd

When connection exits, .frm file gets removed, but .ibd doesn't:

rw-rw--- 1 elenst elenst 98304 2013-01-09 18:22 #sql2ef8_1_1.ibd
rw-rw--- 1 elenst elenst 98304 2013-01-09 18:22 #sql2ef8_1_0.ibd

It doesn't seem to be happening on MySQL 5.6.9, possibly it was fixed in last 5.6 versions. I will need to dig a bit deeper into that to see if it was an upstream bug or not.

Comment by Elena Stepanova [ 2013-01-09 ]

This one from mysql/5.6 commit log looks like a viable suspect:

revno: 3690.34.123
revision-id: kevin.lewis@oracle.com-20120615140142-f67erf3yqtg006cn
parent: dmitry.lenev@oracle.com-20120615112952-34wshruq16dqo9jo
committer: kevin.lewis@oracle.com
branch nick: mysql-trunk
timestamp: Fri 2012-06-15 09:01:42 -0500
message:
Bug #14169459 INNODB; DROP TABLE DOES NOT DELETE THE IBD FILE
FOR A TEMPORARY TABLE.

Comment by Elena Stepanova [ 2013-01-09 ]

Confirmed: the problem was fixed in MySQL 5.6.7-rc, upstream revno: 3690.34.123 revision-id: kevin.lewis@oracle.com-20120615140142-f67erf3yqtg006cn.

Comment by Michael Widenius [ 2013-03-21 ]

The fix is in the InnoDB code in MySQL 5.6. This will be included in the merge of MySQL 5.6 GA that we are doing for next 10.0 release.

A temporary work around is to use CREATE TEMPORARY .... .ENGINE=ARIA

Comment by Sergei Golubchik [ 2013-10-31 ]

Is it still an issue?

Comment by Elena Stepanova [ 2013-11-01 ]

No, it's reproducible on 10.0.3, but not on 10.0.4, apparently was fixed by the InnoDB merge. Closing.

Comment by Ed Greenberg [ 2019-02-20 ]

I think this is still a problem. Or is a problem again. I'm using 10.1.35 on Centos 7 installed from the rackspace-centos7-x86_64-ius repository.

My server was restarted on 2/16/19. It currently has an uptime of 313014 seconds (or 3.6 days).

I have accumulated over 43000 files in the temp directory, in the format of #sqlxxxx_xxx_0.ibd.

Example. The oldest file in my tmp directory is

rw-rw--- 1 mysql mysql 9437184 Feb 16 21:07 #sql1c3d_72fa_0.ibd

Then:
lsof|grep sql1c3d_72fa_0.ibd
produces 38 lines of open file output for the same filename:

  1. lsof|grep sql1c3d_72fa_0.ibd
    mysqld 7229 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
    mysqld 7229 7311 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
    mysqld 7229 7312 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
    mysqld 7229 7354 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
    mysqld 7229 7355 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
    mysqld 7229 7356 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
    mysqld 7229 7357 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
    mysqld 7229 7358 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
    mysqld 7229 7359 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
    mysqld 7229 7360 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
    mysqld 7229 7361 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
    mysqld 7229 7362 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
    mysqld 7229 7363 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
    mysqld 7229 7364 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
    mysqld 7229 7526 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
    mysqld 7229 7527 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
    mysqld 7229 7528 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
    mysqld 7229 7529 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
    mysqld 7229 7530 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
    mysqld 7229 7531 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
    mysqld 7229 7533 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
    mysqld 7229 7534 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
    mysqld 7229 7535 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
    mysqld 7229 7536 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
    mysqld 7229 7537 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
    mysqld 7229 7568 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
    mysqld 7229 7757 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
    mysqld 7229 21398 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
    mysqld 7229 23732 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
    mysqld 7229 23903 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
    mysqld 7229 23972 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
    mysqld 7229 23985 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
    mysqld 7229 24067 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
    mysqld 7229 24324 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
    mysqld 7229 24508 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
    mysqld 7229 24510 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd

Over time, this really can fill up, requiring a server stop/start, which requires notification of outage, and all the procedure that comes with this.

What more information can I provide?

Thanks,
Ed Greenberg

Generated at Thu Feb 08 06:53:04 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.