MySQL Bugs: #82556: Abort query when disk is filling up due to ibtmp1 file

Bug #82556	Abort query when disk is filling up due to ibtmp1 file
Submitted:	11 Aug 2016 21:15	Modified:	18 Jul 2019 12:55
Reporter:	monty solomon	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	5.7.13	OS:	CentOS
Assigned to:		CPU Architecture:	Any

Description:
The data disk filled up due to the size of the ibtmp1 file and the database was unusable until it was restarted.

There does not appear to be any mechanism to control the size of the ibtmp1 file or to have InnoDB abort a query when the size of the ibtmp1 file exceeds a specified threshold.

There does not appear to be any mechanism to shrink the size of the itbmp1 file after the query finishes or is killed.

Filesystem Size Used Avail Use% Mounted on
/dev/xvdf 100G 100G 308K 100% /opt/mysql/dbdata1

# ls -lt
total 64997480
-rw-r----- 1 mysql mysql 60978888704 Aug 11 16:15 ibtmp1
-rw-r----- 1 mysql mysql 31457280 Aug 11 16:15 xb_doublewrite
-rw-rw---- 1 mysql mysql 5538578432 Aug 11 16:15 ibdata1

How to repeat:
2016-08-11T16:00:42.484647Z 1901698 [ERROR] InnoDB: posix_fallocate(): Failed to preallocate data for file /opt/mysql/dbdata1/progression-p0/ibtmp1, desired size 67108864 bytes. Operating system error number 28. Check that the disk is not full or a disk quota exceeded. Make sure the file system supports this function. Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/ operating-system-error-codes.html

2016-08-11T16:00:42.837826Z 1901698 [Warning] InnoDB: Retry attempts for writing partial data failed.

2016-08-11T16:00:42.837856Z 1901698 [ERROR] InnoDB: Write to file /opt/mysql/dbdata1/progression-p0/ibtmp1failed at offset 60978888704, 1048576 bytes should have been written, only 0 were written. Operating system error number 28. Check that your OS and file system support files of this size. Check also that the disk is not full or a disk quota exceeded.

2016-08-11T16:00:42.837885Z 1901698 [ERROR] InnoDB: Error number 28 means 'No space left on device'

2016-08-11T16:00:42.837892Z 1901698 [Note] InnoDB: Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html

2016-08-11T16:00:42.837905Z 1901698 [Warning] InnoDB: Error while writing 67108864 zeroes to /opt/mysql/dbdata1/progression-p0/ibtmp1 starting at offset 60947431424

2016-08-11T16:00:42.837930Z 1901698 [ERROR] /usr/sbin/mysqld: The table '/opt/mysql/dblogs1/progression-p0/tmp/#sql_17ad_0' is full

2016-08-11T16:00:42.837954Z 1901155 [ERROR] InnoDB: posix_fallocate(): Failed to preallocate data for file /opt/mysql/dbdata1/progression-p0/ibtmp1, desired size 35651584 bytes. Operating system error number 28. Check that the disk is not full or a disk quota exceeded. Make sure the file system supports this function. Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/ operating-system-error-codes.html

2016-08-11T16:00:42.838401Z 1901155 [Warning] InnoDB: Retry attempts for writing partial data failed.

2016-08-11T16:00:42.838477Z 1901155 [Warning] InnoDB: Error while writing 35651584 zeroes to /opt/mysql/dbdata1/progression-p0/ibtmp1 starting at offset 60978888704

2016-08-11T16:00:42.838498Z 1901155 [ERROR] /usr/sbin/mysqld: The table '/opt/mysql/dblogs1/progression-p0/tmp/#sql_17ad_1' is full

2016-08-11T16:03:27.127626Z 0 [Note] InnoDB: Dumping buffer pool(s) to /opt/mysql/dbdata1/progression-p0/ib_buffer_pool

2016-08-11T16:03:27.167900Z 0 [ERROR] InnoDB: Cannot write to '/opt/mysql/dbdata1/progression-p0/ib_buffer_pool.incomplete': No space left on device

