MDEV-25342: autosize innodb_buffer_pool_chunk_sizepreview-10.8-MDEV-25342-autosize-innodb-buffer-pool-chunk-sizebb-10.8-MDEV-25342-autosize-innodb-buffer-pool-chunk-size

The previous default innodb_buffer_pool_chunk_size of 128M
made sense when the innodb buffer pool size was a few GB.

When the pool size is 128GB this means the chunk size is 0.1%
of this. Fine tuning the buffer pool size on such a fine
increment doesn't make practical sense. Also on extremely
large buffer pool systems, initializing on the default 128M can
also take a considerable amount of time.

When large pages are enabled, the chunk size has to be a multiple
of an available large page size or memory allocation without
use can occur.

Previously the default 0 was documented as disabling resizing.
With srv_buf_pool_chunk_unit > 0 assertions in the code and the
minimium value set, I doubt this was ever the case.

As such the autosizing (based on default 0) takes place as follows:
* a 64th of the innodb_buffer_pool_size
* if large pages, this is rounded down the the nearest multiple
  of the large page size.
* If less than 1MB, set to 1MB.

This does mean the new default innodb_buffer_pool_chunk size is
2MB, derived form the above formular with 128MB as the buffer pool
size.

The innodb_buffer_pool_chunk_size is changed to a size_t for
better compatiblity with the memory allocations which use size_t.
The previous upper limit is changed to the maxium of a size_t. The
maximium value used is the buffer pool size anyway.

Getting this default value of the chunk size to a more practical
size facilitates further development of more automated resizing
without significant overhead or memory fragmentation.

innodb_buffer_pool_resize test adjusted based on 1M default
chunk size thanks Wlad.

12 files changed, 65 insertions, 33 deletions

diff --git a/extra/mariabackup/xtrabackup.cc b/extra/mariabackup/xtrabackup.cc
index c89c3188b0e..51db70fe431 100644
--- a/extra/mariabackup/xtrabackup.cc
+++ b/extra/mariabackup/xtrabackup.cc
@@ -2054,7 +2054,7 @@ static bool innodb_init_param()
         changes the value so that it becomes the number of database pages. */
 
 	srv_buf_pool_size = (ulint) xtrabackup_use_memory;
-	srv_buf_pool_chunk_unit = (ulong)srv_buf_pool_size;
+	srv_buf_pool_chunk_unit = (size_t) srv_buf_pool_size;
 
 	srv_n_file_io_threads = (uint) innobase_file_io_threads;
 	srv_n_read_io_threads = (uint) innobase_read_io_threads;
