3

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.

TheMAn
  • 31
  • 1
  • 1
  • 3
  • Specifying innodb_buffer_pool_instances = 4 doesn't change the amount of RAM that's used, just how many separate regions it's broken up into. If you aren't doing massively high query counts I would doubt it would have much impact on your performance. If you're running [5.6.6 or later, it uses 8 instances by default when your buffer pool is over 1GB](https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_buffer_pool_instances). As with any changes in production, the better decision is the one that shows the best results when you benchmark a load similar to production. –  Jan 28 '16 at 03:56

2 Answers2

4

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.

koustuv
  • 656
  • 7
  • 11
0

I think the best practices for this are quite clear in the link: https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_buffer_pool_instances

If innodb_buffer_pool_size values are in multiple of GBs, we can increase innodb_buffer_pool_instances to a number so that each instance will have at least 1 GB. Also for dedicated systems running mysql - innodb_buffer_pool_size can be set based on the available system RAM. It may not exactly follow the thumb-rule of 70-80%. I had researched on optimal values for this based on the system RAM, and have built a calculator to compute innod_db_buffer_pool_size which is available as part of the blog post: https://scalegrid.io/blog/calculating-innodb-buffer-pool-size-for-your-mysql-server/

Prasad Nagaraj
  • 1