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.

my.cnf optimization request

Discussion in 'Workarounds and Optimization' started by johnleenas, Mar 4, 2013.

  1. johnleenas

    johnleenas Registered

    Joined:
    Mar 4, 2013
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hi,
    i have a big problem with my mysql 5.5 load is up to 95% of cpu and i need some one to help me optimize my.cnf file

    here is my VPS information:

    Code:
    Total processors: 4
    
    Processor #1
    
        Vendor
            GenuineIntel
    
        Name
            Intel(R) Xeon(R) CPU E5620 @ 2.40GHz
    
        Speed
            2400.085 MHz
    
        Cache
            12288 KB
    
    Processor #2
    
        Vendor
            GenuineIntel
    
        Name
            Intel(R) Xeon(R) CPU E5620 @ 2.40GHz
    
        Speed
            2400.085 MHz
    
        Cache
            12288 KB
    
    Processor #3
    
        Vendor
            GenuineIntel
    
        Name
            Intel(R) Xeon(R) CPU E5620 @ 2.40GHz
    
        Speed
            2400.085 MHz
    
        Cache
            12288 KB
    
    Processor #4
    
        Vendor
            GenuineIntel
    
        Name
            Intel(R) Xeon(R) CPU E5620 @ 2.40GHz
    
        Speed
            2400.085 MHz
    
        Cache
            12288 KB
    
    
    Current Memory Usage
    
                 total       used       free     shared    buffers     cached
    Mem:       1938900    1079784     859116          0      39116     658052
    -/+ buffers/cache:     382616    1556284
    Swap:      4161528          0    4161528
    Total:     6100428    1079784    5020644
    
    
    root@vn [~]#

    mysqltuner

    Code:
    perl mysqltuner.pl
    
     >>  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.1.68-cll
    [OK] Operating on 32-bit architecture with less than 2GB RAM
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 190M (Tables: 428)
    [--] Data in InnoDB tables: 4M (Tables: 26)
    [--] Data in MEMORY tables: 0B (Tables: 24)
    [!!] Total fragmented tables: 3
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 32m 42s (65K q [33.243 qps], 3K conn, TX: 93M, RX: 17M)
    [--] Reads / Writes: 71% / 29%
    [--] Total buffers: 74.0M global + 3.6M per thread (500 max threads)
    [!!] Maximum possible memory usage: 1.8G (97% of installed RAM)
    [OK] Slow queries: 0% (0/65K)
    [OK] Highest usage of available connections: 7% (39/500)
    [!!] Key buffer size / total MyISAM indexes: 16.0M/26.0M
    [!!] Key buffer hit rate: 85.8% (226K cached / 32K reads)
    [OK] Query cache efficiency: 54.2% (23K cached / 42K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 310 sorts)
    [OK] Temporary tables created on disk: 25% (150 on disk / 596 total)
    [OK] Thread cache hit rate: 91% (300 created / 3K connections)
    [OK] Table cache hit rate: 44% (501 open / 1K opened)
    [OK] Open file limit used: 36% (902/2K)
    [OK] Table locks acquired immediately: 97% (24K immediate / 25K locks)
    [OK] InnoDB data size / buffer pool: 4.6M/8.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Reduce your overall MySQL memory footprint for system stability
        Enable the slow query log to troubleshoot bad queries
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        key_buffer_size (> 26.0M)
    
    
     
  2. newmanyak

    newmanyak Member

    Joined:
    Feb 21, 2013
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    pls put here pico /etc/my.cnf
     
  3. johnleenas

    johnleenas Registered

    Joined:
    Mar 4, 2013
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    my.cnf right now is:

     
  4. newmanyak

    newmanyak Member

    Joined:
    Feb 21, 2013
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    try this

    Code:
    [mysqld]
    skip-name-resolve
    thread_cache_size=64
    table_cache=2048
    local-infile=0
    innodb_file_per_table=1
    query_cache_limit=1M
    max_connections=300
    key_buffer_size=256M
    join_buffer_size=1M
    query_cache_type=1
    query_cache_size=64M
    max_tmp_tables=1
    default-storage-engine=MyISAM
    
    
     
    #4 newmanyak, Mar 5, 2013
    Last edited: Mar 5, 2013
Loading...

Share This Page