List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:November 14 1999 9:27am
Subject:Re: LOAD DATA INFILE and >2GB
View as plain text  
>>>>> "Arun" == Arun Bhalla <abhalla@stripped> writes:

Arun> Tonu Samuel writes:
>> Arun Bhalla wrote:
>> >=20
>> > Hi, I'm using 3.23.5 on a Solaris 2.6 Sun 450.  Apparently I had been
>> > running 3.23.4 without bigfiles support because my 4GB table wasn't
>> > completely accessible, and when I was running ALTER TABLE, the table
>> > rebuild would croak at 2GB with "Table is full" errors.
>> 
>> Have you read the manual?:
>> 
>> http://mysql.mirror.ok.ee/Manual_chapter/manual_Problems.html#Full_table

Arun> Should that apply to ALTER TABLE, too?  See the section 18.19 on ALTER
Arun> TABLE.  It doesn't explicitly talk about temporary files.  Regardless,
Arun> my tmp_table_size limit is set to 12M, and the table was already at 2G,
Arun> so that should be irrelevant.  The server was plainly using the disk
Arun> for rebuilding the table, not virtual memory.

Arun> I tried ALTER TABLE again with 3.23.5, and it had a similar problem --
Arun> the table wouldn't grow beyond 2GB on the rebuild process, but the statement
Arun> wouldn't terminate either.  Again I had to use myisamchk to rebuild the
Arun> MyISAM file.

Hi!

Do you think you can do this one more time, but this time
do an

myisamchk -dvv table_name
before and after you run myisamchk

How did you create the table?  Did you use the:

MAX_ROWS=# option to tell MySQL that you want to have support for
really big tables?

Here is a test that shows that ALTER TABLE should be able to handle
this properly:

mysql> create table t1 (a char(255), b char(255)) MAX_ROWS=10000000;
Query OK, 0 rows affected (0.08 sec)

mysql> show table status like "t1";
+------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+---------+
| Name | Type   | Row_format | Rows | Avg_row_length | Data_length | Max_data_length |
Index_length | Data_free | Auto_increment | Create_time         | Update_time         |
Check_time | Create_options    | Comment |
+------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+---------+
| t1   | MYISAM | Fixed      |    0 |              0 |           0 |      8573157375 |    
    1024 |         0 |           NULL | 1999-11-14 11:23:15 | 1999-11-14 11:23:15 | NULL  
    | max_rows=10000000 |         |
+------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+---------+

This created a table that can be 8G with 8573157375/(255+255+1) =
16777216 rows

mysql> ALTER TABLE t1 add c int;
Query OK, 0 rows affected (0.37 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show table status like "t1";
+------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+---------+
| Name | Type   | Row_format | Rows | Avg_row_length | Data_length | Max_data_length |
Index_length | Data_free | Auto_increment | Create_time         | Update_time         |
Check_time | Create_options    | Comment |
+------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+---------+
| t1   | MYISAM | Fixed      |    0 |              0 |           0 |      8640266239 |    
    1024 |         0 |           NULL | 1999-11-14 11:26:31 | 1999-11-14 11:26:31 | NULL  
    | max_rows=10000000 |         |
+------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+---------+

Regards,
Monty
Thread
LOAD DATA INFILE and >2GBArun Bhalla25 Oct
  • Re: LOAD DATA INFILE and >2GBEd Carp26 Oct
    • Re: LOAD DATA INFILE and >2GBArun Bhalla26 Oct
  • Re: LOAD DATA INFILE and >2GBTonu Samuel26 Oct
    • Re: LOAD DATA INFILE and >2GBArun Bhalla27 Oct
      • Re: LOAD DATA INFILE and >2GBTonu Samuel27 Oct
      • Re: LOAD DATA INFILE and >2GBMichael Widenius14 Nov