25

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?

43

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 '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
  • 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 '16 at 11:37
  • 1
    Actually, the manual suggests a default of (1% of the mac_used_connections) + 8 .... or 100...whichever is less. – Christopher McGowan Apr 20 '17 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 '17 at 18:57
11

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.

https://dba.stackexchange.com/a/28701

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

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.

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_thread_cache_size

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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