WL#235: Safety checks for OPTIMIZE TABLE
This is an extract from correspondence between Peter Zaitsev and Monty Widenius, thread "Optimize Folks", May 2001. Currently MySQL waits until disk space is freed for myisam tables for all situations exept "repair" and ... "optimize". A table is marked as crashed and will not be repaired automatically even after disk space is freed. This situation is not really good, I think, because "repair" table is usually called in maintenace mode so you can handle the error, and "optimize" table may be used just to shrink tables in order to free some space, or (in my case) in order to make concurrent inserts work again. Also the idea of "optimize" table is that it should be a safe procedure; if it fails, it should leave the old copy in place. My (Peter Zaitsev's) suggessions are: 1) may be it would be nice to wait for the disk problem to be resolved, as the problem happens in other cases, or maybe it would be nice to make this proposal a configuration option, if Monty decides that it would be better not to eat all disk space for this case. This would be easy to do with an option in "repair" table. 2) "optimize" table may be changed a bit to make a copy of the table before running optimize on it. This will need a bit more disk space, and will be a bit slower, but will be much safer and will allow clients to read from the table during all the time while optimize is working. This, too, could be done with an option.
Copyright (c) 2000, 2023, Oracle Corporation and/or its affiliates. All rights reserved.