# Weird innodb\_buffer\_pool\_size setting **URL:** \ **Category:** Percona Operator for MySQL\ **Created:** [March 25, 2021, 3:37pm UTC](https://forums.percona.com/t/weird-innodb-buffer-pool-size-setting/9825 "2021-03-25T15:37:32Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![mleklund](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/mleklund/32/2871_2.png) [@mleklund](https://forums.percona.com/u/mleklund)\ **Post date:** [March 25, 2021, 3:37pm UTC](https://forums.percona.com/t/weird-innodb-buffer-pool-size-setting/9825/1 "2021-03-25T15:37:32Z")
Cannot for the life of me figure out what is going on. Here is a snippet from my pxc yaml: ```auto pxc: size: 3 image: percona/percona-xtradb-cluster:8.0.21-12.1 autoRecovery: true configuration: | [sst] xbstream-opts=--decompress [xtrabackup] compress=lz4 [mysqld] wsrep_provider_options="gcache.size=1G; gcache.recover=yes" binlog_expire_logs_seconds=604800 skip-name-resolve=ON default-time-zone="EST5EDT" imagePullPolicy: IfNotPresent resources: requests: memory: 2G cpu: 600m limits: memory: 2G cpu: "1" ``` `/etc/mysql/conf.d/auto-config.cnf` looks right: ```auto [mysqld] innodb_buffer_pool_size = 1500000000 max_connections = 158 ``` `/etc/percona-xtradb-cluster.conf.d/init.cnf` looks right: ```auto [sst] xbstream-opts=--decompress [xtrabackup] compress=lz4 [mysqld] wsrep_provider_options="gcache.size=1G; gcache.recover=yes" binlog_expire_logs_seconds=604800 skip-name-resolve=ON default-time-zone="EST5EDT" ``` show variables: ```auto +-------------------------+------------+ | Variable_name | Value | +-------------------------+------------+ | innodb_buffer_pool_size | 2147483648 | +-------------------------+------------+ +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 158 | +-----------------+-------+ ``` Where in the world is that `innodb_buffer_pool_size=2147483648` coming from? ---
**Author:** ![mleklund](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/mleklund/32/2871_2.png) [@mleklund](https://forums.percona.com/u/mleklund)\ **Post date:** [March 25, 2021, 4:10pm UTC](https://forums.percona.com/t/weird-innodb-buffer-pool-size-setting/9825/2 "2021-03-25T16:10:34Z")
Seems to be something about 2G. When I scale the cluster to 1G I get: ```auto +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | innodb_buffer_pool_size | 805306368 | +-------------------------+-----------+ ``` When I scale it to 4G I get: ```auto +-------------------------+------------+ | Variable_name | Value | +-------------------------+------------+ | innodb_buffer_pool_size | 3221225472 | +-------------------------+------------+ ``` I still do not know where 2147483648 is coming from. ---
**Author:** ![mleklund](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/mleklund/32/2871_2.png) [@mleklund](https://forums.percona.com/u/mleklund)\ **Post date:** [March 25, 2021, 4:23pm UTC](https://forums.percona.com/t/weird-innodb-buffer-pool-size-setting/9825/3 "2021-03-25T16:23:34Z")
One more data point. It looks like it sees the 1.5G (2.0G \* .75), but something is raising the buffer pool somewhere. ```auto [0] pxc-operator.cluster1-pxc-2.innobackup.backup.log: [1616688190.547952510, {"log"=>"xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --server-id=12 --innodb_flush_log_at_trx_commit=0 --innodb_flush_method=O_DIRECT --innodb_file_per_table=1 --innodb_buffer_pool_size=1500000000 --defaults_group=mysqld "}] [17] pxc-operator.cluster1-pxc-2.innobackup.backup.log: [1616688190.569888718, {"log"=>"Number of pools: 1"}] [291] pxc-operator.cluster1-pxc-2.innobackup.backup.log: [1616688193.204098578, {"log"=>"210325 16:03:13 [00] Compressing and streaming ib_buffer_pool to "}] [0] pxc-operator.cluster1-pxc-2.innobackup.backup.log: [1616688919.934593590, {"log"=>"xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --server-id=12 --innodb_flush_log_at_trx_commit=0 --innodb_flush_method=O_DIRECT --innodb_file_per_table=1 --innodb_buffer_pool_size=1500000000 --defaults_group=mysqld "}] [17] pxc-operator.cluster1-pxc-2.innobackup.backup.log: [1616688919.954328414, {"log"=>"Number of pools: 1"}] [272] pxc-operator.cluster1-pxc-2.innobackup.backup.log: [1616688922.612918563, {"log"=>"210325 16:15:22 [00] Compressing and streaming ib_buffer_pool to "}] ``` ---
**Author:** ![Sergey\_Pronin](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/sergey_pronin/32/14887_2.png) [@Sergey\_Pronin](https://forums.percona.com/u/Sergey_Pronin)\ **Post date:** [March 26, 2021, 12:27pm UTC](https://forums.percona.com/t/weird-innodb-buffer-pool-size-setting/9825/4 "2021-03-26T12:27:43Z")
Hello @mleklund , weird bug. Thank you for submitting this. I have created a JIRA ticket to fix this: [[K8SPXC-682] Auto tuning sets wrong innodb\_buffer\_pool\_size - Percona JIRA](https://jira.percona.com/browse/K8SPXC-682) We will ship it in 1.9.0 version. ---
**Author:** ![Ege\_Gunes](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/ege_gunes/32/4595_2.png) [@Ege\_Gunes](https://forums.percona.com/u/Ege_Gunes)\ **Post date:** [May 19, 2021, 6:41am UTC](https://forums.percona.com/t/weird-innodb-buffer-pool-size-setting/9825/5 "2021-05-19T06:41:09Z")
Hi @mleklund MySQL automatically adjust the buffer pool size as described in [the docs](https://dev.mysql.com/doc/refman/8.0/en/innodb-buffer-pool-resize.html): > Buffer pool size must always be equal to or a multiple of [`innodb_buffer_pool_chunk_size`](https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_buffer_pool_chunk_size) \* [`innodb_buffer_pool_instances`](https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_buffer_pool_instances). If you configure [`innodb_buffer_pool_size`](https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size) to a value that is not equal to or a multiple of [`innodb_buffer_pool_chunk_size`](https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_buffer_pool_chunk_size) \* [`innodb_buffer_pool_instances`](https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_buffer_pool_instances), buffer pool size is automatically adjusted to a value that is equal to or a multiple of [`innodb_buffer_pool_chunk_size`](https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_buffer_pool_chunk_size) \* [`innodb_buffer_pool_instances`](https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_buffer_pool_instances). Also, there is a [post in Percona blog](https://www.percona.com/blog/2018/06/19/chunk-change-innodb-buffer-pool-resizing/) that describes exactly the same scenario: > And the final size is 2GB. Yes! you intended to set the value to 1.5GB and you succeeded in setting it to 2GB. Even if you set 1 byte higher, like setting: 1073741825, you will end up with a buffer pool of 2GB. ---
**Author:** ![mleklund](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/mleklund/32/2871_2.png) [@mleklund](https://forums.percona.com/u/mleklund)\ **Post date:** [May 19, 2021, 8:34pm UTC](https://forums.percona.com/t/weird-innodb-buffer-pool-size-setting/9825/6 "2021-05-19T20:34:40Z")
Then, IMO, the operator should be better about it’s math since it is setting the `innodb_buffer_pool_size` based on a fraction (75%) of the container limit, so users do not end up in an OOM kill situation. That 2G limit is a hard limit in K8s. ---
**Author:** ![Sergey\_Pronin](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/sergey_pronin/32/14887_2.png) [@Sergey\_Pronin](https://forums.percona.com/u/Sergey_Pronin)\ **Post date:** [May 24, 2021, 10:59am UTC](https://forums.percona.com/t/weird-innodb-buffer-pool-size-setting/9825/7 "2021-05-24T10:59:31Z")
@mleklund I agree with you. Operator should be smarter here. We will handle it through [[K8SPXC-682] Auto tuning sets wrong innodb\_buffer\_pool\_size - Percona JIRA](https://jira.percona.com/browse/K8SPXC-682). ---
**Author:** ![Ege\_Gunes](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/ege_gunes/32/4595_2.png) [@Ege\_Gunes](https://forums.percona.com/u/Ege_Gunes)\ **Post date:** [May 27, 2021, 7:32am UTC](https://forums.percona.com/t/weird-innodb-buffer-pool-size-setting/9825/8 "2021-05-27T07:32:45Z")
@mleklund As I said before, MySQL automatically adjusts the buffer pool size according to buffer pool chunk size and buffer pool instances. To allow 75% of 2G, we need to specify buffer pool chunk size in auto tune config along with buffer pool size. I submitted a [PR](https://github.com/percona/percona-xtradb-cluster-operator/pull/882) to fix the issue.