Optimizing Non-
SELECT
Statements
688
• For
REPAIR TABLE
, the same principle applies, but because the repair is done by the server,
you set server system variables instead of
myisamchk
variables. Also, In addition to setting
memory-allocation variables, increasing the
myisam_max_sort_file_size
[474]
system variable
increases the likelihood that the repair will use the faster filesort method and avoid the slower repair
by key cache method. Set the variable to the maximum file size for your system, after checking to
be sure that there is enough free space to hold a copy of the table files. The free space must be
available in the file system containing the original table files.
Suppose that a
myisamchk
table-repair operation is done using the following options to set its
memory-allocation variables:
--key_buffer_size=128M --sort_buffer_size=256M
--read_buffer_size=64M --write_buffer_size=64M
Some of those
myisamchk
variables correspond to server system variables:
myisamchk
Variable
System Variable
key_buffer_size
key_buffer_size
[458]
sort_buffer_size
myisam_sort_buffer_size
[476]
read_buffer_size
read_buffer_size
[488]
write_buffer_size
none
Each of the server system variables can be set at runtime, and some of them
(
myisam_sort_buffer_size
[476]
,
read_buffer_size
[488]
) have a session value in addition
to a global value. Setting a session value limits the effect of the change to your current session
and does not affect other users. Changing a global-only variable (
key_buffer_size
[458]
,
myisam_max_sort_file_size
[474]
) affects other users as well. For
key_buffer_size
[458]
,
you must take into account that the buffer is shared with those users. For example, if you set
the
myisamchk
key_buffer_size
variable to 128MB, you could set the corresponding
key_buffer_size
[458]
system variable larger than that (if it is not already set larger), to allow for
key buffer use by activity in other sessions. However, changing the global key buffer size invalidates
the buffer, causing increased disk I/O and slowdown for other sessions. An alternative that avoids this
problem is to use a separate key cache, assign to it the indexes from the table to be repaired, and
deallocate it when the repair is complete. See
Section 8.6.1.2, “Multiple Key Caches”
.
Based on the preceding remarks, a
REPAIR TABLE
operation can be done as follows to use settings
similar to the
myisamchk
command. Here a separate 128MB key buffer is allocated and the file
system is assumed to permit a file size of at least 100GB.
SET SESSION myisam_sort_buffer_size = 256*1024*1024;
SET SESSION read_buffer_size = 64*1024*1024;
SET GLOBAL myisam_max_sort_file_size = 100*1024*1024*1024;
SET GLOBAL repair_cache.key_buffer_size = 128*1024*1024;
CACHE INDEX
tbl_name
IN repair_cache;
LOAD INDEX INTO CACHE
tbl_name
;
REPAIR TABLE
tbl_name
;
SET GLOBAL repair_cache.key_buffer_size = 0;
If you intend to change a global variable but want to do so only for the duration of a
REPAIR TABLE
operation to minimally affect other users, save its value in a user variable and restore it afterward. For
example:
SET @old_myisam_sort_buffer_size = @@global.myisam_max_sort_file_size;
SET GLOBAL myisam_max_sort_file_size = 100*1024*1024*1024;
REPAIR TABLE tbl_name ;
SET GLOBAL myisam_max_sort_file_size = @old_myisam_max_sort_file_size;
The system variables that affect
REPAIR TABLE
can be set globally at server startup if you want the
values to be in effect by default. For example, add these lines to the server
my.cnf
file:
[mysqld]
myisam_sort_buffer_size=256M
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
