MDEV-27467: innodb to enforce the minimum innodb_buffer_pool_size in SET GLOBAL

.. to be the same as startup.

In resolving MDEV-27461, BUF_LRU_MIN_LEN (256) is the minimum number of
pages for the innodb buffer pool size. Obviously we need more than just
flushing pages. Taking the 16k page size and its default minimum, an
extra 25% is needed on top of the flushing pages to make a workable buffer
pool.

The minimum innodb_buffer_pool_chunk_size (1M) restricts the minimum
otherwise we'd have a pool made up of different chunk sizes.

The resulting minimum innodb buffer pool sizes are:

Page Size, Previously minimum (startup), with change.
        4k                            5M           2M
        8k                            5M           3M
       16k                            5M           5M
       32k                           24M          10M
       64k                           24M          20M

With this patch, SET GLOBAL innodb_buffer_pool_size minimums are
enforced.

The evident minimum system variable size for innodb_buffer_pool_size
is 2M, however this is only setable if using 4k page size. As
the order of the page_size and buffer_pool_size aren't fixed, we can't
hide this change.

Subsequent changes:
* innodb_buffer_pool_resize_with_chunks.test - raised of pool resize due to new
  minimums. Chunk size also needed increase as the test was for
  pool_size < chunk_size to generate a warning.
* Removed srv_buf_pool_min_size and replaced use with MYSQL_SYSVAR_NAME(buffer_pool_size).min_val
* Removed srv_buf_pool_def_size and replaced constant defination in
  MYSQL_SYSVAR_LONGLONG(buffer_pool_size)
* Reordered ha_innodb to allow for direct use of MYSQL_SYSVAR_NAME(buffer_pool_size).min_val
* Moved buf_pool_size_align into ha_innodb to access to MYSQL_SYSVAR_NAME(buffer_pool_size).min_val
* loose-innodb_disable_resize_buffer_pool_debug is needed in the
  innodb.restart.opt test so that under debug mode, resizing of the
  innodb buffer pool can occur.

12 files changed, 125 insertions, 7 deletions

diff --git a/mysql-test/suite/innodb/r/innodb_buffer_pool_resize_with_chunks.result b/mysql-test/suite/innodb/r/innodb_buffer_pool_resize_with_chunks.result
index 4bf244c9588..efb652091bf 100644
--- a/mysql-test/suite/innodb/r/innodb_buffer_pool_resize_with_chunks.result
+++ b/mysql-test/suite/innodb/r/innodb_buffer_pool_resize_with_chunks.result
@@ -1,6 +1,6 @@
 select @@innodb_buffer_pool_chunk_size;
 @@innodb_buffer_pool_chunk_size
