MySQL Bugs: #43757: A function that returns the memory requirement of repairing a given MyISAM file

Bug #43757	A function that returns the memory requirement of repairing a given MyISAM file
Submitted:	19 Mar 2009 19:01	Modified:	4 Nov 2009 17:12
Reporter:	Serdar S. Kacar	Email Updates:
Status:	Open	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Contribution, myisam_sort_buffer_size, MyISAMchk, myisampack, repair table, sort_buffer_size

Description:
Estimating the memory required for MyISAM repair operations could be useful for some heavy tasks.
Unfortunately, this is not simple - there are too many parameters.

I have compiled a project (see FILES) but it has limitations and vulnerable to changes among versions. It is much better to have a built-in function (or statement).

Known memory requirement can be used to adjust
- myisam_sort_buffer_size for "REPAIR TABLE", or
- sort_buffer_size for "myisamchk -r/-p"

Since we have plenty of CPU cores and memory nowadays, such a utility could help in some environments where index building operations are heavy.

Note that even paralel repair becomes "production ready", some still might run paralel repair operations on multiple tables if average number of indexes per table is much less than the number of cores available.

How to repeat:
N/A

Suggested fix:
A function 
- that returns memory requirements for singlethreaded and multithreaded repairs
- that operates on tables even there is no .frm file yet (read myisampack --join)
- that optionally accepts fullpaths to table
like
  myisam_getrepairmem_singlethreaded(tbl_name)
  myisam_getrepairmem_multithreaded(tbl_name)

myisamidxinfo project - finds the memory requirement for myisam repairs

Attachment: myisamidxinfo.zip (application/x-zip-compressed, text), 73.03 KiB.

For the myisamidxinfo project, tablepath parameters are fullpaths to table except the file extension like :
- WIN : "D:/MySQL/MySQL Server 5.1/data/test/cp"
- X : "/tank/mysql/data/test/cp"

Thank you for a reasonable feature request and code contributed.

I wrote a similar code to help find min value needed for myisam_max_sort_file_size (chng all test and table0 for real vals)

Attachment: get_max_sort_file_size.sql (application/unknown, text), 2.27 KiB.

Hello Serdar - 

In order for us to continue the process of reviewing your contribution to MySQL - We need you to review and sign the Sun|MySQL contributor agreement (the "SCA")

The process is explained here: 
http://forge.mysql.com/wiki/Sun_Contributor_Agreement

Getting a signed/approved SCA on file will help us facilitate your contribution-- this one, and others in the future.

Thank you ! 

Liz Drachnik  - Program Manager - MySQL

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

MySQL does not need my agreement to use the contribution for several reasons :
1. I cannot claim the ownership of the code , because it is basically a trimmed version of MySQL code for a specific purpose. That is, its MySQL's, not mine.
2. Each file that I have added has the following notice,
"/* sskacar, BTT Ltd., 2009
    This work is free for anybody and any usage type WITHOUT ANY WARRANTY. */
 /* Some of the source files are adapted from the original MySQL source
    which are covered by the GPL license. */
 /* Base MySQL source code version : 5.1.31 */"
That is, even I did something that is not there already, it is all free.
3. Things that I did are very limited. It is basically an idea. Your implementation would probably be different (integrated in the code).