Configurable Insert Buffer

Percona has implemented several changes related to MySQL’s InnoDB Insert Buffer. These features enable adjusting the insert buffer to the different workloads and hardware configurations.

System variables:

variable innodb_ibuf_active_contract
Version Info:
Command Line:

Yes

Config File:

Yes

Scope:

Global

Dynamic:

Yes

Variable Type:

Numeric

Default Value:

1

Range:

0 - 1

This variable specifies whether the insert buffer can be processed before it reaches its maximum size. The following values are allowed:

  • 0: the insert buffer is not processed until it is full. This is the standard InnoDB behavior.
  • 1: the insert buffer can be processed even it is not full.
variable innodb_ibuf_accel_rate
Version Info:
Command Line:

Yes

Config File:

Yes

Scope:

Global

Dynamic:

Yes

Default Value:

100

Range:

100 - 999999999

This variable allows better control of the background thread processing the insert buffer. Each time the thread is called, its activity is altered by the value of both innodb_io_capacity and innodb_ibuf_accel_rate this way:

[real activity] = [default activity] * (innodb_io_capacity/100) * (innodb_ibuf_accel_rate/100)

By increasing the value of innodb_ibuf_accel_rate, you will increase the insert buffer activity.

variable innodb_ibuf_max_size
Command Line:Yes
Config File:Yes
Scope:Global
Dynamic:No
Variable Type:Numeric
Default Value:Half the size of the InnoDB buffer pool
Range:0 - Half the size of the InnoDB buffer pool
Units:Bytes

This variable specifies the maximum size of the insert buffer. By default the insert buffer is half the size of the buffer pool so if you have a very large buffer pool, the insert buffer will be very large too and you may want to restrict its size with this variable.

Setting this variable to 0 is equivalent to disabling the insert buffer. But then all changes to secondary indexes will be performed synchronously which will probably cause performance degradation. Likewise a too small value can hurt performance.

If you have very fast storage (ie storage with RAM-level speed, not just a RAID with fast disks), a value of a few MB may be the best choice for maximum performance.