Bug #69588 MyISAM to InnoDB compressed slower than MyISAM to InnoDB, Then InnoDB to Compres

• Submitted: 26 Jun 2013 15:45
• Modified: 21 Jul 2014 20:22
• Reporter: Joffrey MICHAIE
• Status: Verified
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 5.5.31, 5.6.21
• OS: Linux (RHEL 6.4)
• CPU Architecture: Any
• Tags: compressed, innodb, myisam

[26 Jun 2013 15:45] Joffrey MICHAIE

Description:
When converting (unsorted) MyISAM table to InnoDB compressed, time is longer, and datafile is not optimized.
It is faster to first convert the MyISAM table to InnoDB, then ALTER the table again to enable compression

How to repeat:
Start MySQL with innodb_file_per_table =1

CREATE DATABASE IF NOT EXISTS test;
USE test;
CREATE TABLE bigt (a serial, b char(32)) ENGINE=MyISAM;
INSERT INTO bigt SELECT NULL,MD5(rand());
INSERT INTO bigt SELECT NULL,MD5(rand());
INSERT INTO bigt SELECT NULL,MD5(rand());
INSERT INTO bigt SELECT NULL,MD5(rand());
INSERT INTO bigt SELECT NULL,b FROM bigt;
INSERT INTO bigt SELECT NULL,b FROM bigt;
(...)
Repeat until table is 2M rows.

CREATE TABLE bigt_rand LIKE bigt;
CREATE TABLE bigt_rand2 LIKE bigt;

INSERT INTO bigt_rand SELECT * FROM bigt ORDER BY RAND();
INSERT INTO bigt_rand2 SELECT * FROM bigt_rand;

SHOW TABLE STATUS;

Both bigt_rand and bigt_rand2 have same size and ROWS

 ALTER TABLE bigt_rand ENGINE=InnoDB;
Query OK, 2097152 rows affected (2 min 12.95 sec)
-rw-rw---- 1 mysql mysql 216M 25 juin  18:58 /var/lib/mysql/test/bigt_rand.ibd
ALTER TABLE bigt_rand ROW_FORMAT=COMPRESSED;
Query OK, 2097152 rows affected (41.30 sec)
-rw-rw---- 1 mysql mysql  72M 25 juin  19:00 /var/lib/mysql/test/bigt_rand.ibd

ALTER TABLE bigt_rand2 ENGINE=InnoDB ROW_FORMAT=COMPRESSED;
Query OK, 2097152 rows affected (6 min 29.83 sec)
-rw-rw---- 1 mysql mysql 108M 25 juin  19:02 /var/lib/mysql/test/bigt_rand2.ibd
ALTER TABLE bigt_rand2 ENGINE=InnoDB ROW_FORMAT=COMPRESSED;
Query OK, 2097152 rows affected (24.65 sec)
-rw-rw---- 1 mysql mysql  72M 25 juin  19:03 /var/lib/mysql/test/bigt_rand2.ibd

Process to directly convert data from MyISAM to InnoDB compressed is more than 2 times slower than using 2 steps to perform the same operation.

Optimizing the way tables are converted from MyISAM to InnoDB compressed is important because we may run out of disk space if we proceed the two steps

Suggested fix:
Optimize way of converting directly MyISAM to InnoDB Compressed.
Propose an option to use the '2 steps conversion', first InnoDB, then InnoDB compressed.

[21 Jul 2014 20:22] Sveta Smirnova

Thank you for the report.

Verified as described.

mysql> SHOW TABLE STATUS;
+------------+--------+---------+------------+---------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| Name       | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length   | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation         | Checksum | Create_options | Comment |
+------------+--------+---------+------------+---------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| bigt_rand  | MyISAM |      10 | Fixed      |  597152 |             41 |    24483232 | 11540474045136895 |     10052608 |         0 |        2097153 | 2014-07-21 22:56:25 | 2014-07-21 22:59:17 | NULL       | latin1_swedish_ci |     NULL |                |         |
| bigt_rand2 | MyISAM |      10 | Fixed      |  597152 |             41 |    85983232 | 11540474045136895 |     35244032 |  61500000 |        2097153 | 2014-07-21 21:20:08 | 2014-07-21 22:57:30 | NULL       | latin1_swedish_ci |     NULL |                |                    |         |
+------------+--------+---------+------------+---------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE bigt_rand ENGINE=InnoDB;
Query OK, 597152 rows affected (4 min 6.30 sec)
Records: 597152  Duplicates: 0  Warnings: 0

