Details
Major
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.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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.
Confirmed: the problem was fixed in MySQL 5.6.7-rc, upstream revno: 3690.34.123 revision-id: kevin.lewis@oracle.com-20120615140142-f67erf3yqtg006cn.
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
Is it still an issue?
No, it's reproducible on 10.0.3, but not on 10.0.4, apparently was fixed by the InnoDB merge. Closing.
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.frmrw-rw--- 1 elenst elenst 98304 2013-01-09 18:22 #sql2ef8_1_0.ibdrw-rw--- 1 elenst elenst 8554 2013-01-09 18:22 #sql2ef8_1_0.frmrw-rw--- 1 elenst elenst 98304 2013-01-09 18:22 #sql2ef8_1_0.ibdrw-rw--- 1 elenst elenst 8554 2013-01-09 18:22 #sql2ef8_1_1.frmrw-rw--- 1 elenst elenst 98304 2013-01-09 18:22 #sql2ef8_1_1.ibdWhen connection exits, .frm file gets removed, but .ibd doesn't:
rw-rw--- 1 elenst elenst 98304 2013-01-09 18:22 #sql2ef8_1_1.ibdrw-rw--- 1 elenst elenst 98304 2013-01-09 18:22 #sql2ef8_1_0.ibdIt 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.