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.
- variable innodb_ibuf_accel_rate¶
Command Line: Yes Config File: Yes Scope: Global Dynamic: Yes Variable Type: Numeric Default Value: 100 Range: 100 - 999999999
This variable allows a better control of the background thread processing the insert buffer. Each time the insert buffer merge is performed by the InnoDB master thread, its activity is affected 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_active_contract¶
Command Line: Yes Config File: Yes Scope: Global Dynamic: Yes Variable Type: Numeric Default Value: 0(1.0.5), 1(1.0.6) 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_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.