Configure thread_cache_size (tip)

[kernow]

As I get older and older and memory fades I need to remind myself how to do stuff, so if I post a howto here I will know where to find it in future and also it may help others
Running a server with cPanel on it usually means you have lots of short term database connections, WordPress, joomla and lots of other php scripts can increase server load so having a pool of these connections in memory is a good idea and this is what thread_cache_size does. mysqltuner may tell you to increase/decrease thread_cache_size but not how to get the optimum figure, so here's how.
What were aiming for here is to get the thread_cache_size in /etc/my.cnf close to the number of database threads_connected. First make sure that mysql has been up for at least 24 hours then login as root to mysql and find the number of threads created:

MariaDB [(none)]> SHOW STATUS LIKE '%thread%';

+------------------------------------------+--------+
| Variable_name                            | Value  |
+------------------------------------------+--------+
| Delayed_insert_threads                   | 0      |
| Innodb_master_thread_active_loops        | 302694 |
| Innodb_master_thread_idle_loops          | 513998 |
| Performance_schema_thread_classes_lost   | 0      |
| Performance_schema_thread_instances_lost | 0      |
| Slow_launch_threads                      | 0      |
| Threadpool_idle_threads                  | 0      |
| Threadpool_threads                       | 0      |
| Threads_cached                           | 44     |
| Threads_connected                        | 7      |
| Threads_created                          | 52     |
| Threads_running                          | 2      |
+------------------------------------------+--------+
12 rows in set (0.00 sec)

So we see from the data above the answer is 52
Next, find the number of connections made since mysql was last started:

MariaDB [(none)]> show status like 'Connections';

+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Connections   | 3430174 |
+---------------+---------+
1 row in set (0.00 sec)

OK, to see what the percentage thread cache hit rate is from the above data use the following equation:
100 - ((Threads_created / Connections) * 100)
Which gives us the answer of 99.99% so were very happy with that. If your answer is a lot lower then increase your thread_cache_size. If you can't rem what it is in your /my.cnf then while your in mysql run this to find out:

SHOW VARIABLES LIKE 'thread_cache_size';

Disclaimer
Don't blame me if your MySQL database fries or your wife/partner leaves you, I take no responsibility for the above suggestions.
Phew! this must be the longest post I have ever made here so time to head to the pub for an ice cold beer

 

[cPanelMichael]

Hello,

Thank you for taking the time to share some information that others may find useful.