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!

In need of a little help...

Discussion in 'Workarounds and Optimization' started by MrTall, Sep 8, 2013.

  1. MrTall

    MrTall Member

    Joined:
    Aug 18, 2013
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Hello all, first of all I would like to acknowledge this isn't really anything to do with WHM or cPanel and it's regarding the my.conf file. Recently I tried making a backup of my sql database to find the download very slow and making a error on my site indicating too many sql connections.

    My site is a phpbb forum and is on a dedicated server with the following spec:
    Intel Xeon E3110, 3.0GHz Dual-Core
    RAM: 8GB
    HDD: 1 x 500GB SATA HDDs
    Operating System: Linux - CentOS 6.0 (64bit)

    (there's only one web site being hosted on the server which is a phpbb forum)

    I downloaded the my.conf file to find it's contents very basic (2 lines basic):
    [mysqld]
    innodb_file_per_table=1

    This leads me onto configuring the file and my knowledge in this area is next to none! I've had a look at example configurations and I understand the settings to add are really based on the servers capability.

    I know this is a bit cheeky, but is there any out there that might be able to point me towards an idiot proof guide or assist with a configuration?

    Also, do the edit have to be made through SSH or can I adjust via sftp and notepad++ and then restart mysql through WHM?

    Really appreciate any help with this, I know there are a few similar threads of this subject around the forum looking for help but I'm hoping someone will take pitty on me! :)

    Thanks for any help! :D

    ---Edit, please find added in a mystl tuner report:

    Code:
     >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at [url]http://mysqltuner.com/[/url]
     >>  Run with '--help' for additional options and output filtering
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.32-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 329M (Tables: 218)
    [--] Data in InnoDB tables: 3M (Tables: 91)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 124K (Tables: 1)
    [!!] Total fragmented tables: 17
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 24d 6h 57m 31s (65M q [31.099 qps], 3M conn, TX: 70B, RX: 20B)
    [--] Reads / Writes: 90% / 10%
    [--] Total buffers: 168.0M global + 2.8M per thread (151 max threads)
    [OK] Maximum possible memory usage: 583.2M (7% of installed RAM)
    [OK] Slow queries: 0% (16/65M)
    [!!] Highest connection usage: 100%  (152/151)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/401.2M
    [OK] Key buffer hit rate: 99.9% (5B cached / 4M reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 5M sorts)
    [OK] Temporary tables created on disk: 17% (444K on disk / 2M total)
    [!!] Thread cache is disabled
    [!!] Table cache hit rate: 4% (400 open / 9K opened)
    [OK] Open file limit used: 54% (556/1K)
    [OK] Table locks acquired immediately: 99% (53M immediate / 53M locks)
    [OK] InnoDB data size / buffer pool: 3.1M/128.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Enable the slow query log to troubleshoot bad queries
        Reduce or eliminate persistent connections to reduce connection usage
        Set thread_cache_size to 4 as a starting value
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        max_connections (> 151)
        wait_timeout (< 28800)
        interactive_timeout (< 28800)
        query_cache_size (>= 8M)
        thread_cache_size (start at 4)
        table_cache (> 400)
     
    #1 MrTall, Sep 8, 2013
    Last edited: Sep 8, 2013
  2. MrTall

    MrTall Member

    Joined:
    Aug 18, 2013
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    For a little bit more detail, this is the MySQL tuner output:

    Code:
     >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at [url]http://mysqltuner.com/[/url]
     >>  Run with '--help' for additional options and output filtering
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.32-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 329M (Tables: 218)
    [--] Data in InnoDB tables: 3M (Tables: 91)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 124K (Tables: 1)
    [!!] Total fragmented tables: 17
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 24d 6h 57m 31s (65M q [31.099 qps], 3M conn, TX: 70B, RX: 20B)
    [--] Reads / Writes: 90% / 10%
    [--] Total buffers: 168.0M global + 2.8M per thread (151 max threads)
    [OK] Maximum possible memory usage: 583.2M (7% of installed RAM)
    [OK] Slow queries: 0% (16/65M)
    [!!] Highest connection usage: 100%  (152/151)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/401.2M
    [OK] Key buffer hit rate: 99.9% (5B cached / 4M reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 5M sorts)
    [OK] Temporary tables created on disk: 17% (444K on disk / 2M total)
    [!!] Thread cache is disabled
    [!!] Table cache hit rate: 4% (400 open / 9K opened)
    [OK] Open file limit used: 54% (556/1K)
    [OK] Table locks acquired immediately: 99% (53M immediate / 53M locks)
    [OK] InnoDB data size / buffer pool: 3.1M/128.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Enable the slow query log to troubleshoot bad queries
        Reduce or eliminate persistent connections to reduce connection usage
        Set thread_cache_size to 4 as a starting value
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        max_connections (> 151)
        wait_timeout (< 28800)
        interactive_timeout (< 28800)
        query_cache_size (>= 8M)
        thread_cache_size (start at 4)
        table_cache (> 400)
     
  3. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    43,711
    Likes Received:
    1,794
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  4. MrTall

    MrTall Member

    Joined:
    Aug 18, 2013
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Hello Thanks for the reply, I'll have a look at the other stats page to see if I can add more info here.

    Hoping to find someone here who is able to help as I'm keen on getting it right first time :)
     
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