mysql innodbuffer修改_设置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.