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.

Percona Server
Call Us
+1-888-316-9775 (USA - Sales)
+1-208-473-2904 (USA - Sales)
+44-208-133-0309 (UK - Sales)
0-800-051-8984 (UK - Sales)
0-800-181-0665 (GER - Sales)
+1-877-862-4316 (Emergency)
+1-855-55TRAIN (Training)
+1-925-271-5054 (Training)

Table Of Contents

Previous topic

Improved Buffer Pool Scalability

Next topic

Improved InnoDB I/O Scalability

This Page



© Copyright Percona LLC and/or its affiliates 2009-2014.
Except where otherwise noted, this documentation is licensed under the following license:
CC Attribution-ShareAlike 2.0 Generic
Created using Sphinx 1.1.3.
This documentation is developed in Launchpad as part of the Percona Server source code.
If you spotted innacuracies, errors, don't understood it or you think something is missing or should be improved, please file a bug.
]]>