I have a MySQL database that holds a large amount of data (100-200GB – a bunch of scientific measurements). The vast majority of the data is stored in one table Sample
. Now I'm creating a slave replica of the database and I wanted to take the advantages of innodb_file_per_table
during the process. So I set innodb_file_per_table
in my slave configuration and imported the dump of the database. To my surprise, it failed with
ERROR 1114 (HY000) at line 5602: The table 'Sample' is full
The file Sample.ibd
is currently about 93GB, with more than 600GB free space available on the partition, so it's not a disk free-space issue. Neither it seems to be hitting any kind of file-system limit (I'm using ext4).
I'd be grateful for any ideas what could be the cause, or what to investigate.
Update: I'm using 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
Best Answer
FACTS
You said you are using
ext4
. File size limit is 16TB. Thus,Sample.ibd
should not be full.You said your
innodb_data_file_path
isibdata1:10M:autoextend
. Thus, the ibdata1 file itself has no cap to its size except from the OS.Why is this message coming up at all? Notice the message is "The table ... is full", not "The disk ... is full". This table full condition is from a logical standpoint. Think about InnoDB. What interactions are going on ?
My guess is InnoDB is attempting to load 93GB of data as a single transaction. Where would the
Table is Full
message emanate from? I would look at the ibdata1, not in terms its physical size (which you already ruled out), but in terms of what transaction limits are being reached.What is inside ibdata1 when innodb_file_per_table is enabled and you load new data into MySQL?
ibdata1
My suspicions tell me that the Undo Logs and/or Redo Logs are to blame.
What are these logs? According to the Book
Chapter 10 : "Storage Engines" Page 203 Paragraphs 3,4 say the following:
ANALYSIS
There are 1023 Undo Logs inside ibdata1 (See Rollback Segments and Undo Space). Since the undo logs keep copies of data as they appeared before the reload, all 1023 Undo Logs have reached its limit. From another perspective, all 1023 Undo Logs may be dedicated to the one transaction that loads the
Sample
table.BUT WAIT...
You are probably saying "I am loading an empty
Sample
table". How are Undo Logs involved? Before theSample
table was loaded with 93GB of data, it was empty. Representing every row that did not exist must take up some housecleaning space in the Undo Logs. Filling up 1023 Undo Logs seems trivial given the amount of data pouring intoibdata1
. I am not the first person to suspect this:From the MySQL 4.1 Documentation, note
Posted by Chris Calender on September 4 2009 4:25pm
:Here is the bug report for MySQL 5.0 : http://bugs.mysql.com/bug.php?id=18828
SUGGESTIONS
When you create the mysqldump of the
Sample
table, please use --no-autocommitThis will put an explicit
COMMIT;
after everyINSERT
. Then, reload the table.If this does not work (you are not going to like this), do this
This will make each INSERT have just one row. The mysqldump will be much larger (10+ times bigger) and could take 10 to 100 times longer to reload.
In either case, this will spare the Undo Logs from being inundated.
Give it a Try !!!
UPDATE 2013-06-03 13:05 EDT
ADDITIONAL SUGGESTION
If the InnoDB system table (a.k.a ibdata1) strikes a filesize limit and Undo Logs cannot be used, you could just add another system tablespace (ibdata2).
I just encountered this situation just two days ago. I updated my old post with what I did: See Database Design - Creating Multiple databases to avoid the headache of limit on table size
In essence, you have to change innodb_data_file_path to accommodate a new system tablespace file. Let me explain how:
SCENARIO
On disk (ext3), my client's server had the following:
The setting was
Note that
ibdata2
grew to 2196875759616 which is2145386484M
.I had to embed the filesize of
ibdata2
into innodb_data_file_path and addibdata3
When I restarted mysqld, it worked:
In 40 hours,
ibdata3
grew to 31G. MySQL was once again working.