title: Why is the size of a read-only table on slave bigger than the size of original table on master?
body: <p>I have a strange problem with the read-only table. I have two mysql servers, one is for master (for write operations), the other one is for slave (for read operations). The problem is that the table on slave becomes bigger than the same table on the master, even if I drop the table on slave and then re-sync it once again from master. They have the same record number.</p>
  
  <pre><code>root@localhost:xxxx 02:11:41&gt;select version();
  +------------+
  | version()  |
  +------------+
  | 5.7.12-log |
  +------------+
  1 row in set (0.00 sec)
  </code></pre>
  
  <p>on slave </p>
  
  <pre><code># ls -lh dw_out_of_stock_all_info*
  -rw-r----- 1 mysql mysql  19K Feb 11 10:59 dw_out_of_stock_all_info.frm
  -rw-r----- 1 mysql mysql 1.2G Feb 12 14:19 dw_out_of_stock_all_info.ibd
  
  root@localhost:tqdw 09:52:39&gt;show table status like 'dw_out_of_stock_all_info'\G
  *************************** 1. row ***************************
             Name: dw_out_of_stock_all_info
           Engine: InnoDB
          Version: 10
       Row_format: Dynamic
             Rows: 99170
   Avg_row_length: 9146
      Data_length: 907018240
  Max_data_length: 0
     Index_length: 96698368
        Data_free: 7340032
   Auto_increment: 2078841
      Create_time: 2019-02-11 10:59:51
      Update_time: NULL
       Check_time: NULL
        Collation: utf8_general_ci
         Checksum: NULL
   Create_options:
          Comment:
  1 row in set (0.04 sec)
  
  root@localhost:tqdw 09:55:04&gt;select count(1) from dw_out_of_stock_all_info;
  +----------+
  | count(1) |
  +----------+
  |    94494 |
  +----------+
  1 row in set (40.30 sec)
  </code></pre>
  
  <p>on master</p>
  
  <pre><code># ls -lh dw_out_of_stock_all_info*
  -rw-r----- 1 mysql mysql  19K Oct 18 19:32 dw_out_of_stock_all_info.frm
  -rw-r----- 1 mysql mysql 104M Feb 12 14:16 dw_out_of_stock_all_info.ibd
  
  root@localhost:tqdw 09:52:23&gt;show table status like 'dw_out_of_stock_all_info'\G
  *************************** 1. row ***************************
             Name: dw_out_of_stock_all_info
           Engine: InnoDB
          Version: 10
       Row_format: Dynamic
             Rows: 91235
   Avg_row_length: 897
      Data_length: 81920000
  Max_data_length: 0
     Index_length: 10518528
        Data_free: 49283072
   Auto_increment: 2078841
      Create_time: 2018-10-18 19:32:31
      Update_time: 2019-02-12 09:16:01
       Check_time: NULL
        Collation: utf8_general_ci
         Checksum: NULL
   Create_options:
          Comment:
  1 row in set (0.00 sec)
  
  root@localhost:tqdw 09:55:14&gt;select count(1) from dw_out_of_stock_all_info;
  +----------+
  | count(1) |
  +----------+
  |    94494 |
  +----------+
  1 row in set (1.32 sec)
  </code></pre>
  
  <hr>
  
  <p><strong>UPDATE</strong></p>
  
  <p>This table is deleted many times within a day on master, can it be the cause that it grows so big? If so, why does it not grow on master too?
  I should probably mention that I use row-format replication. </p>
  
  <p>on master</p>
  
  <pre><code>CREATE TABLE `dw_out_of_stock_all_info` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '',
    `is_deleted` char(1) NOT NULL DEFAULT 'N' COMMENT '',
    `gmt_create` datetime NOT NULL DEFAULT '1970-01-01 12:00:00' COMMENT '',
    `gmt_modified` datetime NOT NULL DEFAULT '1970-01-01 12:00:00' COMMENT '',
    `etl_creator` varchar(45) NOT NULL DEFAULT 'etlService' COMMENT '',
    `warehouse_id` int(11) unsigned NOT NULL COMMENT '',
    `parent_warehouse_id` int(11) unsigned NOT NULL COMMENT '',
    `warehouse_type` tinyint(1) unsigned DEFAULT NULL COMMENT '',
    `sub_warehouse_type` tinyint(1) unsigned DEFAULT '0' COMMENT '',
    `goods_id` int(11) unsigned NOT NULL COMMENT '',
    `sold_qty_third_month` int(11) DEFAULT NULL COMMENT '',
    `sold_qty_second_month` int(11) DEFAULT NULL COMMENT '',
    `sold_qty_first_month` int(11) DEFAULT NULL COMMENT '',
    `sold_qty_this_month` int(11) DEFAULT NULL COMMENT '',
    `stock_qty` int(11) DEFAULT NULL COMMENT '',
    `on_way_allocate` int(11) unsigned DEFAULT NULL COMMENT '',
    `last_allocate_from_warehouse` int(11) unsigned DEFAULT NULL COMMENT '',
    `on_way_purchase` int(11) unsigned DEFAULT NULL COMMENT '',
    `on_way_purchase_audit` int(11) unsigned DEFAULT NULL COMMENT '',
    `last_purchase_tax_price` decimal(10,2) DEFAULT NULL COMMENT '',
    `last_purchase_no_tax_price` decimal(10,2) DEFAULT NULL COMMENT '',
    `convert_rate` decimal(10,2) DEFAULT NULL COMMENT '',
    `include_freight` decimal(10,2) DEFAULT NULL COMMENT '',
    `include_freight_dbo` decimal(10,2) DEFAULT NULL COMMENT '',
    `min_no_tax_price_one_year` decimal(10,2) DEFAULT NULL COMMENT '',
    `max_no_tax_price_one_year` decimal(10,2) DEFAULT NULL COMMENT '',
    `is_on_sale` tinyint(1) unsigned DEFAULT '0' COMMENT '',
    `data_source` tinyint(1) unsigned DEFAULT '3' COMMENT '',
    `max_on_way_convert_price` decimal(10,2) DEFAULT NULL COMMENT '',
    `warehouse_name` varchar(50) NOT NULL DEFAULT '' COMMENT '',
    `parent_warehouse_name` varchar(50) NOT NULL DEFAULT '' COMMENT '',
    `warehouse_status` tinyint(1) DEFAULT '1' COMMENT '',
    `goods_sn` varchar(16) NOT NULL DEFAULT '0' COMMENT '',
    `goods_name` varchar(120) NOT NULL DEFAULT '' COMMENT '',
    `goods_format` varchar(255) NOT NULL DEFAULT '' COMMENT '',
    `min_measure_unit` varchar(16) NOT NULL DEFAULT '' COMMENT '',
    `goods_is_delete` tinyint(1) NOT NULL DEFAULT '0' COMMENT '',
    `package_unit` int(11) DEFAULT '0' COMMENT '',
    `package_measure_unit` varchar(16) NOT NULL DEFAULT '' COMMENT '',
    `packing_value` int(11) NOT NULL DEFAULT '1' COMMENT '',
    `goods_car_type` tinyint(4) DEFAULT '1' COMMENT '',
    `cat_id` int(11) DEFAULT '0' COMMENT '',
    `product_name` varchar(20) DEFAULT '' COMMENT '',
    `first_level_name` varchar(90) NOT NULL DEFAULT '' COMMENT '',
    `second_level_name` varchar(90) NOT NULL DEFAULT '' COMMENT '',
    `measure_unit` varchar(32) NOT NULL DEFAULT '' COMMENT '',
    `conversion_value` int(6) NOT NULL DEFAULT '1' COMMENT '',
    `need_qty` int(11) DEFAULT '0' COMMENT '',
    `is_yphq` tinyint(1) DEFAULT '0' COMMENT '',
    `goods_tag` varchar(64) DEFAULT '' COMMENT '',
    `max_pa_on_way_include_freight` decimal(10,2) DEFAULT NULL COMMENT '',
    `last_pa_in_ware_date` datetime DEFAULT NULL COMMENT '',
    `last_pa_in_ware_include_freight` decimal(10,2) DEFAULT NULL COMMENT '',
    `last_pa_in_ware_convert_rate` decimal(10,2) DEFAULT NULL COMMENT '',
    `avg_stock_include_freight` decimal(10,2) DEFAULT NULL COMMENT '',
    `main_pl` int(11) DEFAULT NULL COMMENT '',
    `total_pl` int(11) DEFAULT NULL COMMENT '',
    `total_stock` int(11) DEFAULT NULL COMMENT '',
    `on_way_total` int(11) DEFAULT '0' COMMENT '',
    `min_batch_include_freight` decimal(10,2) DEFAULT NULL COMMENT '',
    `max_batch_include_freight` decimal(10,2) DEFAULT NULL COMMENT '',
    `purchase_delivery_memo` varchar(255) DEFAULT '' COMMENT '',
    `min_tax_price_one_year` decimal(10,2) DEFAULT NULL COMMENT '',
    `max_tax_price_one_year` decimal(10,2) DEFAULT NULL COMMENT '',
    `max_on_way_tax_price` decimal(10,4) DEFAULT NULL COMMENT '',
    `stock_type` tinyint(1) DEFAULT '10' COMMENT '',
    `main_city_retail_price_one` decimal(10,2) DEFAULT '0.00' COMMENT '',
    `main_city_retail_price_two` decimal(10,2) DEFAULT '0.00' COMMENT '',
    `main_city_retail_price_three` decimal(10,2) DEFAULT '0.00' COMMENT '',
    `min_retail_price_one` decimal(10,2) DEFAULT '0.00' COMMENT '',
    `min_retail_price_two` decimal(10,2) DEFAULT '0.00' COMMENT '',
    `min_retail_price_three` decimal(10,2) DEFAULT '0.00' COMMENT '',
    `main_city_batch_price_one` decimal(10,2) DEFAULT '0.00' COMMENT '',
    `main_city_batch_price_two` decimal(10,2) DEFAULT '0.00' COMMENT '',
    `main_city_batch_price_three` decimal(10,2) DEFAULT '0.00' COMMENT '',
    `min_batch_price_one` decimal(10,2) DEFAULT '0.00' COMMENT '',
    `min_batch_price_two` decimal(10,2) DEFAULT '0.00' COMMENT '',
    `min_batch_price_three` decimal(10,2) DEFAULT '0.00' COMMENT '',
    PRIMARY KEY (`id`),
    KEY `idx_goods_ware` (`goods_id`,`warehouse_id`),
    KEY `idx_ware` (`warehouse_id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=2456828 DEFAULT CHARSET=utf8 COMMENT=''
  </code></pre>
  
  <p>on slave</p>
  
  <pre><code>CREATE TABLE `dw_out_of_stock_all_info` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '',
    `is_deleted` char(1) NOT NULL DEFAULT 'N' COMMENT '',
    `gmt_create` datetime NOT NULL DEFAULT '1970-01-01 12:00:00' COMMENT '',
    `gmt_modified` datetime NOT NULL DEFAULT '1970-01-01 12:00:00' COMMENT '',
    `etl_creator` varchar(45) NOT NULL DEFAULT 'etlService' COMMENT '',
    `warehouse_id` int(11) unsigned NOT NULL COMMENT '',
    `parent_warehouse_id` int(11) unsigned NOT NULL COMMENT '',
    `warehouse_type` tinyint(1) unsigned DEFAULT NULL COMMENT '',
    `sub_warehouse_type` tinyint(1) unsigned DEFAULT '0' COMMENT '',
    `goods_id` int(11) unsigned NOT NULL COMMENT '',
    `sold_qty_third_month` int(11) DEFAULT NULL COMMENT '',
    `sold_qty_second_month` int(11) DEFAULT NULL COMMENT '',
    `sold_qty_first_month` int(11) DEFAULT NULL COMMENT '',
    `sold_qty_this_month` int(11) DEFAULT NULL COMMENT '',
    `stock_qty` int(11) DEFAULT NULL COMMENT '',
    `on_way_allocate` int(11) unsigned DEFAULT NULL COMMENT '',
    `last_allocate_from_warehouse` int(11) unsigned DEFAULT NULL COMMENT '',
    `on_way_purchase` int(11) unsigned DEFAULT NULL COMMENT '',
    `on_way_purchase_audit` int(11) unsigned DEFAULT NULL COMMENT '',
    `last_purchase_tax_price` decimal(10,2) DEFAULT NULL COMMENT '',
    `last_purchase_no_tax_price` decimal(10,2) DEFAULT NULL COMMENT '',
    `convert_rate` decimal(10,2) DEFAULT NULL COMMENT '',
    `include_freight` decimal(10,2) DEFAULT NULL COMMENT '',
    `include_freight_dbo` decimal(10,2) DEFAULT NULL COMMENT '',
    `min_no_tax_price_one_year` decimal(10,2) DEFAULT NULL COMMENT '',
    `max_no_tax_price_one_year` decimal(10,2) DEFAULT NULL COMMENT '',
    `is_on_sale` tinyint(1) unsigned DEFAULT '0' COMMENT '',
    `data_source` tinyint(1) unsigned DEFAULT '3' COMMENT '',
    `max_on_way_convert_price` decimal(10,2) DEFAULT NULL COMMENT '',
    `warehouse_name` varchar(50) NOT NULL DEFAULT '' COMMENT '',
    `parent_warehouse_name` varchar(50) NOT NULL DEFAULT '' COMMENT '',
    `warehouse_status` tinyint(1) DEFAULT '1' COMMENT '',
    `goods_sn` varchar(16) NOT NULL DEFAULT '0' COMMENT '',
    `goods_name` varchar(120) NOT NULL DEFAULT '' COMMENT '',
    `goods_format` varchar(255) NOT NULL DEFAULT '' COMMENT '',
    `min_measure_unit` varchar(16) NOT NULL DEFAULT '' COMMENT '',
    `goods_is_delete` tinyint(1) NOT NULL DEFAULT '0' COMMENT '',
    `package_unit` int(11) DEFAULT '0' COMMENT '',
    `package_measure_unit` varchar(16) NOT NULL DEFAULT '' COMMENT '',
    `packing_value` int(11) NOT NULL DEFAULT '1' COMMENT '',
    `goods_car_type` tinyint(4) DEFAULT '1' COMMENT '',
    `cat_id` int(11) DEFAULT '0' COMMENT '',
    `product_name` varchar(20) DEFAULT '' COMMENT '',
    `first_level_name` varchar(90) NOT NULL DEFAULT '' COMMENT '',
    `second_level_name` varchar(90) NOT NULL DEFAULT '' COMMENT '',
    `measure_unit` varchar(32) NOT NULL DEFAULT '' COMMENT '',
    `conversion_value` int(6) NOT NULL DEFAULT '1' COMMENT '',
    `need_qty` int(11) DEFAULT '0' COMMENT '',
    `is_yphq` tinyint(1) DEFAULT '0' COMMENT '',
    `goods_tag` varchar(64) DEFAULT '' COMMENT '',
    `max_pa_on_way_include_freight` decimal(10,2) DEFAULT NULL COMMENT '',
    `last_pa_in_ware_date` datetime DEFAULT NULL COMMENT '',
    `last_pa_in_ware_include_freight` decimal(10,2) DEFAULT NULL COMMENT '',
    `last_pa_in_ware_convert_rate` decimal(10,2) DEFAULT NULL COMMENT '',
    `avg_stock_include_freight` decimal(10,2) DEFAULT NULL COMMENT '',
    `main_pl` int(11) DEFAULT NULL COMMENT '',
    `total_pl` int(11) DEFAULT NULL COMMENT '',
    `total_stock` int(11) DEFAULT NULL COMMENT '',
    `on_way_total` int(11) DEFAULT '0' COMMENT '',
    `min_batch_include_freight` decimal(10,2) DEFAULT NULL COMMENT '',
    `max_batch_include_freight` decimal(10,2) DEFAULT NULL COMMENT '',
    `purchase_delivery_memo` varchar(255) DEFAULT '' COMMENT '',
    `min_tax_price_one_year` decimal(10,2) DEFAULT NULL COMMENT '',
    `max_tax_price_one_year` decimal(10,2) DEFAULT NULL COMMENT '',
    `max_on_way_tax_price` decimal(10,4) DEFAULT NULL COMMENT '',
    `stock_type` tinyint(1) DEFAULT '10' COMMENT '',
    `main_city_retail_price_one` decimal(10,2) DEFAULT '0.00' COMMENT '',
    `main_city_retail_price_two` decimal(10,2) DEFAULT '0.00' COMMENT '',
    `main_city_retail_price_three` decimal(10,2) DEFAULT '0.00' COMMENT '',
    `min_retail_price_one` decimal(10,2) DEFAULT '0.00' COMMENT '',
    `min_retail_price_two` decimal(10,2) DEFAULT '0.00' COMMENT '',
    `min_retail_price_three` decimal(10,2) DEFAULT '0.00' COMMENT '',
    `main_city_batch_price_one` decimal(10,2) DEFAULT '0.00' COMMENT '',
    `main_city_batch_price_two` decimal(10,2) DEFAULT '0.00' COMMENT '',
    `main_city_batch_price_three` decimal(10,2) DEFAULT '0.00' COMMENT '',
    `min_batch_price_one` decimal(10,2) DEFAULT '0.00' COMMENT '',
    `min_batch_price_two` decimal(10,2) DEFAULT '0.00' COMMENT '',
    `min_batch_price_three` decimal(10,2) DEFAULT '0.00' COMMENT '',
    PRIMARY KEY (`id`),
    KEY `idx_goods_ware` (`goods_id`,`warehouse_id`),
    KEY `idx_ware` (`warehouse_id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=2456828 DEFAULT CHARSET=utf8 COMMENT=''
  </code></pre>