The MySQL InnoDB storage engine is used by default in all newer versions of MySQL (5.5.5+). As a well-performing transactional storage engine with crash recovery capability, it is the storage engine of choice for most database settings.

One of the most important variables in the configuration of a MySQL database using InnoDB tables is the InnoDB buffer pool. The InnoDB buffer pool stores data and indexes in memory for all InnoDB tables and changes to the data. If data is not found in the InnoDB buffer pool the data must be retrieved from the disk, which will occur much more slowly.

Often a rough guideline of 80% of RAM on the server is suggested to be allocated to this buffer pool. However, there are other factors to consider such as whether the MySQL instance is being hosted on a dedicated server and if there will be contention for memory from other applications. Ideally, the amount of memory allocated will be enough to house the total InnoDB dataset and indexes. If that is not possible, allocating enough memory for the working set of the InnoDB tables is recommended.

The size of the InnoDB buffer pool can be modified by changing the innodb_buffer_pool_size variable in the configuration file. Any changes to innodb_buffer_pool_size in the configuration file will require a restart of the instance to take effect.

After increasing the size of the InnoDB buffer pool, it is possible to have contention and bottlenecks occurring due to the locking of rows during heavy traffic to the database. An InnoDB buffer pool that is greater in size than 1GB can be divided into separate instances to reduce this contention. The variable innodb_buffer_pool_instances has a default value of 8 if innodb_buffer_pool_size is greater than or equal to 1GB, thereby creating eight separate regions where read and write operations can occur.

Summary: When optimizing a MySQL instance with InnoDB as the default storage engine, innodb_buffer_pool_size is one of the most important variables to adjust.

More questions? XTIVIA offers excellent technical support for MySQL customers!