InnoDB row size changes exponentially and table grows?

Question

I have a huge InnoDB table with three columns (int, mediumint, int). The option is innodb_file_per_tableenabled, and there are only the PRIMARY KEYfirst two columns

Table layout:

CREATE TABLE `big_table` (
  `user_id` int(10) unsigned NOT NULL,
  `another_id` mediumint(8) unsigned NOT NULL,
  `timestamp` int(10) unsigned NOT NULL,
  PRIMARY KEY (`user_id`,`another_id `)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

MySQL Version - 5.6.16

Currently, I repeatedly insert over 150 rows per second. No deletion or updates. There are no significant rollbacks or other interruptions to transactions, which can result in wasted space.

MySQL shows the estimated size of 75.7 GB in this table.

.ibd disk size: 136 679 784 448 bytes (127.29 GiB)

Counted lines: 2,901,937,966 (47.10 bytes per line)

After 2 days, MySQL also shows the estimated size of 75.7 GB in this table.

.ibd disk size: 144,263,086,080 bytes (135.35 gigabytes)

: 2,921,284,863 (49,38 )

SHOW TABLE STATUS :

Engine | Version | Row_format | Rows       | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Collation 
InnoDB |      10 | Compact    | 2645215723 |             30 | 81287708672 |               0 |            0 |   6291456 | utf8_unicode_ci

:

• ?
• Avg_row_length Data_length ?

, - , . , .

Answer 1

, ~ 2.9 , ( ALTER TABLE OPTIMIZE TABLE, ). , .

(, , ), () :

(Header)              5 bytes
`user_id`             4 bytes
`another_id`          3 bytes
(Transaction ID)      6 bytes
(Rollback Pointer)    7 bytes
`timestamp`           4 bytes
=============================
Total                29 bytes

InnoDB ~ 15/16 ( 1/2 ). 32 60 .

ALTER TABLE OPTIMIZE TABLE ( ) PRIMARY KEY, InnoDB . ( ) , , , B + Tree . 16 KiB, ~ 32 , , , (~ 16 KiB ), "" 16 KiB.

, . - 15/16 - , , , , .

, ( ) , . , , , .

InnoDB : InnoDB, InnoDB B + Tree InnoDB.