(After having explained this for the 35th time, I decided it's time to simply put something on-line.)
When a MyISAM table grows large enough, you'll encounter the dreaded "The table is full" error. Now I could simply point at that page and leave this subject alone, but there's more to this story.
When this happens, the first reaction I hear is "You never told me that MySQL has a 4GB limit! What am I going to do?" Amusingly, I usually do describe the limit when I discuss the possibility of using MySQL with various groups--they often forget or underestimate the impact it will have. Putting that aside, the problem is easily fixed, as that page explains. You simply need to run an ALTER TABLE command.
And you'll need to wait. That ALTER TABLE is going to take some time. Really.
To protect yourself in the future, use the MAX_ROWS and AVG_ROW_LENGTH options at CREATE TABLE time if the table is likely to get big.
InnoDB tables do not have this limitation because their storage model is completely different.
Where does this limit come from?
In a MyISAM table with dynamic (variable length) rows, the index file for the table (tablename.MYI) stores row locations using 32-bit pointers into the data file (tablename.MYD). That means it can address only 4GB of space.
This problem is both a historical artifact and an optimization. Back when MySQL was created, it wasn't common to store that much data in a single table. Heck, for a long time 4GB was an entire hard disk and most operating systems had trouble with files larger than 2GB. Obviously those days are gone. Modern operating systems have no trouble with large files and hard disks larger than 100GB are quite common.
From an optimization point of view, however, the 32-bit pointer still makes sense. Why? Because most people are running MySQL on 32-bit hardware (Intel/Linux). That will change as use of AMD's Opteron becomes more widespread, but 32-bit will be the majority for the next few years. Using 32-bit pointers is the most efficient way to do this on 32-bit hardware. And even today, most MySQL installations don't have tables anywhere near 4GB in size. Sure, there are a lot of larger deployments emerging. They're all relatively new.
An Example
Here's a table that you might use to store weather data:
mysql> describe weather; +-----------+--------------+------+-----+------------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+--------------+------+-----+------------+-------+ | city | varchar(100) | | MUL | | | | high_temp | tinyint(4) | | | 0 | | | low_temp | tinyint(4) | | | 0 | | | the_date | date | | | 0000-00-00 | | +-----------+--------------+------+-----+------------+-------+ 4 rows in set (0.01 sec)
To find its size limit, we'll use SHOW TABLE STATUS
mysql> show table status like 'weather' \G
*************************** 1. row ***************************
Name: weather
Type: MyISAM
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 4294967295
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2003-03-03 00:43:43
Update_time: 2003-03-03 00:43:43
Check_time: 2003-06-14 15:11:21
Create_options:
Comment:
1 row in set (0.00 sec)
There it is. Notice that Max_data_length is 4GB. Let's fix that.
mysql> alter table weather max_rows = 200000000000 avg_row_length = 50;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show table status like 'weather' \G
*************************** 1. row ***************************
Name: weather
Type: MyISAM
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 1099511627775
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2003-06-17 13:12:49
Update_time: 2003-06-17 13:12:49
Check_time: NULL
Create_options: max_rows=4294967295 avg_row_length=50
Comment:
1 row in set (0.00 sec)
Excellent. Now MySQL will let us store a lot more data in that table.
Too Many Rows?
Now, the astute reader will notice the Create_options specify a limit of 4.2 billion rows. That's right, there's still a limit, but now it's a limit on number of rows, not the size of the table. Even if you have a table with rows that are 10 times as large, you're still limited to roughly 4.2 billion rows.
Why?
Again, this is 32-bit hardware. If you move to a 64-bit system, the limit is raised accordingly.
Posted by jzawodn at June 17, 2003 01:42 PM
this, of course, assumes you're running some kind of OS that doesn't have max-file-size limits or max-filesystem-size limits. Some versions of solaris have their own file size limits that this won't fix...
chuq
(who has a table that takes 14 hours to rebuild. Encourages you to not let the database crash)
Does it make sense to use MySQL if you have to store billions of rows? Would performance be sufficient, and are there any other limits that could be a constraint? And - what's the size of the biggest database at Yahoo >:)
I gave production tables with 125.0000.000 rows
whats too big?
I'm not sure if this is a bug that was already fixed in newer versions of MySQL, but we actually found out at work that using mysqldump on a table that was changed in order to support more than 4 gigs of data, that the dump file didn't have the ALTER TABLE lines in them.
Which basically means that if you are dumping a huge database into a backup file, and you have this table with more than 4 gigs in it, then when you try to restore from the backup, you will get the 'The table is full' error message :)
Pretty annoying but we cannot upgrade to a newer version to check on this problem. Anyway, just thought it would be nice to warn people here.
--Joao
I figured that since we are using a 3 year old version of MySQL, that it was already fixed in newer releases.
So no, I never reported the bug. But since you were talking about it, I thought that you know that this feature does work properly with mysqldump-based backups.
--Joao
Sorry, I need this explained.
I'm not a real DBA, I just use MySQL to dump lots of network traffic accounting, which means I've tables with over 4GB already. Now, unless I did it in my sleep, I never did the alter table thing, but my Max_data_length is over 20GB.
Even if I create a new table, I get this large Max_data_length:
mysql> create table wiggle (woggle int(1));
Query OK, 0 rows affected (0.00 sec)
mysql> show table status like 'wiggle' \G
*************************** 1. row ***************************
Name: wiggle
Type: MyISAM
Row_format: Fixed
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 21474836479
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2003-06-18 10:28:19
Update_time: 2003-06-18 10:28:19
Check_time: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql>
I'm running 4.0.2-alpha on Redhat.
So, I'm wondering what changes the default size of tables.
The reason that wiggle can have so much is because it is a fixed width table, not a dynamic one. Jeremy did mention it at the very beginning, you just need to know what the difference is. A variable width table is any that have varchar, text, or blobs in them.
Forgive me... I'm not a real DBA either. Just a happy user of MySQL.
I have not checked, but I know I have several enormous tables, several of which are close to the 4GB point. I have the MySQL Big_table session variable turned on. I was under the impression that would allow MyISAM tables to exceed 4GB. Yes? No?
The person that was saying that ALTER TABLE lines aren't in the dump file: they don't need to be. Any table options such as MAX_ROWS and/or AVG_ROW_LENGTH will be included in the CREATE TABLE statement (assuming they're there and you didn't just dump the data (-t/--no-create-info mysqldump option)). Because remember, anything done with ALTER TABLE is like it was done with CREATE. ;-)
BTW, can someone tell me what the file size limit is with current versions of Linux? I've heard it's 2G without a patch or whatever, which is pathetic. Or is it 4G? Or more? I mean, even FAT32's limit is 4G. And NTFS is like virtually unlimited I think.
So it'd be nice to hear the exact limits of modern Linux and what, if anything, determines them.
Your page helped me a lot when the database log for seventeenorbust.com went down due to the 4gig limit. Thanks Jeremy.
Cheers,
Louie
Too Many Rows?
Now, the astute reader will notice the Create_options specify a limit of 4.2 billion rows. That's right, there's still a limit, but now it's a limit on number of rows, not the size of the table. Even if you have a table with rows that are 10 times as large, you're still limited to roughly 4.2 billion rows.
Hi Jeremy
I tried to see if one can increase the limit to more than 4.2B rows on a 64 bit machine but I
couldn't. Is there any settable parameter other
than MAX_ROWS that will make mysql go beyond the
4.2B rows limit ? Or is there any simple modification to mysql source that will do the trick ?
Hi Jeremy,
I was wondering when does memeory allocation takes place for the tables? If I use the create options or the alter table statements to change the max_data_length of the table to some exaggerated size(but less than 4.2 billion records), does it try to allocate the memory for that huge table?
Also, another question out of curiosity, how does the database handle the memory address once we go beyound the 4GB size of table?
Thanks.
Carmen
Thanks for this Tip, this saved my Life today!!!
Great to find guys with such good knowledge.
Kind Regards
Torsten
What are you pumping to those tables to get higher than 4.2 B rows
I just had the experience of trying to load a data file of 41 million rows using LOAD DATA INFLIE. MySQL would stop at about 5 million rows. Which, is well below the 4gb limit. However, after I altered the table to handle a larger data-set LOAD DATA INFILE worked perfectly.
I can only theorize that while LOAD DATA INFILE is executing it is constantly calculating the AVG_ROW_LEN * the NUM_ROWS in the file. And stops when it knows it won't fit.
One thing Jeremy forgot to mention in his article was that when you do an ALTER TABLE command, it not only changes the table structure, it also DELETES ALL ROWS from your table. Therefore, if you do not have a good backup, your table has just been wiped out.
This should be added to the article since it is dangerous for anyone who doesn't know that bit of information.
I would like to know if there is any limit on the size of mysql database? how big it can grow?
Also is there any thing which talks about how long it takes to perform backup of the mysql database based on the size of the database
Any reply would be appreciated.
Thanks,
Vinay
Thanks for the comment by Kris, though unfortunately it was too late, the rows went 'poof'. Not all alter tables commands delete rows, right? I've added fields / indices to tables with alter table statements to no visible effect on existing data... . Now let's see if the db admin really does the backing up he claims to.
What about if I don't get the name of the table in the errormessage like:
java.sql.SQLException: The table '' is full
I've checked datalength on all my tables but none is near max_data_length...
Any ideas?
BR /Kenneth
This is no longer required for mysql 5.0.6 and greater.
Note this minor change, coming from:
http://dev.mysql.com/doc/refman/5.0/en/news-5-0-6.html
Changed default value of myisam_data_pointer_size from 4 to 6. This allows us to avoid table is full errors for most cases.
I came across this problem aswell, because I store files in a table (yes, and no comments about you dont think they should not be in the database).
So I altered my table to contain 1T instead of 4G, this worked perfectly, I could now store more than 4G.
Now came the problem, it now takes forever to extract from the table, I have 1477 row in the table, and the size of the table is around 6.5G.
this sql line works like a charm:
"SELECT fileId FROM articlefile"
My guess is that it takes around 5-10 milliseconds.
But if I do one of these sql lines
"SELECT fileId, articleId, filename FROM articlefile"
"SELECT fileId, articleId FROM articlefile"
it then takes around 5 minuttes, which doesn't make sense to me.
Table Info
fileId, bigint, auto increment, primary key
articleid, bigint
filename, varchar, 255
contentType, varchar, 45
fileData, longblob
summary, text
Any help is much appreciated
Thanks for this post Jeremy, it certainly helped better than the MySQL Docs :)
for those who wonder the max number of rows in mysql.
I'm running mysql-4.1.16-r30 + gentoo-linux on Opteron252.
Now having 120270382 (120M) rows.
run into 'table full error'. now I try:
ALTER TABLE xyz MAX_ROWS=102003004005
but alter takes a while...
- update -
tried to extend the MAX_ROWS for MyIsam with:
"ALTER TABLE tanimoto MAX_ROWS=180000111000" and realized, that the value of 180,000,111,000 caused a (smaller) maximum rowsize of only: 3,906,451,864. A higher value (280,...) caused the real value (used/shown by the db) to decrease again -> seems to be a bug.
I need the table vor statistical data evaluation (only SELECTS) - would innodb be a better choice? or an upgrade to version 5 (might have better 64bit support?)
(running mysql-4.1.16-r30 + gentoo-linux on Opteron252 + 6GB Ram)
...now used mysql-5.0.18-r30
ALTER TABLE tbl_name MAX_ROWS=4294967295 ist the maximum which is accepted my MyIsam tables.
(...but 4.3 billion rows is quite a lot ;-)
last statement about mysql:
had about 470 000 000 rows and about 45GB data files (myisam) when db crashed. now showing still 45GB but only 37000 rows (myisamchk produced nothing usefull)
this means a loss of 4 days of simulation data.
...now switching to postgres.
For these large size tables, what sort of server spec is required for fast processing?
To ensure that the alter table process does "repair by sorting" (as opposed to the much slower "repair by key cache"), add the following to /etc/my.cnf
# /etc/my.cnf
myisam_max_sort_file_size=150G
myisam_max_extra_sort_file_size=150G
Now, the astute reader will notice the Create_options specify a limit of 4.2 billion rows. That's right, there's still a limit, but now it's a limit on number of rows, not the size of the table. Even if you have a table with rows that are 10 times as large, you're still limited to roughly 4.2 billion rows.
So, would that mean if I'm storing rows that contain large BLOBs (~0.5MB each), I can change MAX_ROWS to 8000 to double storage size?! And what should AVG_ROW_LENGTH be?
Thanks for putting this all together
I have a highish transaction table, that creates lots of rows, but subsequently deletes the rows 15minutes after they are created. Monitoring the momory size, creating then deleting, the memory usage still grows? Why does that happen? Will it continue to happen? Or will some 'cache' be removed, after some limit?
For the record most default installs of recent Linux support files up to 4GiB. However you can use the 'RAID' option when creating tables to get much more than that. (see: http://www.devshed.com/c/a/MySQL/Storage-Engine-Table-Types/12/ as well as the create table syntax: http://dev.mysql.com/doc/refman/4.1/en/create-table.html).
Unfortuantly you'll have to drop the table and recreate it, so it might not be of much use.
I've got a large table, and my max_data_size is big (much more than 4GB).
When the table exceeds 4GB, mysql goes crazy. It just ignores the first 4GB of data.
While queries now take a lot of time (several minutes for SELECT COUNT(*) FROM table), they ignore most of the data (the above query returns 3000, while the table contains over 100000 rows).
Did any of you encountered similar problems?
Regarding Kris' and Rich's comments (about the data being deleted), while it's always a good idea to backup your database before you make any major changes, I did not see data loss when I ran the 'alter table xxx max_rows ...' statement. I ran the statement on mysql 4.1.7-max-log.
hey All...plz let me know how many entries can their in a single table of database in Mysql-5.0..thanks in advance..
regards
Deshwal sanjay
# sanjay D said:
hey All...plz let me know how many entries can their in a single table of database in Mysql-5.0..thanks in advance..
regards
Deshwal sanjay
on October 12, 2007 12:39 AM
<<<
i dunno, but i'm sitting here and adding one by one, telling you when i'm done.
Hi,
I had about 38 lakh records in my table to which occupied 4GB of data storage. In order to increase the storage limit I ran an alter command (max_rows = 100000000 avg_row_length = 1115) to increase the storage. 1/4 of my data got deleted. Any particular reason for mysql 4.0 to delete the any random rows.
I have MySQL 5.1
mysqladmin -u root -p variables sad, what myisam_data_pointer_size= 6 bytes.
But MAX_ROWS=4294967295 all tyhe same.
I think MAX_ROWS, if pointer size = 6 bytes, must have 48^2-1.
@Bor:
Your math is a bit wrong here. If we're using a MAX_ROWS pointer size of 6 bytes, it would be 2^48 - 1, not 48^2. Though I'm confident that you had the right idea in mind.
Of course with 6 bytes, we'd have about 281 trillion rows.
Nice unique post, thanks for clearing all doubts...
Thank you very much for your help! I was almost formating a Debian Sarge install thinking that culprit was file system limitations but I thank this limit awful at a ext3 file system. Now, reading your text, I discovered the solution.
Thanks for your several posts on MySql database. However,just want to know whether MySql would be a good backend for a cargo management system running on a Windows 2000 system. The volume of transactions are expected to be quite high, so in no time 4GB would be exhausted and dropping the tables wont be an option then. Do I go for another database like Microsoft SQL Server or Oracle? Cost is a constraint though.
Thanks
I have a problem, basically i want to build a forum for students where they can share ideas. i want it to make on phpBB and joomla, so i will be using mysql5.0, i want to ask how many users it can handle without crushing down. i am planning not to store BLOB information in the dadabase.
i will be using Windows(32 bit) Ntfs.
please help me out.
Thanks in advance.
Hello Jeremy,
does Mysql Archive Storage Engine have similar limitation inter of size of table or number of rows?
Now that most intel, and AMD processors both support x86_64 and people have access to windows 7 x64 and ubuntu x86_64 the hardware and OS are easy enough to get a hold of.
(note that 1EB is 2^60 bytes)
64-bit pointers can address up to 16 EB
NTFS has a max file size of 16 EB minus 1K
Ext4 has a max file size of 1TB (2^40)
so if you use one of the above file systems on a 64 bit system your database can be much, much larger than 4 GB now.
I wasn't looking for this -- I stumpbled upon it. But I had to say that this was interesting and informative. So, thank you.
You've also explained it clearly.
