Bug #92024 Setting thread_cache_size to 0 does not empty the Thread Cache.
Submitted: 15 Aug 2018 17:48 Modified: 2 Nov 2018 17:53
Reporter: Jean-Francois Gagne Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Connection Handling Severity:S3 (Non-critical)
Version:5.7.23, 8.0.12, 5.6.41 OS:Any
Assigned to: CPU Architecture:Any

[15 Aug 2018 17:48] Jean-Francois Gagne
Description:
Hi,

If I have Threads_cached greater than zero and I set thread_cache_size to 0, I would expect Threads_cached to drop to 0.  However, this is not the case: Threads_cached keeps its greater than zero value after setting thread_cache_size to 0.  What will make Threads_cached drop is new connection establishment or purging the thread cache (sending SIGHUP to mysqld).

Many thanks for looking into that,

JFG

How to repeat:
We are starting with the following dbdeployer configuration:

mysql [localhost] {msandbox} ((none)) > select version();
+-----------+
| version() |
+-----------+
| 5.7.23    |
+-----------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} ((none)) > show global variables like 'thread_cache_size';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 9     |
+-------------------+-------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} ((none)) > show global status like 'Threads_cached';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Threads_cached | 0     |
+----------------+-------+
1 row in set (0.00 sec)

Let's fill the thread cache:

jgagne@jgagne-mysql-tests-01:~/sandboxes/msb_5_7_23$ for i in $(seq 10); do ./use <<< "do sleep(1)" & done
[...]

mysql [localhost] {msandbox} ((none)) > show global status like 'Threads_cached';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Threads_cached | 9     |
+----------------+-------+
1 row in set (0.00 sec)

Let's disable the thread cache, but even if disabled, it is still containing cached threadd:

mysql [localhost] {msandbox} ((none)) > set global thread_cache_size=0;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} ((none)) > show global variables like 'thread_cache_size';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 0     |
+-------------------+-------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} ((none)) > show global status like 'Threads_cached';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Threads_cached | 9     |
+----------------+-------+
1 row in set (0.01 sec)

Consuming connections will slowly empty the thread cache:

jgagne@jgagne-mysql-tests-01:~/sandboxes/msb_5_7_23$ for i in $(seq 10); do ./use -N <<< "show global status like 'Threads_cached'"; done
Threads_cached  8
Threads_cached  7
Threads_cached  6
Threads_cached  5
Threads_cached  4
Threads_cached  3
Threads_cached  2
Threads_cached  1
Threads_cached  0
Threads_cached  0

Suggested fix:
Please consider shrinking the thread cache when reducing its size.
[16 Aug 2018 5:56] MySQL Verification Team
Hello Jean-Franc╠žois,

Thank you for the report!

regards,
Umesh
[2 Nov 2018 17:53] Paul Dubois
Posted by developer:
 
Fixed in 8.0.15.

Reducing the value of thread_cache_size at runtime did not reduce the
size of the thread cache.