Allow for faster creation of threads in corner cases where pool would be overloaded with long non-yielding queries.

To allow it, change minimum of thread_pool_stall_limit to be 10 milliseconds.

Also introduce a new parameter to oversubscribe a group . Number of threads running in  parallel would be higher than it normally should, leading to thrashing, but it may improving preemptiveness, which is useful for the described corner case.

7 files changed, 104 insertions, 5 deletions

diff --git a/mysql-test/suite/sys_vars/r/thread_pool_oversubscribe_basic.result b/mysql-test/suite/sys_vars/r/thread_pool_oversubscribe_basic.result
new file mode 100644
index 00000000000..a9f1afa9f62
--- /dev/null
+++ b/mysql-test/suite/sys_vars/r/thread_pool_oversubscribe_basic.result
@@ -0,0 +1,47 @@
+SET @start_global_value = @@global.thread_pool_oversubscribe;
+select @@global.thread_pool_oversubscribe;
+@@global.thread_pool_oversubscribe
+3
+select @@session.thread_pool_oversubscribe;
+ERROR HY000: Variable 'thread_pool_oversubscribe' is a GLOBAL variable
+show global variables like 'thread_pool_oversubscribe';
+Variable_name	Value
+thread_pool_oversubscribe	3
+show session variables like 'thread_pool_oversubscribe';
+Variable_name	Value
+thread_pool_oversubscribe	3
+select * from information_schema.global_variables where variable_name='thread_pool_oversubscribe';
+VARIABLE_NAME	VARIABLE_VALUE
+THREAD_POOL_OVERSUBSCRIBE	3
+select * from information_schema.session_variables where variable_name='thread_pool_oversubscribe';
+VARIABLE_NAME	VARIABLE_VALUE
+THREAD_POOL_OVERSUBSCRIBE	3
+set global thread_pool_oversubscribe=60;
+select @@global.thread_pool_oversubscribe;
+@@global.thread_pool_oversubscribe
+60
+set global thread_pool_oversubscribe=1000;
+select @@global.thread_pool_oversubscribe;
+@@global.thread_pool_oversubscribe
+1000
+set session thread_pool_oversubscribe=1;
+ERROR HY000: Variable 'thread_pool_oversubscribe' is a GLOBAL variable and should be set with SET GLOBAL
+set global thread_pool_oversubscribe=1.1;
+ERROR 42000: Incorrect argument type to variable 'thread_pool_oversubscribe'
+set global thread_pool_oversubscribe=1e1;
+ERROR 42000: Incorrect argument type to variable 'thread_pool_oversubscribe'
+set global thread_pool_oversubscribe="foo";
+ERROR 42000: Incorrect argument type to variable 'thread_pool_oversubscribe'
+set global thread_pool_oversubscribe=-1;
+Warnings:
+Warning	1292	Truncated incorrect thread_pool_oversubscribe value: '-1'
+select @@global.thread_pool_oversubscribe;
+@@global.thread_pool_oversubscribe
+1
+set global thread_pool_oversubscribe=10000000000;
+Warnings:
+Warning	1292	Truncated incorrect thread_pool_oversubscribe value: '10000000000'
+select @@global.thread_pool_oversubscribe;
+@@global.thread_pool_oversubscribe
+1000
+set @@global.thread_pool_oversubscribe = @start_global_value;
diff --git a/mysql-test/suite/sys_vars/r/thread_pool_stall_limit_basic.result b/mysql-test/suite/sys_vars/r/thread_pool_stall_limit_basic.result
index 552ac5f54f8..eda4e6baebe 100644
--- a/mysql-test/suite/sys_vars/r/thread_pool_stall_limit_basic.result
+++ b/mysql-test/suite/sys_vars/r/thread_pool_stall_limit_basic.result
@@ -37,7 +37,7 @@ Warnings:
 Warning	1292	Truncated incorrect thread_pool_stall_limit value: '-1'
 select @@global.thread_pool_stall_limit;
 @@global.thread_pool_stall_limit
-60
+10
 set global thread_pool_stall_limit=10000000000;
 Warnings:
 Warning	1292	Truncated incorrect thread_pool_stall_limit value: '10000000000'
