首页 > 其他分享 >单条记录大小增长倍数和ibd文件大小的增长倍数不成正比

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

时间:2024-08-16 10:06:28浏览次数:10  
标签:文件大小 ibd GreatSQL page user 100 row PAGE 倍数

单条记录大小增长倍数和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_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重整表后,几乎没有任何优化,查看系统视图,也没有多少空洞。

file

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

file

  • INNODB的行溢出

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

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

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

file

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

file

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

file

  • 小量数据溢出的情况

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

file

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%的空间用不上,实在太浪费了。

file

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,避免小量列数据溢出,导致磁盘容量和性能问题。

延伸阅读


Enjoy GreatSQL

标签:文件大小,ibd,GreatSQL,page,user,100,row,PAGE,倍数
From: https://www.cnblogs.com/greatsql/p/18362349

相关文章

  • 100以内3的倍数
    时间限制:1秒        内存限制:128M题目描述小可学会了除法运算,想知道小于100的正整数中3的倍数有哪些。输入描述无输出描述输出若干行,每行一个数,这个数是3的倍数。样例输出暂不提供 #include<cmath>#include<queue>#include<cctype>#include<cstdio>#......
  • PbootCMS上传文件大小限制
    PbootCMS上传文件大小限制是由php.ini限制的,所以我们需要修改php.ini,找到PHP目录下的php.ini,一、打开php.ini1.file_uploads=on;是否允许通过HTTP上传文件的开关,默认为ON即是开;2.upload_tmp_dir;文件上传至服务器上存储临时文件的地方,如果没指定就会用系统默认的......
  • 【MySQL核心】数据恢复-批量修复 ibd 文件实战-拯救即将跑路的你
    简介本文章主要讲解MySQL异常宕机等意外情况下导致ibd文件损坏,配置mysql强制恢复(innodb_force_recovery)1-6级仍然无法启动的情况。下面是恢复整个实例的所有流程和思路。这里恢复的实例是mysql5.6的整个流程,5.7和5.6类似,8.0可以直接用ibd2sql进行坏表的恢......
  • 2019的倍数
    题目描述给定的字符串 �S 由从 1 到 9 的数字组成。求满足以下条件的整数对 (�,�)(i,j) ( 1≤�≤�≤∣�∣1≤i≤j≤∣S∣ )的个数:条件:在十进制中, �S 的第 �i 个到第 �j 个字符组成的整数是 20192019 的倍数。输入描述测试样例第一行输入一个字符串 �(1≤∣�∣≤2......
  • 如何调整 PDF 文件大小
    你有没有遇到过一个似乎需要很长时间才能下载或通过电子邮件发送的PDF?大文件大小可能是一个真正的负担,尤其是在处理包含图像的文档时。这些图像虽然通常对信息至关重要,但会显着增加整体PDF大小。斗争是真实的!但不要害怕,有一些方法可以简化您的PDF并使其更易于管理。我们将......
  • FFmpeg在游戏视频录制中的应用:画质与文件大小的综合比较
    我们游戏内的视频录制目前只支持avi固定码率,在玩家见面会上有玩家反馈希望改善录制画质,我最近在研究了有关视频画质的一些内容并做了一些统计。录制视频大小对比首先在游戏引擎中增加了对录制mp4格式的支持,并且使用h246编码可以直接在网页上播放无法再做转码测试场景:视频尺寸固......
  • 从MySQL大量数据清洗到TiBD说起
    一、业务背景公司主要做的业务是类似贝壳的二手房租售,数据库中存了上亿级别的房源数据,之前数据库使用的是mysql,后面需要将mysql数据库切换成了Tidb,在切换的过程中,需要将老库的数据经过数据清洗后再存入新库(因为有一些表结构的设计变了),其中我们处理的一个逻辑就是将房间......
  • KingbaseES 更改 WAL 日志文件大小
    一、引言WAL日志的目的:WAL(Write-AheadLogging)是KingbaseES数据库中的一个关键特性,用于确保在数据库崩溃或故障后能够恢复数据。WAL日志记录了数据库的所有更改,这样即使数据库突然宕机,也可以从WAL文件中恢复数据到一致性状态。命令的功能:sys_resetwal命令用于重置WAL日志,它可以......
  • binascii.Error:无效的 base64 编码字符串:数据字符数 (41) 不能多于 1 4 的倍数
    我正在尝试使用py-vapid、pywebpush和django-push-notifications通过Webpush发送通知。当我尝试从django管理网站发送测试通知时,我在控制台中收到此回溯日志:|InternalServerError:/djangoadmin/push_notifications/webpushdevice/......
  • 超强mysql灾难恢复工具--ibd2sql
    超强mysql灾难恢复工具--ibd2sql小董学编程 DBA札记  2024年07月19日16:51 上海 1人听过DBA札记dba数据库知识科普踩坑指南经验分享原理解读88篇原创内容公众号前言昨晚23:00,刚躺到床上准备刷会儿抖音睡了。好友微信视频求救,说他的mysql......