How much space is needed to reclaim space from a table in MyISAM

Question

MariaDB 10.x | MyISAM

My question is very similar to this one but regarding MyISAM.

I have a DB server that's running very low on disk space (2.5 GB free) and a MyISAM database with individual tables that are 3 GB and 8 GB in size. I've successfully deleted entries from the tables so I'm confident that some of the space is white space but I'm not sure how much.

I've heard conflicting statements from folks regarding what happens when running OPTIMIZE TABLE. Some people say it doesn't require any extra disk space... others tell me it will create a new table and copy individual rows into the new table... and only AFTER that's complete will it begin reclaiming disk space.

Can someone please clarify?

Answer 1

As far as I can tell, the documentation is not very clear on the subject.

From my experience, I'd say it always rebuilds fresh table files for data and index.

Intuitively, from what I know about MyISAM storage engine, I'd say it should be possible to run a full data file in place optimisation if and only if the rows have constant length. That is if you don't use columns with dynamic lengths (VARCHAR, TEXT, etc.).

Regarding index file, rebalancing the trees might be possible in place, but at a higher cost (duration).