MDEV-26520 Make innodb_purge_threads dynamicbb-10.7-MDEV-26520

preallocate max. count of THDs puge might need at startup.
Doing this at any other point of time is difficult, error prone

5 files changed, 53 insertions, 10 deletions

diff --git a/mysql-test/suite/sys_vars/r/innodb_purge_threads_basic.result b/mysql-test/suite/sys_vars/r/innodb_purge_threads_basic.result
index 2cb697acb6d..68d3d40091a 100644
--- a/mysql-test/suite/sys_vars/r/innodb_purge_threads_basic.result
+++ b/mysql-test/suite/sys_vars/r/innodb_purge_threads_basic.result
@@ -6,9 +6,27 @@ SELECT COUNT(@@innodb_purge_threads);
 COUNT(@@innodb_purge_threads)
 1
 1 Expected
+SET @save_threads = @@GLOBAL.innodb_purge_threads;
+SET @@GLOBAL.innodb_purge_threads=-1;
+Warnings:
+Warning	1292	Truncated incorrect innodb_purge_threads value: '-1'
+SELECT @@GLOBAL.innodb_purge_threads;
+@@GLOBAL.innodb_purge_threads
+1
+SET @@GLOBAL.innodb_purge_threads=0;
+Warnings:
+Warning	1292	Truncated incorrect innodb_purge_threads value: '0'
+SELECT @@GLOBAL.innodb_purge_threads;
+@@GLOBAL.innodb_purge_threads
+1
+SET @@GLOBAL.innodb_purge_threads=33;
+Warnings:
+Warning	1292	Truncated incorrect innodb_purge_threads value: '33'
+SELECT @@GLOBAL.innodb_purge_threads;
+@@GLOBAL.innodb_purge_threads
+32
 SET @@GLOBAL.innodb_purge_threads=1;
-ERROR HY000: Variable 'innodb_purge_threads' is a read only variable
-Expected error 'Read-only variable'
+SET @@GLOBAL.innodb_purge_threads=32;
 SELECT innodb_purge_threads = @@SESSION.innodb_purge_threads;
 ERROR 42S22: Unknown column 'innodb_purge_threads' in 'field list'
 Expected error 'Read-only variable'
@@ -38,4 +56,5 @@ SELECT VARIABLE_NAME, VARIABLE_VALUE
 FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
 WHERE VARIABLE_NAME = 'innodb_purge_threads';
 VARIABLE_NAME	VARIABLE_VALUE
-INNODB_PURGE_THREADS	4
+INNODB_PURGE_THREADS	32
+SET GLOBAL innodb_purge_threads = @save_threads;
diff --git a/mysql-test/suite/sys_vars/r/sysvars_innodb.result b/mysql-test/suite/sys_vars/r/sysvars_innodb.result
index 7182d42c6ca..068ef4da079 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_innodb.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_innodb.result
@@ -1387,7 +1387,7 @@ NUMERIC_MIN_VALUE	1
 NUMERIC_MAX_VALUE	32
 NUMERIC_BLOCK_SIZE	0
 ENUM_VALUE_LIST	NULL
-READ_ONLY	YES
+READ_ONLY	NO
 COMMAND_LINE_ARGUMENT	OPTIONAL
 VARIABLE_NAME	INNODB_RANDOM_READ_AHEAD
 SESSION_VALUE	NULL
diff --git a/mysql-test/suite/sys_vars/t/innodb_purge_threads_basic.test b/mysql-test/suite/sys_vars/t/innodb_purge_threads_basic.test
index 4d039601e40..1156fa40a4a 100644
--- a/mysql-test/suite/sys_vars/t/innodb_purge_threads_basic.test
+++ b/mysql-test/suite/sys_vars/t/innodb_purge_threads_basic.test
@@ -11,9 +11,17 @@ SELECT COUNT(@@GLOBAL.innodb_purge_threads);
 SELECT COUNT(@@innodb_purge_threads);
 --echo 1 Expected
 
---error ER_INCORRECT_GLOBAL_LOCAL_VAR
+SET @save_threads = @@GLOBAL.innodb_purge_threads;
+
+SET @@GLOBAL.innodb_purge_threads=-1;
+SELECT @@GLOBAL.innodb_purge_threads;
+SET @@GLOBAL.innodb_purge_threads=0;
+SELECT @@GLOBAL.innodb_purge_threads;
+SET @@GLOBAL.innodb_purge_threads=33;
+SELECT @@GLOBAL.innodb_purge_threads;
+
 SET @@GLOBAL.innodb_purge_threads=1;
---echo Expected error 'Read-only variable'
+SET @@GLOBAL.innodb_purge_threads=32;
 
 --Error ER_BAD_FIELD_ERROR
 SELECT innodb_purge_threads = @@SESSION.innodb_purge_threads;
@@ -51,3 +59,5 @@ FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
 WHERE VARIABLE_NAME = 'innodb_purge_threads';
 --enable_warnings
 
+
+SET GLOBAL innodb_purge_threads = @save_threads;
diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc
index 188643b3a2f..43504e2a3be 100644
--- a/storage/innobase/handler/ha_innodb.cc
+++ b/storage/innobase/handler/ha_innodb.cc
@@ -18752,10 +18752,23 @@ static MYSQL_SYSVAR_ULONG(purge_batch_size, srv_purge_batch_size,
   1,			/* Minimum value */
   5000, 0);		/* Maximum value */
 
+extern void srv_update_purge_thread_count(uint n);
+
+static
+void
+innodb_purge_threads_update(THD*, struct st_mysql_sys_var*, void*, const void*save )
+{
+  srv_update_purge_thread_count(*static_cast<const uint*>(save));
+}
+
 static MYSQL_SYSVAR_UINT(purge_threads, srv_n_purge_threads,
-  PLUGIN_VAR_OPCMDARG | PLUGIN_VAR_READONLY,
+  PLUGIN_VAR_OPCMDARG,
   "Number of tasks for purging transaction history",
-  NULL, NULL, 4, 1, innodb_purge_threads_MAX, 0);
+  NULL, innodb_purge_threads_update,
+  4,			    /* Default setting */
+  1,			    /* Minimum value */
+  innodb_purge_threads_MAX, /* Maximum value */
+  0);
 
 static MYSQL_SYSVAR_UINT(fast_shutdown, srv_fast_shutdown,
   PLUGIN_VAR_OPCMDARG,
diff --git a/storage/innobase/srv/srv0srv.cc b/storage/innobase/srv/srv0srv.cc
index 7416bbd415d..a22d75033c6 100644
--- a/storage/innobase/srv/srv0srv.cc
+++ b/storage/innobase/srv/srv0srv.cc
@@ -1723,7 +1723,8 @@ std::mutex purge_thread_count_mtx;
 void srv_update_purge_thread_count(uint n)
 {
 	std::lock_guard<std::mutex> lk(purge_thread_count_mtx);
-	purge_create_background_thds(n);
+	ut_ad(n > 0);
+	ut_ad(n <= innodb_purge_threads_MAX);
 	srv_n_purge_threads = n;
 	srv_purge_thread_count_changed = 1;
 }
@@ -1964,7 +1965,7 @@ static void purge_coordinator_callback(void*)
 
 void srv_init_purge_tasks()
 {
-  purge_create_background_thds(srv_n_purge_threads);
+  purge_create_background_thds(innodb_purge_threads_MAX);
   purge_coordinator_timer= srv_thread_pool->create_timer
     (purge_coordinator_callback, nullptr);
 }