How to set the optimal memory value with INNODB_BUFFER_POOL_SIZE flag

What is INNODB BUFFER POOL?

Computers use most of their memory to improve the performance of frequently accessed data. This is what we know as cache, and it is a very important part of the system, because the data accessing the hard disk may be as slow as 100 to 100,000 times, depending on the amount of data you access. MyISAM uses the operating system’s file system cache to cache data that is frequently queried. However, InnoDB uses a very different approach. Without relying on the operating system’s cache, InnoDB itself handles the cache in the InnoDB Buffer Pool. After this article, you will learn how it works and why it is a good idea to implement it that way.

InnoDB Buffer Pool is more than just a cache

The InnoDB Buffer Pool is actually used for multiple purposes. It is used for:

• Data caching: this is definitely its most important purpose
• Index caching: this uses the same buffer pool
• Buffer-changed data (often called dirty data) before being flushed to the hard disk to be stored into the buffer
• Internal storage structure: some constructions adaptive hash index, or row lock also stored in the buffer pool InnoDB
  following is a classic the innodb-buffer-pool-size setting The distribution of 62G InnoDB buffer pool pages: As you can see, Buffer Pool is mostly used for ordinary InnoDB pages, but about 10% is used for other purposes. The unit of this table is InnoDB page. The single-page size is actually 16K, so you can multiply by 16,384 to get a more intuitive usage in bytes.

Set the optimal memory value

Standalone server

In an independent MySQL server that only uses the InnoDB engine, based on experience, it is recommended to set innodb-buffer-pool-size to 80% of the total available memory of the server. So, why not 90% or 100%?
Because other things also require memory:

• Each query requires at least a few K of memory (sometimes a few M)
• There are various other internal MySQL structures and caches
• InnoDB has some structures that do not use buffer pool memory (dictionary Cache, file system, lock system, page hash table, etc.)
• There are also some MySQL files in the OS cache (binary log, relay log, innodb transaction log, etc.)
• Here, you must also set aside for the operating system RAM

Shared server

If your MySQL server shares resources with other applications, then the above 80% of the total available memory of the server is not so applicable. In such an environment, setting the correct number is a bit difficult.
First, let’s count the actual occupied size of InnoDB tables. Perform the following query:

SELECT engine,

  count(*) as TABLES,

  concat(round(sum(table_rows)/1000000,2),'M') rows,

  concat(round(sum(data_length)/(1024*1024*1024),2),'G') DATA,

  concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx,

  concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size,

  round(sum(index_length)/sum(data_length),2) idxfrac

FROM information_schema.TABLES

WHERE table_schema not in ('mysql', 'performance_schema', 'information_schema')

GROUP BY engine

ORDER BY sum(data_length+index_length) DESC LIMIT 10;

This will give a reference to let you know how much memory should be set for the InnoDB buffer pool if you want to cache the entire data set.
But in most cases you don’t need to do that, you just need to cache the datasets you use frequently. After setting, let’s take a look at how to check whether the InnoDB buffer pool size is set enough. In the terminal, execute the following command:

$ mysqladmin ext -ri1 | grep Innodb_buffer_pool_reads

| Innodb_buffer_pool_reads                 | 1832098003     |

| Innodb_buffer_pool_reads                 | 595            |

| Innodb_buffer_pool_reads                 | 915            |

| Innodb_buffer_pool_reads                 | 734            |

| Innodb_buffer_pool_reads                 | 622            |

| Innodb_buffer_pool_reads                 | 710            |

| Innodb_buffer_pool_reads                 | 664            |

| Innodb_buffer_pool_reads                 | 987            |

| Innodb_buffer_pool_reads                 | 1287           |

| Innodb_buffer_pool_reads                 | 967            |

| Innodb_buffer_pool_reads                 | 1181           |

| Innodb_buffer_pool_reads                 | 949            |

Change InnoDB buffer pool

If you are running MySQL 5.7 or newer, you can change this variable online, just need to execute the following query as root:

mysql> SET GLOBAL innodb_buffer_pool_size=size_in_bytes;

Or you still need to change the my.cnf file, but at least you don’t need to restart the server for it to take effect.