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

    Joined:
    Jul 23, 2004
    Messages:
    867
    Likes Received:
    9
    Trophy Points:
    18
    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 :)
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator

Share This Page