title: mysql repartitioned table much larger
body: <p>I have a very large table on a mysql 5.6.10 instance (roughly 480 million rows).
  The storage engine is InnoDB. (Table and DB Default).
  The table was partitioned by hash of merchantId (bigint: a kind of client identifier) which helped when queries related to a single merchant.  Due to significant performance degradation when queries spanned multiple merchants, I decided to repartition the table by Range on ACTION_DATE (the DATE that an activity occurred).  Thinking I was being clever, I decided to add a few (5) new fields for future use (unused_varchar1 varchar(200), etc.), since the table is so large, adding new fields essentially requires a rebuild anyway, so why not...</p>
  
  <p>I created the new table structure as _new, dumped the existing file to a secondary server using mysql dump.  I then used an awk script to finesse the name and a few other details to fit the new table  (change tableName to tableName_new), and started the load.</p>
  
  <p>The existing table was approximately 430 GB.  The text file similarly was about 403 GB.  I was surprised therefore that the new table ended up taking about 840 GB!!  (Based on the linux fize size of the .ibd files)</p>
  
  <p>So, I have 2 basic questions, which really amount to why and what now...</p>
  
  <p>I imagine that the new table is larger because the dump file was in the order of the previous partition (merchantId) while the load was inserting into the new partitioning (Activity date) creating a semi-random insertion order.  The randomness led mysql to leave plenty of space (roughly 50%) in the pages for future insertions. (I'm a little fuzzy on the terminology here, having spent much more time in my career with Sql Server DBs than MySql Dbs...)  I'm not able to find any internal statistics in mysql for space free per page.  The INFORMATION_SCHEMA.TABLES DATA_FREE stat is an unconvincing 68MB.</p>
  
  <p>If it helps these are the relevant stats from I_S.TABLES:</p>
  
  <p>TABLE_TYPE:   BASE TABLE<br>
  Engine:       InnoDB<br>
  VERSION:      10<br>
  ROW_FORMAT:   Compact<br>
  TABLE_ROWS:     488,094,271<br>
  AVG_ROW_LENGTH:       1,564
  DATA_LENGTH:    763,509,358,592 (711 GB)
  INDEX_LENGTH:   100,065,574,912 (93.19 GB) 
  DATA_FREE:        68,157,440 (0.06 GB)</p>
  
  <p>I realize that that doesn't add up to 840 GB, but as I said, that was the size of the .ibd files which seems to be slightly different than the I_S.TABLES stats.  Either way, it is significantly more than the text dump file.
  I digress...  </p>
  
  <p>My question is whether my theory about whether the repartioning explains the roughly doubled size.  Or is there another explanation?  I think the extra columns (2 Bigint, 2 Varchar(200), 1 Date) are not the culprit since they are all null.  My napkin calculation was that the additional columns would add &lt; 9 GB.  Likewise, one additional index on UID should be a relatively small addition.</p>
  
  <p>The follow up question is what can I do now if I want to try to compact the table.  (Server now only has about 385 GB free...)
  If I repeated the procedure, dump to file, reload, this time in the current partition order, would I end up with a table more like the size of my original table ~430 GB?</p>
  
  <p>Following are relevant parts of DDL.</p>
  
  <p>OLD TABLE:</p>
  
  <pre><code>CREATE TABLE table_name (
    `AUTO_SEQ` bigint(20) NOT NULL,
    `MERCHANT_ID` bigint(20) NOT NULL,
    `AFFILIATE_ID` bigint(20) DEFAULT NULL,
    `PROGRAM_ID` bigint(20) NOT NULL,
    `ACTION_DATE` date DEFAULT NULL,
    `UID` varchar(128) DEFAULT NULL,
  ... additional columns ... 
    PRIMARY KEY (`AUTO_SEQ`,`MERCHANT_ID`,`PROGRAM_ID`),
    KEY `oc_rpt_mpad_idx` (`MERCHANT_ID`,`PROGRAM_ID`,`ACTION_DATE`,`AFFILIATE_ID`),
    KEY `oc_rpt_mapd` (`MERCHANT_ID`,`ACTION_DATE`),
    KEY `oc_rpt_apda_idx` (`AFFILIATE_ID`,`PROGRAM_ID`,`ACTION_DATE`,`MERCHANT_ID`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1
  /*!50100 PARTITION BY HASH (merchant_id)
  PARTITIONS 16 */ 
  </code></pre>
  
  <p>NEW TABLE:</p>
  
  <pre><code>CREATE TABLE `tableName_new` (
    `AUTO_SEQ` bigint(20) NOT NULL,
    `MERCHANT_ID` bigint(20) NOT NULL,
    `AFFILIATE_ID` bigint(20) DEFAULT NULL,
    `PROGRAM_ID` bigint(20) NOT NULL,
    `ACTION_DATE` date NOT NULL DEFAULT '0000-00-00',
    `UID` varchar(128) DEFAULT NULL,
  ... additional columns...
  # NEW COLUMNS (ALL NULL)
    `UNUSED_BIGINT1` bigint(20) DEFAULT NULL,
    `UNUSED_BIGINT2` bigint(20) DEFAULT NULL,
    `UNUSED_VARCHAR1` varchar(200) DEFAULT NULL,
    `UNUSED_VARCHAR2` varchar(200) DEFAULT NULL,
    `UNUSED_DATE1` date DEFAULT NULL,
    PRIMARY KEY (`AUTO_SEQ`,`ACTION_DATE`),
    KEY `oc_rpt_mpad_idx` (`MERCHANT_ID`,`PROGRAM_ID`,`ACTION_DATE`,`AFFILIATE_ID`),
    KEY `oc_rpt_mapd` (`ACTION_DATE`),
    KEY `oc_rpt_apda_idx` (`AFFILIATE_ID`,`PROGRAM_ID`,`ACTION_DATE`,`MERCHANT_ID`),
    KEY `oc_uid` (`UID`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1
  /*!50500 PARTITION BY RANGE  COLUMNS(ACTION_DATE)
  (PARTITION p01 VALUES LESS THAN ('2012-01-01') ENGINE = InnoDB,
   PARTITION p02 VALUES LESS THAN ('2012-04-01') ENGINE = InnoDB,
   PARTITION p03 VALUES LESS THAN ('2012-07-01') ENGINE = InnoDB,
   PARTITION p04 VALUES LESS THAN ('2012-10-01') ENGINE = InnoDB,
   PARTITION p05 VALUES LESS THAN ('2013-01-01') ENGINE = InnoDB,
   PARTITION p06 VALUES LESS THAN ('2013-04-01') ENGINE = InnoDB,
   PARTITION p07 VALUES LESS THAN ('2013-07-01') ENGINE = InnoDB,
   PARTITION p08 VALUES LESS THAN ('2013-10-01') ENGINE = InnoDB,
   PARTITION p09 VALUES LESS THAN ('2014-01-01') ENGINE = InnoDB,
   PARTITION p10 VALUES LESS THAN ('2014-04-01') ENGINE = InnoDB,
   PARTITION p11 VALUES LESS THAN ('2014-07-01') ENGINE = InnoDB,
   PARTITION p12 VALUES LESS THAN ('2014-10-01') ENGINE = InnoDB,
   PARTITION p13 VALUES LESS THAN ('2015-01-01') ENGINE = InnoDB,
   PARTITION p14 VALUES LESS THAN ('2015-04-01') ENGINE = InnoDB,
   PARTITION p15 VALUES LESS THAN ('2015-07-01') ENGINE = InnoDB,
   PARTITION p16 VALUES LESS THAN ('2015-10-01') ENGINE = InnoDB,
   PARTITION p17 VALUES LESS THAN ('2016-01-01') ENGINE = InnoDB,
   PARTITION p18 VALUES LESS THAN ('2016-04-01') ENGINE = InnoDB,
   PARTITION p19 VALUES LESS THAN ('2016-07-01') ENGINE = InnoDB,
   PARTITION p20 VALUES LESS THAN ('2016-10-01') ENGINE = InnoDB,
   PARTITION p21 VALUES LESS THAN ('2017-01-01') ENGINE = InnoDB,
   PARTITION p22 VALUES LESS THAN ('2017-04-01') ENGINE = InnoDB,
   PARTITION p23 VALUES LESS THAN ('2017-07-01') ENGINE = InnoDB,
   PARTITION p24 VALUES LESS THAN ('2017-10-01') ENGINE = InnoDB,
   PARTITION p25 VALUES LESS THAN ('2018-01-01') ENGINE = InnoDB,
   PARTITION p26 VALUES LESS THAN ('2018-04-01') ENGINE = InnoDB,
   PARTITION p27 VALUES LESS THAN ('2018-07-01') ENGINE = InnoDB,
   PARTITION p28 VALUES LESS THAN ('2018-10-01') ENGINE = InnoDB,
   PARTITION p29 VALUES LESS THAN ('2019-01-01') ENGINE = InnoDB,
   PARTITION p30 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */
  </code></pre>