* GreatSQL社区原创内容未经授权不得随意使用,转载请联系小编并注明来源。
环境信息
数据库版本: 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_100
Create 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_ci
1 row in set (0.00 sec)
greatsql> show create table t_user_100_10000_100 \G;
*************************** 1. row ***************************
Table: t_user_100_10000_100
Create 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_ci
1 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 文件大小
ll
total 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,避免小量列数据溢出,导致磁盘容量和性能问题。
延伸阅读
https://dev.mysql.com/doc/refman/8.0/en/out-of-range-and-overflow.html
环境信息
数据库版本: 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_100
Create 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_ci
1 row in set (0.00 sec)
greatsql> show create table t_user_100_10000_100 \G;
*************************** 1. row ***************************
Table: t_user_100_10000_100
Create 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_ci
1 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 文件大小
ll
total 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,避免小量列数据溢出,导致磁盘容量和性能问题。
延伸阅读
https://dev.mysql.com/doc/refman/8.0/en/out-of-range-and-overflow.html
《GreatSQL运维实战》视频课程
GreatSQL数据库是一款开源免费数据库,可在普通硬件上满足金融级应用场景,具有高可用、高性能、高兼容、高安全等特性,可作为MySQL或Percona Server for MySQL的理想可选替换。
⏩GitHub : https://github.com/GreatSQL/
🆙BiliBili : https://space.bilibili.com/1363850082
(对文章有疑问或见解可去社区官网提出哦~)
加入微信交流群 |
加入QQ交流群 |
本文分享自微信公众号 - GreatSQL社区(GreatSQL)。
如有侵权,请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”,欢迎正在阅读的你也加入,一起分享。