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
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.