MySQL OPTIMIZE TABLE command help

[Dan Bowling]

I've just ran into a situation that I've never encountered before, and was hoping someone might give me some insight.

I have a server with 29 GB of disk space, and a database with 6.2 GB of data in it (6.1 gigs of the data is in a single table) that is currently using 22 GB of space. I need to perform an OPTIMIZE TABLE command on the offending table to return unused disk space.

The MySQL documentation has a comment in it that warns that this command needs at least [database + data of biggest table] storage space remaining to perform the optimization. In my case, that is 22GB + 22GB, which is much greater than the disk space available.

Is this accurate? Is my only alternative to back up the database, and optimize it on another machine, then re-create the database and restore from the backup?

Dan

[Tim Greiser]

Are you using MyISAM for a storage engine? It's going to be different if you are using InnoDB.

If you don't have the space to do a MyISAM OPTIMIZE you could lock the database, dump all the data, drop the database and re-create it from your dump.

Tim.
-
Sustainability - Inspiration - Innovation - Community
http://www.AskNature.org

--

You received this message because you are subscribed to the Google Groups "Missoula Web Discussion Group" group.
To post to this group, send email to missoula-web-d...@googlegroups.com.
To unsubscribe from this group, send email to missoula-web-discuss...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/missoula-web-discussion-group?hl=en.

[Dan Bowling]

Sorry, I am using MyISAM tables.

That suggestion sounds more reasonable than what I was thinking.

Dan

[Michael Loftis]

OPTIMIZE is purely engine specific. You *could* scp the data over,
OPTIMIZE, then scp it back, or as Tim suggests, dump, drop, import.
Remember at the database keeps indexes too. Use SHOW TABLE STATUS
to see if you've actually got free space or not.