Configure thread_cache_size (tip)

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 :)