diff --git a/mysql-test/suite/sys_vars/t/thread_pool_oversubscribe_basic.test b/mysql-test/suite/sys_vars/t/thread_pool_oversubscribe_basic.test
new file mode 100644
index 00000000000..74f0f5e6ea7
--- /dev/null
+++ b/mysql-test/suite/sys_vars/t/thread_pool_oversubscribe_basic.test
@@ -0,0 +1,43 @@
+# uint global
+--source include/not_windows.inc
+--source include/not_embedded.inc
+SET @start_global_value = @@global.thread_pool_oversubscribe;
+
+#
+# exists as global only
+#
+select @@global.thread_pool_oversubscribe;
+--error ER_INCORRECT_GLOBAL_LOCAL_VAR
+select @@session.thread_pool_oversubscribe;
+show global variables like 'thread_pool_oversubscribe';
+show session variables like 'thread_pool_oversubscribe';
+select * from information_schema.global_variables where variable_name='thread_pool_oversubscribe';
+select * from information_schema.session_variables where variable_name='thread_pool_oversubscribe';
+
+#
+# show that it's writable
+#
+set global thread_pool_oversubscribe=60;
+select @@global.thread_pool_oversubscribe;
+set global thread_pool_oversubscribe=1000;
+select @@global.thread_pool_oversubscribe;
+--error ER_GLOBAL_VARIABLE
+set session thread_pool_oversubscribe=1;
+
+#
+# incorrect types
+#
+--error ER_WRONG_TYPE_FOR_VAR
+set global thread_pool_oversubscribe=1.1;
+--error ER_WRONG_TYPE_FOR_VAR
+set global thread_pool_oversubscribe=1e1;
+--error ER_WRONG_TYPE_FOR_VAR
+set global thread_pool_oversubscribe="foo";
+
+
+set global thread_pool_oversubscribe=-1;
+select @@global.thread_pool_oversubscribe;
+set global thread_pool_oversubscribe=10000000000;
+select @@global.thread_pool_oversubscribe;
+
+set @@global.thread_pool_oversubscribe = @start_global_value;
diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc
index 6feba50a00d..a6485dcaef8 100644
--- a/sql/sys_vars.cc
+++ b/sql/sys_vars.cc
@@ -2215,7 +2215,8 @@ static bool fix_threadpool_size(sys_var*, THD*, enum_var_type)
 
 static bool fix_threadpool_stall_limit(sys_var*, THD*, enum_var_type)
 {
-  tp_set_threadpool_stall_limit(threadpool_size);
+  tp_set_threadpool_stall_limit(threadpool_stall_limit);
+  return false;
 }
 #endif
 
@@ -2236,6 +2237,12 @@ static Sys_var_uint Sys_threadpool_idle_thread_timeout(
   GLOBAL_VAR(threadpool_idle_timeout), CMD_LINE(REQUIRED_ARG),
   VALID_RANGE(1, UINT_MAX), DEFAULT(60), BLOCK_SIZE(1)
 );
+static Sys_var_uint Sys_threadpool_oversubscribe(
+  "thread_pool_oversubscribe",
+  "How many additional active worker threads in a group are allowed.",
+  GLOBAL_VAR(threadpool_oversubscribe), CMD_LINE(REQUIRED_ARG),
+  VALID_RANGE(1, 1000), DEFAULT(3), BLOCK_SIZE(1)
+);
 static Sys_var_uint Sys_threadpool_size(
  "thread_pool_size",
  "Number of concurrently executing threads in the pool. "
@@ -2252,7 +2259,7 @@ static Sys_var_uint Sys_threadpool_stall_limit(
  "If a worker thread is stalled, additional worker thread "
  "may be created to handle remaining clients.",
   GLOBAL_VAR(threadpool_stall_limit), CMD_LINE(REQUIRED_ARG),
-  VALID_RANGE(60, UINT_MAX), DEFAULT(500), BLOCK_SIZE(1),
+  VALID_RANGE(10, UINT_MAX), DEFAULT(500), BLOCK_SIZE(1),
   NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(0), 
   ON_UPDATE(fix_threadpool_stall_limit)
 );
diff --git a/sql/threadpool.h b/sql/threadpool.h
index a8e7f9031b3..966dcbc18e0 100644
--- a/sql/threadpool.h
+++ b/sql/threadpool.h
@@ -7,6 +7,7 @@ extern uint threadpool_idle_timeout; /* Shutdown idle worker threads  after this
 extern uint threadpool_size; /* Number of parallel executing threads */
 extern uint threadpool_stall_limit;  /* time interval in 10 ms units for stall checks*/
 extern uint threadpool_max_threads;  /* Maximum threads in pool */
+extern uint threadpool_oversubscribe;  /* Maximum active threads in group */
 
 /*
   Threadpool statistics
diff --git a/sql/threadpool_common.cc b/sql/threadpool_common.cc
index b6676576fb3..91ae41a058f 100644
--- a/sql/threadpool_common.cc
+++ b/sql/threadpool_common.cc
@@ -23,6 +23,7 @@ uint threadpool_idle_timeout;
 uint threadpool_size;
 uint threadpool_stall_limit;
 uint threadpool_max_threads;
+uint threadpool_oversubscribe;
 
 
 /*
diff --git a/sql/threadpool_unix.cc b/sql/threadpool_unix.cc
index d9eb90532af..ec9f5a91d40 100644
--- a/sql/threadpool_unix.cc
+++ b/sql/threadpool_unix.cc
@@ -839,13 +839,13 @@ static void post_event(thread_group_t *thread_group, pool_event_t* ev)
 
 
 /* 
-   Check if pool is already overcommited. 
    This is used to prevent too many threads executing at the same time,
    if the workload is not CPU bound.
 */
 static bool too_many_threads(thread_group_t *thread_group)
 {
-  return (thread_group->active_thread_count >= 4 && !thread_group->stalled);
+  return (thread_group->active_thread_count >= 1+(int)threadpool_oversubscribe 
+   && !thread_group->stalled);
 }