Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Configure thread_cache_size (tip)

Discussion in 'Workarounds and Optimization' started by kernow, Jun 4, 2015.

  1. kernow

    kernow Well-Known Member

    Jul 23, 2004
    Likes Received:
    Trophy Points:
    cPanel Access Level:
    Root Administrator
    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';
    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 :)
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Apr 11, 2011
    Likes Received:
    Trophy Points:
    cPanel Access Level:
    Root Administrator

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

Share This Page