diff --git a/include/my_sys.h b/include/my_sys.h
index c6526e581cd..3d2a7280e53 100644
--- a/include/my_sys.h
+++ b/include/my_sys.h
@@ -178,6 +178,7 @@ extern char *my_strndup(PSI_memory_key key, const char *from, size_t length, myf
 int my_init_large_pages(my_bool super_large_pages);
 uchar *my_large_malloc(size_t *size, myf my_flags);
 void my_large_free(void *ptr, size_t size);
+void my_large_page_truncate(size_t *size);
 
 #ifdef _WIN32
 extern BOOL my_obtain_privilege(LPCSTR lpPrivilege);
diff --git a/mysql-test/suite/innodb/r/innodb_buffer_pool_resize.result b/mysql-test/suite/innodb/r/innodb_buffer_pool_resize.result
index 667d31a0b69..cafa3f45eab 100644
--- a/mysql-test/suite/innodb/r/innodb_buffer_pool_resize.result
+++ b/mysql-test/suite/innodb/r/innodb_buffer_pool_resize.result
@@ -3,22 +3,20 @@ select @@innodb_buffer_pool_size;
 @@innodb_buffer_pool_size
 8388608
 set global innodb_buffer_pool_size = 10485760;
-Warnings:
-Warning	1292	Truncated incorrect innodb_buffer_pool_size value: '10485760'
 select @@innodb_buffer_pool_size;
 @@innodb_buffer_pool_size
-16777216
+10485760
 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;
 set @`v_val` := 0;
 replace into t1 select (@`v_id` := (@`v_id` + 4) mod 4294967296) as id, (@`v_val` := (@`v_val` + 4) mod 4294967296) as val from view0 v0, view0 v1, view0 v2, view0 v3, view0 v4, view0 v5, view0 v6, view0 v7, view0 v8, view0 v9, view0 v10, view0 v11, view0 v12, view0 v13, view0 v14, view0 v15, view0 v16, view0 v17;
-set global innodb_buffer_pool_size = 7340032;
+set global innodb_buffer_pool_size = 64 * 1024 * 1024 + 512 * 1024;
 Warnings:
-Warning	1292	Truncated incorrect innodb_buffer_pool_size value: '7340032'
+Warning	1292	Truncated incorrect innodb_buffer_pool_size value: '67633152'
 select @@innodb_buffer_pool_size;
 @@innodb_buffer_pool_size
-8388608
+68157440
 select count(val) from t1;
 count(val)
 262144
diff --git a/mysql-test/suite/innodb/t/innodb_buffer_pool_resize.test b/mysql-test/suite/innodb/t/innodb_buffer_pool_resize.test
index 932829470e3..da9e19c3d1c 100644
--- a/mysql-test/suite/innodb/t/innodb_buffer_pool_resize.test
+++ b/mysql-test/suite/innodb/t/innodb_buffer_pool_resize.test
@@ -45,7 +45,7 @@ SET GLOBAL innodb_read_only_compressed=@save_innodb_read_only_compressed;
 --enable_query_log
 
 # Shrink buffer pool
-set global innodb_buffer_pool_size = 7340032;
+set global innodb_buffer_pool_size = 64 * 1024 * 1024 + 512 * 1024;
 --source include/wait_condition.inc
 
 select @@innodb_buffer_pool_size;
diff --git a/mysql-test/suite/sys_vars/r/sysvars_innodb,32bit.rdiff b/mysql-test/suite/sys_vars/r/sysvars_innodb,32bit.rdiff
index 65937a4f358..a912ce82cf8 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_innodb,32bit.rdiff
+++ b/mysql-test/suite/sys_vars/r/sysvars_innodb,32bit.rdiff
@@ -20,14 +20,14 @@
  NUMERIC_MAX_VALUE	2
 @@ -97,10 +97,10 @@
  SESSION_VALUE	NULL
- DEFAULT_VALUE	134217728
+ DEFAULT_VALUE	0
  VARIABLE_SCOPE	GLOBAL
 -VARIABLE_TYPE	BIGINT UNSIGNED
 +VARIABLE_TYPE	INT UNSIGNED
- VARIABLE_COMMENT	Size of a single memory chunk for resizing buffer pool. Online buffer pool resizing happens at this granularity. 0 means disable resizing buffer pool.
- NUMERIC_MIN_VALUE	1048576
--NUMERIC_MAX_VALUE	9223372036854775807
-+NUMERIC_MAX_VALUE	2147483647
+VARIABLE_COMMENT	Size of a single memory chunk for resizing buffer pool. Online buffer pool resizing happens at this granularity. 0 means autosize this variable based on buffer pool size.
+ NUMERIC_MIN_VALUE	0
+-NUMERIC_MAX_VALUE	18446744073709551615
++NUMERIC_MAX_VALUE	4294967295
  NUMERIC_BLOCK_SIZE	1048576
  ENUM_VALUE_LIST	NULL
  READ_ONLY	YES
diff --git a/mysql-test/suite/sys_vars/r/sysvars_innodb.result b/mysql-test/suite/sys_vars/r/sysvars_innodb.result
index 177a27925e6..324433acbe3 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_innodb.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_innodb.result
@@ -83,12 +83,12 @@ READ_ONLY	YES
 COMMAND_LINE_ARGUMENT	REQUIRED
 VARIABLE_NAME	INNODB_BUFFER_POOL_CHUNK_SIZE
 SESSION_VALUE	NULL
-DEFAULT_VALUE	134217728
+DEFAULT_VALUE	0
 VARIABLE_SCOPE	GLOBAL
 VARIABLE_TYPE	BIGINT UNSIGNED
-VARIABLE_COMMENT	Size of a single memory chunk for resizing buffer pool. Online buffer pool resizing happens at this granularity. 0 means disable resizing buffer pool.
-NUMERIC_MIN_VALUE	1048576
-NUMERIC_MAX_VALUE	9223372036854775807
+VARIABLE_COMMENT	Size of a single memory chunk for resizing buffer pool. Online buffer pool resizing happens at this granularity. 0 means autosize this variable based on buffer pool size.
+NUMERIC_MIN_VALUE	0
+NUMERIC_MAX_VALUE	18446744073709551615
 NUMERIC_BLOCK_SIZE	1048576
 ENUM_VALUE_LIST	NULL
 READ_ONLY	YES
diff --git a/mysys/my_largepage.c b/mysys/my_largepage.c
index 0fdc4e17a26..9cc1e19772b 100644
--- a/mysys/my_largepage.c
+++ b/mysys/my_largepage.c
@@ -251,6 +251,28 @@ int my_init_large_pages(my_bool super_large_pages)
 }
 
 
