1

This question already has an answer here:

I have a database dedicated server with one quad-core cpu and 48GB Memory.

Total database size is about 6GB and my ultimate goal is to get the maximum performance out of this system.

At First, to get maximum performance I set innodb_buffer_pool_size to '35GB' with innodb_buffer_pool_instance to 5, thinking that each instance will take up 7GB so total 35GB will be optimum.

But after several days, Innodb_buffer_pool_pages_free says I had 26GB free space in the buffer pool.

So I increased the instances number to 10 and waited some weeks, but the Innodb_buffer_pool_pages_free says about the same thing.

Was my calculation(expecting that 7GB * 5 instances would take up 35GB of buffer pool) wrong? Then how can I use all the free buffer pool size? Should I increase the buffer pool instances even more? Or run 2 mysqld processes to get replication or something?

If anyone get any clue, please give some hint to get the point right about my issue.

marked as duplicate by RolandoMySQLDBA innodb Oct 29 '15 at 3:27

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

2

The number of pools does not change the total amount of pages innodb would use. A page exists on one of the pools, not a copy of it in each pool. That's why your 6GB database doesn't take much more than 6GB, the extra probably being for indexes.

From the documentation:

http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_buffer_pool_instances

The number of regions that the InnoDB buffer pool is divided into. For systems with buffer pools in the multi-gigabyte range, dividing the buffer pool into separate instances can improve concurrency, by reducing contention as different threads read and write to cached pages. Each page that is stored in or read from the buffer pool is assigned to one of the buffer pool instances randomly, using a hashing function. Each buffer pool manages its own free lists, flush lists, LRUs, and all other data structures connected to a buffer pool, and is protected by its own buffer pool mutex.

Not the answer you're looking for? Browse other questions tagged or ask your own question.