大网数据广告活动
服务器运维问答

Mysql innodb_buffer_pool_size in relation to innodb_buffer_pool_instances

· 52CE 我爱测

I run a virtual server with an Owncloud installation on it. Now Owncloud has the problem that the way it's developed at the moment, each file upload causes a little MySql overhead. So is very important to tweek the MySql database…

问题原文

原帖链接 · TheMAn · CC BY-SA 3.0

I run a virtual server with an Owncloud installation on it. Now Owncloud has the problem that the way it's developed at the moment, each file upload causes a little MySql overhead.

So is very important to tweek the MySql database as good as possible. There many people out in the web who recommend a very large innodb_buffer_pool_size up to 4, 5 or more GByte, yes some even say up to 70 till 80 % of the total ram, hmm?

Well, but very few people mind a second parameter called innodb_buffer_pool_instances

So lets look at my situation. My virtual machine has limited resources. That means 6400 GByte total ram and in about 3000 in normal mode (handled by libvirt). So if I set innodb_buffer_pool_instances on more than 1 or let it in the default conf of newer MySql servers (8 or something) means (as long as I understood that right) in the worst case for example innodb_buffer_pool_instances = 4 and innodb_buffer_pool_size = 4 => 4*4 = 16 GByte ram usage in maximum situations. What would result in bad usage of the swap disk and every swap usage for MySql innodb buffer is a overkill for any performance.

The conclusion is easy. The available buffer size must be smaller than at least, well I would say, in about 70 % percent of the available ram and the rest may needed for php, apache and the system itself.

So what would be the better decisions. Only one possible innodb_buffer_pool_instances but a very large innodb_buffer_pool_size or better a smaller innodb_buffer_pool_size and therefore more innodb_buffer_pool_instances at the same time if needed.

My server is in use of in about 10 to 30 people max.

社区高分解答

原帖链接 · mysqlrockstar · CC BY-SA 3.0

innodb_buffer_pool_instances:

You cannot see its effect unless innodb_buffer_pool_size is few gigs, dividing the buffer pool into separate instances can improve efficiency. This is also a tuning practice for innodb_buffer_pool_instance so that each buffer pool instance is at least 1GB.

eg, If innodb_buffer_pool_size = 4GB then, innodb_buffer_pool_instances = 4

MySQL 5.6 has a default of 8


innodb_buffer_pool_size:

innodb_buffer_pool_size should be 80% of RAM

The idea is only for dedicated MySQL server where no other process running. On the other hand buffer pool should not be less and if have sufficient RAM. Also, need to check if the system is 64 / 32 bit.

摘自公开归档,保留原文语言,版式经整理,外部媒体与邮箱地址已移除。

资料来源与适用范围

资料来源
Server Fault
适用范围
以原帖系统、软件版本与发布日期为准(2016-01-27)
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY-SA 3.0

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。