MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Brent Baisley Date:July 19 2004 6:02pm
Subject:Re: Optimize problem on BIG table
View as plain text  
When you delete records, space is freed up within the table, not from 
the disk. Just like when you delete a file, it's not deleted, the space 
is just marked as available. So technically you do now have space in 
your table and you may be able to do inserts. I'm pretty sure the 
documentation says you will need either 2x or 2.5x times the amount of 
space your table currently takes to optimize and compress.
Perhaps now is a good time to buy one of those 160GB disks or setup a 
RAID.

If you are really in a bind, you may try dropping your indexes, 
optimizing the table and creating the indexes again. Or export the 
data, truncate the table and import again. Both will most likely take 
quite some time.


On Jul 19, 2004, at 12:31 PM, Aman Raheja wrote:

> I have a HUGE table, with hundreds of millions of records.
>
> mytab.MYD 78GB
> mytab.MYI 16GB
>
> I decided to get rid of some old data because I can't INSERT into this
> table anymore, since the MAX for index file is reached.
>
> Following is what I did and what I got.
>
> mysql> DELETE FROM mytab WHERE modified < 20040601000000; OPTIMIZE 
> TABLE
> mytab;
> Query OK, 163592026 rows affected (4 hours 5 min 48.61 sec)
>
> mysql> OPTIMIZE TABLE mytab;
> +--------------+----------+----------+----------------------+
> | Table        | Op       | Msg_type |Msg_text              |
> +--------------+----------+----------+----------------------+
> | trlogs.mytab | optimize | error    | 28 when fixing table |
> | trlogs.mytab | optimize | status   | Operation failed     |
> +--------------+----------+----------+----------------------+
> 2 rows in set (27 min 52.21 sec)
> mysql>
>
> $ perror 28
> Error code  28:  No space left on device
>
> I agree that there is not much disk space.
>
> THE ISSUE: I want to reclaim the disk space that should be freed 
> because
> of the millions of records I deleted. I need disk space to reclaim disk
> space? Atleast that's what it seems to be.
>
> I would await suggestions from experts.
> Regards
> Aman
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    
> http://lists.mysql.com/mysql?unsub=1
>
>
-- 
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577

Thread
Optimize problem on BIG tableAman Raheja19 Jul
  • Re: Optimize problem on BIG tableBrent Baisley19 Jul
    • MySQL port questionYves19 Jul
  • Re: Optimize problem on BIG tableEgor Egorov20 Jul
Re: MySQL port questionAman Raheja19 Jul
Re: MySQL port questionaman20 Jul