Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

My MyISAM table consumed most of my disk space. I deleted a significant portion of the table, but the optimize command (to clean out the deleted data) does not have sufficient space.

Is there any path around this? One idea is that as I add new data, it might consume from this extra space, rather than add to the disk usage, but I don't know how MySQL implements this.

The DB use is around 70GB and the available disk space is about 72GB. This is an isolated machine to which I have no physical access, hence I cannot add space myself.

share|improve this question

migrated from serverfault.com Jan 30 '12 at 11:10

This question came from our site for professional system and network administrators.

    
What size table are we talking about, and how big is your HDD? –  Derek Downey Jan 30 '12 at 15:17
    
edited to answer –  muckabout Jan 30 '12 at 16:04

1 Answer 1

There is a user comment on the OPTIMIZE TABLE documentation page that had the same situation as you. His solution was to copy the table somewhere else, optimize, then copy back. This required offline time.

Unfortunately, my solution would also require offline time as well, for 70GB worth of data. Something like this:

  • mysqldump table from another machine with enough space
  • DROP TABLE foo
  • Load table from the mysqldump file: mysql -hXXX.XX.XX.XXX -u... -p... < foo.sql

The downtime will be from the DROP TABLE step until the load table from a remote host of 70GB of data can be completed.

One potential bright spot is that new INSERTS will re-use the space created from DELETES:

Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions.

But this is what causes fragmentation to begin with. So you will need to make sure your tablespace is optimized frequently to prevent this in the future.

I just want to throw out there that to run an optimize query, you're always going to need at least the database size + the size of the table you're optimizing available on disk.

As an example, this means for a database size of 30GB and the table your optimizing is 28GB, you will need at minimum a harddrive that can fit 58GB of data space. This is because MySQL rewrites a new temporary file and then replaces the old one.

I say at least, because there's always other things going on. You'll need space for other programs, MySQL might be creating on-disk temporary tables during the operation, etc.

share|improve this answer
    
Ah, I was typing my answer still and you beat me to it. +1 !!! I was thinking the same thing: Reloading from external mysqldump. –  RolandoMySQLDBA Jan 30 '12 at 16:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.