MySQL Bugs: #119043: in certain situations the table space will be greatly wasted

Bug #119043	in certain situations the table space will be greatly wasted
Submitted:	19 Sep 9:14	Modified:	23 Sep 1:33
Reporter:	peng gao	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	8.0.41	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
hi team:

   If there are large records in the table but they do not exceed 8192, in certain situations, the table space will be greatly wasted.
   In repeat example record 6/7/8/9/10/11  in separate pages.

How to repeat:
CREATE TABLE `sbtest2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pad` varchar(12000),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

insert into sbtest2 values (10000, repeat('a',5120));
insert into sbtest2 values (10001, repeat('a',5120));
insert into sbtest2 values (1, repeat('a',5120));
insert into sbtest2 values (2, repeat('a',5120));
insert into sbtest2 values (3, repeat('a',5120));
insert into sbtest2 values (4, repeat('a',5120));
insert into sbtest2 values (5, repeat('a',5120));
insert into sbtest2 values (6,repeat('a',5120));
insert into sbtest2 values (7,repeat('a',5120));
insert into sbtest2 values (8,repeat('a',5120));
insert into sbtest2 values (9,repeat('a',5120));
insert into sbtest2 values (10,repeat('a',5120));
insert into sbtest2 values (11,repeat('a',5120));

Suggested fix:
At a certain moment, the page‘s records as follows

  block n            block x 
9   10000    -->     10001
   |
   |

insert rec 10

The current logic is:

block n   block n+1  block x
9  ---> 10 10000 --->  10001

block n space will be greatly wasted !  

This example key logic to insert an old page or a new split page,if insert new record to old page can be solved.

Like this :
 
 block n     block n+1    block x
9  10 --->  10000 --->     10001

 next new rec 11 insert to block n+1
 
  block n      block n    block x
9  10  ----> 11 10000 --> 10001

This way, the space won't be wasted. 

key logic:
  /* 6. The split and the tree modification is now completed. Decide the
  page where the tuple should be inserted */

  if (insert_left) {  
    insert_block = left_block; 
  } else {
    insert_block = right_block;
  }

Thank you for your report but this is intended and expected behavior.

Hi,

> > If there are large records in the table but they do not exceed 8192, in certain situations, the table space will be greatly wasted.
>
> Thank you for your report but this is intended and expected behavior.

I am very surprised by this answer.  It is basically saying that MySQL wasting disk space (and InnoDB Buffer Pool space, hence memory) is "normal", which is clearly not the "expected" behavior to me.

With a lot of rows, this can take significant proportions.  In the example below, we are seeing a table of 2.1 GiB which I would expect to be only 1.1 GiB.  If we "grow" this by 100 (or 1024), we have 100 GiB (or 1 TiB) "wasted".

Many thanks for reconsidering "Not a Bug",

Jean-François Gagné

Sorry, I forgot to add the link to the conversation...

https://mysqlcommunity.slack.com/archives/C8R1336M7/p1758551730069869

Also, unable to add the repro for above, either as a comment or as a file, I am getting "Access Denied", the test case is in above link.

dbda="-c skip-log-bin"
dbdeployer deploy single mysql_8.4.6 $dbda

./use test <<< "CREATE TABLE test_jfg (id bigint(11) NOT NULL PRIMARY KEY, pad varchar(12000))"

# We are aiming at a table of one GiB.
nb_rows=$(( 1024*1024*1024*2 / (16*1024) ))

Unable to add the insert function, adding the rest...

# Below expected.
insert $nb_rows &&
  ./use test <<< "
    insert into test_jfg values ($nb_rows + 100, repeat('a',5120));
    insert into test_jfg values ($nb_rows + 101, repeat('a',5120));" &&
  ls -lh data/test/test_jfg.ibd
   insert: 0:00:50
-rw-r----- 1 jgagne jgagne 1.1G Sep 22 14:22 data/test/test_jfg.ibd

# Below half wasted.
./use test <<< "
  TRUNCATE TABLE test_jfg;
  insert into test_jfg values ($nb_rows + 100, repeat('a',5120));
  insert into test_jfg values ($nb_rows + 101, repeat('a',5120));" &&
  insert $nb_rows &&
  ls -lh data/test/test_jfg.ibd
   insert: 0:01:42
-rw-r----- 1 jgagne jgagne 2.1G Sep 22 14:27 data/test/test_jfg.ibd

This is the insert function:

  seq 1 $1 |
    awk '{print "(" $1 ",repeat(\"a\",5120))"}' |
    tr " " "," | paste -s -d "$(printf ',%.0s' {1..1000})\n" |
    sed -e 's/.*/INSERT INTO test_jfg(id, pad) values &;/' |
    ./use test | pv -tN insert

Hi Jean,

We verified the issue for double-checking. Thanks for your input.