The Ultimate Guide to High Availability with MariaDB

MyISAM Server System Variables

You are viewing an old version of this article. View the current version here.

Contents

  1. myisam_block_size
  2. myisam_data_pointer_size
  3. myisam_max_extra_sort_file_size
  4. myisam_max_sort_file_size
  5. myisam_mmap_size
  6. myisam_recover_options
  7. myisam_repair_threads
  8. myisam_sort_buffer_size
  9. myisam_stats_method
  10. myisam_use_mmap

See Server System Variables for a complete list of system variables and instructions on setting them.

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

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 to 7

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 to 4294967295
  • Range - 64-bit: 7 to 18446744073709547520
  • 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 to OFF. 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. Previously called myisam_recover, which is still a valid synonym.
    • OFF: The default as of MariaDB 5.5.3, 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-datetime.BAK.
    • BACKUP_ALL: Same as BACKUP but also backs up the .MYI index file. 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[=name]
  • Scope: Global
  • Dynamic: No
  • Data Type: enumeration
  • Default Value: OFF
  • Valid Values: OFF, DEFAULT, BACKUP, BACKUP_ALL, FORCE or QUICK

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 to 4294967295
  • Range - 64-bit: 1 to 18446744073709547520

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: 8388608
  • Range - 32 bit: 4 to 4294967295
  • Range - 64 bit: 4 to 18446744073709547520

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 to nulls_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 to nulls_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.