+/**
+   Large page size helper.
+   This rounds down, if needed, the size parameter to the largest
+   multiple of an available large page size on the system.
+*/
+void my_large_page_truncate(size_t *size)
+{
+  if (my_use_large_pages)
+  {
+    size_t large_page_size= 0;
+#ifdef _WIN32
+    large_page_size= my_large_page_size;
+#elif defined(HAVE_MMAP)
+    int page_i= 0;
+    large_page_size= my_next_large_page_size(*size, &page_i);
+#endif
+    if (large_page_size > 0)
+      *size-= *size % large_page_size;
+  }
+}
+
+
 #if defined(HAVE_MMAP) && !defined(_WIN32)
 /* Solaris for example has only MAP_ANON, FreeBSD has MAP_ANONYMOUS and
 MAP_ANON but MAP_ANONYMOUS is marked "for compatibility" */
diff --git a/storage/innobase/buf/buf0buf.cc b/storage/innobase/buf/buf0buf.cc
index c31d9aef8eb..2c0de02cce2 100644
--- a/storage/innobase/buf/buf0buf.cc
+++ b/storage/innobase/buf/buf0buf.cc
@@ -1910,7 +1910,7 @@ calc_buf_pool_size:
   read_ahead_area= s >= READ_AHEAD_PAGES
     ? READ_AHEAD_PAGES
     : my_round_up_to_next_power(static_cast<uint32_t>(s));
-  curr_pool_size= n_chunks * srv_buf_pool_chunk_unit;
+  curr_pool_size= (ulint) n_chunks * srv_buf_pool_chunk_unit;
   srv_buf_pool_curr_size= curr_pool_size;/* FIXME: remove*/
   extern ulonglong innobase_buffer_pool_size;
   innobase_buffer_pool_size= buf_pool_size_align(srv_buf_pool_curr_size);
diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc
index ba1b2a7883c..468125dcd2a 100644
--- a/storage/innobase/handler/ha_innodb.cc
+++ b/storage/innobase/handler/ha_innodb.cc
@@ -171,6 +171,8 @@ static const long AUTOINC_OLD_STYLE_LOCKING = 0;
 static const long AUTOINC_NEW_STYLE_LOCKING = 1;
 static const long AUTOINC_NO_LOCKING = 2;
 
+static constexpr size_t buf_pool_chunk_min_size= 1U << 20;
+
 static ulong innobase_open_files;
 static long innobase_autoinc_lock_mode;
 
@@ -3719,17 +3721,26 @@ static ulonglong innodb_prepare_commit_versioned(THD* thd, ulonglong *trx_id)
   return 0;
 }
 
-/** Initialize and normalize innodb_buffer_pool_size. */
+/** Initialize and normalize innodb_buffer_pool_{chunk_,}size. */
 static void innodb_buffer_pool_size_init()
 {
-	if (srv_buf_pool_chunk_unit > srv_buf_pool_size) {
-		/* Size unit of buffer pool is larger than srv_buf_pool_size.
-		adjust srv_buf_pool_chunk_unit for srv_buf_pool_size. */
-		srv_buf_pool_chunk_unit = ulong(srv_buf_pool_size);
-	}
+  if (srv_buf_pool_chunk_unit > srv_buf_pool_size)
+  {
+    /* Size unit of buffer pool is larger than srv_buf_pool_size.
+    adjust srv_buf_pool_chunk_unit for srv_buf_pool_size. */
+    srv_buf_pool_chunk_unit = ulong(srv_buf_pool_size);
+  }
+  else if (srv_buf_pool_chunk_unit == 0)
+  {
+    srv_buf_pool_chunk_unit = srv_buf_pool_size / 64;
+    my_large_page_truncate(&srv_buf_pool_chunk_unit);
+  }
+
+  if (srv_buf_pool_chunk_unit < buf_pool_chunk_min_size)
+    srv_buf_pool_chunk_unit = buf_pool_chunk_min_size;
 
-	srv_buf_pool_size = buf_pool_size_align(srv_buf_pool_size);
-	innobase_buffer_pool_size = srv_buf_pool_size;
+  srv_buf_pool_size = buf_pool_size_align(srv_buf_pool_size);
+  innobase_buffer_pool_size = srv_buf_pool_size;
 }
 
 
