List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:October 22 2002 8:28pm
Subject:Re: How to index a large table?
View as plain text  
At 15:11 -0500 10/22/02, Chris Stoughton wrote:
>I have 760 GB free, not 375.

Perhaps, but error 136 still means "no more room in index file".

>
>bash-2.04$ df -h .
>Filesystem            Size  Used Avail Use% Mounted on
>/export/data/dp20.a   1.0T  302G  760G  29% /data/dp20.a
>bash-2.04$
>
>The .MYD file is 134 GBytes
>bash-2.04$ ls -lh targetTsObj*
>-rw-rw----    1 mysql    mysql        134G Oct 20 05:37 targetTsObj.MYD
>-rw-rw----    1 mysql    mysql        5.0k Oct 21 11:38 targetTsObj.MYI
>-rw-rw----    1 mysql    mysql         32k Oct 18 21:05 targetTsObj.frm
>
>so while the "create index" command is running, after the #sql* 
>files are the same size as the targetTsObj.* files, we still have 
>over 600 GB of disk space left.
>
>
>Jocelyn Fournier wrote:
>
>>Hi,
>>
>>In fact, I think you run out of disk space.
>>You MYD takes 135 GB.
>>You have 375 GB free
>>When MySQL try to add the index on your database, it copies the MYD and frm
>>under a #sql* name.
>>So again 135 GB are eaten again.
>>So it remains 240 GB to build the index file, so it's possible you run out
>>of free space during the MYI generation ? (what does df report ? )
>>
>>Regards,
>>  Jocelyn
>>----- Original Message -----
>>From: "Chris Stoughton" <stoughto@stripped>
>>To: <mysql@stripped>
>>Sent: Tuesday, October 22, 2002 9:49 PM
>>Subject: How to index a large table?
>>
>>
>>
>>>I have tried a few different variables to allow a "create index" command
>>>to finish successfully.
>>>
>>>1.  I set tmpdir to be a file system with ample space
>>>2.  I increased tmp_table_size
>>>3. I increases myisam_sort_buffer_size to 100M
>>>
>>>Here are the sizes of the database files:
>>>
>>>bash-2.04$ ls -l  targetTsObj*
>>>-rw-rw----    1 mysql    mysql    144173128578 Oct 20 05:37
>>>   
>>>
>>targetTsObj.MYD
>>
>>>-rw-rw----    1 mysql    mysql        5120 Oct 21 11:38 targetTsObj.MYI
>>>-rw-rw----    1 mysql    mysql       32750 Oct 18 21:05 targetTsObj.frm
>>>
>>>
>>>I continue to get this error:
>>>
>>>
>>>Database changed
>>>mysql>  create index targetTsObjobjId on targetTsObj (objId);
>>>ERROR 1034: 136 when fixing table
>>>mysql>

Thread
How to index a large table?Chris Stoughton22 Oct
  • Re: How to index a large table?Jocelyn Fournier23 Oct
  • Re: How to index a large table?Chris Stoughton23 Oct
    • Re: How to index a large table?Paul DuBois23 Oct
  • Re: How to index a large table?Chris Stoughton23 Oct
  • Re: How to index a large table?Ken Menzel23 Oct
Re: How to index a large table?stoughto23 Oct
  • Re: How to index a large table?Ken Menzel23 Oct