2016-08-11T16:11:01.538832Z 1904299 [ERROR] InnoDB: posix_fallocate(): Failed to preallocate data for file ./Progression_2/progression_transitions.ibd, desired size 4194304 bytes. Operating system error number 28. Check that the disk is not full or a disk quota exceeded. Make sure the file system supports this function. Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/ operating-system-error-codes.html

2016-08-11T16:11:01.539305Z 1904299 [Warning] InnoDB: Retry attempts for writing partial data failed.

2016-08-11T16:11:01.539386Z 1904299 [Warning] InnoDB: Error while writing 4194304 zeroes to ./Progression_2/progression_transitions.ibd starting at offset 15028191232

2016-08-11T16:11:01.542390Z 1904299 [ERROR] /usr/sbin/mysqld: The table 'progression_transitions' is full

2016-08-11T16:11:01.576896Z 1904096 [ERROR] InnoDB: posix_fallocate(): Failed to preallocate data for file ./Progression_2/progression_transitions.ibd, desired size 4194304 bytes. Operating system error number 28. Check that the disk is not full or a disk quota exceeded. Make sure the file system supports this function. Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/ operating-system-error-codes.html

2016-08-11T16:11:01.577062Z 1904096 [Warning] InnoDB: Retry attempts for writing partial data failed.

2016-08-11T16:11:01.577070Z 1904096 [Warning] InnoDB: Error while writing 4194304 zeroes to ./Progression_2/progression_transitions.ibd starting at offset 15028191232

2016-08-11T16:11:01.577712Z 1904096 [ERROR] /usr/sbin/mysqld: The table 'progression_transitions' is full

2016-08-11T16:11:03.570897Z 1904263 [ERROR] InnoDB: posix_fallocate(): Failed to preallocate data for file ./Progression_2/progression_transitions.ibd, desired size 4194304 bytes. Operating system error number 28. Check that the disk is not full or a disk quota exceeded. Make sure the file system supports this function. Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/ operating-system-error-codes.html

2016-08-11T16:11:03.571053Z 1904263 [Warning] InnoDB: Retry attempts for writing partial data failed.

2016-08-11T16:11:03.571060Z 1904263 [Warning] InnoDB: Error while writing 4194304 zeroes to ./Progression_2/progression_transitions.ibd starting at offset 15028191232

2016-08-11T16:11:03.575515Z 1904263 [ERROR] /usr/sbin/mysqld: The table 'progression_transitions' is full

2016-08-11T16:11:03.706533Z 1904300 [ERROR] InnoDB: posix_fallocate(): Failed to preallocate data for file ./Progression_2/progression_transitions.ibd, desired size 4194304 bytes. Operating system error number 28. Check that the disk is not full or a disk quota exceeded. Make sure the file system supports this function. Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/ operating-system-error-codes.html

2016-08-11T16:11:03.706996Z 1904300 [Warning] InnoDB: Retry attempts for writing partial data failed.

2016-08-11T16:11:03.707076Z 1904300 [Warning] InnoDB: Error while writing 4194304 zeroes to ./Progression_2/progression_transitions.ibd starting at offset 15028191232

2016-08-11T16:11:03.707745Z 1904300 [ERROR] /usr/sbin/mysqld: The table 'progression_transitions' is full

2016-08-11T16:11:04.303713Z 1904299 [ERROR] InnoDB: posix_fallocate(): Failed to preallocate data for file ./Progression_2/progression_transitions.ibd, desired size 4194304 bytes. Operating system error number 28. Check that the disk is not full or a disk quota exceeded. Make sure the file system supports this function. Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/ operating-system-error-codes.html

2016-08-11T16:11:04.304167Z 1904299 [Warning] InnoDB: Retry attempts for writing partial data failed.

2016-08-11T16:11:04.304264Z 1904299 [Warning] InnoDB: Error while writing 4194304 zeroes to ./Progression_2/progression_transitions.ibd starting at offset 15028191232

2016-08-11T16:11:04.304953Z 1904299 [ERROR] /usr/sbin/mysqld: The table 'progression_transitions' is full

