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: | |
Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
Version: | OS: | Linux (Linux) | |
Assigned to: | Paul DuBois | CPU Architecture: | Any |
[27 Nov 2006 7:17]
Yoshiaki Tajika
[27 Nov 2006 8:06]
Valeriy Kravchuk
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.
[27 Nov 2006 9:11]
Yoshiaki Tajika
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:
[27 Nov 2006 10:57]
Valeriy Kravchuk
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.
[18 Dec 2006 17:42]
Paul DuBois
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.