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.

Compact will not recover space, because it really just defragments the data inside the existing data files, making the data more dense, more compact. In fact, a compact occasionally needs extra space to do its work and so can end up using up more space rather than reclaiming it.

Because a repair rewrites the data files from scratch, it reclaims the "wasted" space and recovers disk space.

Besides bringing the secondaries down and running a repair there as you mention, another option is to delete the data files from a secondary and re-sync them from the primary from scratch. This has a similar effect to a repair, because in both cases the data files are re-written, the difference being that you don't need ~2x the disk space because you wipe out the originals and pull the data from the primary.

Once you have done the first secondary, generally the quickest way to do the second would be to snapshot (with journaling enabled) or fsync and lock the secondary and just to a file based copy to seed the other secondary.

Your final option assuming you have run the repair already on the primary, which is really just a variation, would be to snapshot the primary (or fsync and lock) and use its repaired files as a seed to populate a secondary.

These methods are covered in more detail as part of the Backup docs:

http://www.mongodb.org/display/DOCS/Backups#Backups-Methods