我有一个 MySQL 数据库,其中包含大量数据(100-200GB - 一堆科学测量值)。绝大多数数据都存储在一张表中Sample。现在我正在创建数据库的从属副本,我想innodb_file_per_table在此过程中利用这些优势。所以我设置innodb_file_per_table了我的从属配置并导入了数据库的转储。令我惊讶的是,它失败了
Sample
innodb_file_per_table
ERROR 1114 (HY000) at line 5602: The table ‘Sample’ is full
该文件Sample.ibd目前约为 93GB,分区上有超过 600GB 的可用空间,因此不是磁盘可用空间问题。它似乎都没有达到任何类型的文件系统限制(我使用的是 ext4)。
Sample.ibd
如果有任何想法可能是原因或要调查的内容,我将不胜感激。
更新:我正在使用mysql Ver 14.14 Distrib 5.1.66, for debian-linux-gnu (x86_64).
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
如果 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
2145386484M
我必须将文件大小嵌入ibdata2到 innodb_data_file_path 并添加ibdata3
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 再次工作。
你说你正在使用ext4. 文件大小限制为 16TB。因此,Sample.ibd不应满。
ext4
你说你的innodb_data_file_path是ibdata1:10M:autoextend。因此,ibdata1 文件本身的大小没有上限,除非来自操作系统。
innodb_data_file_path
ibdata1:10M:autoextend
为什么会出现这条消息?请注意消息是“表…已满”,而不是“磁盘…已满”。从逻辑的角度来看,此表已满情况。想想 InnoDB。正在发生什么互动?
我的猜测是 InnoDB 正在尝试将 93GB 的数据作为单个事务加载。Table is Full消息会从哪里发出?我会查看 ibdata1,而不是根据其物理大小(您已经排除),而是根据达到的交易限制。
Table is Full
启用innodb_file_per_table并将新数据加载到 MySQL时,ibdata1 内部是什么?
ibdata1
我的怀疑告诉我,撤消日志和/或重做日志是罪魁祸首。
这些日志是什么?根据书
第 10 章:“存储引擎”第 203 页第 3,4 段说明如下:
InnoDB 引擎保留两种类型的日志:撤消日志和重做日志。撤消日志的目的是回滚事务,以及显示在需要它的事务隔离级别中运行的查询的旧版本数据。处理撤消日志的代码可以在storage/innobase/buf/log/log0log.c中找到。 重做日志的目的是存储用于崩溃恢复的信息。它允许恢复过程重新执行崩溃前可能已完成或未完成的事务。重新执行这些事务后,数据库将进入一致状态。处理重做日志的代码可以在storage/innobase/log/log0recv.c中找到。
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:
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. 然后,重新加载表。
COMMIT;
INSERT
如果这不起作用(*您不会喜欢这个*),请执行此操作
mysqldump --no-autocommit --skip-extended-insert ... mydb Sample > Sample.sql
这将使每个 INSERT 只有一行。mysqldump 会大得多(大 10 倍以上),重新加载可能需要 10 到 100 倍的时间。
在任何一种情况下,这都会避免 Undo Logs 被淹没。
试试看 !!!