MySQL Bugs: #57126: truncate of dc_ng

Bug #57126	truncate of dc_ng_* not frees the space
Submitted:	30 Sep 2010 8:40	Modified:	24 Jan 2011 20:09
Reporter:	Susanne Ebrecht	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Enterprise Monitor: Server	Severity:	S3 (Non-critical)
Version:	2.2.3	OS:	Any
Assigned to:	Mark Leith	CPU Architecture:	Any

Description:
A customer recognised this.

After truncating old dc_ng_* space isn't free.

It's easy to reproduce. When you truncate an innodb table and ibd file stays full.

This is an expected behaviour according to:
http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-other-changes-truncate.html

A solution could be ALTER TABLE ENGINE=INNODB after truncating.

How to repeat:
See above

Mark Leith writes: 
Patch pushed to 2.2:

8147 Mark Leith	2010-10-05
     Bug#57126 / EM-4931 - truncate of dc_ng_* not frees the space
     InnoDB does not release file space back to the OS when using innodb_file_per_table and TRUNCATE TABLE, unless using the new plugin.
     So we detect running on an "old" version, and then after doing a TRUNCATE, we null ALTER TABLE to release the space. 

   modified:
     Monitor/src/com/mysql/etools/monitor/pom/hib/QueryUtils.java
     Monitor/test/com/mysql/etools/monitor/pom/hib/QueryUtilsTest.java

8148 Mark Leith	2010-10-05
     Bug#57126 / EM-4931 - truncate of dc_ng_* not frees the space
     Check whether innodb_file_per_table is enabled as well. 
     The hudson instance did not have it enabled, so also added the following to it's my.cnf:

     innodb_file_per_table
     innodb_buffer_pool_size = 256M
     innodb_log_file_size = 64M

   modified:
     Monitor/src/com/mysql/etools/monitor/pom/hib/QueryUtils.java
     Monitor/test/com/mysql/etools/monitor/pom/hib/QueryUtilsTest.java

Merged to 2.3:

8205 Mark Leith	2010-10-05
     Bug#57126 / EM-4931 - truncate of dc_ng_* not frees the space
     Merged from 2.2

   modified:
     Monitor/src/com/mysql/etools/monitor/pom/hib/QueryUtils.java
     Monitor/test/com/mysql/etools/monitor/pom/hib/QueryUtilsTest.java

Awaiting merge to trunk currently.

Andy Bang writes: 
In build 2.3.0.2030.

The major tables truncated are the legacy data collection ones, those starting with dc_ng_*, such as:

dc_ng_string_now
dc_ng_long_now
dc_ng_double_now

The point release does not matter for the innodb_file_per_table option, as we always set that explicitly within the MEM my.cnf file. The only thing that matters is whether the plugin is in use or not in 5.1, which we detect by checking for the existence of the "innodb_adaptive_flushing" variable. 

If we are on the "old", we "TRUNCATE TABLE ...;" and then "ALTER TABLE ... ENGINE = InnoDB;". For "new" we simply "TRUNCATE TABLE ...;".

Added to change log for 2.2.4 and 2.3.0:

When MySQL Enterprise Monitor truncated the the legacy data
collection tables, such as dc_ng_string_now and dc_ng_long_now, the
disk space was not released back to the operating system, even if the
innodb_file_per_table setting was enabled. Now, the disk space is
released correctly, for all combinations of MySQL with or without the
InnoDB Plugin.