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.

MySQL Optimisation advice

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

  1. Irksome73

    Irksome73 Member

    Joined:
    Oct 17, 2013
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    1
    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

    Code:
    >>  MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  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 -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
    [--] 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: http://bit.ly/1mi7c4C
    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

    Code:
    cat my.cnf
    [mysqld]
    innodb_file_per_table=1
    open_files_limit=11790
    log-slow-queries="/var/lib/mysql/slow.log"
    max_allowed_packet=268435456
    innodb_buffer_pool_size=123731968
    query_cache_size=10485760
    MTIA
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,811
    Likes Received:
    667
    Trophy Points:
    113
    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.
     
  3. Irksome73

    Irksome73 Member

    Joined:
    Oct 17, 2013
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    1
    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!
     
Loading...

Share This Page