Threads_cached is zero

Even though thread_cache_size is 256 Threads_cached is 0.

mysql> show global variables like '%thread%';
+-----------------------------------------+---------------------------+
| Variable_name | Value |
+-----------------------------------------+---------------------------+
| innodb_purge_threads | 1 |
| innodb_read_io_threads | 64 |
| innodb_thread_concurrency | 24 |
| innodb_thread_sleep_delay | 0 |
| innodb_write_io_threads | 64 |
| max_delayed_threads | 20 |
| max_insert_delayed_threads | 20 |
| myisam_repair_threads | 1 |
| performance_schema_max_thread_classes | 50 |
| performance_schema_max_thread_instances | 612 |
| thread_cache_size | 256 |
| thread_concurrency | 10 |
| thread_handling | one-thread-per-connection |
| thread_pool_high_prio_mode | transactions |
| thread_pool_high_prio_tickets | 4294967295 |
| thread_pool_idle_timeout | 60 |
| thread_pool_max_threads | 100000 |
| thread_pool_oversubscribe | 3 |
| thread_pool_size | 24 |
| thread_pool_stall_limit | 500 |
| thread_stack | 262144 |
| thread_statistics | OFF |
| wsrep_slave_threads | 1 |
+-----------------------------------------+---------------------------+
mysql> show global status like '%threads_%';
+-------------------+--------+
| Variable_name | Value |
+-------------------+--------+
| Threads_cached | 0 |
| Threads_connected | 3 |
| Threads_created | 223544 |
| Threads_running | 1 |
+-------------------+--------+

Any reason you could think why server is not caching threads?

Hi,

As per the doc, “When a client disconnects, the client’s threads are put in the cache if there are fewer than thread_cache_size threads there.”
[URL=“MySQL :: MySQL 8.0 Reference Manual :: 5.1.8 Server System Variables”]https://dev.mysql.com/doc/refman/5.5...ead_cache_size[/URL]

So, thread will be only cached when client/thread disconnected. As per above result, you have 3 threads_connected. Just close any of the client and check, it will be cached.

We have recently upgraded two clusters to 5.6 and after upgrade we are observing Threads_cached as zero.

MySQL Tuner report.

[!!] Thread cache hit rate: 0% (231K created / 231K connections)

Node1

mysql> show global status like '%threads_%';
+-------------------+--------+
| Variable_name | Value |
+-------------------+--------+
| Threads_cached | 0 |
| Threads_connected | 4 |
| Threads_created | 305604 |
| Threads_running | 1 |
+-------------------+--------+

Node2

+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 0 |
| Threads_connected | 12 |
| Threads_created | 18525 |
| Threads_running | 1 |
+-------------------+-------+

Node3

+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 0 |
| Threads_connected | 9 |
| Threads_created | 32066 |
| Threads_running | 2 |
+-------------------+-------+

This is with all nodes in both clusters.

i have same issue with u, i set thread_cache_size = 10 or thread_cache_size=100 or thread_cache_size=1000. they are the same, the status of Threads_cached is always zero.
did u find solution of this issue? please help me if u can solve it. i use same version , 5.6. before this i used 5.5, and no issue on 5.5.
thank you

Hi,

Tested locally with 5.6.16, it seems, you have to connect more than thread_cache_size once, then you’ll see cache used. As soon as we have more concurrently running threads than thread_cache_size, we’ll see it >0 Try to set it 10 or 20 and then test it with mysqlslap like this.

mysqlslap -uroot -p --create-schema=test --concurrency=20 --no-drop --number-of-queries=1000 --query=“select 1”

Hi,
i use version 5.6.15-56. i set thread_cache_size=5 and then i try test with mysqlslap. after that i see status:
±------------------±------+
| Variable_name | Value |
±------------------±------+
| Threads_cached | 0 |
| Threads_connected | 25 |
| Threads_created | 43243 |
| Threads_running | 3 |
±------------------±------+
4 rows in set (0.00 sec)

Threads_cached is still zero.
so what i have to do now?

Hello?
there is no solution for this issue?

Hi,

I have checked on both Percona Server 5.6.15 and 5.6.16 versions. I’m really confused that why you can’t able to do it. check below log

