| 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: | |
| 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: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]
MySQL Verification Team
Hello Laurynas, Thank you for the report and test case. Thanks, Umesh
[6 Oct 2015 9:07]
MySQL Verification Team
[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.
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