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:
So we see from the data above the answer is 52
Next, find the number of connections made since mysql was last started:
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:
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
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:
Code:
MariaDB [(none)]> SHOW STATUS LIKE '%thread%';
Code:
+------------------------------------------+--------+
| 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)
Next, find the number of connections made since mysql was last started:
Code:
MariaDB [(none)]> show status like 'Connections';
Code:
+---------------+---------+
| Variable_name | Value |
+---------------+---------+
| Connections | 3430174 |
+---------------+---------+
1 row in set (0.00 sec)
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:
Code:
SHOW VARIABLES LIKE 'thread_cache_size';
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