频繁使用MySQL，磁盘IO高峰或爆满，innodb_buffer_pool_size解决方案

直接说结论，innodb_buffer_pool_size的值官方建议在32位机器下设置为2-3.5G。我们的服务器是16G，本次设置成了3G，就已经解决了磁盘爆满问题。（数据量没有非常大）

InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes androw data. The bigger you set this the less disk I/O is needed to access data in tables. On a dedicated database server you may set this parameter up to 80% of the machine physical memory size. Do not set it too large, though, because competition of the physical memory may cause paging in the operating system.  Note that on 32bit systems you might be limited to 2-3.5G of user level memory per process, so do not set it too high.

• 进入cmd，输入mysql -uroot -p，输入密码登录MySQL。查看原先缓冲池大小：
  show variables like 'innodb_buffer_pool_size';
  
  
  

• 输入 set global innodb_buffer_pool_size=缓存池大小。
  这里的单位是B，所以1M是1024 * 1024 * 1 = 1,048,576，1G是1024  *  1024  * 1024 * 1 =  1,073,741,824。如果需要3G空间就是1024 * 1024 * 1024 * 3 = 3221225472
  

• 如果新值小于my.ini中的值，这里不会设置成功。
  
• 值不是innodb_buffer_pool_instances 或者innodb_buffer_pool_chunk_size 倍数时，会自动补正。(下面会介绍这两个参数)
  
  

• 注意文件的编码格式，用记事本打开写可能变成utf-8格式，在网上看见有人说这里用utf-8可能会出现问题，保险起见还是ANSI格式。
  

• innodb_buffer_pool_instances
  

The number of regions that the InnoDB buffer pool is divided into. For systems with buffer pools in the multi-gigabyte range, dividing the buffer pool into separate instances can improve concurrency, by reducing contention as different threads read and write to cached pages.

• innodb_buffer_pool_chunk_size