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?

3 Answers 3


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.

  • Thanks for great comment! I updated my question a little bit.
    – Martin
    Jul 19, 2012 at 6:59
  • I think you were intending mysql> SHOW GLOBAL STATUS LIKE 'connections'; (you have written max_used_connections twice)
    – Alekc
    Oct 11, 2012 at 7:15
  • 1
    I just want to add that by referring to this dev.mysql.com/doc/refman/5.7/en/… you should add 8 to Max_used_connections for the thread_cache_size value (without exceeding 100)
    – CME64
    Nov 23, 2016 at 11:37
  • 1
    Actually, the manual suggests a default of (1% of the mac_used_connections) + 8 .... or 100...whichever is less. Apr 20, 2017 at 22:36
  • For me the #'s are 264/103134 which works out to 0.0026 which is way less than 0.01 But my MaxUsedConnections is 72, so the last sentence is terribly conflicted
    – boatcoder
    Nov 1, 2017 at 18:57

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.


  • i think so! after i test this config, "thread_cache_size should be greater than Max_used_connections" not helpfull.
    – CK.Nguyen
    Aug 11, 2016 at 12:47

In the common working day, would a 'new hire' possibly need a connection? Most magicians will not know how many people could be hired in the next few days. V 8 of MySQL suggests CAP thread_cache_size at 100 to prevent overload regardless of max_used_connections. For me, 100 is a good CAP.

See this link, please.


You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .