Mysql innodb_buffer_pool_size in relation to innodb_buffer_pool_instances

Question

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.

Solution

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.