[MDEV-18785] On-disk Temporary Tables are leaking AGAIN Created: 2019-03-01  Updated: 2023-05-08

Status: Open
Project: MariaDB Server
Component/s: Data Definition - Temporary
Affects Version/s: 10.1.35
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Ed Greenberg Assignee: Matthias Leich
Resolution: Unresolved Votes: 0
Labels: need_verification
Environment:

mariadb101u-server.x86_64 1:10.1.35-1.ius.centos7 @rackspace-centos7-x86_64-ius


 Description   

Back in 2013, MDEV-4016 was posted , and marked resolved in 10.0.4. Temporary tables are being created on disk and not cleaned up.

We are seeing this behavior in 10.1.35.

I posted a comment on the closed but, but I suspect that was not the correct way to raise the issue, since it got no comments. Here it is in a new bug.

My server was restarted on 2019-02-16. On 2019-02-20, it had 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:

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

Then we have a bunch of

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

 Comments   
Comment by Elena Stepanova [ 2019-03-11 ]

mleich, could you please try to reproduce it?

Comment by Marko Mäkelä [ 2023-05-08 ]

For the record: Starting with MariaDB Server 10.2.2 (when InnoDB was updated from MySQL 5.7.9 and 5.7.14), InnoDB actually treats temporary tables as non-persistent and writes them into a shared tablespace whose default name is ibtmp1. There have been some issues related to that, such as MDEV-28240 and MDEV-26782.

Generated at Thu Feb 08 08:46:43 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.