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 !!!
I don't think you will need to have too many buffer pools for you queried data because the size of the frequently queried data doesn't quite justify it. This does of course depend on the definition of "frequently".
The appropriate documentation you should be referencing the page on innodb buffer pools, here:
http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-buffer-pools.html
The numbers I'm focusing on here is
the frequently queried data is ~1.25 gb.
My rule of thumb when trying to keep InnoDB buffers the right size is to keep them at or around 1GB a piece, in order to keep the list of blocks as short as possible but keeping the list of buffers from being too far fetched, this will always depend on your actual needs however.
This is in line with MySQL's recomendations:
For best efficiency, specify a combination of innodb_buffer_pool_instances and innodb_buffer_pool_size so that each buffer pool instance is at least 1 gigabyte.
The point of the multiple buffer pools is to ensure your CPU threads don't meet high contention in accessing the data. Or as they put it:
You might encounter bottlenecks from multiple threads trying to access the buffer pool at once. You can enable multiple buffer pools to minimize this contention.
However this feature is more for larger amounts of data being frequently accessed as opposed to the 1.25GB you're system uses frequently. Ultimately if I were in your position, I wouldn't see a need for having more buffer pools than the number of CPU's assuming all CPU's are only performing MySQL related tasks. I would also look into the affects of using innodb_old_blocks_time
to prevent the occasional query of your archived tables from taking the place of a block of data that is used over and over again.
I hope that helps, let me know how everything works out.
Best Answer
When I go back to MySQL 5.5, I would think about this same thing.
What I learned over those years was the following: If the Buffer Pool was bigger than half the installed RAM and innodb_buffer_pool_instances was 1 (default for 5.5), the threat of swapping was always imminent.
I discussed this before : Is there a rule of thumb regarding the size and number of a buffer pool instances?. In that post, I mentioned an example of a client who had 192GB RAM on the server with 162GB Buffer Pool. When innodb_buffer_pool_instances was 1, swapping happened. When I set innodb_buffer_pool_instances to 2, things got way better.
In your case, since the Buffer Pool is exactly half, a value of 1 may be OK. I would not chance it. I would set it to 2.
Since MySQL 5.6 has a default of 8, you shouldn't have to think about it anymore.
I will say this: akuzminsky's answer has the highest principle. My answer is just shooting from hip based on past experiences (good and bad).