Bug #111182 buffer pool online resize warning message incorrect
Submitted: 29 May 2023 9:15 Modified: 5 Jun 2023 10:01
Reporter: Ke Yu (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:8.0.33 OS:Any
Assigned to: CPU Architecture:Any

[29 May 2023 9:15] Ke Yu
Description:
When online resizing buffer pool size to a value equal to innodb_buffer_pool_chunk_size, the client returns a warning message:

mysql> show warnings;
+---------+------+--------------------------------------------------------------------------------+
| Level   | Code | Message                                                                        |
+---------+------+--------------------------------------------------------------------------------+
| Warning | 1210 | InnoDB: Cannot resize buffer pool to lesser than chunk size of 33554432 bytes. |
+---------+------+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)

The message show that "Cannot resize buffer pool to lesser than chunk size", but  we just set the buffer pool size to a value equal to innodb_buffer_pool_chunk_size.

How to repeat:
1. Start server with --innodb_buffer_pool_chunk_size=33554432 --innodb_buffer_pool_size=67108864 --innodb_buffer_pool_instances=2

2. Set buffer pool size to a value equal to innodb_buffer_pool_chunk_size online.
mysql> set global innodb_buffer_pool_size=33554432;
Query OK, 0 rows affected, 1 warning (24.19 sec)

3. Show warnings and you can see the incorrect message.
mysql> show warnings;
+---------+------+--------------------------------------------------------------------------------+
| Level   | Code | Message                                                                        |
+---------+------+--------------------------------------------------------------------------------+
| Warning | 1210 | InnoDB: Cannot resize buffer pool to lesser than chunk size of 33554432 bytes. |
+---------+------+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
In function innodb_buffer_pool_size_validate, the judgment of srv_buf_pool_size is incomplete.
In the 8.0.33 at ha_innodb.cc:20442 "} else if (srv_buf_pool_size == aligned_buffer_pool_size) {", 
it also contains cases where the requseted size is equal to or greater than the chunk size. 
It is suggested to add a case comparing the requseted size with chunk size here.
[5 Jun 2023 8:16] MySQL Verification Team
Hello Ke Yu,

Thank you for the report and feedback.
I quickly tried to reproduce the issue but not seeing any warnings which you observed. Is there anything that I'm missing here? Please let me know. Thank you.

## 
rm -rf 110722/
bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/110722 --log-error-verbosity=3
bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/110722 --core-file --socket=/tmp/mysql_ushastry.sock  --port=3306 --log-error=$PWD/110722/log.err --mysqlx-port=33330 --mysqlx-socket=/tmp/mysql_x_ushastry.sock --log-error-verbosity=3  --secure-file-priv=/tmp/ --innodb-buffer-pool-chunk-size=33554432 --innodb-buffer-pool-size=67108864 --innodb-buffer-pool-instances=2 2>&1 &

 bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.33 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>  set global innodb_buffer_pool_size=33554432;
Query OK, 0 rows affected (0.00 sec)

^^ innodb_buffer_pool_instances was adjusted from 2 to 1 since innodb_buffer_pool_size is less than 1024 MiB during the start up.

2023-06-05T08:10:50.722685Z 0 [Note] [MY-012359] [InnoDB] Adjusting innodb_buffer_pool_instances from 2 to 1 since innodb_buffer_pool_size is less than 1024 MiB

regards,
Umesh
[5 Jun 2023 8:39] Ke Yu
Hi,
Probably because I changed the innodb_buffer_pool_instances parameter in the code. I am now using open source mysql binaries and can now reproduce the warnings.

I adjusted the 
--innodb-buffer-pool-chunk-size to 1073741824 and --innodb-buffer-pool-size to 2147483648. And then we can see the warnings.

You can reproduce by the follew step:

rm -rf 110722/
bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/110722 --log-error-verbosity=3
bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/110722 --core-file --socket=/tmp/mysql_ushastry.sock  --port=3306 --log-error=$PWD/110722/log.err --mysqlx-port=33330 --mysqlx-socket=/tmp/mysql_x_ushastry.sock --log-error-verbosity=3  --secure-file-priv=/tmp/ --innodb-buffer-pool-chunk-size=1073741824 --innodb-buffer-pool-size=2147483648 --innodb-buffer-pool-instances=2 2>&1 &

mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.33 Source distribution

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set global innodb_buffer_pool_size=1073741824;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------------------------------------------------+
| Level   | Code | Message                                                                          |
+---------+------+----------------------------------------------------------------------------------+
| Warning | 1210 | InnoDB: Cannot resize buffer pool to lesser than chunk size of 1073741824 bytes. |
+---------+------+----------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[5 Jun 2023 10:01] MySQL Verification Team
Thank you for the feedback.
Verified as described.

regards,
Umesh