草庐IT

mysql - InnoDB 行大小在表增长时呈指数变化？

coder 2023-10-04 原文

我有一个包含三列(int、mediumint、int)的巨大 InnoDB 表。 innodb_file_per_table设置开启，前两列只有一个PRIMARY KEY

表架构是:

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版本为5.6.16

目前我每秒多行插入超过 150 行。没有删除，也没有更新。 没有明显的回滚或其他事务中止，这会导致空间使用浪费。

MySQL 显示该表的计算大小为 75.7GB。

.ibd size on disc: 136,679,784,448 byte (127.29 GiB)

Counted rows: 2,901,937,966 (47.10 byte per row)

2 天后，MySQL 还显示该表的计算大小为 75.7 GB。

.ibd size on disc: 144,263,086,080 byte (135.35 GiB)

Counted rows: 2,921,284,863 (49.38 byte per row)

为表运行 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 完全是错误的？

希望有人能帮助我，光盘使用量不会再像这样增长了。我没有注意到，因为 table 较小。

最佳答案

我假设您的表还没有有机地增长到现在的 29 亿行，并且您最近加载了这些数据或者导致表被重新组织(使用 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。

当然这不是真的。随着时间的推移，索引树会在 1/2 满和 15/16 满之间的某处稳定下来——它不会永远拆分页面，因为必须发生在同一页面中的下一次插入会发现已经有足够的空间存在做插入。

不过，如果您最初将数据批量加载(并因此有效地打包)到表中，然后切换到有机增长，这可能会有点令人不安。最初，表格似乎正在以疯狂的速度增长，但如果您随着时间的推移跟踪增长率，它应该会放缓。

您可以在我的博文中阅读更多关于 InnoDB 索引和记录布局的信息:The physical structure of records in InnoDB , The physical structure of InnoDB index pages , 和 B+Tree index structures in InnoDB .

关于mysql - InnoDB 行大小在表增长时呈指数变化？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/22037778/

长时InnoDBcodesectionmysqlprimary-keymysql-5.6

有关mysql - InnoDB 行大小在表增长时呈指数变化？的更多相关文章

  1. ruby-on-rails - 在 Rails 中将文件大小字符串转换为等效千字节 - 2

    我的目标是转换表单输入，例如“100兆字节”或“1GB”，并将其转换为我可以存储在数据库中的文件大小(以千字节为单位)。目前，我有这个:defquota_convert@regex=/([0-9]+)(.*)s/@sizes=%w{kilobytemegabytegigabyte}m=self.quota.match(@regex)if@sizes.include?m[2]eval("self.quota=#{m[1]}.#{m[2]}")endend这有效，但前提是输入是倍数(“gigabytes”，而不是“gigabyte”)并且由于使用了eval看起来疯狂不安全。所以，功能正常，

  2. ruby-on-rails - 启用 Rack::Deflater 时 ETag 发生变化 - 2

    在启用Rack::Deflater来gzip我的响应主体时偶然发现了一些奇怪的东西。也许我遗漏了一些东西，但启用此功能后，响应被压缩，但是资源的ETag在每个请求上都会发生变化。这会强制应用程序每次都响应，而不是发送304。这在没有启用Rack::Deflater的情况下有效，我已经验证页面源没有改变。我正在运行一个使用thin作为Web服务器的Rails应用程序。Gemfile.lockhttps://gist.github.com/2510816有没有什么方法可以让我从Rack中间件获得更多的输出，这样我就可以看到发生了什么？提前致谢。 最佳答案

  3. 使用canal同步MySQL数据到ES - 2

    文章目录一、概述简介原理模块二、配置Mysql使用版本环境要求1.操作系统2.mysql要求三、配置canal-server离线下载在线下载上传解压修改配置单机配置集群配置分库分表配置1.修改全局配置2.实例配置垂直分库水平分库3.修改group-instance.xml4.启动监听四、配置canal-adapter1修改启动配置2配置映射文件3启动ES数据同步查询所有订阅同步数据同步开关启动4.验证五、配置canal-admin一、概述简介canal是Alibaba旗下的一款开源项目，Java开发。基于数据库增量日志解析，提供增量数据订阅&消费。Git地址：https://github.co

  4. HBase Region 简介和建议数量&大小 - 2

    Region是HBase数据管理的基本单位,region有一点像关系型数据的分区。region中存储这用户的真实数据，而为了管理这些数据，HBase使用了RegionSever来管理region。Region的结构hbaseregion的大小设置默认情况下，每个Table起初只有一个Region，随着数据的不断写入，Region会自动进行拆分。刚拆分时，两个子Region都位于当前的RegionServer，但处于负载均衡的考虑，HMaster有可能会将某个Region转移给其他的RegionServer。RegionSplit时机：当1个region中的某个Store下所有StoreFile

  5. ruby-on-rails - Ruby 中意外的大小写行为 - 2

    我在一段非常简单的代码(如我所想)中得到了一个错误的值:org=4caseorgwhenorg=4val='H'endputsval=>nil请不要生气，我希望我错过了一些非常明显的东西，但我真的想不通。谢谢。 最佳答案 这是典型的Ruby错误。case有两种被调用的方法，一种是你传递一个东西作为分支的基础，另一种是你不传递的东西。如果您确实在case中指定了一个表达式语句然后评估所有其他条件并与===进行比较.在这种情况下org评估为false和org===false显然不是真的。所有其他情况也是如此，它们要么是真的，要么是假的。

  6. ruby - 改变替换的大小写 - 2

    我有以下内容:text.gsub(/(lower)(upper)/,'\1\2')我可以将\2替换为大写吗？类似于:sed-e's/\(abc\)/\U\1/'这在Ruby中可行吗？ 最佳答案 查看gsub文档:str.gsub(模式){|匹配|block}→new_str在block形式中，当前匹配字符串作为参数传入，$1、$2、$`、$&、$'等变量将被适当设置。block返回的值将替换为每次调用的匹配项。"alowerupperb".gsub(/(lower)(upper)/){|s|$1+""+$2.upcase}

  7. ruby-on-rails - 无法安装 mysql2 0.3.14 gem - 2

    我看到其他人也遇到过类似的问题，但没有一个解决方案对我有用。0.3.14gem与其他gem文件一起存在。我已经完全按照此处指示完成了所有操作:https://github.com/brianmario/mysql2.我仍然得到以下信息。我不知道为什么安装程序指示它找不到include目录，因为我已经检查过它存在。thread.h文件存在，但不在ruby​​目录中。相反，它在这里:C:\RailsInstaller\DevKit\lib\perl5\5.8\msys\CORE\我正在运行Windows7并尝试在Aptana3中构建我的Rails项目。我的Ruby是1.9.3。$gemin

  8. ruby - 可以正常中断的来自 Rake 的长时间运行的 shell 命令？ - 2

    在几个项目中，我希望有一个类似rakeserver的rake任务，它将通过任何需要的方式开始为该应用程序提供服务。这是一个示例:task:serverdo%x{bundleexecrackup-p1234}end这行得通，但是当我准备停止它时，按Ctrl+c并没有正常关闭；它中断了Rake任务本身，它说rakeaborted!并给出堆栈跟踪。在某些情况下，我必须执行Ctrl+c两次。我可能可以用Signal.trap写一些东西来更优雅地中断它。有没有更简单的方法？ 最佳答案 trap('SIGINT'){puts"Yourmessa

  9. ruby - 如何使用 ruby​​ mysql2 执行事务 - 2

    我已经开始使用mysql2gem。我试图弄清楚一些基本的事情——其中之一是如何明确地执行事务(对于批处理操作，比如多个INSERT/UPDATE查询)。在旧的ruby-mysql中，这是我的方法:client=Mysql.real_connect(...)inserts=["INSERTINTO...","UPDATE..WHEREid=..",#etc]client.autocommit(false)inserts.eachdo|ins|beginclient.query(ins)rescue#handleerrorsorabortentirelyendendclient.commi

  10. ruby-on-rails - Ruby 长时间运行的进程对队列事件使用react - 2

    我有一个将某些事件写入队列的Rails3应用。现在我想在服务器上创建一个服务，每x秒轮询一次队列，并按计划执行其他任务。除了创建ruby​​脚本并通过cron作业运行它之外，还有其他稳定的替代方案吗？ 最佳答案 尽管启动基于Rails的持久任务是一种选择，但您可能希望查看更有序的系统，例如delayed_job或Starling管理您的工作量。我建议不要在cron中运行某些东西，因为启动整个Rails堆栈的开销可能很大。每隔几秒运行一次它是不切实际的，因为Rails上的启动时间通常为5-15秒，具体取决于您的硬件。不过，每天这样做几

随机推荐