Mysql 8 tables size

All we need is an easy explanation of the problem, so here it is.

We have a problem with an unreasonably large table size on the server, for example:
Yesterday, a table with partitions was created, data began to be written to a specific partition.

mysql> show table status like 'in_followers_bots_p'\G
*************************** 1. row ***************************
           Name: in_followers_bots_p
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 38517
 Avg_row_length: 73
    Data_length: 2818048
Max_data_length: 0
   Index_length: 1589248
      Data_free: 4194304
 Auto_increment: NULL
    Create_time: 2021-05-04 14:58:32
    Update_time: 2021-05-04 14:59:45
     Check_time: NULL
      Collation: utf8mb4_unicode_ci
       Checksum: NULL
 Create_options: partitioned
        Comment: 

But at the same time, the size of this table(partition) on disk 3.7G
-rw-r----- 1 mysql mysql 3.7G May 5 12:29 in_followers_bots_p#p#p05.ibd

mysql Ver 8.0.20
Centos 8

This problem is observed with many tables, please tell me the reason for this problem and if there are solutions.
I will be happy to provide all the necessary information.

UPD

mysql> SHOW CREATE TABLE in_followers_bots_p\G
*************************** 1. row ***************************
       Table: in_followers_bots_p
Create Table: CREATE TABLE `in_followers_bots_p` (
  `owner_id` bigint unsigned NOT NULL DEFAULT '0',
  `bot_id` bigint unsigned NOT NULL DEFAULT '0',
  `month_id` tinyint NOT NULL,
  PRIMARY KEY (`bot_id`,`owner_id`,`month_id`),
  KEY `in_followers_bots_owner_id_index` (`owner_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
/*!50100 PARTITION BY RANGE (`month_id`)
(PARTITION p01 VALUES LESS THAN (2) ENGINE = InnoDB,
 PARTITION p02 VALUES LESS THAN (3) ENGINE = InnoDB,
 PARTITION p03 VALUES LESS THAN (4) ENGINE = InnoDB,
 PARTITION p04 VALUES LESS THAN (5) ENGINE = InnoDB,
 PARTITION p05 VALUES LESS THAN (6) ENGINE = InnoDB,
 PARTITION p06 VALUES LESS THAN (7) ENGINE = InnoDB,
 PARTITION p07 VALUES LESS THAN (8) ENGINE = InnoDB,
 PARTITION p08 VALUES LESS THAN (9) ENGINE = InnoDB,
 PARTITION p09 VALUES LESS THAN (10) ENGINE = InnoDB,
 PARTITION p10 VALUES LESS THAN (11) ENGINE = InnoDB,
 PARTITION p11 VALUES LESS THAN (12) ENGINE = InnoDB,
 PARTITION p12 VALUES LESS THAN (13) ENGINE = InnoDB) */```

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

The problem was solved by transferring data to a partition table with the parameter ROW_FORMAT=COMPRESSED.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply