Mysql – rule of thumb regarding the size and number of a buffer pool instances

innodblockingMySQLmysql-5.5

I know the consensus seems to be approximately 75% of ram should be dedicated to the buffer pool. But, I'm more interested in the optimal number of buffer pool instances. What are some things to consider when determining the setting?

For example, let's say I have a 75GB buffer pool. Should I just assign 75 buffer pool instances at 1GB each? 15 at 5GB each? Or, 10 at 7.5GB each?

Should I start out with a single buffer pool and increase based on whether or not I see locking?

What should the max number of instances be based on – available cores?

Thanks – Your response is greatly appreciated.

Best Answer

BUFFER POOL INSTANCES

The rule of thumb I usually use is based on a special program in in the Linux environment

numactl --hardware

When I run this, I get the following output

sh-4.1# numactl --hardware
available: 4 nodes (0-3)
node 0 cpus: 0 1 2 3
node 0 size: 49151 MB
node 0 free: 241 MB
node 1 cpus: 4 5 6 7
node 1 size: 32768 MB
node 1 free: 39 MB
node 2 cpus: 8 9 10 11
node 2 size: 49152 MB
node 2 free: 49 MB
node 3 cpus: 12 13 14 15
node 3 size: 32752 MB
node 3 free: 32 MB
node distances:
node   0   1   2   3
  0:  10  16  16  16
  1:  16  10  16  16
  2:  16  16  10  16
  3:  16  16  16  10

This quickly tells me how many CPUs and cores I have on my DB Server.

In general, I set the innodb_buffer_pool_instances to the number of physical CPUs or the number of cores. In your particular, I set the innodb_buffer_pool_instances to 4 or 16.

BUFFER POOL SIZE

If you make the InnoDB Buffer Pool bigger than 50% of the installed RAM, mysqld causes the OS to start swapping ... BADLY !!!! If you need a Buffer Pool that big, then tuning innodb_buffer_pool_instances becomes even more critical.

EPILOGUE

As soon as innodb_buffer_pool_instances was first introduced, I immediately experimented with it. I had a client that had 192GB DB Server, dual hexacore with a 162GB Buffer Pool. I simply set it to 2 and everything worked out just fine. Please see my old post from Feb 12, 2011 on this : How do you tune MySQL for a heavy InnoDB workload?

GIVE IT A TRY !!!

Related Question