Why is the size of a read-only table on slave bigger than the size of original table on master?

Question

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.

root@localhost:xxxx 02:11:41>select version();
+------------+
| version()  |
+------------+
| 5.7.12-log |
+------------+
1 row in set (0.00 sec)

on slave

# 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>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>select count(1) from dw_out_of_stock_all_info;
+----------+
| count(1) |
+----------+
|    94494 |
+----------+
1 row in set (40.30 sec)

on master

# 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>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>select count(1) from dw_out_of_stock_all_info;
+----------+
| count(1) |
+----------+
|    94494 |
+----------+
1 row in set (1.32 sec)

UPDATE

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.

on master

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=''

on slave

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=''

I suggest you run `SHOW CREATE TABLE ` on both master and replica, and compare them. I suspect the replica has more indexes, based on the difference I see in the `Index_length`. — Bill Karwin, Feb 12 '19 at 02:08
What version of MySQL? [I reported a bug against 8.0.11](https://bugs.mysql.com/bug.php?id=86170) that it shows table statistics that are wildly inaccurate. The workaround is to set `information_schema_stats_expiry=0`. — Bill Karwin, Feb 12 '19 at 02:14
I already updated the info, the ddl on both is same. no variable call "information_schema_stats_expiry" on mysql 5.7.12 — user1208081, Feb 12 '19 at 06:14
Well, it's uncommon, but it's possible for many pages in the `data_length` to be sparsely filled. I would try to execute `OPTIMIZE TABLE dw_out_of_stock_all_info` on the replica, and see if that rewrites the data in a size closer to that of the master. — Bill Karwin, Feb 12 '19 at 07:54
Maybe this can help https://stackoverflow.com/questions/1270944/mysql-innodb-not-releasing-disk-space-after-deleting-data-rows-from-table — nacho, Feb 12 '19 at 11:11

Why is the size of a read-only table on slave bigger than the size of original table on master?

0 Answers0