MySQL Bugs: #80151: Unexpected rapid table datafile growth

Bug #80151	Unexpected rapid table datafile growth
Submitted:	26 Jan 2016 9:57	Modified:	26 Jan 2016 18:33
Reporter:	Rafael Bos	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.6.27	OS:	Red Hat
Assigned to:		CPU Architecture:	Any

Description:
Hi,
we've discovered a possible bug in InnoDB which leads to unexpected and rapid table datafile growth. This happened on a table with datafile size of couple MBs after optimization and resulted in a datafile of over 120GB. 
We've narrowed it down to a reoccurring UPDATE statement on a MEDIUMTEXT column, which is used in a database logging solution.
This is happening for persistent connection as well as with "connect/update/disconnect" scenario.
After couple of tests this is reproducible (see How to repeat) where the growth in the order of GBs starts at about 18000 updates performed, reaching 10GB datafile in about 1500 updates, at that time the actual string length in the column is below 2MB.

How to repeat:
1) Have 'innodb_file_per_table' enabled

2)
Create table (row format compact or dynamic - does not matter)

CREATE TABLE `tabtest` (
  `server_name` varchar(254) COLLATE latin1_general_ci NOT NULL,
  `script_name` varchar(254) COLLATE latin1_general_ci NOT NULL,
  `last_output` mediumtext COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (`server_name`,`script_name`)
);

3)
inert one row of data

4)
in a loop execute update similar to this one:
UPDATE test.tabtest SET last_output=CONCAT(last_output, 'Lorem ipsum dolor sit amet, enim electram eum te. Elitr animal');

5)
watch the table datafile on filesystem grow or watch the DATA_FREE column value in information_schema.TABLES for that test table

Suggested fix:
Reuse of allocated space in the table datafiles

Thank you for your bug report. However, this is not a bug.

Our manual describes so many other InnoDB options that are at disposal to optimize your setup. Other file formats, row formats and many other options that influence disk usage and that have an effect on the size of the tablespace size for the given table.

Additionally, our manual describes how to compress tablespaces that take too much disk space.