主页 / dba / 问题 / 43503
Accepted
Petr
Asked: 2013-06-01 00:15:16 +0800 CST

`ERROR 1114 (HY000) the table ... is full` innodb_file_per_table 设置为自动扩展

  • 772

我有一个 MySQL 数据库，其中包含大量数据（100-200GB - 一堆科学测量值）。绝大多数数据都存储在一张表中Sample。现在我正在创建数据库的从属副本，我想innodb_file_per_table在此过程中利用这些优势。所以我设置innodb_file_per_table了我的从属配置并导入了数据库的转储。令我惊讶的是，它失败了

第 5602 行的错误 1114 (HY000)：表“样本”已满

该文件Sample.ibd目前约为 93GB，分区上有超过 600GB 的可用空间，因此不是磁盘可用空间问题。它似乎都没有达到任何类型的文件系统限制（我使用的是 ext4）。

如果有任何想法可能是原因或要调查的内容，我将不胜感激。

更新：我正在使用mysql Ver 14.14 Distrib 5.1.66, for debian-linux-gnu (x86_64).

SELECT @@datadir; -- returns `/home/var/lib/mysql/`
SHOW VARIABLES LIKE '%innodb_data_file_path%'; -- ibdata1:10M:autoextend 

df -h /home/var/lib/mysql/
768G   31G  699G   5% /home
mysql innodb

2 个回答

  1. Best Answer

    事实

    你说你正在使用ext4. 文件大小限制为 16TB。因此，Sample.ibd不应满。

    你说你的innodb_data_file_path是ibdata1:10M:autoextend。因此，ibdata1 文件本身的大小没有上限，除非来自操作系统。

    为什么会出现这条消息？请注意消息是“表...已满”，而不是“磁盘...已满”。从逻辑的角度来看，此表已满情况。想想 InnoDB。正在发生什么互动？

    我的猜测是 InnoDB 正在尝试将 93GB 的数据作为单个事务加载。Table is Full消息会从哪里发出？我会查看 ibdata1，而不是根据其物理大小（您已经排除），而是根据达到的交易限制。

    启用innodb_file_per_table并将新数据加载到 MySQL时，ibdata1 内部是什么？

    • 数据字典
    • 双写缓冲器
      • 防止数据损坏的安全网
      • 帮助绕过操作系统进行缓存
    • 插入缓冲区（简化对二级索引的更改）
    • 回滚段
    • 撤消日志
    • 单击此处查看图示ibdata1

    我的怀疑告诉我，撤消日志和/或重做日志是罪魁祸首。

    这些日志是什么？根据书

    sxs

    第 10 章：“存储引擎”第 203 页第 3,4 段说明如下：

    InnoDB 引擎保留两种类型的日志：撤消日志和重做日志。撤消日志的目的是回滚事务，以及显示在需要它的事务隔离级别中运行的查询的旧版本数据。处理撤消日志的代码可以在storage/innobase/buf/log/log0log.c中找到。

    重做日志的目的是存储用于崩溃恢复的信息。它允许恢复过程重新执行崩溃前可能已完成或未完成的事务。重新执行这些事务后，数据库将进入一致状态。处理重做日志的代码可以在storage/innobase/log/log0recv.c中找到。

    分析

    ibdata1中有 1023 条撤消日志（请参阅回滚段和撤消空间） 。由于撤消日志保留了重新加载之前出现的数据副本，因此所有 1023 个撤消日志都已达到其限制。从另一个角度来看，所有 1023 个 Undo Logs 都可能专用于加载Sample表的一个事务。

    可是等等...

    您可能会说“我正在加载一张空Sample桌子”。撤消日志如何参与？在Sample表加载 93GB 数据之前，它是空的。表示不存在的每一行必须占用撤消日志中的一些清理空间。考虑到大量数据涌入ibdata1. 我不是第一个怀疑这一点的人：

    从 MySQL 4.1 文档中，注意Posted by Chris Calender on September 4 2009 4:25pm：

    请注意，在 5.0（5.0.85 之前）和 5.1（5.1.38 之前）中，如果 InnoDB 用完撤消槽（错误 #18828），您可能会收到 InnoDB 表的“表已满”错误。

    这是 MySQL 5.0 的错误报告：http ://bugs.mysql.com/bug.php?id=18828

    建议

    创建Sample表的mysqldump时，请使用--no-autocommit

    mysqldump --no-autocommit ... mydb Sample > Sample.sql

    这将COMMIT;在每个INSERT. 然后，重新加载表。

    如果这不起作用（您不会喜欢这个），请执行此操作

    mysqldump --no-autocommit --skip-extended-insert ... mydb Sample > Sample.sql

    这将使每个 INSERT 只有一行。mysqldump 会大得多（大 10 倍以上），重新加载可能需要 10 到 100 倍的时间。

    在任何一种情况下，这都会避免 Undo Logs 被淹没。

    试试看 ！！！

    更新 2013-06-03 13:05 EDT

    其他建议

    如果 InnoDB 系统表（又名 ibdata1）达到文件大小限制并且无法使用撤消日志，则可以添加另一个系统表空间（ibdata2）。

    我前两天刚遇到这种情况。我用我所做的更新了我的旧帖子：请参阅数据库设计 - 创建多个数据库以避免表大小限制的头痛

    本质上，您必须更改innodb_data_file_path以适应新的系统表空间文件。让我解释一下如何：

    设想

    在磁盘（ext3）上，我的客户服务器具有以下内容：

    [root@l*****]# ls -l ibd*
-rw-rw---- 1 s-em7-mysql s-em7-mysql     362807296 Jun  2 00:15 ibdata1
-rw-rw---- 1 s-em7-mysql s-em7-mysql 2196875759616 Jun  2 00:15 ibdata2

    设置是

    innodb_data_file_path=ibdata1:346M;ibdata2:500M:autoextend:max:10240000M

    请注意，ibdata2增长到 2196875759616 即2145386484M.

    我必须将文件大小嵌入ibdata2到 innodb_data_file_path 并添加ibdata3

    innodb_data_file_path=ibdata1:346M;ibdata2:2196875759616;ibdata3:10M:autoextend

    当我重新启动mysqld时，它起作用了：

    [root@l*****]# ls -l ibd*
-rw-rw---- 1 s-em7-mysql s-em7-mysql     362807296 Jun  3 17:02 ibdata1
-rw-rw---- 1 s-em7-mysql s-em7-mysql 2196875759616 Jun  3 17:02 ibdata2
-rw-rw---- 1 s-em7-mysql s-em7-mysql   32315015168 Jun  3 17:02 ibdata3

    在 40 小时内，ibdata3增长到 31G。MySQL 再次工作。

    • 33

  2. 我有同样的问题，我只做了一件事，它奏效了。

    innodb_data_file_path您的my.cnf配置文件中的最大大小似乎太低了。只需更改以下代码 -

    innodb_data_file_path = ibdata1:10M:autoextend:max:512M

    重要说明512MB您不能在所有表中托管超过InnoDB 合并的数据。

    您还可以使用innodb_file_per_table.

    • 3

相关问题