-2097152
+4194304
 create table t1 (id int not null, val int not null default '0', primary key (id)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;
 create or replace view view0 as select 1 union all select 1;
 set @`v_id` := 0;
@@ -18,9 +18,9 @@ count(val)
 262144
 drop table t1;
 drop view view0;
-set global innodb_buffer_pool_size = 1048576;
+set global innodb_buffer_pool_size = 2*1048576;
 Warnings:
-Warning	1292	Truncated incorrect innodb_buffer_pool_size value: '1048576'
+Warning	1292	Truncated incorrect innodb_buffer_pool_size value: '2097152'
 select @@innodb_buffer_pool_size;
 @@innodb_buffer_pool_size
-6291456
+4194304
diff --git a/mysql-test/suite/innodb/r/restart,16k,innodb.rdiff b/mysql-test/suite/innodb/r/restart,16k,innodb.rdiff
new file mode 100644
index 00000000000..b36ed067913
--- /dev/null
+++ b/mysql-test/suite/innodb/r/restart,16k,innodb.rdiff
@@ -0,0 +1,16 @@
+--- ./suite/innodb/r/restart.result	2022-01-18 20:36:56.054653376 +1100
++++ suite/innodb/r/restart.reject	2022-01-19 08:12:28.602794678 +1100
+@@ -32,10 +32,10 @@
+ SELECT @@innodb_buffer_pool_size INTO @innodb_buffer_pool_size_orig;
+ SELECT CEILING((256 + 64) * @@innodb_page_size / 1048576) * 1048576 INTO @min_pool_size;
+ EXECUTE IMMEDIATE 'SET GLOBAL innodb_buffer_pool_size = ?' USING (@min_pool_size -1);
+-ERROR 42000: Variable 'innodb_buffer_pool_size' can't be set to the value of 'WRONG_VALUE'
++ERROR 42000: Variable 'innodb_buffer_pool_size' can't be set to the value of '5242879'
+ SHOW WARNINGS;
+ Level	Code	Message
+-Warning	1210	innodb_buffer_pool_size must be at least MIN_VAL for innodb_page_size=PAGE_SIZE
+-Error	1231	Variable 'innodb_buffer_pool_size' can't be set to the value of 'WRONG_VALUE'
++Warning	1210	innodb_buffer_pool_size must be at least 5242880 for innodb_page_size=16384
++Error	1231	Variable 'innodb_buffer_pool_size' can't be set to the value of '5242879'
+ EXECUTE IMMEDIATE 'SET GLOBAL innodb_buffer_pool_size = ?' USING (@min_pool_size);
+ SET GLOBAL innodb_buffer_pool_size = @innodb_buffer_pool_size_orig;
diff --git a/mysql-test/suite/innodb/r/restart,32k,innodb.rdiff b/mysql-test/suite/innodb/r/restart,32k,innodb.rdiff
new file mode 100644
index 00000000000..8fa057814c4
--- /dev/null
+++ b/mysql-test/suite/innodb/r/restart,32k,innodb.rdiff
@@ -0,0 +1,16 @@
+--- ./suite/innodb/r/restart.result	2022-01-18 20:36:56.054653376 +1100
++++ suite/innodb/r/restart.reject	2022-01-19 08:07:57.402230887 +1100
+@@ -32,10 +32,10 @@
+ SELECT @@innodb_buffer_pool_size INTO @innodb_buffer_pool_size_orig;
+ SELECT CEILING((256 + 64) * @@innodb_page_size / 1048576) * 1048576 INTO @min_pool_size;
+ EXECUTE IMMEDIATE 'SET GLOBAL innodb_buffer_pool_size = ?' USING (@min_pool_size -1);
+-ERROR 42000: Variable 'innodb_buffer_pool_size' can't be set to the value of 'WRONG_VALUE'
++ERROR 42000: Variable 'innodb_buffer_pool_size' can't be set to the value of '10485759'
+ SHOW WARNINGS;
+ Level	Code	Message
+-Warning	1210	innodb_buffer_pool_size must be at least MIN_VAL for innodb_page_size=PAGE_SIZE
+-Error	1231	Variable 'innodb_buffer_pool_size' can't be set to the value of 'WRONG_VALUE'
++Warning	1210	innodb_buffer_pool_size must be at least 10485760 for innodb_page_size=32768
++Error	1231	Variable 'innodb_buffer_pool_size' can't be set to the value of '10485759'
+ EXECUTE IMMEDIATE 'SET GLOBAL innodb_buffer_pool_size = ?' USING (@min_pool_size);
+ SET GLOBAL innodb_buffer_pool_size = @innodb_buffer_pool_size_orig;
diff --git a/mysql-test/suite/innodb/r/restart,4k,innodb.rdiff b/mysql-test/suite/innodb/r/restart,4k,innodb.rdiff
new file mode 100644
index 00000000000..7d0846360e0
--- /dev/null
+++ b/mysql-test/suite/innodb/r/restart,4k,innodb.rdiff
@@ -0,0 +1,16 @@
+--- ./suite/innodb/r/restart.result	2022-01-18 20:36:56.054653376 +1100
++++ suite/innodb/r/restart.reject	2022-01-19 08:13:56.397475513 +1100
+@@ -32,10 +32,10 @@
+ SELECT @@innodb_buffer_pool_size INTO @innodb_buffer_pool_size_orig;
+ SELECT CEILING((256 + 64) * @@innodb_page_size / 1048576) * 1048576 INTO @min_pool_size;
+ EXECUTE IMMEDIATE 'SET GLOBAL innodb_buffer_pool_size = ?' USING (@min_pool_size -1);
+-ERROR 42000: Variable 'innodb_buffer_pool_size' can't be set to the value of 'WRONG_VALUE'
++ERROR 42000: Variable 'innodb_buffer_pool_size' can't be set to the value of '2097151'
+ SHOW WARNINGS;
+ Level	Code	Message
+-Warning	1210	innodb_buffer_pool_size must be at least MIN_VAL for innodb_page_size=PAGE_SIZE
+-Error	1231	Variable 'innodb_buffer_pool_size' can't be set to the value of 'WRONG_VALUE'
++Warning	1210	innodb_buffer_pool_size must be at least 2097152 for innodb_page_size=4096
++Error	1231	Variable 'innodb_buffer_pool_size' can't be set to the value of '2097151'
+ EXECUTE IMMEDIATE 'SET GLOBAL innodb_buffer_pool_size = ?' USING (@min_pool_size);
+ SET GLOBAL innodb_buffer_pool_size = @innodb_buffer_pool_size_orig;
diff --git a/mysql-test/suite/innodb/r/restart,64k,innodb.rdiff b/mysql-test/suite/innodb/r/restart,64k,innodb.rdiff
new file mode 100644
index 00000000000..3ac9f45b196
--- /dev/null
+++ b/mysql-test/suite/innodb/r/restart,64k,innodb.rdiff
@@ -0,0 +1,16 @@
+--- ./suite/innodb/r/restart.result	2022-01-18 20:36:56.054653376 +1100
++++ suite/innodb/r/restart.reject	2022-01-19 08:11:32.418759095 +1100
+@@ -32,10 +32,10 @@
+ SELECT @@innodb_buffer_pool_size INTO @innodb_buffer_pool_size_orig;
+ SELECT CEILING((256 + 64) * @@innodb_page_size / 1048576) * 1048576 INTO @min_pool_size;
+ EXECUTE IMMEDIATE 'SET GLOBAL innodb_buffer_pool_size = ?' USING (@min_pool_size -1);
+-ERROR 42000: Variable 'innodb_buffer_pool_size' can't be set to the value of 'WRONG_VALUE'
++ERROR 42000: Variable 'innodb_buffer_pool_size' can't be set to the value of '20971519'
+ SHOW WARNINGS;
+ Level	Code	Message
+-Warning	1210	innodb_buffer_pool_size must be at least MIN_VAL for innodb_page_size=PAGE_SIZE
+-Error	1231	Variable 'innodb_buffer_pool_size' can't be set to the value of 'WRONG_VALUE'
++Warning	1210	innodb_buffer_pool_size must be at least 20971520 for innodb_page_size=65536
++Error	1231	Variable 'innodb_buffer_pool_size' can't be set to the value of '20971519'
+ EXECUTE IMMEDIATE 'SET GLOBAL innodb_buffer_pool_size = ?' USING (@min_pool_size);
+ SET GLOBAL innodb_buffer_pool_size = @innodb_buffer_pool_size_orig;
diff --git a/mysql-test/suite/innodb/r/restart,8k,innodb.rdiff b/mysql-test/suite/innodb/r/restart,8k,innodb.rdiff
new file mode 100644
index 00000000000..4da55ebfcef
--- /dev/null
+++ b/mysql-test/suite/innodb/r/restart,8k,innodb.rdiff
@@ -0,0 +1,16 @@
+--- ./suite/innodb/r/restart.result	2022-01-18 20:36:56.054653376 +1100
++++ suite/innodb/r/restart.reject	2022-01-19 08:13:11.027788852 +1100
+@@ -32,10 +32,10 @@
+ SELECT @@innodb_buffer_pool_size INTO @innodb_buffer_pool_size_orig;
+ SELECT CEILING((256 + 64) * @@innodb_page_size / 1048576) * 1048576 INTO @min_pool_size;
+ EXECUTE IMMEDIATE 'SET GLOBAL innodb_buffer_pool_size = ?' USING (@min_pool_size -1);
+-ERROR 42000: Variable 'innodb_buffer_pool_size' can't be set to the value of 'WRONG_VALUE'
++ERROR 42000: Variable 'innodb_buffer_pool_size' can't be set to the value of '3145727'
+ SHOW WARNINGS;
+ Level	Code	Message
+-Warning	1210	innodb_buffer_pool_size must be at least MIN_VAL for innodb_page_size=PAGE_SIZE
+-Error	1231	Variable 'innodb_buffer_pool_size' can't be set to the value of 'WRONG_VALUE'
++Warning	1210	innodb_buffer_pool_size must be at least 3145728 for innodb_page_size=8192
++Error	1231	Variable 'innodb_buffer_pool_size' can't be set to the value of '3145727'
+ EXECUTE IMMEDIATE 'SET GLOBAL innodb_buffer_pool_size = ?' USING (@min_pool_size);
+ SET GLOBAL innodb_buffer_pool_size = @innodb_buffer_pool_size_orig;
diff --git a/mysql-test/suite/innodb/r/restart.result b/mysql-test/suite/innodb/r/restart.result
index 5e3315be204..606e94d81db 100644
--- a/mysql-test/suite/innodb/r/restart.result
+++ b/mysql-test/suite/innodb/r/restart.result
@@ -26,3 +26,16 @@ a
 SELECT * FROM td;
 a
 DROP TABLE tr,tc,td;
+#
+# MDEV-27467 innodb to enfore the minimum innodb_buffer_pool_size in SET (resize) the same as startup
+#
+SELECT @@innodb_buffer_pool_size INTO @innodb_buffer_pool_size_orig;
+SELECT CEILING((256 + 64) * @@innodb_page_size / 1048576) * 1048576 INTO @min_pool_size;
+EXECUTE IMMEDIATE 'SET GLOBAL innodb_buffer_pool_size = ?' USING (@min_pool_size -1);
+ERROR 42000: Variable 'innodb_buffer_pool_size' can't be set to the value of 'WRONG_VALUE'
+SHOW WARNINGS;
+Level	Code	Message
+Warning	1210	innodb_buffer_pool_size must be at least MIN_VAL for innodb_page_size=PAGE_SIZE
+Error	1231	Variable 'innodb_buffer_pool_size' can't be set to the value of 'WRONG_VALUE'
+EXECUTE IMMEDIATE 'SET GLOBAL innodb_buffer_pool_size = ?' USING (@min_pool_size);
+SET GLOBAL innodb_buffer_pool_size = @innodb_buffer_pool_size_orig;
diff --git a/mysql-test/suite/innodb/t/innodb_buffer_pool_resize_with_chunks.opt b/mysql-test/suite/innodb/t/innodb_buffer_pool_resize_with_chunks.opt
index b97a3995457..ade197de338 100644
--- a/mysql-test/suite/innodb/t/innodb_buffer_pool_resize_with_chunks.opt
+++ b/mysql-test/suite/innodb/t/innodb_buffer_pool_resize_with_chunks.opt
@@ -1,3 +1,3 @@
 --innodb-buffer-pool-size=16M
---innodb-buffer-pool-chunk-size=2M
+--innodb-buffer-pool-chunk-size=4M
 --innodb-page-size=4k
diff --git a/mysql-test/suite/innodb/t/innodb_buffer_pool_resize_with_chunks.test b/mysql-test/suite/innodb/t/innodb_buffer_pool_resize_with_chunks.test
index b04b0306bf1..7a26db65d33 100644
--- a/mysql-test/suite/innodb/t/innodb_buffer_pool_resize_with_chunks.test
+++ b/mysql-test/suite/innodb/t/innodb_buffer_pool_resize_with_chunks.test
@@ -49,7 +49,7 @@ drop table t1;
 drop view view0;
 
 # Try to shrink buffer pool to smaller than chunk size
-set global innodb_buffer_pool_size = 1048576;
+set global innodb_buffer_pool_size = 2*1048576;
 --source include/wait_condition.inc
 select @@innodb_buffer_pool_size;
 
diff --git a/mysql-test/suite/innodb/t/restart.opt b/mysql-test/suite/innodb/t/restart.opt
new file mode 100644
index 00000000000..ce43e89cb2b
--- /dev/null
+++ b/mysql-test/suite/innodb/t/restart.opt
@@ -0,0 +1,2 @@
+--loose-innodb_disable_resize_buffer_pool_debug=0
+--innodb-buffer-pool-chunk-size=1M
diff --git a/mysql-test/suite/innodb/t/restart.test b/mysql-test/suite/innodb/t/restart.test
index d7582306492..3f28b3976c3 100644
--- a/mysql-test/suite/innodb/t/restart.test
+++ b/mysql-test/suite/innodb/t/restart.test
@@ -77,3 +77,26 @@ SELECT * FROM tr;
 SELECT * FROM tc;
 SELECT * FROM td;
 DROP TABLE tr,tc,td;
+
+--echo #
+--echo # MDEV-27467 innodb to enfore the minimum innodb_buffer_pool_size in SET (resize) the same as startup
+--echo #
+
+let $wait_timeout = 180;
+let $wait_condition =
+  SELECT SUBSTR(variable_value, 1, 34) = 'Completed resizing buffer pool at '
+  FROM information_schema.global_status
+  WHERE LOWER(variable_name) = 'innodb_buffer_pool_resize_status';
+
+SELECT @@innodb_buffer_pool_size INTO @innodb_buffer_pool_size_orig;
+SELECT CEILING((256 + 64) * @@innodb_page_size / 1048576) * 1048576 INTO @min_pool_size;
+--error ER_WRONG_VALUE_FOR_VAR
+EXECUTE IMMEDIATE 'SET GLOBAL innodb_buffer_pool_size = ?' USING (@min_pool_size -1);
+
+SHOW WARNINGS;
+
+EXECUTE IMMEDIATE 'SET GLOBAL innodb_buffer_pool_size = ?' USING (@min_pool_size);
+
+--source include/wait_condition.inc
+
+SET GLOBAL innodb_buffer_pool_size = @innodb_buffer_pool_size_orig;
diff --git a/mysql-test/suite/sys_vars/r/sysvars_innodb.result b/mysql-test/suite/sys_vars/r/sysvars_innodb.result
index ecc5e9b50bb..a1a59425502 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_innodb.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_innodb.result
@@ -300,7 +300,7 @@ DEFAULT_VALUE	134217728
 VARIABLE_SCOPE	GLOBAL
 VARIABLE_TYPE	BIGINT
 VARIABLE_COMMENT	The size of the memory buffer InnoDB uses to cache data and indexes of its tables.
-NUMERIC_MIN_VALUE	5242880
+NUMERIC_MIN_VALUE	2097152
 NUMERIC_MAX_VALUE	9223372036854775807
 NUMERIC_BLOCK_SIZE	1048576
 ENUM_VALUE_LIST	NULL