On 5.6.16 :

nilnandan@Nil-Dell-XPS:~$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 38
Server version: 5.6.16-64.0-553.saucy (Ubuntu)

Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> show global variables like ‘thread_cache%’;
±------------------±------+
| Variable_name | Value |
±------------------±------+
| thread_cache_size | 8 |
±------------------±------+
1 row in set (0.00 sec)

mysql> show global status like ‘%threads_%’;
±------------------±------+
| Variable_name | Value |
±------------------±------+
| Threads_cached | 0 |
| Threads_connected | 1 |
| Threads_created | 1 |
| Threads_running | 1 |
±------------------±------+
4 rows in set (0.00 sec)

I run this on second session,

nilnandan@Nil-Dell-XPS:~$ mysqlslap -uroot -p --create-schema=test --concurrency=20 --no-drop --number-of-queries=1000 --query=“select 1”
Enter password:
Benchmark
Average number of seconds to run all queries: 0.014 seconds
Minimum number of seconds to run all queries: 0.014 seconds
Maximum number of seconds to run all queries: 0.014 seconds
Number of clients running queries: 20
Average number of queries per client: 50

nilnandan@Nil-Dell-XPS:~$

Again check status.

mysql> show global status like ‘%threads_%’;
±------------------±------+
| Variable_name | Value |
±------------------±------+
| Threads_cached | 8 |
| Threads_connected | 1 |
| Threads_created | 22 |
| Threads_running | 1 |
±------------------±------+
4 rows in set (0.00 sec)

mysql>

On 5.6.15,

nilnandan@Nil-Dell-XPS:~/sandboxes/msb_5_6_15$ mysql -umsandbox -p --socket=/tmp/mysql_sandbox5615.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.15-rel63.0 Percona Server with XtraDB (GPL), Release rel63.0, Revision 519

Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql>
mysql> show global variables like ‘thread_cache%’;
±------------------±------+
| Variable_name | Value |
±------------------±------+
| thread_cache_size | 9 |
±------------------±------+
1 row in set (0.01 sec)

mysql> show global status like ‘%threads_%’;
±------------------±------+
| Variable_name | Value |
±------------------±------+
| Threads_cached | 0 |
| Threads_connected | 1 |
| Threads_created | 1 |
| Threads_running | 1 |
±------------------±------+
4 rows in set (0.00 sec)

Run this on second session,

nilnandan@Nil-Dell-XPS:~/sandboxes/msb_5_6_15$ mysqlslap -umsandbox -p --socket=/tmp/mysql_sandbox5615.sock --create-schema=test --concurrency=20 --no-drop --number-of-queries=1000 --query=“select 1”
Enter password:
Benchmark
Average number of seconds to run all queries: 0.012 seconds
Minimum number of seconds to run all queries: 0.012 seconds
Maximum number of seconds to run all queries: 0.012 seconds
Number of clients running queries: 20
Average number of queries per client: 50

nilnandan@Nil-Dell-XPS:~/sandboxes/msb_5_6_15$

mysql> show global status like ‘%threads_%’;
±------------------±------+
| Variable_name | Value |
±------------------±------+
| Threads_cached | 9 |
| Threads_connected | 1 |
| Threads_created | 21 |
| Threads_running | 1 |
±------------------±------+
4 rows in set (0.00 sec)

mysql>

here is mine:

mysql> show global variables like ‘thread_cache%’;
±------------------±------+
| Variable_name | Value |
±------------------±------+
| thread_cache_size | 8 |
±------------------±------+
1 row in set (0.01 sec)

mysql> show global status like ‘%threads_%’;
±------------------±-------+
| Variable_name | Value |
±------------------±-------+
| Threads_cached | 0 |
| Threads_connected | 26 |
| Threads_created | 110280 |
| Threads_running | 2 |
±------------------±-------+
4 rows in set (0.00 sec)

and on second session:

[root@db-1-ssd ~]# mysqlslap -uroot -p --create-schema=test --concurrency=20 --no-drop --number-of-queries=1000 --query=“select 1”
Enter password:
Benchmark
Average number of seconds to run all queries: 0.021 seconds
Minimum number of seconds to run all queries: 0.021 seconds
Maximum number of seconds to run all queries: 0.021 seconds
Number of clients running queries: 20
Average number of queries per client: 50

[root@db-1-ssd ~]# mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 115198
Server version: 5.6.15-56 Percona XtraDB Cluster (GPL), Release 25.4, Revision 731, wsrep_25.4.r4043

Copyright (c) 2009-2013 Percona LLC and/or its affiliates
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> show global status like ‘%threads_%’;
±------------------±-------+
| Variable_name | Value |
±------------------±-------+
| Threads_cached | 0 |
| Threads_connected | 32 |
| Threads_created | 115343 |
| Threads_running | 5 |
±------------------±-------+
4 rows in set (0.00 sec)

it is why i am confused. i follow your steps, but i get different result. threads_cached is still zero. i am frustrated now.

Hi,

Now, I came to know that you are using PXC (XtraDB Cluster) which you should mention earlier. Still, I have checked with the same version you are using but I can’t able reproduce what you are saying…Definitely, you should check from your side that where you are running test and where you are checking. Are there multiple mysql instances on same server?

[root@percona-pxc56-1 mysql]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.15-56 Percona XtraDB Cluster (GPL), Release 25.4, Revision 731, wsrep_25.4.r4043

Copyright (c) 2009-2013 Percona LLC and/or its affiliates
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> show global variables like ‘thread_cache%’;
±------------------±------+
| Variable_name | Value |
±------------------±------+
| thread_cache_size | 9 |
±------------------±------+
1 row in set (0.00 sec)

mysql>
mysql> show global status like ‘%threads_%’;
±------------------±------+
| Variable_name | Value |
±------------------±------+
| Threads_cached | 0 |
| Threads_connected | 3 |
| Threads_created | 3 |
| Threads_running | 1 |
±------------------±------+
4 rows in set (0.00 sec)

mysql> quit
Bye
[root@percona-pxc56-1 mysql]# mysqlslap -uroot -p --create-schema=test --concurrency=20 --no-drop --number-of-queries=1000 --query=“select 1”
Enter password:
Benchmark
Average number of seconds to run all queries: 0.053 seconds
Minimum number of seconds to run all queries: 0.053 seconds
Maximum number of seconds to run all queries: 0.053 seconds
Number of clients running queries: 20
Average number of queries per client: 50

[root@percona-pxc56-1 mysql]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 206
Server version: 5.6.15-56 Percona XtraDB Cluster (GPL), Release 25.4, Revision 731, wsrep_25.4.r4043

Copyright (c) 2009-2013 Percona LLC and/or its affiliates
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> show global status like ‘%threads_%’;
±------------------±------+
| Variable_name | Value |
±------------------±------+
| Threads_cached | 8 |
| Threads_connected | 3 |
| Threads_created | 36 |
| Threads_running | 1 |
±------------------±------+
4 rows in set (0.01 sec)

Still facing this problem. I am also using Cluster.

Server 1

mysql> show global status like '%threads_%';
+-------------------+---------+
| Variable_name | Value |
+-------------------+---------+
| Threads_cached | 0 |
| Threads_connected | 14 |
| Threads_created | 3284767 |
| Threads_running | 2 |
+-------------------+---------+
4 rows in set (0.00 sec)

Server 2

mysql> show global status like '%threads_%';
+-------------------+---------+
| Variable_name | Value |
+-------------------+---------+
| Threads_cached | 0 |
| Threads_connected | 57 |
| Threads_created | 3237214 |
| Threads_running | 1 |
+-------------------+---------+
4 rows in set (0.00 sec)

Surprisingly, it is working for third node.

mysql> show global status like '%threads_%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 57 |
| Threads_connected | 9 |
| Threads_created | 66 |
| Threads_running | 1 |
+-------------------+-------+
4 rows in set (0.00 sec)

All configurations on all three nodes are exactly same including thread_cache_size which is 256. We have haproxy solution in front, so that have same traffic (i.e. number of connections).

Hi,

As you are using HAproxy, it might be possible that each time connection goes to 3rd node.
Can you try to change “round robin” in HAproxy settings and then check?

If we see number of Threads_created, it shows a very high number which certifies that MySQL server is active and receiving many connections.

I am also attaching haproxy stats that shows that it is distributing connections correctly.

[TABLE="class: wysiwyg_table_tbl"]
	 		[TR="class: wysiwyg_table_titre"]
		[/TR]
	 [/TABLE]
[TABLE="class: wysiwyg_table_tbl"]
	 		[TR="class: wysiwyg_table_titre"]
		[/TR]
		[TR="class: wysiwyg_table_titre"]
		[/TR]
		[TR="class: wysiwyg_table_frontend"]
			[TD="class: wysiwyg_table_ac"]Frontend[/TD]
			[TD="colspan: 3"] [/TD]
			[TD]1[/TD]
			[TD]233[/TD]
			[TD]-[/TD]
			[TD]0[/TD]
			[TD]154[/TD]
			[TD]3000[/TD]
			[TD]4815359[/TD]
			[TD] [/TD]
			[TD]26794882489[/TD]
			[TD]2910594723339[/TD]
			[TD]0[/TD]
			[TD]0[/TD]
			[TD]0[/TD]
			[TD] [/TD]
			[TD] [/TD]
			[TD] [/TD]
			[TD] [/TD]
			[TD="class: wysiwyg_table_ac"]OPEN[/TD]
			[TD="class: wysiwyg_table_ac, colspan: 8"] [/TD]
		[/TR]
		[TR="class: wysiwyg_table_active3"]
			[TD="class: wysiwyg_table_ac"]db01[/TD]
			[TD]0[/TD]
			[TD]0[/TD]
			[TD]-[/TD]
			[TD]1[/TD]
			[TD]223[/TD]
			[TD] [/TD]
			[TD]0[/TD]
			[TD]51[/TD]
			[TD]200[/TD]
			[TD]1592074[/TD]
			[TD]1592074[/TD]
			[TD]9698974961[/TD]
			[TD]994083732645[/TD]
			[TD] [/TD]
			[TD]0[/TD]
			[TD] [/TD]
			[TD]0[/TD]
			[TD][U]0[/U][/TD]
			[TD]0[/TD]
			[TD]0[/TD]
			[TD="class: wysiwyg_table_ac"]4d23h UP[/TD]
			[TD="class: wysiwyg_table_ac"][U]L7OK/200 in 12ms[/U][/TD]
			[TD="class: wysiwyg_table_ac"]1[/TD]
			[TD="class: wysiwyg_table_ac"]Y[/TD]
			[TD="class: wysiwyg_table_ac"]-[/TD]
			[TD][U]0[/U][/TD]
			[TD]0[/TD]
			[TD]0s[/TD]
			[TD="class: wysiwyg_table_ac"]-[/TD]
		[/TR]
		[TR="class: wysiwyg_table_active3"]
			[TD="class: wysiwyg_table_ac"]db02[/TD]
			[TD]0[/TD]
			[TD]0[/TD]
			[TD]-[/TD]
			[TD]1[/TD]
			[TD]220[/TD]
			[TD] [/TD]
			[TD]0[/TD]
			[TD]52[/TD]
			[TD]200[/TD]
			[TD]1539503[/TD]
			[TD]1539503[/TD]
			[TD]8723529847[/TD]
			[TD]944306623426[/TD]
			[TD] [/TD]
			[TD]0[/TD]
			[TD] [/TD]
			[TD]0[/TD]
			[TD][U]0[/U][/TD]
			[TD]0[/TD]
			[TD]0[/TD]
			[TD="class: wysiwyg_table_ac"]4d23h UP[/TD]
			[TD="class: wysiwyg_table_ac"][U]L7OK/200 in 13ms[/U][/TD]
			[TD="class: wysiwyg_table_ac"]1[/TD]
			[TD="class: wysiwyg_table_ac"]Y[/TD]
			[TD="class: wysiwyg_table_ac"]-[/TD]
			[TD][U]0[/U][/TD]
			[TD]0[/TD]
			[TD]0s[/TD]
			[TD="class: wysiwyg_table_ac"]-[/TD]
		[/TR]
		[TR="class: wysiwyg_table_active3"]
			[TD="class: wysiwyg_table_ac"]db03[/TD]
			[TD]0[/TD]
			[TD]0[/TD]
			[TD]-[/TD]
			[TD]0[/TD]
			[TD]220[/TD]
			[TD] [/TD]
			[TD]0[/TD]
			[TD]51[/TD]
			[TD]200[/TD]
			[TD]1683782[/TD]
			[TD]1683782[/TD]
			[TD]8372377681[/TD]
			[TD]972204367268[/TD]
			[TD] [/TD]
			[TD]0[/TD]
			[TD] [/TD]
			[TD]0[/TD]
			[TD][U]0[/U][/TD]
			[TD]0[/TD]
			[TD]0[/TD]
			[TD="class: wysiwyg_table_ac"]4d23h UP[/TD]
			[TD="class: wysiwyg_table_ac"][U]L7OK/200 in 13ms[/U][/TD]
			[TD="class: wysiwyg_table_ac"]1[/TD]
			[TD="class: wysiwyg_table_ac"]Y[/TD]
			[TD="class: wysiwyg_table_ac"]-[/TD]
			[TD][U]0[/U][/TD]
			[TD]0[/TD]
			[TD]0s[/TD]
			[TD="class: wysiwyg_table_ac"]-[/TD]
		[/TR]
		[TR="class: wysiwyg_table_backend"]
			[TD="class: wysiwyg_table_ac"]Backend[/TD]
			[TD]0[/TD]
			[TD]0[/TD]
			[TD] [/TD]
			[TD]1[/TD]
			[TD]233[/TD]
			[TD] [/TD]
			[TD]0[/TD]
			[TD]154[/TD]
			[TD]3000[/TD]
			[TD]4815359[/TD]
			[TD]4815359[/TD]
			[TD]26794882489[/TD]
			[TD]2910594723339[/TD]
			[TD]0[/TD]
			[TD]0[/TD]
			[TD] [/TD]
			[TD]0[/TD]
			[TD][U]0[/U][/TD]
			[TD]0[/TD]
			[TD]0[/TD]
			[TD="class: wysiwyg_table_ac"]4d23h UP[/TD]
			[TD="class: wysiwyg_table_ac"] [/TD]
			[TD="class: wysiwyg_table_ac"]3[/TD]
			[TD="class: wysiwyg_table_ac"]3[/TD]
			[TD="class: wysiwyg_table_ac"]0[/TD]
			[TD="class: wysiwyg_table_ac"] [/TD]
			[TD]0[/TD]
			[TD]0s[/TD]
			[TD] [/TD]
		[/TR]
	 [/TABLE]

no surprisingly, you upgraded only two clusters to 5.6. i think 3rd node, it is still 5.5, it is why your 3rd node has Threads_cached = 57, and the others = zero,
the difference is the version. so we get problem with 5.6

there is no multiple mysql instance on each node. i have 3 nodes. each of them only has single mysql instance.

Nice post. Thanks for sharing!

Hi All,

Did any one find out the reason why thread_cached is not working.

I am also facing the same issue.

Galera 3 node cluster running on the following version

Server version: 5.6.15-56-log Percona XtraDB Cluster (GPL), Release 25.5, Revision 759, wsrep_25.5.r4061

±--------------------+
| @@thread_cache_size |
±--------------------+
| 100 |
±--------------------+

±------------------±---------+
| Variable_name | Value |
±------------------±---------+
| Threads_cached | 0 |
| Threads_connected | 48 |
| Threads_created | 11378557 |
| Threads_running | 1 |
±------------------±---------+

Thank you,
Trimurthy

I am also facing the same issue after upgrading percona cluster from 5.5 to 5.6.


mysql> show variables like '%thread_cach%';show status like '%threads%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| thread_cache_size | 300 |
+-------------------+-------+
1 row in set (0.00 sec)

+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Delayed_insert_threads | 0 |
| Slow_launch_threads | 0 |
| Threadpool_idle_threads | 0 |
| Threadpool_threads | 0 |
| Threads_cached | 0 |
| Threads_connected | 817 |
| Threads_created | 8954166 |
| Threads_running | 2 |
+-------------------------+---------+
8 rows in set (0.00 sec)


Anyone concluded to something about this?