MyISAM System Variables
You are viewing an old version of this article. View
the current version here.
Contents
- key_buffer_size
- key_cache_age_threshold
- key_cache_block_size
- key_cache_division_limit
- key_cache_file_hash_size
- key_cache_segments
- myisam_block_size
- myisam_data_pointer_size
- myisam_max_extra_sort_file_size
- myisam_max_sort_file_size
- myisam_mmap_size
- myisam_recover_options
- myisam_repair_threads
- myisam_sort_buffer_size
- myisam_stats_method
- myisam_use_mmap
See Server System Variables for a complete list of system variables and instructions on setting them.
See also the Full list of MariaDB options, system and status variables.
key_buffer_size
- Description: Size of the buffer for the index blocks used by MyISAM tables and shared for all threads. See Optimizing key_buffer_size for more on selecting the best value.
- Commandline:
--key-buffer-size=#
- Scope: Global
- Dynamic: Yes
- Data Type:
numeric
- Default Value:
134217728
- Range:
8
upwards (upper limit determined by operating system per process limit)
key_cache_age_threshold
- Description: The lower the setting, the more quickly buffers move from the hot key cache sublist to the warm sublist.
- Commandline:
--key-cache-age-threshold=#
- Scope: Global
- Dynamic: Yes
- Data Type:
numeric
- Default Value:
300
- Range:
100
to4294967295
key_cache_block_size
- Description: MyISAM key cache block size in bytes .
- Commandline:
--key-cache-block-size=#
- Scope: Global
- Dynamic: Yes
- Data Type:
numeric
- Default Value:
1024
- Range:
512
to16384
key_cache_division_limit
- Description: Percentage to use for the warm key cache buffer list (the remainder is allocated between the hot and cold caches).
- Commandline:
--key-cache-division-limit=#
- Scope: Global
- Dynamic: Yes
- Data Type:
numeric
- Default Value:
100
- Range:
1
to100
key_cache_file_hash_size
- Description: Number of hash buckets for open and changed files. If you have many MyISAM files open you should increase this for faster flushing of changes. A good value is probably 1/10th of the number of possible open MyISAM files.
- Commandline:
--key-cache-file-hash-size=#
- Scope: Global
- Dynamic: Yes
- Data Type:
numeric
- Default Value:
512
- Range:
128
to16384
- Introduced:
MariaDB 10.0.13
key_cache_segments
- Description: The number of segments in a key cache. See Segmented Key Cache.
- Commandline:
--key-cache-segments=#
- Scope: Global
- Dynamic: Yes
- Type: numeric
- Default Value:
0
(non-segmented) - Range:
0
to64
- Introduced: MariaDB 5.2.0
myisam_block_size
- Description: Block size to be used for MyISAM index pages.
- Commandline:
--myisam-block-size=#
- Scope: Global
- Dynamic: No
- Data Type:
numeric
- Default Value:
1024
- Introduced:
MariaDB 5.2.6
myisam_data_pointer_size
- Description: Size in bytes of the default pointer, used in a MyISAM CREATE TABLE with no MAX_ROWS option.
- Commandline:
--myisam-data-pointer-size=#
- Scope: Global
- Dynamic: Yes
- Data Type:
numeric
- Default Value:
6
- Range:
2
to7
myisam_max_extra_sort_file_size
- Description: Removed in MySQL 5.0.6, was used as a way to force long character keys in large tables to use the key cache method.
- Removed:
MySQL 5.0.6
myisam_max_sort_file_size
- Description: Maximum size in bytes of the temporary file used while recreating a MyISAM index. If the this size is exceeded, the slower process of using the key cache is done instead.
- Commandline:
--myisam-max-sort-file-size=#
- Scope: Global
- Dynamic: Yes
- Data Type:
numeric
- Default Value - 32 bit:
2147483648
- Default Value - 64 bit:
9223372036854775807
myisam_mmap_size
- Description: Maximum memory in bytes that can be used for memory mapping compressed MyISAM files. Too high a value may result in swapping if there are many compressed MyISAM tables.
- Commandline:
--myisam-mmap-size=#
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Default Value - 32 bit:
4294967295
- Default Value - 64 bit:
18446744073709547520
- Range - 32-bit:
7
to4294967295
- Range - 64-bit:
7
to18446744073709547520
- Introduced:
MariaDB/MySQL 5.5.1
myisam_recover_options
- Description: MyISAM recovery mode. Multiple options can be selected, comma-delimited. Using no argument is equivalent to specifying
DEFAULT
, while specifying "" is equivalent toOFF
. If enabled each time the server opens a MyISAM table, it checks whether it has been marked as crashed, or wasn't closed properly. If so, mysqld will run a check and then attempt to repair the table, writing to the error log beforehand.- OFF: The default, no recovery.
- BACKUP: If the data file is changed while recovering, saves a backup of the .MYD data file. t.MYD will be saved as t.MYD-datetime.BAK.
- BACKUP_ALL: Same as
BACKUP
but also backs up the .MYI index file. t.MYI will be saved as t.MYI-datetime.BAK. Added in MariaDB 5.1.51. - DEFAULT: Recovers without backing up, forcing, or quick checking.
- FORCE: Runs the recovery even if it determines that more than one row from the data file will be lost.
- QUICK: Does not check rows in the table if there are no delete blocks.
- Commandline:
--myisam-recover-options[=name]
- Scope: Global
- Dynamic: No
- Data Type:
enumeration
- Default Value:
BACKUP,QUICK
(>= MariaDB 10.2.4)OFF
(<= MariaDB 10.2.3)
- Valid Values:
OFF
,DEFAULT
,BACKUP
,BACKUP_ALL
,FORCE
orQUICK
myisam_repair_threads
- Description: If set to more than
1
, the default, MyISAM table indexes each have their own thread during repair and sorting. Increasing from the default will usually result in faster repair, but will use more CPU and memory. - Commandline:
--myisam-repair-threads=#
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Default Value:
1
- Range - 32-bit:
1
to4294967295
- Range - 64-bit:
1
to18446744073709547520
myisam_sort_buffer_size
- Description: Size in bytes of the buffer allocated when creating or sorting indexes on a MyISAM table.
- Commandline:
--myisam-sort-buffer-size=#
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Default Value:
134217720
(128MB),8388608
(8MB - before MariaDB 10.0.3) - Range:
4096
to18446744073709547520
myisam_stats_method
- Description: Determines how NULLs are treated for MyISAM index statistics purposes. If set to
nulls_equal
, the default, all NULL index values are treated as a single group. This is usually fine, but if you have large numbers of NULLs the average group size is slanted higher, and the optimizer may miss using the index for ref accesses when it would be useful. If set tonulls_unequal
, the opposite approach is taken, with each NULL forming its own group of one. Conversely, the average group size is slanted lower, and the optimizer may use the index for ref accesses when not suitable. Setting tonulls_ignored
ignores NULLs altogether from index group calculations. See also Index Statistics, aria_stats_method, innodb_stats_method. - Commandline:
--myisam-stats-method=name
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
enumeration
- Default Value:
nulls_equal
- Valid Values:
nulls_equal
,nulls_unequal
,nulls_ignored
myisam_use_mmap
- Description: If set to
1
(0 is default), memory mapping will be used to reading and writing MyISAM tables. - Commandline:
--myisam-use-mmap
- Scope: Global
- Dynamic: Yes
- Data Type:
boolean
- Default Value:
OFF
Comments
Comments loading...
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.