InnoDB Buffer Pool

Background

InnoDB is the default storage system for current MySQL or MariaDB installations. Unless otherwise specified, tables are stored in this storage system.

With the SQL option ENGINE tables can also be created explicitly with InnoDB:

CREATE TABLE t (i INT) ENGINE = InnoDB;

Alternatively, tables can also be "converted" to InnoDB afterwards:

ALTER TABLE t ENGINE=InnoDB;

Part of this storage system is the so-called "buffer pool", an area in the main memory (RAM) that is reserved for InnoDB data. Database operations on data in the buffer pool are executed more quickly because they are held in RAM and do not have to be read from the mass storage (SSD).

The size of the buffer pool has a significant influence on the database server performance and is configured via the "innodb_buffer_pool_size" option. For optimal performance, the amount of all existing InnoDB data from all databases should find room in the buffer pool.

Solution

To optimize MySQL performance, please check the current size of your InnoDB tables with this MySQL command:

select sum(DATA_LENGTH) + sum(INDEX_LENGTH) FROM information_schema.TABLES WHERE ENGINE like "InnoDB" AND table_schema not in ("mysql", "performance_schema", "information_schema")

The size of the configured InnoDB pool can additionally be checked with this command:

show variables like "innodb_buffer_pool_size";

Since root privileges are required for any changes to the "innodb_buffer_pool_size" in the configuration files of the database server, please contact our support for the adjustment of the values. They will then adjust the value to the amount of your InnoDB data. Please note that it may be necessary to increase the RAM of the cluster to accommodate the larger buffer pool.