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 optimize help needed

Discussion in 'Workarounds and Optimization' started by koolaquarian, May 24, 2012.

  1. koolaquarian

    koolaquarian Member

    Joined:
    May 10, 2012
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Hello Everyone,

    i dont know much about optimizing mysql.

    I need a little help to edit my.cnf i hired 2 different people but none of them has still solved my problem

    I have a dedicated server with 4gb Ram :

    Code:
    Total processors: 2
    
    Processor #1
    
        Vendor
            GenuineIntel
    
        Name
            Intel(R) Pentium(R) Dual CPU E2160 @ 1.80GHz
    
        Speed
            1800.000 MHz
    
        Cache
            1024 KB
    
    Processor #2
    
        Vendor
            GenuineIntel
    
        Name
            Intel(R) Pentium(R) Dual CPU E2160 @ 1.80GHz
    
        Speed
            1800.000 MHz
    
        Cache
            1024 KB

    my.cnf that i have now is :

    Code:
    [mysqld]
    max_connections = 200
    #log-slow-queries
    #safe-show-database
    #port            = 3306
    #socket          = /var/lib/mysql/mysql.sock
    #skip-locking
    table_cache = 12000
    read_buffer_size = 8M
    skip-networking
    skip-federated
    #log-bin=mysql-bin
    thread_cache_size = 12M
    #open_files_limit = 8192
    max_allowed_packet = 12M
    local-infile=0
    #max_user_connections = 5
    query_cache_size = 16M
    query_cache_type = 1
    key_buffer_size = 256M
    #query_cache_min_res_unit = 2M
    
    [mysqldump]
    quick
    max_allowed_packet = 64M
    
    [mysql]
    no-auto-rehash
    
    [isamchk]
    key_buffer = 128M
    sort_buffer_size = 128M
    read_buffer = 2M
    write_buffer = 2M
    
    [myisamchk]
    key_buffer = 128M
    sort_buffer_size = 128M
    write_buffer = 2M
    
    [mysqlhotcopy]
    interactive-timeout


    MYSQLTUNER RESULTS :


    Code:
    >>  MySQLTuner 1.2.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
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.22-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 390M (Tables: 390)
    [--] Data in InnoDB tables: 512K (Tables: 32)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 1M (Tables: 2)
    [!!] Total fragmented tables: 52
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 19h 31m 40s (5M q [33.055 qps], 129K conn, TX: 32B, RX: 459M)
    [--] Reads / Writes: 79% / 21%
    [--] Total buffers: 432.0M global + 10.6M per thread (200 max threads)
    [OK] Maximum possible memory usage: 2.5G (66% of installed RAM)
    [OK] Slow queries: 0% (0/5M)
    [OK] Highest usage of available connections: 23% (46/200)
    [OK] Key buffer size / total MyISAM indexes: 256.0M/230.7M
    [OK] Key buffer hit rate: 100.0% (58M cached / 22K reads)
    [OK] Query cache efficiency: 89.3% (4M cached / 4M selects)
    [!!] Query cache prunes per day: 63622
    [OK] Sorts requiring temporary tables: 0% (4 temp sorts / 123K sorts)
    [!!] Joins performed without indexes: 81066
    [!!] Temporary tables created on disk: 28% (44K on disk / 156K total)
    [OK] Thread cache hit rate: 99% (46 created / 129K connections)
    [OK] Table cache hit rate: 96% (538 open / 558 opened)
    [OK] Open file limit used: 3% (901/24K)
    [OK] Table locks acquired immediately: 99% (751K immediate / 751K locks)
    [OK] InnoDB data size / buffer pool: 512.0K/128.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Enable the slow query log to troubleshoot bad queries
        Adjust your join queries to always utilize indexes
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
        query_cache_size (> 16M)
        join_buffer_size (> 128.0K, or always use indexes with joins)
        tmp_table_size (> 16M)
        max_heap_table_size (> 16M)
    Load averages goes up to 5.84 1.68 1.88
    and sometimes more also..


    Please help me out

    Thanks....
     
  2. -GR-

    -GR- Active Member

    Joined:
    May 2, 2012
    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Are you sure mysql is what is causing your load to be high? Only your one minute load average is high, your 5 minute and 15 minute load averages are not bad.

    The only things I would change right now on your my.cnf would be:

    Code:
    query_cache_size = 128M
    query_cache_limit = 4M
    tmp_table_size = 128M
    max_heap_table_size = 128M
    
    Judging by the joins performed without indexes you are running some addons with your software that are using joins without indexes. Finding and adding indexes to the bad queries would help with that or disable the mod entirely.

    We use Invision Power Board on our main site and the classifieds add on does a lot of joins without indexes.
     
    #2 -GR-, May 24, 2012
    Last edited: May 24, 2012
  3. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Also, it is table_open_cache not table_cache under MySQL 5.1 and higher. Next, remove everything with a # on it entirely. It's just filling up the /etc/my.cnf for no reason.

    You might want to enable the slow query logging for MySQL 5.5:

    MySQL :: MySQL 5.5 Reference Manual :: 5.2.5 The Slow Query Log

    This would be slow_query_log in /etc/my.cnf file.
     
  4. koolaquarian

    koolaquarian Member

    Joined:
    May 10, 2012
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Actually I dont know anything about servers or linux.... i know just a bit by searching and stuff. i hired two diff people to work on it but they dont have time and dont reply me at all. so i am trying to do it myself..


    can you tell me what do i edit or remove??

     
  5. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    I don't really like skip-networking being in there. I've seen issues with that setting for certain functions working.
     
Loading...

Share This Page