1

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.

Won
  • 23
  • 4

1 Answers1

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.

Collector
  • 145
  • 5