MySQL 如何设置全局 innodb_buffer_pool_size？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19534144/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to set global innodb_buffer_pool_size?
提问by Vaibhav Jain
How to set the global innodb_buffer_pool_sizemySQL variable? When I set it to system displayI get this error:
如何设置全局innodb_buffer_pool_sizemySQL变量？当我将其设置system display为时，出现此错误：
ERROR 1238 (HY000): Variable 'innodb_buffer_pool_size' is a read only variable
回答by 1000111
In the earlier versions of MySQL ( < 5.7.5 )the only way to set
在早期版本的MySQL ( < 5.7.5 )中设置的唯一方法
'innodb_buffer_pool_size'
'innodb_buffer_pool_size'
variable was by writing it to my.cnf (for linux)and my.ini (for windows)under [mysqld] section :
变量是通过将其写入[mysqld] 部分下的my.cnf（对于 linux）和my.ini（对于 Windows）：
[mysqld]
innodb_buffer_pool_size = 2147483648
You need to restart your mysql server to have it's effect in action.
您需要重新启动 mysql 服务器才能使其生效。
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;
从 MySQL 5.7.5 开始，可以使用 SET 语句动态设置 innodb_buffer_pool_size 配置选项，允许您在不重新启动服务器的情况下调整缓冲池的大小。例如：
mysql> SET GLOBAL innodb_buffer_pool_size=402653184;
Reference : http://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-online-resize.html
参考：http: //dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-online-resize.html
Note
笔记
Buffer pool size must always be equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances. If you configure innodb_buffer_pool_size to a value that is not equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances, buffer pool size is automatically adjusted to a value that is equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances that is not less than the specified buffer pool
缓冲池大小必须始终等于或为 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 的倍数。如果将 innodb_buffer_pool_size 配置为不等于或不等于 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 的倍数的值，则缓冲池大小会自动调整为等于或不小于指定缓冲池的 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 倍数的值
By @FlyingAtom
来自@FlyingAtom
回答by Mahesh Patil
innodb_buffer_pool_size
innodb_buffer_pool_size
You should set this variable value under [mysqld] section as :
您应该在 [mysqld] 部分下将此变量值设置为：
innodb_buffer_pool_size=2G
and restart MySQL service to have effect.
并重启MySQL服务生效。
InnoDB buffer pool caches both data and index pages. You can set this value to 70-80% of available memory for Innodb-only installations
InnoDB 缓冲池缓存数据和索引页。对于 Innodb-only 安装，您可以将此值设置为可用内存的 70-80%
回答by pattwo
Look here innodb_buffer_pool_size documentation
In there it says this variable can be set in option-file (my.cnf) or on the command line when starting the server.
在那里它说这个变量可以在启动服务器时在选项文件（my.cnf）或命令行中设置。
Also. I don't think you'll be able to assign the value of "system display" to this variable as it is a numeric. See the docs.
还。我认为您无法将“系统显示”的值分配给这个变量，因为它是一个数字。查看文档。
回答by Kernelv5
I faced that same problem:
我遇到了同样的问题：
ERROR 1238 (HY000): Variable 'innodb_buffer_pool_size' is a read only variable
错误 1238 (HY000)：变量“innodb_buffer_pool_size”是只读变量
Then I notice I wrote [mysqly]wrongly at the top, and that's why my.cnf was not working properly. I change it to [mysqld]and restart MySQL, then everything is working perfectly.
然后我注意到我[mysqly]在顶部写错了，这就是为什么 my.cnf 不能正常工作。我将其更改为[mysqld]并重新启动 MySQL，然后一切正常。
回答by FlyingZebra1
You could do something like this from mysql command line / mysql workbench
你可以从 mysql 命令行 / mysql workbench 做这样的事情
SET GLOBAL innodb_buffer_pool_size= (SELECT @@innodb_buffer_pool_size)*10
#check via this:
#SELECT @@innodb_buffer_pool_size/1024/1024/1024
