MySQL Bugs: #35620: Please make "Repair by sorting" the default method to rebuild indexes

Bug #35620	Please make "Repair by sorting" the default method to rebuild indexes
Submitted:	27 Mar 2008 17:55	Modified:	27 Mar 2008 17:59
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S4 (Feature request)
Version:	5.1.24, 6.0.5	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	myisam_max_sort_file_size, Repair by sorting, Repair with keycache
Triage:	Triaged: D5 (Feature request)

Description:
This is a request to make "repair by sorting" the default method to attempt to rebuild indexes for any table.

On tiny or small tables, repair with keycache can be fast. On any table
moderately sized, keycache repair can take days, weeks, or years.

Killing a repair table operation after finding out mysql chose keycache method will lead to corruption of the indexes, longer than planned downtime, and user frustrations.

The current default implementation is flawed:

o) small tables are repaired by sort
o) large tables are repaired with keycache. <---
o) repair table on a ~20GB table takes days. <---

Modern computers have enough disk space for repair by sort (at least for majority of tables). It seems *most* people prefer faster repairs at the expense of some disk space, by default.

I request the following:

All tables be repaired by sorting. If this fails due to
lack of disk space, then revert to keycache method, with an appropriate warning being issued if the repair succeeds. Fully log the decision process in the error log, stating when and why a repair by keycache was chosen, and for which table.

How to repeat:
this is a conceptual discussion

Suggested fix:
For now it will be good enough to set much higher default values for:

myisam_max_sort_file_size=268435456000
myisam_sort_buffer_size=134217728

In longer term, I hope there will be some better solution, such as determining at runtime whether there's enough free disk space or not, and picking the fastest method.

whats the status on this request?
this is essential...
can a quick, 'use at your own risk' patch please be provided for users?
or, can a sure-fire workaround/hack be suggested???

- the method of adjusting the server variables cannot always be trusted, and doesnt guarantee that the repair-by-sort method will be chosen...
mysql team, please respond...

related: bug #46945

Albeit 4 years later... I would like to request this too. At least at the 'warning' level. Been hit by trying to reindex 83m rows, and running out of disk space, as Mysql tried to use 'repair by keycache' as a defualt.

Looks like a Boolean round the wrong way....