6

I set the innodb_buffer_pool_size to 20GB on a server with 12 CPU cores. My full database is 11gb, however most of it is archived tables that are almost never used. The total queried data is around 3 gb, and the frequently queried data is ~1.25 gb.

What should I set the innodb_buffer_pool_instances to?

  1. innodb_buffer_pool_size / total queried data = 6 pool instances
  2. innodb_buffer_pool_size / frequently queried data = 16 pool instances

Normally I'd pick option #2, but logically it seems number of buffer pools that can be used at any one time is no more than the total number of CPU cores.

Is it a waste to set innodb_buffer_pool_instances to more than the # of CPU cores?

  • with 20G buffer pool having more than one instance makes no sense – akuzminsky Mar 4 '15 at 19:56
  • Why do you say that? I would have said the opposite. Whenever a buffer pool size is an integer multiple of the total queried data size, it'd be foolish to not set the buffer pool instances to that same integer. The exception that I'm wondering about is whether the number of instances should also be constrained to no more than the number of CPU cores. – Jeff Widman Mar 4 '15 at 20:05
  • 1
    as far as I remember multiple buffer pool instances were introduced to reduce contention on the buffer pool mutex. Which (the contention) never happens on such a small buffer pool. Has anything changed since? – akuzminsky Mar 4 '15 at 20:07
  • 1
    Did you see it on your server? – akuzminsky Mar 4 '15 at 20:21
  • 1
    "Maxed out" != "buffer pool mutex contention ", rather opposite. Anyway, ignore it if it makes no sense. – akuzminsky Mar 4 '15 at 20:40
1

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.

0

With 11gb data size, I would consider having a 12gb pool size, with 12 1gb pools, unless your server is pure db, and you just have ram to throw away

0

More than cpu cores ... I think it is over optimization, you risk to hit other subtle limits inside the mysql engine routines.

The total queried data and frequently queried data are daily statistics, they can change.

Mysql developers outline the importance to avoid cpu contention and give us the tunable innodb_buffer_pool_instances

You have 12 core and a 20 Gb pool. I suggest to start with

innodb_buffer_pool_instances = 2

You satisfy the developers rules and you have the working cores (did you get any metric ?) splitted in two memory arenas.

Even if (now) there aren't contentions, I see no problems with this "minimal safeguard" setting.

See also the accepted answer of Is there a rule of thumb regarding the size and number of a buffer pool instances?

Then you will collect some performance metrics and you will decide if it is useful to split more the pool.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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