• MySQL知识库 :: performance
  • InnoDB Buffer Pool Size May Not Be Optimal

  • Description

    If the size of InnoDB buffer pool is non-optimal, it can cause performance issues due to intense disk I/O operations. Buffering table data and table indexes increases the performance of MySQL server.

    Best Practice

    Since disk reads/writes take much more time than the same operations in memory, it makes sense to put some data such as table indexes in memory cache. This helps to reduce the disk activity and allows the server to process more data at the same time. Typically, it's advisable to set innodb_buffer_pool_size to 80% of all available memory only on a dedicated database server. Otherwise its value should be balanced so that MySQL server's memory use does not affect other applications running in the same environment.

    Production systems may often suffer because of insufficient data processing speed. When a growing database that has reached some critical threshold and the performance drops dramatically, increasing InnoDB buffer pool solves usually will the problem. This can be done by changing the value of innodb_buffer_pool_size in the options file (i.e., my.cnf or my.ini, depending on your system) and then restarting the server.

    Do not set innodb_buffer_pool_size too large, not more than 80% of all available memory, because competition for physical memory might cause paging in the operating system and negatively affect the performance. When setting innodb_buffer_pool_size, make sure Linux x86 total memory usage is less than 2GB. In case of a 32-bit windows. using so-called Address Windowing Extensions can allocate the InnoDB buffer pool into the AWE physical memory using innodb_buffer_pool_awe_mem_mb variable, if your operating system supports more than 4GB memory.