MySQL Bugs: #24609: Doc: maximum size for a MyISAM table

Bug #24609	Doc: maximum size for a MyISAM table
Submitted:	27 Nov 2006 7:17	Modified:	18 Dec 2006 17:42
Reporter:	Yoshiaki Tajika (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:		OS:	Linux (Linux)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
You say in the reference manual:

> 13.1.5. CREATE TABLE Syntax
> 
> AVG_ROW_LENGTH
>
> ... When you create a MyISAM table, MySQL uses the product of the 
> MAX_ROWS and AVG_ROW_LENGTH options to decide how big the resulting 
> table is. If you don't specify either option, the maximum size for 
> a table is 65,536TB of data (4GB before MySQL 5.0.6). ...

No, without either option, max size is 256TB, because MyISAM internal 
pointer size is 6 bytes as default. When the pointer size becoms 7 bytes 
with appropriate AVG_ROW_LENGTH and MAX_ROWS, 65,536TB can be reached. 

How to repeat:
N/A.

Suggested fix:
Please correct manual desctription.

Thank you for a documentation request. Sorry, but documentation is correct (even if not to verbous or complete, as this size seems correct for fixed format only) in this part. You can just try to create:

CREATE TABLE tisam(c1 int auto_increment primary key, c2 char(100)) engine=MyISAM;

and then run:

show table status like 'tisam'\G

Note Max_data_length value!

Read http://dev.mysql.com/doc/internals/en/the-myi-file.html and/or sources for some details.

I tried, and got 29554872554618879 as Max_data_length
and 105 as Avg_row_length. 
So, even if the manual description is for case of fixed length,
65,536TB is incorrect value, isn't it?

mysql> insert into tisam values(1,1);
mysql> show table status like 'tisam'\G
*************************** 1. row ***************************
           Name: tisam
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 1
 Avg_row_length: 105
    Data_length: 105
Max_data_length: 29554872554618879
   Index_length: 2048
      Data_free: 0
 Auto_increment: 1
    Create_time: 2006-11-27 17:28:38
    Update_time: 2006-11-27 17:28:38
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment:

Yes, you are right. With 5.0.27 on Windows I've got the results I refered to in my previous email:

mysql> create table tisamt (c1 int auto_increment primary key, c2 char(100)) engine=MyISAM;
Query OK, 0 rows affected (0.14 sec)

mysql> show table status like 'tisamt'\G
*************************** 1. row ***************************
           Name: tisamt
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 85849867896750079
   Index_length: 1024
      Data_free: 0
 Auto_increment: 1
    Create_time: 2006-11-27 09:50:19
    Update_time: 2006-11-27 09:50:19
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.03 sec)

mysql> select (((85849867896750079/1024)/1024)/1024)/1024;
+---------------------------------------------+
| (((85849867896750079/1024)/1024)/1024)/1024 |
+---------------------------------------------+
|                      78079.9999999999990905 |
+---------------------------------------------+
1 row in set (0.00 sec)

But with 5.0.32-BK on Linux I have the same results as you:

mysql> drop table tisam;
Query OK, 0 rows affected (0.18 sec)

mysql> CREATE TABLE tisam(c1 int auto_increment primary key, c2 char(100))
    -> engine=MyISAM;
Query OK, 0 rows affected (0.03 sec)

mysql> show table status like 'tisam'\G
*************************** 1. row ***************************
           Name: tisam
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 29554872554618879
   Index_length: 1024
      Data_free: 0
 Auto_increment: 1
    Create_time: 2006-11-27 11:54:39
    Update_time: 2006-11-27 11:54:39
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.04 sec)

mysql> show variables like '%pointer%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| myisam_data_pointer_size | 6     |
+--------------------------+-------+
1 row in set (0.00 sec)

So, manual really needs clarification.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

The difference between 256TB and 65535TB is that the latter
is the maximum table size allowed by 7-byte row pointers,
whereas 256TB is the *default* maximum size allowed by
6-byte row pointers.  By default, row pointers are 6 bytes
so tables can grow to 256TB by default.  This should now
be clearer in the manual.