1

I want to know reasonable values for thread_cache_size in relation with the connections that you have. Which is the good proportion? And which proportions do you handle in relation with your clients connected?

Thanks!

closed as too broad by Erik Darling, LowlyDBA, RDFozz, SqlWorldWide, John aka hot2use Jan 2 '18 at 20:57

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    Did you do any research? Are the recommendations from the manual not sufficient in your environment? – mustaccio Jan 2 '18 at 14:42
  • I think i didn't do enough research, but i read something on another Stack community bout Mysql's documentation says: "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%." serverfault.com/questions/408845/… But i can't really foun this answer on documentation. – Matias Ruiz Jan 2 '18 at 15:20
  • @MatiasRuiz - Perhaps you could add this information to the question, especially if it's the motivation for the question in the first place (as opposed to using the recommendations from the manual, as mustaccio suggested. – RDFozz Jan 2 '18 at 16:57
2

Keep in mind that thread_cache_size controls a "cache". It is the number of threads (or processes, depending on the OS) to keep open just in case another connection request comes soon. There is some cost in creating a thread. (This depends on the OS, etc.)

For Windows, thread_cache_size should be zero. (Sorry, I don't have a reference stating that.)

If thread_cache_size / max_connections < 2% or > 15%, it may be poorly tuned.

If Threads_running / thread_cache_size > 2, then the cache may not be big enough. (This depends on whether the number of connections is stable or fluctuating wildly.)

I suggest that Threads_created / Connections is not a good metric -- What if you have only one Connection/sec or 1000 Connections/sec? Hence, I prefer this: If Threads_created / Uptime > 3/second, I suggest that the cache needs increasing.

In a survey of systems, only 10% had Threads_created / Uptime > 0.26 / sec.

MySQL 5.6.8 automatically computes thread_cache_size based on max_connections.

MariaDB 10.2 takes a different approach; it "increases value of thread_cache_size to 32; adds 5 minute timeout before automatically removing threads from thread cache." This feels like a good algorithm.

Bottom line: One should rarely have to worry about thread_cache_size.

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