Skip to content

InnoDB System Variables

Davi Arnaut edited this page · 9 revisions

InnoDB-related command options and system variables. Boolean system variables can be enabled at server startup by naming them, or disabled by using a --skip- prefix.

Name Cmd-Line Option file System Var Status Var Var Scope Dynamic
innodb_anticipatory_flushing Yes Yes Yes No Global Yes
innodb_flush_dirty_pages_age Yes Yes Yes No Global Yes
innodb_flush_neighbors Yes Yes Yes No Global Yes
innodb_free_extents_reservation_factor Yes Yes Yes No Global Yes
innodb_index_fill_factor Yes Yes Yes No Global Yes
innodb_index_page_split_mode Yes Yes Yes No Both Yes
innodb_reserve_free_extents Yes Yes Yes No Global Yes
innodb_segment_fill_factor Yes Yes Yes No Global Yes

innodb_anticipatory_flushing

Specifies whether the master thread will flush dirty pages from the buffer pool if there is I/O bandwidth available for background tasks.

Command-Line Format --innodb_anticipatory_flushing
Option-File Format innodb_anticipatory_flushing
Option Sets Variable Yes, innodb_anticipatory_flushing
Variable Name innodb_anticipatory_flushing
Variable Scope Global
Dynamic Variable Yes
Type Boolean
Default ON
Valid Values ON, OFF

innodb_flush_dirty_pages_age

Minimum age (since the last flush) of pages to be flushed from the buffer pool if the number of dirty pages is below the maximum percentage.

Command-Line Format --innodb_flush_dirty_pages_age
Option-File Format innodb_flush_dirty_pages_age
Option Sets Variable Yes, innodb_flush_dirty_pages_age
Variable Name innodb_flush_dirty_pages_age
Variable Scope Global
Dynamic Variable Yes
Type Numeric
Default 0 (OFF)
Valid Values [0, 2^32]

innodb_flush_neighbors

Specifies whether flushing a page from the InnoDB buffer pool also flushes other dirty pages in the same extent.

Command-Line Format --innodb_flush_neighbors
Option-File Format innodb_flush_neighbors
Option Sets Variable Yes, innodb_flush_neighbors
Variable Name innodb_flush_neighbors
Variable Scope Global
Dynamic Variable Yes
Type Boolean
Default ON
Valid Values ON, OFF

innodb_free_extents_reservation_factor

Percentage of a space size to reserve for operations that may cause more space to be used. If the resulting amount of free space (in extents) is not available, the operation is not allowed to proceed.

Command-Line Format --innodb_free_extents_reservation_factor
Option-File Format innodb_free_extents_reservation_factor
Option Sets Variable Yes, innodb_free_extents_reservation_factor
Variable Name innodb_free_extents_reservation_factor
Variable Scope Global
Dynamic Variable Yes
Type Floating-point
Default 1.0
Valid Values [0.0, 100.0]

innodb_index_fill_factor

Specifies the percentage of how much to fill a leaf page when inserting data, the remaining space being reserved to accommodate row expansion.

Command-Line Format --innodb_index_fill_factor
Option-File Format innodb_index_fill_factor
Option Sets Variable Yes, innodb_index_fill_factor
Variable Name innodb_index_fill_factor
Variable Scope Global
Dynamic Variable Yes
Type Floating-point
Default 93.75
Valid Values [0.0, 100.0]

innodb_index_page_split_mode

Specifies the index page split behavior. Currently, symmetric is always required and will be added if not stated.

Command-Line Format --innodb_index_page_split_mode
Option-File Format innodb_index_page_split_mode
Option Sets Variable Yes, innodb_index_page_split_mode
Variable Name innodb_index_page_split_mode
Variable Scope Global, Session
Dynamic Variable Yes
Type Set
Default ""
Valid Values
  • symmetric

    Pages are always split roughly in the middle.

  • upper

    Split point is possibly at or near the insertion point if a pattern of sequential inserts is detected in the right-most page of the index tree.

  • lower

    Split point is possibly at or near the insertion point if a pattern of sequential inserts is detected in the left-most page of the index tree.

innodb_reserve_free_extents

Whether to reserve a certain percentage of a space size as free space for operations that might allocate several pages.

Command-Line Format --innodb_reserve_free_extents
Option-File Format innodb_reserve_free_extents
Option Sets Variable Yes, innodb_reserve_free_extents
Variable Name innodb_reserve_free_extents
Variable Scope Global
Dynamic Variable Yes
Type Boolean
Default ON
Valid Values ON, OFF

innodb_segment_fill_factor

Specifies the percentage of space on a segment to be used, reserving the remainder as free space for future data growth.

Command-Line Format --innodb_segment_fill_factor
Option-File Format innodb_segment_fill_factor
Option Sets Variable Yes, innodb_segment_fill_factor
Variable Name innodb_segment_fill_factor
Variable Scope Global
Dynamic Variable Yes
Type Floating-point
Default 87.50
Valid Values [0.0, 100.00]
Something went wrong with that request. Please try again.