2016-08-11T16:11:04.800155Z 1904300 [ERROR] InnoDB: posix_fallocate(): Failed to preallocate data for file ./Progression_2/progression_transitions.ibd, desired size 4194304 bytes. Operating system error number 28. Check that the disk is not full or a disk quota exceeded. Make sure the file system supports this function. Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/ operating-system-error-codes.html

2016-08-11T16:11:04.800635Z 1904300 [Warning] InnoDB: Retry attempts for writing partial data failed.

2016-08-11T16:11:04.800721Z 1904300 [Warning] InnoDB: Error while writing 4194304 zeroes to ./Progression_2/progression_transitions.ibd starting at offset 15028191232

2016-08-11T16:11:04.801403Z 1904300 [ERROR] /usr/sbin/mysqld: The table 'progression_transitions' is full

2016-08-11T16:11:05.432712Z 1904299 [ERROR] InnoDB: posix_fallocate(): Failed to preallocate data for file ./Progression_2/progression_transitions.ibd, desired size 4194304 bytes. Operating system error number 28. Check that the disk is not full or a disk quota exceeded. Make sure the file system supports this function. Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/ operating-system-error-codes.html

Suggested fix:
The integrity of the database is more important than a running query that would fill the disk. It is more important to protect the database and abort the query than to fill the disk.

Provide a mechanism for the automatic cleanup of the ibtmp1 file.

Provide configurable limits on the size of the ibtmp1 file.

Provide a mechanism for the automatic aborting of queries that are causing the disk to fill up.

Provide a mechanism for the manual resizing of the ibtmp1 file.

Can this feature be used with the ibtmp1 file?

Online Truncate of InnoDB UNDO Tablespaces
http://mysqlserverteam.com/online-truncate-of-innodb-undo-tablespaces/

Is a resolution coming soon to this bug? I am seeing the same behavior in 5.7.17, Red Hat Enterprise Linux Server release 7.3 (Maipo).

This is not acceptable:

-rw-r----- 1 mysql mysql 859G May 11 19:37 ibtmp1

This is on a business intelligence server dedicated to mysql with a ~3.5TB data directory. A massive temporary table was generated during report generation over a 2.5 day span, this led to the file growing 600GB in 6 days. Such DDL/DML is sub-optimal and should be addressed at the user level, but that does not minimize the impact of this bug. Environments that have >99% uptime requirements cannot be effectively managed without dedicating a massive amount of disk to temporary table creation or risk db server stalls and unplanned outages.

Why don't you limit the size of the ibtmp1 file?  Try in my.cnf :

innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:1G

will limit it to 1G in size.   When that is hit, queries fail like this:

mysql> insert into t values(repeat('a',1000000));
ERROR 1114 (HY000): The table 't' is full

Halting the use of any and all temp tables is hardly a solution, and still requires a restart of the daemon.

If:

select s,sum(g) from joinit2 group by s;

fails with:

ERROR 1114 (HY000): The table '/tmp/#sql_1bef4_0' is full

and it does, setting a hard limit is worse behavior IMO.

[12 May 7:39] Shane Bester
Why don't you limit the size of the ibtmp1 file?  Try in my.cnf :

innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:1G

will limit it to 1G in size.   When that is hit, queries fail like this:

mysql> insert into t values(repeat('a',1000000));
ERROR 1114 (HY000): The table 't' is full

Hi!

This problem is well known to the developers and there are some ideas on how to improve the behaviour in this respect. However, this is not a bug, but a change in design.

Verified as a feature request.

This bug can be closed. 8.0 uses temp table engine with disk overflow and InnoDB introduced "session temporary tablespaces". ibtmp1 is no longer used.

Thank you , Satya ......

Well in the interim of the 18 months it was fixed as a part of some other WorkLog entry.

Regarding 8.0.x not using `ibtmp1`, I can confirm that the file *is* present on our servers, and if we remove it and restart, a new one is created.

However, there is also a folder which is not present on our MySQL 5.7 boxes, which may be the newer implementation.  If so, it is not clear why `ibtmp1` is being created and seems to be being used:

'#innodb_temp'

Hi Mr. solomon,

You are writing about 8.0 in the bug dedicated to 5.7.

Also, presence of the file or directory that is, seemingly, not used can not be considered a bug nor a feature request.