Bug #78623 Small tablespaces with BLOBs take up to 80 times more space in 5.7 than in 5.6
Submitted: 29 Sep 2015 10:58 Modified: 19 Oct 2015 12:56
Reporter: Laurynas Biveinis (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.7.8 OS:Any
Assigned to: CPU Architecture:Any
Tags: BLOB, compact, compressed, innodb, ROW_FORMAT, Tablespace

[29 Sep 2015 10:58] Laurynas Biveinis
Description:
Case 1:

CREATE TABLE t2 (a INT PRIMARY KEY, b BLOB) ENGINE=InnoDB;
insert 4 rows: INSERT INTO t2 VALUES($i, REPEAT('a', 30000))
IBD size on disk with 5.6: 196608 bytes, with 5.7: 3145728 bytes. 16 times larger.

Case 2:

CREATE TABLE t3 (a INT PRIMARY KEY, b BLOB)
       ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
insert 8 to 24 rows: INSERT INTO t3 VALUES($i, REPEAT('a', 30000))
IBD size on disk with 5.6: 65536 bytes, with 5.7: 5242880 bytes. 80 times larger.

How to repeat:
The above shows two points. A full MTR script to show a range of IBD size differences, from empty to the point the difference vanishes is below.

1. Put the following to mysql-test/include/percona_show_ibd_size.inc:

perl;
 my $table = $ENV{'IBD_TO_CHECK'};
 my $tablespace = $ENV{'MYSQLD_DATADIR'}."test/".$table.".ibd";
 my $size = -s "$tablespace";
 print "The size of $table tablespace file in bytes: $size\n";
 exit(0)
EOF

2. Put the following to an arbitrarily-named mysql-test/t/foo.test:

--source include/have_innodb.inc

--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings

let MYSQLD_DATADIR=`SELECT @@datadir`;

SET GLOBAL innodb_file_per_table=ON;

#
# Table 1: small rows
#
CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;

let IBD_TO_CHECK=t1;
echo Empty table:;
--source include/percona_show_ibd_size.inc

let $i = 0;

--disable_query_log
BEGIN;
while ($i < 25000) {
      eval INSERT INTO t1 VALUES($i);
      inc $i;
      let $print= `SELECT $i MOD 5000`;
      if ($print == 0) {
         COMMIT;
         echo Rows inserted: $i;
         source include/percona_show_ibd_size.inc;
         BEGIN;
      }
}
COMMIT;
--enable_query_log

DROP TABLE t1;

#
# Table 2: BLOB
#
CREATE TABLE t2 (a INT PRIMARY KEY, b BLOB) ENGINE=InnoDB;

let IBD_TO_CHECK=t2;
echo Empty table:;
--source include/percona_show_ibd_size.inc

let $i = 0;

--disable_query_log
BEGIN;
while ($i < 20) {
      eval INSERT INTO t2 VALUES($i, REPEAT('a', 30000));
      inc $i;
      let $print= `SELECT $i MOD 4`;
      if ($print == 0) {
         COMMIT;
         echo Rows inserted: $i;
         source include/percona_show_ibd_size.inc;
         BEGIN;
      }
}
COMMIT;
--enable_query_log

DROP TABLE t2;

#
# Table 3: compressed BLOB
#
SET GLOBAL innodb_file_format='barracuda';
CREATE TABLE t3 (a INT PRIMARY KEY, b BLOB)
       ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;

let IBD_TO_CHECK=t3;
echo Empty table:;
--source include/percona_show_ibd_size.inc

let $i = 0;

--disable_query_log
BEGIN;
while ($i < 40) {
      eval INSERT INTO t3 VALUES($i, REPEAT('a', 30000));
      inc $i;
      let $print= `SELECT $i MOD 8`;
      if ($print == 0) {
         COMMIT;
         echo Rows inserted: $i;
         source include/percona_show_ibd_size.inc;
         BEGIN;
      }
}
COMMIT;
--enable_query_log

DROP TABLE t3;

SET GLOBAL innodb_file_per_table=default;
SET GLOBAL innodb_file_format=default;

3. Run the above under 5.6 and record its output.

4. Run the above under 5.7 and notice the result diff against 5.6:

@@ -18,31 +18,35 @@
 Empty table:
 The size of t2 tablespace file in bytes: 98304
 Rows inserted: 4
-The size of t2 tablespace file in bytes: 196608
+The size of t2 tablespace file in bytes: 3145728
 Rows inserted: 8
-The size of t2 tablespace file in bytes: 327680
+The size of t2 tablespace file in bytes: 3145728
 Rows inserted: 12
-The size of t2 tablespace file in bytes: 458752
+The size of t2 tablespace file in bytes: 3145728
 Rows inserted: 16
-The size of t2 tablespace file in bytes: 589824
+The size of t2 tablespace file in bytes: 3145728
 Rows inserted: 20
 The size of t2 tablespace file in bytes: 9437184
 DROP TABLE t2;
 SET GLOBAL innodb_file_format='barracuda';
+Warnings:
+Warning	131	Using innodb_file_format is deprecated and the parameter may be removed in future releases. See http://dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html
 CREATE TABLE t3 (a INT PRIMARY KEY, b BLOB)
 ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
 Empty table:
 The size of t3 tablespace file in bytes: 65536
 Rows inserted: 8
-The size of t3 tablespace file in bytes: 65536
+The size of t3 tablespace file in bytes: 5242880
 Rows inserted: 16
-The size of t3 tablespace file in bytes: 65536
+The size of t3 tablespace file in bytes: 5242880
 Rows inserted: 24
-The size of t3 tablespace file in bytes: 65536
+The size of t3 tablespace file in bytes: 5242880
 Rows inserted: 32
-The size of t3 tablespace file in bytes: 2097152
+The size of t3 tablespace file in bytes: 5242880
 Rows inserted: 40
-The size of t3 tablespace file in bytes: 2097152
+The size of t3 tablespace file in bytes: 5242880
 DROP TABLE t3;

Suggested fix:
Make 5.7 IBDs smaller
[29 Sep 2015 10:59] Laurynas Biveinis
This could be very serious for users with large number of compressed tablespaces with BLOBs
[29 Sep 2015 12:04] Morgan Tocker
The default row format for innodb tables changes from COMPACT to DYNAMIC in 5.7.9 (with the introduction of variable innodb_default_row_format as well).

So I modified your test case #1 slightly:

mysql [localhost] {msandbox} (test) > CREATE TABLE t2_compact (a INT PRIMARY KEY, b BLOB) ENGINE=InnoDB ROW_FORMAT=compact;
Query OK, 0 rows affected (0.02 sec)

mysql [localhost] {msandbox} (test) > CREATE TABLE t2_dynamic (a INT PRIMARY KEY, b BLOB) ENGINE=InnoDB ROW_FORMAT=dynamic; 
Query OK, 0 rows affected (0.01 sec)

File sizes are the same with both row formats:

-rw-r-----  1 morgo  staff     8578 29 Sep 07:59 t2_compact.frm
-rw-r-----  1 morgo  staff  3145728 29 Sep 08:00 t2_compact.ibd
-rw-r-----  1 morgo  staff     8578 29 Sep 07:59 t2_dynamic.frm
-rw-r-----  1 morgo  staff  3145728 29 Sep 08:00 t2_dynamic.ibd
[6 Oct 2015 9:07] Umesh Shastry
Hello Laurynas,

Thank you for the report and test case.

Thanks,
Umesh
[6 Oct 2015 9:07] Umesh Shastry
[umshastr@hod03]/export/umesh/server/binaries/mysql-5.7.8-rc/mysql-test: ./mtr foo
Logging: ./mtr  foo
MySQL Version 5.7.8
Checking supported features...
 - SSL connections supported
Collecting tests...
Checking leftover processes...
Removing old var directory...
Creating var directory '/export/umesh/server/binaries/mysql-5.7.8-rc/mysql-test/var'...
Installing system database...

==============================================================================

TEST                                      RESULT   TIME (ms) or COMMENT
--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
DROP TABLE IF EXISTS t1;
SET GLOBAL innodb_file_per_table=ON;
CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
Empty table:
The size of t1 tablespace file in bytes: 98304
Rows inserted: 5000
The size of t1 tablespace file in bytes: 212992
Rows inserted: 10000
The size of t1 tablespace file in bytes: 344064
Rows inserted: 15000
The size of t1 tablespace file in bytes: 458752
Rows inserted: 20000
The size of t1 tablespace file in bytes: 589824
Rows inserted: 25000
The size of t1 tablespace file in bytes: 9437184
DROP TABLE t1;
CREATE TABLE t2 (a INT PRIMARY KEY, b BLOB) ENGINE=InnoDB;
Empty table:
The size of t2 tablespace file in bytes: 98304
Rows inserted: 4
The size of t2 tablespace file in bytes: 3145728
Rows inserted: 8
The size of t2 tablespace file in bytes: 3145728
Rows inserted: 12
The size of t2 tablespace file in bytes: 3145728
Rows inserted: 16
The size of t2 tablespace file in bytes: 3145728
Rows inserted: 20
The size of t2 tablespace file in bytes: 9437184
DROP TABLE t2;
SET GLOBAL innodb_file_format='barracuda';
Warnings:
Warning 131     Using innodb_file_format is deprecated and the parameter may be removed in future releases. See http://dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html
CREATE TABLE t3 (a INT PRIMARY KEY, b BLOB)
ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
Empty table:
The size of t3 tablespace file in bytes: 65536
Rows inserted: 8
The size of t3 tablespace file in bytes: 5242880
Rows inserted: 16
The size of t3 tablespace file in bytes: 5242880
Rows inserted: 24
The size of t3 tablespace file in bytes: 5242880
Rows inserted: 32
The size of t3 tablespace file in bytes: 5242880
Rows inserted: 40
The size of t3 tablespace file in bytes: 5242880
DROP TABLE t3;
SET GLOBAL innodb_file_per_table=default;
SET GLOBAL innodb_file_format=default;
Warnings:
Warning 131     Using innodb_file_format is deprecated and the parameter may be removed in future releases. See http://dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html
main.foo                                 [ pass ]   3101
--------------------------------------------------------------------------
The servers were restarted 0 times
Spent 3.101 of 8 seconds executing testcases

Completed: All 1 tests were successful.

// 5.6.28

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.28/mysql-test: ./mtr foo
Logging: ./mtr  foo
2015-10-06 11:05:55 0 [Note] /export/umesh/server/binaries/mysql-advanced-5.6.28/bin/mysqld (mysqld 5.6.28-enterprise-commercial-advanced) starting as process 28861 ...
2015-10-06 11:05:55 28861 [Note] Plugin 'FEDERATED' is disabled.
2015-10-06 11:05:55 28861 [Note] Binlog end
2015-10-06 11:05:55 28861 [Note] Shutting down plugin 'CSV'
2015-10-06 11:05:55 28861 [Note] Shutting down plugin 'MyISAM'
MySQL Version 5.6.28
Checking supported features...
 - SSL connections supported
Collecting tests...
Removing old var directory...
Creating var directory '/export/umesh/server/binaries/mysql-advanced-5.6.28/mysql-test/var'...
Installing system database...

==============================================================================

TEST                                      RESULT   TIME (ms) or COMMENT
--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
DROP TABLE IF EXISTS t1;
SET GLOBAL innodb_file_per_table=ON;
CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
Empty table:
The size of t1 tablespace file in bytes: 98304
Rows inserted: 5000
The size of t1 tablespace file in bytes: 212992
Rows inserted: 10000
The size of t1 tablespace file in bytes: 344064
Rows inserted: 15000
The size of t1 tablespace file in bytes: 458752
Rows inserted: 20000
The size of t1 tablespace file in bytes: 573440
Rows inserted: 25000
The size of t1 tablespace file in bytes: 9437184
DROP TABLE t1;
CREATE TABLE t2 (a INT PRIMARY KEY, b BLOB) ENGINE=InnoDB;
Empty table:
The size of t2 tablespace file in bytes: 98304
Rows inserted: 4
The size of t2 tablespace file in bytes: 196608
Rows inserted: 8
The size of t2 tablespace file in bytes: 327680
Rows inserted: 12
The size of t2 tablespace file in bytes: 458752
Rows inserted: 16
The size of t2 tablespace file in bytes: 589824
Rows inserted: 20
The size of t2 tablespace file in bytes: 9437184
DROP TABLE t2;
SET GLOBAL innodb_file_format='barracuda';
CREATE TABLE t3 (a INT PRIMARY KEY, b BLOB)
ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
Empty table:
The size of t3 tablespace file in bytes: 65536
Rows inserted: 8
The size of t3 tablespace file in bytes: 65536
Rows inserted: 16
The size of t3 tablespace file in bytes: 65536
Rows inserted: 24
The size of t3 tablespace file in bytes: 65536
Rows inserted: 32
The size of t3 tablespace file in bytes: 5242880
Rows inserted: 40
The size of t3 tablespace file in bytes: 5242880
DROP TABLE t3;
SET GLOBAL innodb_file_per_table=default;
SET GLOBAL innodb_file_format=default;
main.foo                                 [ pass ]   2574
--------------------------------------------------------------------------
The servers were restarted 0 times
Spent 2.574 of 7 seconds executing testcases

Completed: All 1 tests were successful.

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.28/mysql-test:
[6 Oct 2015 20:54] Sunny Bains
Satya has figured out the problem and we are working on the fix.
[19 Oct 2015 12:56] Daniel Price
Posted by developer:
 
Fixed as of the upcoming 5.7.10, 5.8.0 release, and here's the changelog entry:

Compared to previous releases, small tablespaces containing tables with
BLOB values had larger data files due to regression introduced in MySQL
5.7.5. 

Thank you for the bug report.