Sign up ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I'm using Asp.Net together with MySQL. In the .Net connection string, I have set Max Pool Size to 150.

If I run the following I get these values:

SHOW GLOBAL STATUS LIKE 'max_used_connections'; gives 66
SHOW GLOBAL STATUS LIKE 'Threads_created'; gives 66
SHOW GLOBAL STATUS LIKE 'connections'; gives 474

Which gives Threads_created / Connections = 0,1392. 

So from that it seems like I need to increase thread_cache_size .

But if I run SHOW PROCESSLIST I always see that I have a lot of connections open (most of them sleeping) because of the pool created by .Net. Do I still need to set the thread_cache_size as I still will reuse the connections from the connection pool? If the Pool Size is 150 do you think a good value would be to set thread_cache_size to 150+? Would this affect CPU and memory a lot?

share|improve this question

2 Answers 2

Based on the info in the MySQL Documentation you should do the following: Find out what the highest number of simultaneous connections mysqld has had using Connections, Threads_created, and Max_used_connections,

  • SHOW GLOBAL STATUS LIKE 'Connections';
  • SHOW GLOBAL STATUS LIKE 'Threads_created';
  • SHOW GLOBAL STATUS LIKE 'Max_used_connections';

Try calculating the following

Threads_created / Connections : If this is over 0.01, then increase thread_cache_size. At the very least, thread_cache_size should be greater than Max_used_connections.

share|improve this answer
Thanks for great comment! I updated my question a little bit. – Martin Jul 19 '12 at 6:59
I think you were intending mysql> SHOW GLOBAL STATUS LIKE 'connections'; (you have written max_used_connections twice) – Alekc Oct 11 '12 at 7:15

According to the MySQL docs, you should set thread_cache_size so that most new connections use threads from the cache rather than newly created threads. This saves some thread-creation overhead, though normally does not create a significant performance improvement:

Requests for threads are satisfied by reusing threads taken from the cache if possible, and only when the cache is empty is a new thread created. This variable can be increased to improve performance if you have a lot of new connections. Normally, this does not provide a notable performance improvement if you have a good thread implementation. However, if your server sees hundreds of connections per second you should normally set thread_cache_size high enough so that most new connections use cached threads. (source)

This would mean that you should set your thread_cache_size so that Threads_created / Connections (the % of connections that lead to the creation of new threads) is rather low. If you take the MySQL docs literally ("most"), the value should be < 50%. RolandoMySQLDBA's answer says < 1%. I don't know who's closer to the truth.

You should not set thread_cache_size higher than Max_used_connections. The final sentence in RolandoMySQLDBA's answer ("At the very least, thread_cache_size should be greater than Max_used_connections") doesn't seem sensible because it says that you should keep more threads in the cache than your server ever uses. MySQL will never put that many threads in the cache anyway -- it does not pre-emptively put threads in the cache -- it only puts them there after a client creates a thread and disconnects. If you never have X clients connecting at the same time, you will never have X threads in the cache:

When a client disconnects, the client's threads are put in the cache if there are fewer than thread_cache_size threads there. (source)

See also this answer by Michael:

Setting thread_cache_size to a value larger than max_connections seems like tremendously unhelpful advice... the cache can't possibly grow larger than max_connections and even a cache anywhere close to that size could only make sense if you have a tremendous amount of churn on your threads... which, in a well-behaved application, won't be the case.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.