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.

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:
    30,694
    Likes Received:
    654
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  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