mysql设置bufferpool_设置MySQL的innodb_buffer_pool_size参数

In the earlier versions of MySQL ( < 5.7.5 ) the only way to set

'innodb_buffer_pool_size'

variable was by writing it to my.cnf (for linux) and my.ini (for windows) under [mysqld] section :

[mysqld]

innodb_buffer_pool_size = 2147483648

You need to restart your mysql server to have it's effect in action.

UPDATE :

As of MySQL 5.7.5, the innodb_buffer_pool_size configuration option can be set dynamically using a SET statement, allowing you to resize the buffer pool without restarting the server. For example:

mysql> SET GLOBAL innodb_buffer_pool_size=402653184;

Windows上修改my.ini文件后，需要运行net stop mysql和net start mysql关闭并重启MySQL服务。

# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and # row 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.    这段注释是说innodb_buffer_pool_size大致应该设为物理内存的80%，但又不能设的太大。我的物理内存显示为3.48G，如果按80%，设为2G，1G都是可以的，但是实际上，如果修改my.ini文件设置为2G或者1G，mysql服务启动都出错，设置为较小的100M，系统可以启动。    MySQL5.5 on Windows XP.