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!

Configure thread_cache_size (tip)

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

  1. kernow

    kernow Well-Known Member

    Joined:
    Jul 23, 2004
    Messages:
    920
    Likes Received:
    13
    Trophy Points:
    168
    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:
    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)
    So we see from the data above the answer is 52
    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)
    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:
    Code:
    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 :)
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  2. cPanelMichael

    cPanelMichael Technical Support Community Manager
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    44,749
    Likes Received:
    1,886
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    Hello,

    Thank you for taking the time to share some information that others may find useful.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
Loading...

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice