MySQL Bugs: #6657: Thread cache does not work since it have passed a few days after server started

Bug #6657	Thread cache does not work since it have passed a few days after server started
Submitted:	16 Nov 2004 8:05	Modified:	6 Jul 2005 0:11
Reporter:	Jookwan Kim	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	Mysql 4.0.20	OS:	Linux (Linux 2.4.20-8smp)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Hi. I'm a programmer at some internet company in Korea.
I've used MySQL DB in serveral internet services which have many concurrent requests (over 100 per second).

My problem is fallowing as I wrote in synopsis:
Thread cache does not work since it have passed a few days after server started.
It happens often especially when requests/second is high. (over 100)

Here is an example:

I set the "thread_cache_size" as 80 before server start.
Thread cache worked well for a few days, so Thread_created/Connections was below 0.01. (Cache hit ratio was over 99%)
But after several days had passed, 'Thread_cached' was fixed as 80(=thread_cache_size) and 'Thread_created' was inscreased as many as 'Connections' (That is thread cache does not work. If 100 connections are newly connected, 100 threads are newly created!)

Fallowing is the extended-status at different time (100 seconds difference)

> mysqladmin -uroot -p extended-status
| Connections              | 1095519057 |
| Threads_cached           | 80         |
| Threads_created          | 1072562133 |
| Threads_connected        | 85         |
| Threads_running          | 4          |
| Uptime                   | 13859218   |

> mysqladmin -uroot -p extended-status
| Connections              | 1095532432 |
| Threads_cached           | 80         |
| Threads_created          | 1072575508 |
| Threads_connected        | 85         |
| Threads_running          | 4          |
| Uptime                   | 13859318   |

Connection difference(in 100 sec): 1095532432 - 1095519057 = 13375
Threads_created difference(in 100 sec): 1072575508 - 1072562133 = 13375

I tried to flush thread cache by 'mysqladmin -uroot -p flush-threads', but that command was blocked and there was no response.

When I used command 'mysqladmin -uroot -p status processlist', 'Thread' value was same with process list in general.
But in this situation, 'Thread' value includes 'Thread-cached' as fallows:

> mysqladmin -uroot -p status processlist
Uptime: 13860236  Threads: 84  Questions: 1182718868  Slow queries: 1303  Opens: 78129  ...
+------------+--------+-----------+------------+-------------+---------+-------+------------------+
| Id         | User   | Host      | db         | Command     | Time    | State | Info             |
+------------+--------+-----------+------------+-------------+---------+-------+------------------+
| 864165843  | ...    | ...       |            | Binlog Dump | 3614749 | ...   |                  |
| 942010956  | ...    | ...       |            | Binlog Dump | 2343208 | ...   |                  |
| 1095650940 | ...    | ...       | db_xxx     | Sleep       | 0       |       |                  |
| 1095651303 | root   | localhost |            | Query       | 0       |       | show processlist |
+------------+--------+-----------+------------+-------------+---------+-------+------------------+

How to repeat:
I don't know how to repeat this bug exactly.
But db servers which show this phenomenon have common things.
- connections/second is high (over 100/sec)
- questions/second is also high (over 500/sec)
- Thread cache works well at beginning, but it fails after some days (within a month)

I know another bug related to Questions. When it reaches to 2G, it resets to 0.
After then, values of 'Questions' and 'Queries per second avg' are incorrect.
I'm not sure this bug influnces to thread cache bug.
But 'Questions' values of all db servers which show thread cache bug are all reset before.

I know another bug related to Questions. When it reaches to 2G, it resets to 0.
=> I have to amend this sentence: '2G' should be '4G'. I guess the variable type of which value is 'Questions' is 4 byte unsigned integer, so it can't express the number over 4G(=2^32).

You say you have thread_cache_size=80, and that seems to be correct:

| Threads_cached           | 80         |

But you have more than 80 threads connected:

| Threads_connected        | 85         |

So all 80 of the cached threads are in use, so it had to create 5 new threads.

This looks normal to me.  If you are creating 5 new threads constantly, your Threads_created will eventually grow large.

If you have more information please let us know.

'So all 80 of the cached threads are in use, so it had to create 5 new threads.'
=> Yes, you're right. all 80 threads are cached.
But all 80 cached threads doesn't work any more, they just consume the thread cache space.
If thread cache works correctly, # of newly created threads should be much less than # of new connections.
(Usually # of newly created threads is less than 1/100 # of new connection. => cache hit ratio > 99%)
But above example shows that those values are exactly same to each other(13375).
That means cache hit ratio is 0, so thread cache doesn't work.

Jookwan Kim,

your number of threads_created is really way too large.   Normally the ratio between "connections" and thread_created should be large

Here is the sample:

| Connections              | 4435045    |
| Threads_cached           | 5          |
| Threads_connected        | 25         |
| Threads_created          | 9353       |

In your case connections and threads_created are very close, which does not look right.

It is also very strange you have 80 "threads cached" in both cases.  It is rather strange as this means maximum number of threads allowed is "cached" but it was not used during the 100 seconds you was watching. 

What OS are you using  (Which linux exactly) ? Is this our standard RPM ? 

It looks like a bug, but to be able to help you we need to be able to repeat it.

I am returning this bug to "Need Feedback" state, in response to the comment made by Peter Zaitsev.  --Timothy

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".