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!

MySQL Optimisation advice

Discussion in 'Workarounds and Optimization' started by Irksome73, Sep 17, 2014.

  1. Irksome73

    Irksome73 Member

    Oct 17, 2013
    Likes Received:
    Trophy Points:
    cPanel Access Level:
    Root Administrator
    Please may I have some optimisation advice on the following MySQL Tuner report :

    Server specs : Dual AMD Opteron @ 2.1GHz with 4GB RAM

    >>  MySQLTuner 1.3.0 - Major Hayden <>
     >>  Bug reports, feature requests, and downloads at
     >>  Run with '--help' for additional options and output filtering
    [OK] Currently running supported MySQL version 5.5.37-cll
    [OK] Operating on 64-bit architecture
    -------- Storage Engine Statistics -------------------------------------------
    [--] Data in MyISAM tables: 690M (Tables: 396)
    [--] Data in InnoDB tables: 760M (Tables: 3549)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 144
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 6d 8h 50m 58s (15M q [27.783 qps], 214K conn, TX: 48B, RX: 2B)
    [--] Reads / Writes: 74% / 26%
    [--] Total buffers: 168.0M global + 2.8M per thread (151 max threads)
    [OK] Maximum possible memory usage: 583.2M (15% of installed RAM)
    [OK] Slow queries: 0% (78/15M)
    [OK] Highest usage of available connections: 70% (107/151)
    [!!] Key buffer size / total MyISAM indexes: 8.0M/170.2M
    [!!] Key buffer hit rate: 93.1% (26M cached / 1M reads)
    [OK] Query cache efficiency: 79.0% (10M cached / 13M selects)
    [!!] Query cache prunes per day: 263687
    [OK] Sorts requiring temporary tables: 0% (68 temp sorts / 435K sorts)
    [OK] Temporary tables created on disk: 20% (106K on disk / 525K total)
    [!!] Thread cache is disabled
    [!!] Table cache hit rate: 0% (400 open / 15M opened)
    [OK] Open file limit used: 3% (373/11K)
    [OK] Table locks acquired immediately: 99% (4M immediate / 4M locks)
    [!!] InnoDB  buffer pool / data size: 118.0M/760.4M
    [OK] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Set thread_cache_size to 4 as a starting value
        Increase table_open_cache gradually to avoid file descriptor limits
        Read this before increasing table_open_cache over 64:
    Variables to adjust:
        key_buffer_size (> 170.2M)
        query_cache_size (> 10M)
        thread_cache_size (start at 4)
        table_open_cache (> 400)
        innodb_buffer_pool_size (>= 760M)
    and my.cnf

    cat my.cnf
  2. cPanelMichael

    cPanelMichael Technical Support Community Manager
    Staff Member

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

    Sometimes it's helpful to provide information about what lead you to seeking assistance with optimization. Are you experiencing a specific issue with speed, or is it simply a matter of wanting to optimize the performance the most you can?

    Thank you.
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  3. Irksome73

    Irksome73 Member

    Oct 17, 2013
    Likes Received:
    Trophy Points:
    cPanel Access Level:
    Root Administrator
    Dear Micheal,

    Yes indeed I quite understand. Some of the sites hosted on this VM are very peaky - they go from little traffic to relatively large amounts for short spaces of time (5-10 mins). We're finding the load averages are getting very very high - I saw 80+ today!

    The code of these sites appears very sub-optimal - I don't think the client has undertaken any optimisation, and therefore there seem to be very large numbers of SQL hits - I want to ensure that the SQL performance is optimised for the websites we host so that I can convince the client to optimise the website code.

    Of course they don't want to pay for further resources!

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