mysql> \! ls -lh /home/sveta/src/mysql-5.6/mysql-test/var/mysqld.1/data/test/bigt_rand*
-rw-rw---- 1 sveta sveta 8.4K Jul 21 21:20 /home/sveta/src/mysql-5.6/mysql-test/var/mysqld.1/data/test/bigt_rand2.frm
-rw-rw---- 1 sveta sveta  82M Jul 21 22:57 /home/sveta/src/mysql-5.6/mysql-test/var/mysqld.1/data/test/bigt_rand2.MYD
-rw-rw---- 1 sveta sveta  34M Jul 21 22:57 /home/sveta/src/mysql-5.6/mysql-test/var/mysqld.1/data/test/bigt_rand2.MYI
-rw-rw---- 1 sveta sveta 8.4K Jul 21 23:01 /home/sveta/src/mysql-5.6/mysql-test/var/mysqld.1/data/test/bigt_rand.frm
-rw-rw---- 1 sveta sveta  68M Jul 21 23:05 /home/sveta/src/mysql-5.6/mysql-test/var/mysqld.1/data/test/bigt_rand.ibd
mysql> ALTER TABLE bigt_rand ROW_FORMAT=COMPRESSED;
Query OK, 0 rows affected (4 min 1.65 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> \! ls -lh /home/sveta/src/mysql-5.6/mysql-test/var/mysqld.1/data/test/bigt_rand*
-rw-rw---- 1 sveta sveta 8.4K Jul 21 21:20 /home/sveta/src/mysql-5.6/mysql-test/var/mysqld.1/data/test/bigt_rand2.frm
-rw-rw---- 1 sveta sveta  82M Jul 21 22:57 /home/sveta/src/mysql-5.6/mysql-test/var/mysqld.1/data/test/bigt_rand2.MYD
-rw-rw---- 1 sveta sveta  34M Jul 21 22:57 /home/sveta/src/mysql-5.6/mysql-test/var/mysqld.1/data/test/bigt_rand2.MYI
-rw-rw---- 1 sveta sveta 8.4K Jul 21 23:07 /home/sveta/src/mysql-5.6/mysql-test/var/mysqld.1/data/test/bigt_rand.frm
-rw-rw---- 1 sveta sveta  18M Jul 21 23:11 /home/sveta/src/mysql-5.6/mysql-test/var/mysqld.1/data/test/bigt_rand.ibd
mysql> ALTER TABLE bigt_rand2 ENGINE=InnoDB ROW_FORMAT=COMPRESSED;
Query OK, 597152 rows affected (5 min 34.86 sec)
Records: 597152  Duplicates: 0  Warnings: 0

mysql> \! ls -lh /home/sveta/src/mysql-5.6/mysql-test/var/mysqld.1/data/test/bigt_rand*
-rw-rw---- 1 sveta sveta 8.4K Jul 21 23:11 /home/sveta/src/mysql-5.6/mysql-test/var/mysqld.1/data/test/bigt_rand2.frm
-rw-rw---- 1 sveta sveta  36M Jul 21 23:17 /home/sveta/src/mysql-5.6/mysql-test/var/mysqld.1/data/test/bigt_rand2.ibd
-rw-rw---- 1 sveta sveta 8.4K Jul 21 23:07 /home/sveta/src/mysql-5.6/mysql-test/var/mysqld.1/data/test/bigt_rand.frm
-rw-rw---- 1 sveta sveta  18M Jul 21 23:11 /home/sveta/src/mysql-5.6/mysql-test/var/mysqld.1/data/test/bigt_rand.ibd
mysql> ALTER TABLE bigt_rand2 ENGINE=InnoDB ROW_FORMAT=COMPRESSED;
Query OK, 0 rows affected (4 min 8.80 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> \! ls -lh /home/sveta/src/mysql-5.6/mysql-test/var/mysqld.1/data/test/bigt_rand*
-rw-rw---- 1 sveta sveta 8.4K Jul 21 23:18 /home/sveta/src/mysql-5.6/mysql-test/var/mysqld.1/data/test/bigt_rand2.frm
-rw-rw---- 1 sveta sveta  18M Jul 21 23:22 /home/sveta/src/mysql-5.6/mysql-test/var/mysqld.1/data/test/bigt_rand2.ibd
-rw-rw---- 1 sveta sveta 8.4K Jul 21 23:07 /home/sveta/src/mysql-5.6/mysql-test/var/mysqld.1/data/test/bigt_rand.frm
-rw-rw---- 1 sveta sveta  18M Jul 21 23:11 /home/sveta/src/mysql-5.6/mysql-test/var/mysqld.1/data/test/bigt_rand.ibd