单条记录大小增长倍数和 ibd 文件大小的增长倍数不成正比

作者：GreatSQL

2024-08-16
福建
本文字数：2145 字
阅读完需：约 7 分钟

单条记录大小增长倍数和 ibd 文件大小的增长倍数不成正比

环境信息

数据库版本: GreatSQL 8.0.25

字符集：utf8mb4

innodb_default_row_format: dynamic

innodb_page_size: 16384

问题描述

表数据为新 insert 数据，无 delete、无 update

GreatSQL 一个数据量为 1 万的 A 表，有 100 个 varchar 字段，每个字段存 10 字节，ibd 大小为 21M

GreatSQL 一个数据量为 1 万的 B 表，有 100 个 varchar 字段，每个字段存 100 字节，ibd 大小为 4.7G

问题：相同数据量，相同数据量，B 表的每行比 A 表大 10 倍，磁盘使用大小不是 10 倍，而是 200 多倍？

greatsql> show create table t_user_100_1000_100  \G;*************************** 1. row ***************************       Table: t_user_100_1000_100Create Table: CREATE TABLE `t_user_100_1000_100` (  `id` int NOT NULL AUTO_INCREMENT,  `c_name1` varchar(10) NOT NULL DEFAULT '',。。。  `c_name100` varchar(10) NOT NULL DEFAULT '',  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)
greatsql> show create table t_user_100_10000_100  \G;*************************** 1. row ***************************       Table: t_user_100_10000_100Create Table: CREATE TABLE `t_user_100_10000_100` (  `id` int NOT NULL AUTO_INCREMENT,  `c_name1` varchar(100) NOT NULL DEFAULT '',。。。  `c_name100` varchar(100) NOT NULL DEFAULT '',  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)
greatsql> select count() from t_user_100_10000_100 ;+----------+| count() |+----------+| 10000 |+----------+1 row in set (0.06 sec)
greatsql> select count() from t_user_100_1000_100 ;+----------+| count() |+----------+| 10000 |+----------+1 row in set (0.18 sec)
#os ibd 文件大小lltotal 4313096-rw-r----- 1 mysql mysql 5016387584 Apr 9 18:52 t_user_100_10000_100.ibd-rw-r----- 1 mysql mysql 20971520 Apr 9 18:40 t_user_100_1000_100.ibd
greatsql> select 5016387584/20971520 from dual;+---------------------+| 5016387584/20971520 |+---------------------+| 239.2000 |+---------------------+1 row in set (0.00 sec)

复制代码

问题分析

多出来的 24 倍难道是碎片导致的？

使用 optimize table 重整表后，几乎没有任何优化，查看系统视图，也没有多少空洞。

使用官方工具 innochecksum 查看表空间文件 PAGE 类型分布，可以看到，B 表相对 A 表多了大量的 Other type of page。看来主要的空间消耗是在这个“Other”上。

INNODB 的行溢出

INNODB 默认下每个 PAGE 的大小为 16K。B 表每行 10K，每个 PAGE 只能存放 1 行记录，余下的 6K 就浪费了。但即使按照这个算法，也只浪费了 37%的空间。

实际上，INNODB 在这里有个处理，当记录过大，会将最大的列使用一个指针替代，指针指向一个新的 PAGE，在该 PAGE 上存放实际数据。

由函数 page_zip_rec_needs_ext()判断是否需要溢出。判断方式是该记录长度是否大于空 PAGE 的可用空间。

GDB 执行一下，可以看到一个 16k 的 PAGE 实际可用的空间为 16252 字节（页头等占用了小量字节）。一半粗略算作是 8k。

如果行长度大于 8K，会将最长的列存放到新的 PAGE，原位置使用 20 字节的指针代替。如果处理后，行长度依然大于 8K，则选择当前最长的列进一步处理，不断循环。如果列长度无法进一步缩少，仍然大于 8K，则抛出 DB_TOO_BIG_RECORD 错误，即“row size too large”。dtuple_convert_big_rec()函数上可以看到更多的执行细节。

小量数据溢出的情况

以下堆栈展示把溢出数据写入"Off Page"。主要函数为 lob::insert()。

log::insert()会申请一个新的 16K 大小的 PAGE，并将数据写入新的 page。

dberr_t insert(){  ......  //分配一个新的16KB的PAGE  first_page_t first(mtr, index);  buf_block_t *first_block = first.alloc(mtr, ctx->is_bulk());  ......  //将100字节写入写入  ulint to_write = first.write(trxid, ptr, len);  ...... }

复制代码

以下是 B 表插入数据，往 innodb"Off page"写入数据时候的断点，可以看到只写了 B 表单列 100 字节数据。16KB 的容量只写 100 字节的数据，剩余 99%的空间用不上，实在太浪费了。

B 表有 100 个 varchar 列，每个列 100 字节。如果需要满足列长少于 8K，需要 25 个列以上进行溢出（一个列还有隐藏列和其它数据，实际需要溢出的列略多于 25）。使用 innochecksum 查询到“OTHER” page 有 29 万，B 表有一万行，平均每行 29 个“OTHER” page。看来这个“OTHER” page 基本都是这种“Off page”了。

问题总结

GreatSQL 白白浪费了 95%的磁盘空间，是因为大量的列溢出了小量的数据。INNODB 存放每个溢出列的数据的最小分配单元大小是 16KB。原本 10KB 的行长度，需要多占 N 倍的存储空间。

优化建议

表设计时，要注意控制行长度小于 8k,避免小量列数据溢出，导致磁盘容量和性能问题。

创作场景

单条记录大小增长倍数和 ibd 文件大小的增长倍数不成正比

单条记录大小增长倍数和 ibd 文件大小的增长倍数不成正比

环境信息

问题描述

问题分析

问题总结

优化建议

延伸阅读

GreatSQL

评论