Not the answer you need?
Register and ask your own question!

ibd file very big when convert to innodb engine

chenzhe07chenzhe07 EntrantCurrent User Role Beginner
I have a special question, the innodb table become very big when I convert table from MyISAM to InnoDB, the table like this:
original table is MyISAM engine, data file and index file size is:
-rw-r
1 mysql mysql 8.7K Aug 17 13:34 udb_home_follow.frm
-rw-r
1 mysql mysql 1.9G Aug 17 13:35 udb_home_follow.MYD
-rw-r
1 mysql mysql 1.5G Aug 17 13:35 udb_home_follow.MYI
CREATE TABLE `udb_home_follow` (
  `uid` int(11) unsigned NOT NULL DEFAULT '0',
  `username` varchar(255) NOT NULL DEFAULT '',
  `followuid` int(11) unsigned NOT NULL DEFAULT '0',
  `fusername` varchar(255) NOT NULL DEFAULT '',
  `bkname` varchar(255) NOT NULL DEFAULT '',
  `status` tinyint(1) NOT NULL DEFAULT '0',
  `mutual` tinyint(1) NOT NULL DEFAULT '0',
  `dateline` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`uid`,`followuid`),
  KEY `idx_fuid` (`followuid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
there is about 60 million records in udb_home_follow table, most of the records seems like this:
+----------+-----------------------+-----------+--------------------+--------+--------+--------+------------+
| uid      | username              | followuid | fusername          | bkname | status | mutual | dateline   |
+----------+-----------------------+-----------+--------------------+--------+--------+--------+------------+
|  6235195 | curapikt87            |  14644915 | coolmayi           |        |      0 |      0 | 1347433303 |
| 17425562 | 1442453108xia         |  14919966 | zczczczc0          |        |      0 |      0 | 1347433673 |
+----------+-----------------------+-----------+--------------------+--------+--------+--------+------------+

           Name: udb_home_follow
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 62833131
 Avg_row_length: 31
    Data_length: 1948544532
Max_data_length: 281474976710655
   Index_length: 1563675648
      Data_free: 0

after I execute any of the following sql query, new innodb become very huge :
alter table udb_home_follow  engine = innodb;
insert into udb_innodb select * from udb_home_follow;
innodb table, it seems like table Number of lines increased tenfold, and ibd file become to 267G:
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 641272410
 Avg_row_length: 73
    Data_length: 237746100224
Max_data_length: 0
   Index_length: 33729784832
      Data_free: 3306556672

-rw-rw---- 1 mysql mysql 8.7K Aug 18 17:45 udb_innodb.frm
-rw-rw---- 1 mysql mysql  267G Aug 18 20:16 udb_innodb.ibd


my system env :
DELL PowerEdge R610, RAM 32G system: CentOS release 6.4 (Final) Percona-Server-5.5.33-rel31.1-566.Linux.x86_64 or Percona-Server-5.5.23-rel25.3-240.Linux.x86_64

I have no idea for this error message, thanks a lot for everyone help.

Comments

  • GenryUGenryU Entrant Current User Role Beginner
    InnoDB stores all records inside a fixed-size unit which is commonly called a "page". Therefore InnoDB (XtraDB) very inefficiently uses disk space. Try to change row_format to dynamic or compressed (InnoDB compression can increase CPU load in many times).
    TokuDB with lzma/lzo compression will be 2-3 times smaller than MyISAM table, and faster InnoDB .
    Similar topic: https://www.percona.com/forums/quest...ers-in-7-times
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.