@@ -19046,13 +19057,13 @@ static MYSQL_SYSVAR_ULONGLONG(buffer_pool_size, innobase_buffer_pool_size,
   srv_buf_pool_min_size,
   LLONG_MAX, 1024*1024L);
 
-static MYSQL_SYSVAR_ULONG(buffer_pool_chunk_size, srv_buf_pool_chunk_unit,
+static MYSQL_SYSVAR_SIZE_T(buffer_pool_chunk_size, srv_buf_pool_chunk_unit,
   PLUGIN_VAR_RQCMDARG | PLUGIN_VAR_READONLY,
   "Size of a single memory chunk"
-  " for resizing buffer pool. Online buffer pool resizing happens"
-  " at this granularity. 0 means disable resizing buffer pool.",
+  " for resizing buffer pool. Online buffer pool resizing happens at this"
+  " granularity. 0 means autosize this variable based on buffer pool size.",
   NULL, NULL,
-  128 * 1024 * 1024, 1024 * 1024, LONG_MAX, 1024 * 1024);
+  0, 0, SIZE_T_MAX, 1024 * 1024);
 
 static MYSQL_SYSVAR_STR(buffer_pool_filename, srv_buf_dump_filename,
   PLUGIN_VAR_RQCMDARG | PLUGIN_VAR_MEMALLOC,
diff --git a/storage/innobase/include/buf0buf.ic b/storage/innobase/include/buf0buf.ic
index 5baee629dde..2eb69edddd0 100644
--- a/storage/innobase/include/buf0buf.ic
+++ b/storage/innobase/include/buf0buf.ic
@@ -151,7 +151,7 @@ ulint
 buf_pool_size_align(
 	ulint	size)
 {
-	const ulong	m = srv_buf_pool_chunk_unit;
+	const size_t m = srv_buf_pool_chunk_unit;
 	size = ut_max(size, srv_buf_pool_min_size);
 
 	if (size % m == 0) {
diff --git a/storage/innobase/include/srv0srv.h b/storage/innobase/include/srv0srv.h
index 6e8e2952876..077239b7515 100644
--- a/storage/innobase/include/srv0srv.h
+++ b/storage/innobase/include/srv0srv.h
@@ -314,7 +314,7 @@ extern const ulint	srv_buf_pool_min_size;
 /** Default pool size in bytes */
 extern const ulint	srv_buf_pool_def_size;
 /** Requested buffer pool chunk size */
-extern ulong		srv_buf_pool_chunk_unit;
+extern size_t		srv_buf_pool_chunk_unit;
 /** Scan depth for LRU flush batch i.e.: number of blocks scanned*/
 extern ulong	srv_LRU_scan_depth;
 /** Whether or not to flush neighbors of a block */
diff --git a/storage/innobase/srv/srv0srv.cc b/storage/innobase/srv/srv0srv.cc
index 9c5aea2a453..faa896ff0e1 100644
--- a/storage/innobase/srv/srv0srv.cc
+++ b/storage/innobase/srv/srv0srv.cc
@@ -192,7 +192,7 @@ const ulint	srv_buf_pool_min_size	= 5 * 1024 * 1024;
 /** Default pool size in bytes */
 const ulint	srv_buf_pool_def_size	= 128 * 1024 * 1024;
 /** Requested buffer pool chunk size */
-ulong	srv_buf_pool_chunk_unit;
+size_t	srv_buf_pool_chunk_unit;
 /** innodb_lru_scan_depth; number of blocks scanned in LRU flush batch */
 ulong	srv_LRU_scan_depth;
 /** innodb_flush_neighbors; whether or not to flush neighbors of a block */