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.
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.