[Maria-developers] innodb-buffer-pool-instances confusion
I was chatting with some folks on IRC and someone pointed this out to me. On the "Upgrading From MariaDB 5.5 to MariaDB 10.0" page, it says that the new default value for innodb-buffer-pool-instances is '0': https://mariadb.com/kb/en/upgrading-from-mariadb-55-to-mariadb-100/ And when I run the following, it says the default is indeed '0': mysqld --no-defaults --verbose --help | grep innodb-buffer-pool-instances ... innodb-buffer-pool-instances 0 But when I'm in the client, I get the following: MariaDB [(none)]> select @@innodb_buffer_pool_instances; +--------------------------------+ | @@innodb_buffer_pool_instances | +--------------------------------+ | 8 | +--------------------------------+ So when innodb-buffer-pool-instances is set to '0', does that mean the actual value is dynamic or calculated in some way based on my other settings? Just trying to understand what's going on, and once I do, use it to update the XtraDB/InnoDB buffer-pool page (which doesn't appear to have been updated for 10.0). https://mariadb.com/kb/en/xtradbinnodb-buffer-pool/ Thanks! -- Daniel Bartholomew, MariaDB Release Manager MariaDB | http://mariadb.com
I think that the MySQL manual documents this pretty well: http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_... "Before MySQL 5.6.6, the default is 1. As of MySQL 5.6.6, the default is 8, except on 32-bit Windows systems, where the default depends on the value of innodb_buffer_pool_size" The 0 value you see on the commandline, however, looks weird... Regards Federico -------------------------------------------- Mer 6/8/14, Daniel Bartholomew <dbart@mariadb.com> ha scritto: Oggetto: [Maria-docs] innodb-buffer-pool-instances confusion A: "maria-developers" <maria-developers@lists.launchpad.net> Cc: "maria-docs" <maria-docs@lists.launchpad.net> Data: Mercoledì 6 agosto 2014, 23:26 I was chatting with some folks on IRC and someone pointed this out to me. On the "Upgrading From MariaDB 5.5 to MariaDB 10.0" page, it says that the new default value for innodb-buffer-pool-instances is '0': https://mariadb.com/kb/en/upgrading-from-mariadb-55-to-mariadb-100/ And when I run the following, it says the default is indeed '0': mysqld --no-defaults --verbose --help | grep innodb-buffer-pool-instances ... innodb-buffer-pool-instances 0 But when I'm in the client, I get the following: MariaDB [(none)]> select @@innodb_buffer_pool_instances; +--------------------------------+ | @@innodb_buffer_pool_instances | +--------------------------------+ | 8 | +--------------------------------+ So when innodb-buffer-pool-instances is set to '0', does that mean the actual value is dynamic or calculated in some way based on my other settings? Just trying to understand what's going on, and once I do, use it to update the XtraDB/InnoDB buffer-pool page (which doesn't appear to have been updated for 10.0). https://mariadb.com/kb/en/xtradbinnodb-buffer-pool/ Thanks! -- Daniel Bartholomew, MariaDB Release Manager MariaDB | http://mariadb.com _______________________________________________ Mailing list: https://launchpad.net/~maria-docs Post to : maria-docs@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-docs More help : https://help.launchpad.net/ListHelp
Haha, this is the code in storage/innobase/handler/ha_innodb.cc: 3260 ▸ if (innobase_buffer_pool_instances == 0) { 3261 ▸ ▸ innobase_buffer_pool_instances = 8; 3262 3263 #if defined(__WIN__) && !defined(_WIN64) 3264 ▸ ▸ if (innobase_buffer_pool_size > 1331 * 1024 * 1024) { 3265 ▸ ▸ ▸ innobase_buffer_pool_instances 3266 ▸ ▸ ▸ ▸ = ut_min(MAX_BUFFER_POOLS, 3267 ▸ ▸ ▸ ▸ ▸ (long) (innobase_buffer_pool_size 3268 ▸ ▸ ▸ ▸ ▸ / (128 * 1024 * 1024))); 3269 ▸ ▸ } 3270 #endif /* defined(__WIN__) && !defined(_WIN64) */ 3271 ▸ } So basically 0 means 8 unless you're on 32-bit Windows where it means one per 128 MiB of buffer pool, but only if the buffer pool is bigger than 1.3 GiB. Gotta love the lack of comments. Basically 0 is the default for the actual parameters, but it's fixed up to 8 at runtime. So the --help output will show 0 even though it will end up with 8 (or N). It's basically using 0 as a way to say "the user didn't specify, so figure it out". Regards, Jeremy On Wed, Aug 6, 2014 at 2:26 PM, Daniel Bartholomew <dbart@mariadb.com> wrote:
I was chatting with some folks on IRC and someone pointed this out to me.
On the "Upgrading From MariaDB 5.5 to MariaDB 10.0" page, it says that the new default value for innodb-buffer-pool-instances is '0':
https://mariadb.com/kb/en/upgrading-from-mariadb-55-to-mariadb-100/
And when I run the following, it says the default is indeed '0':
mysqld --no-defaults --verbose --help | grep innodb-buffer-pool-instances ... innodb-buffer-pool-instances 0
But when I'm in the client, I get the following:
MariaDB [(none)]> select @@innodb_buffer_pool_instances; +--------------------------------+ | @@innodb_buffer_pool_instances | +--------------------------------+ | 8 | +--------------------------------+
So when innodb-buffer-pool-instances is set to '0', does that mean the actual value is dynamic or calculated in some way based on my other settings?
Just trying to understand what's going on, and once I do, use it to update the XtraDB/InnoDB buffer-pool page (which doesn't appear to have been updated for 10.0).
https://mariadb.com/kb/en/xtradbinnodb-buffer-pool/
Thanks!
-- Daniel Bartholomew, MariaDB Release Manager MariaDB | http://mariadb.com
_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
Thanks, the new page should rather use 8 as the default, so I've changed it there and mentioned the Windows 32-bit oddity on the other pages. On 06/08/2014 23:45, Jeremy Cole wrote:
Haha, this is the code in storage/innobase/handler/ha_innodb.cc:
3260 ▸ if (innobase_buffer_pool_instances == 0) { 3261 ▸ ▸ innobase_buffer_pool_instances = 8; 3262 3263 #if defined(__WIN__) && !defined(_WIN64) 3264 ▸ ▸ if (innobase_buffer_pool_size > 1331 * 1024 * 1024) { 3265 ▸ ▸ ▸ innobase_buffer_pool_instances 3266 ▸ ▸ ▸ ▸ = ut_min(MAX_BUFFER_POOLS, 3267 ▸ ▸ ▸ ▸ ▸ (long) (innobase_buffer_pool_size 3268 ▸ ▸ ▸ ▸ ▸ / (128 * 1024 * 1024))); 3269 ▸ ▸ } 3270 #endif /* defined(__WIN__) && !defined(_WIN64) */ 3271 ▸ }
So basically 0 means 8 unless you're on 32-bit Windows where it means one per 128 MiB of buffer pool, but only if the buffer pool is bigger than 1.3 GiB.
Gotta love the lack of comments.
Basically 0 is the default for the actual parameters, but it's fixed up to 8 at runtime. So the --help output will show 0 even though it will end up with 8 (or N). It's basically using 0 as a way to say "the user didn't specify, so figure it out".
Regards,
Jeremy
On Wed, Aug 6, 2014 at 2:26 PM, Daniel Bartholomew <dbart@mariadb.com <mailto:dbart@mariadb.com>> wrote:
I was chatting with some folks on IRC and someone pointed this out to me.
On the "Upgrading From MariaDB 5.5 to MariaDB 10.0" page, it says that the new default value for innodb-buffer-pool-instances is '0':
https://mariadb.com/kb/en/upgrading-from-mariadb-55-to-mariadb-100/
And when I run the following, it says the default is indeed '0':
mysqld --no-defaults --verbose --help | grep innodb-buffer-pool-instances ... innodb-buffer-pool-instances 0
But when I'm in the client, I get the following:
MariaDB [(none)]> select @@innodb_buffer_pool_instances; +--------------------------------+ | @@innodb_buffer_pool_instances | +--------------------------------+ | 8 | +--------------------------------+
So when innodb-buffer-pool-instances is set to '0', does that mean the actual value is dynamic or calculated in some way based on my other settings?
Just trying to understand what's going on, and once I do, use it to update the XtraDB/InnoDB buffer-pool page (which doesn't appear to have been updated for 10.0).
participants (4)
-
Daniel Bartholomew -
Federico Razzoli -
Ian Gilfillan -
Jeremy Cole