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
