Re: Are tables limited to 2GB on Win NT?

[Michael Widenius]

Hi!

>>>>> "Steve" == Steve Ruby <stever@stripped> writes:

Steve> sinisa@stripped wrote:
>> 
>> Chris L. Smith writes:
>> > Just wondering if the maximum table size is 2GB on windoze NT?  I tried
>> > adding table options (3.23.5a) of avg_row_length, max_rows, and so on
>> > ... but I still get cut off at about 2GB.
>> > chris
>> >
>> >
>> >
>> 
>> On NT a maximum is 4 Gb, with MySQL 3.23 ...
>> 

Steve> Here are my final findings to clarify issue with 2gb table max on NT
Steve> I have this table:

Steve> CREATE TABLE big_test; (
Steve>   id bigint(20) NOT NULL auto_increment,
Steve>   data float(11,9),
Steve>   data2 bigint(20),
Steve>   addtime timestamp(14),
Steve>   bigthing longtext,
Steve>   PRIMARY KEY (id)
Steve> ) max_rows=2800000 avg_row_length=1500;

Steve> after my program halts inserting data the status looks like:
mysql> show table status\G
Steve> *************************** 1. row ***************************
Steve> Name: big_test
Steve> Type: MYISAM
Steve> Row_format: Dynamic
Steve> Rows: 1512312
Steve> Avg_row_length: 1420
Steve> Data_length: 2147483040
Steve> Max_data_length: 2147483647
Steve> Index_length: 27293696
Steve> Data_free: 0
Steve> Auto_increment: 1512313
Steve> Create_time: 1999-11-19 13:32:13
Steve> Update_time: 1999-11-19 14:36:48
Steve> Check_time: NULL
Steve> Create_options: max_rows=2800000 avg_row_length=1500
Steve> Comment:
Steve> 1 row in set (0.00 sec)


Steve> the manual says:
Steve> When you use a MyISAM table, MySQL uses the product of max_rows *
Steve> avg_row_length to decide how big the resulting table will be. If you
Steve> don't specify any of the above options, the maximum size for a
Steve>      table will be 4G

Steve> setting max_rows and avg_row_length didn't seem to do anything.

This is because 3.23.5 has still a 2G limit on NT (This is mainly
because I forgot to write a wrapper on NT for the lseeki64() system
call;  Sorry about that.)

Steve> What is the deal with Max_data_length? Little is mentioned of it in the
Steve> manual
Steve> it doesn't appear setable in create table.

It's automaticly set based on max_rows and avg_row_length (the later
is only of use if you have blob/text columns)

Steve> If I create a table in 3.23.5a (win32 on NT) with type=isam
Steve> max_data_length =4gb but if I create a myisam table it is
Steve> as shown above.

Regards,
Monty