MySQL Bugs: #53636: Docs: please add options affecting REPAIR that correspond with myisamchk options

Bug #53636	Docs: please add options affecting REPAIR that correspond with myisamchk options
Submitted:	14 May 2010 2:49	Modified:	20 Jul 2010 18:49
Reporter:	Roel Van de Paar	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
http://dev.mysql.com/doc/refman/5.1/en/myisamchk.html
http://dev.mysql.com/doc/refman/5.1/en/repair-table.html

Both could use an addition to include the options which affect REPAIR. These options are documented when using myisamchk (as they are command line options), but similar options (mysql session or global options) are not documented for REPAIR.

How to repeat:
Will add info soon.

An example used for a customer which can be reworded to suit the manual format: 

==========
Let's assume the following myisamchk options:
--key_buffer_size=128M --sort_buffer_size=256M --read_buffer_size=64M --write_buffer_size=64M

The first thing to look at is the equivalents:

Myisamchk variable = Server variable (Scope?) (Dynamic?) [ Documentation at ]
key_buffer_size = key_buffer_size (Global only) (Yes) [ http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_key_buffer_size ]
sort_buffer_size = myisam_sort_buffer_size (Glob/Session) (Yes) [ http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_myisam_sort_buf... ]
read_buffer_size = read_buffer_size (Glob/Session) (Yes) [ http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_read_buffer_siz... ]
write_buffer_size = None

Besides this, the key to avoiding a repair by keycache is to set myisam_max_sort_file_size to the size of the free space on the machine.

myisam_max_sort_file_size is documented here:
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_myisam_max_sort...

This variable is global only in scope and dynamic.

So, to do the above myisamchk in a mysql session using REPAIR instead, one would execute:

mysql> SET SESSION myisam_sort_buffer_size = 256*1024*1024; /* Note that you cannot use M(egabyte) or G(igabyte) notations here like in the my.cnf */
mysql> SET SESSION read_buffer_size = 64*1024*1024;

Then you would check the available free space and set:

mysql> SET GLOBAL myisam_max_sort_file_size = 100*1024*1024*1024; /* An example of 100Gb free space */

And you would set the key buffer sufficiently large (if it is not set so already), keeping in mind available memory:

mysql> SET GLOBAL key_buffer_size = 1*1024*1024*1024; /* An example of 1Gb Key Buffer */

To ensure it is not set sufficiently large already you can check the current setting as follows:

mysql> SHOW GLOBAL VARIABLES LIKE 'key_buffer_size';

To set these same options in the my.cnf file, simply include them in your my.cnf's file [mysqld] section:

[mysqld]
myisam_sort_buffer_size = 256M;
key_buffer_size = 1G;
myisam_max_sort_file_size = 100G;

Note that I did not include read_buffer_size. The reason for this (as per the documentation) is that this is a setting allocated per-thread. Setting the same would quickly exhaust your severs memory if you have several/many connections.
==========

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.