MySQL Bugs: #59783: InnoDB data grows unexpectedly when inserting, truncating, inserting the same nu

Bug #59783	InnoDB data grows unexpectedly when inserting, truncating, inserting the same nu
Submitted:	27 Jan 2011 18:39	Modified:	6 Apr 2012 19:06
Reporter:	Arnaud Adant	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.1.51, 5.1.54, 5.5.8	OS:	Any
Assigned to:	Marko Mäkelä	CPU Architecture:	Any
Tags:	truncate innodb increase datafile

Description:
When inserting N rows using a stored procedure into an InnoDB table in a fresh datafile, truncate will not free space. 
Inserting back N rows will still increase the size of the datafile.

The datafile size should remain as it was after the first insert.

innodb_max_purge_lag = 0 

How to repeat:
create database test1;

use test1;

CREATE TABLE `aa_1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `txt_val` varchar(1024) DEFAULT NULL,
  `ins_dt` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DELIMITER ;;

CREATE  PROCEDURE `usp_inserts`(
in p_vals int
)
BEGIN

DECLARE v1 INT;

set v1 = p_vals;

WHILE v1 > 0 DO

insert into test1.aa_1(txt_val, ins_dt)
values( concat('some text to go here but not a full composition, 
just enough to make the innodb table space to grow. 
Adding more text to the data and finally, 
records to go before end: ', cast(v1 as char)), now() );

SET v1 = v1 - 1;

END WHILE;

END ;;
DELIMITER ;

call test1.usp_inserts(200000);

truncate table test1.aa_1;

-- at this point the size should not increase

call test1.usp_inserts(200000);

Suggested fix:
none

Thank you for the report.

Do you use InnoDB Plugin?

If not this is expected behavior. See at http://dev.mysql.com/doc/refman/5.1/en/truncate-table.html:

When you use this fast truncation technique with the innodb_file_per_table option enabled, the operating system can reuse the freed disk space. For users of the InnoDB Plugin, the space is reclaimed automatically, as decribed in TRUNCATE TABLE Reclaims Space. If you do not have the InnoDB Plugin installed, issue the OPTIMIZE TABLE statement to free the disk space for the table.

Hi Sveta,

It also occurs with innodb_file_per_table = 1.

The problem here is not that the space is not reclaimed by the OS, but that it is not reused as stated in : 

http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-other-changes-truncate.html

[Previous versions of InnoDB would re-use the existing .idb file, thus releasing the space only to InnoDB for storage management, but not to the operating system.] 

However, as you mentioned, with innodb_file_per_table = 1, if you issue an optimize table statement after the truncate, the table datafile is emptied.

With the InnoDB plugin enabled, the space is reclaimed as the truncate statement.

With a single datafile, the optimize table after the truncate table will not change anything.

Not sure it is a bug. A fast [so I may be wrong] reading through the data says innodb_file_per_table=1
works as expected. For innodb_file_per_table=0 we can't release anything to OS
especially giving the lack of OS level file blocks-in-the-middle truncate.

If this is a bug, it cannot be fixed when using innodb_file_per_table=0, at least not without implementing something like defragmentation of InnoDB tablespaces, which would be a major undertaking.

When innodb_file_per_table=1, the built-in InnoDB in MySQL 5.1 will not re-create the *.ibd file. The InnoDB Plugin in MySQL 5.1 will, and so will subsequent versions of InnoDB.

OPTIMIZE TABLE will copy and replace the original table. When using innodb_file_per_table=1 in the built-in InnoDB of MySQL 5.1, the following commands will reclaim the space, if the table existed in a tablename.ibd file of its own.

TRUNCATE TABLE tablename;
OPTIMIZE TABLE tablename;

With the InnoDB Plugin in MySQL 5.1 or with subsequent versions of InnoDB, you should not need the OPTIMIZE.

Arnaud, can you please formulate what exactly is the problem here, and what you would like to have fixed, and how?

With innodb_file_per_table = 0, users would expect that subsequent inserts after a truncate table would re-use the datafile disk space that has already been allocated so that :

- insert N lines in a table
- truncate this table
- insert N same lines

results in close to zero data file increase.

Same behavior for innodb_file_per_table = 1 without the innoDB plugin.

In these cases, the OPTIMIZE statement will not help.

Stated otherwise, what is the reason for not re-using the space after a truncate under these conditions ? It is surely not a fragmentation issue : 
there is a full empty tablespace ready to host the new data.

The disk increase in innoDB datafiles is a major concern for users as it forces them to backup lost space (more time and resources) and eventually dump there database from time to time to reclaim the "lost" disk space.

Added to changelog for 5.1.63, 5.5.23, 5.6.6: 

When data was removed from an InnoDB table, newly inserted data might
not reuse the freed disk blocks, leading to an unexpected size
increase for the system tablespace or .ibd file (depending on the
setting of innodb_file_per_table. The OPTIMIZE TABLE could compact a
.ibd file in some cases but not others. The freed disk blocks would
eventually be reused as additional data was inserted.

https://bugs.mysql.com/bug.php?id=92023 marked as duplicate of this one.