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 10, 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 am new here and i dont know much about optimizing mysql.

    I have a dedicated server and mysql is taking too much of load and my websites opens slow...

    please help me out...

    My Server Details :

    Total processors: 2

    Processor #1

    Intel(R) Pentium(R) Dual CPU E2160 @ 1.80GHz


    Processor #2

    Intel(R) Pentium(R) Dual CPU E2160 @ 1.80GHz


    This is what happens when my website gets traffic...

    34znd36.jpg


    I tried running mysqltuner.pl and this was the result :


    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.1.62-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 368M (Tables: 472)
    [--] Data in InnoDB tables: 208K (Tables: 13)
    [--] Data in MEMORY tables: 248K (Tables: 2)
    [!!] Total fragmented tables: 55
    
    -------- Security Recommendations -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 2d 2h 7m 17s (6M q [33.973 qps], 118K conn, TX: 27B, RX: 550M)
    [--] Reads / Writes: 97% / 3%
    [--] Total buffers: 34.0M global + 2.7M per thread (500 max threads)
    [OK] Maximum possible memory usage: 1.4G (36% of installed RAM)
    [OK] Slow queries: 0% (20/6M)
    [OK] Highest usage of available connections: 11% (55/500)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/241.5M
    [OK] Key buffer hit rate: 100.0% (113M cached / 51K reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 472K sorts)
    [!!] Joins performed without indexes: 87873
    [!!] Temporary tables created on disk: 44% (229K on disk / 517K total)
    [!!] Thread cache is disabled
    [!!] Table cache hit rate: 0% (64 open / 18K opened)
    [OK] Open file limit used: 4% (113/2K)
    [OK] Table locks acquired immediately: 99% (6M immediate / 6M locks)
    [OK] InnoDB data size / buffer pool: 208.0K/8.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    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
    Set thread_cache_size to 4 as a starting value
    Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
    query_cache_size (>= 8M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    table_cache (> 64) 

    Now this is by help of few guys who told me some commands to show these things.. i'll need step to step guidance... can anyone please help me out and solve this issue :(

    I would be really thankful to you for your favour..
     
  2. al0r

    al0r Member

    Joined:
    May 10, 2012
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Can u use any caching in your scripts?
    this prolem may be from your websites scripts.
    First which version yours mysql? last? check it.
    Then check your scripts which scripts u are using?
     
  3. koolaquarian

    koolaquarian Member

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

    I am using pligg and wordpress.
    and Mysql version Currently installed is 5.1.62
    also how do i do caching in my scripts??


     
  4. al0r

    al0r Member

    Joined:
    May 10, 2012
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    I know wordpress, wordpress loading server when u get high traffic.
    I recommen you upgrade mysql to MySQL 5.5
    Then rebuild apache and use some caching plugins for wordpress.
     
  5. koolaquarian

    koolaquarian Member

    Joined:
    May 10, 2012
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Bro, i think i am facing problem due to pligg site. i was facing this issue earlier also when i didnt have wordpress....
    can you tell me about that?


     
  6. al0r

    al0r Member

    Joined:
    May 10, 2012
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    and my recommend write script yourself, open source scripts not good for me with high-traffic.
     
  7. al0r

    al0r Member

    Joined:
    May 10, 2012
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    I didn't used pligg, but u can rebuild apache with caching example:
    Xcache for PHP

    Are u sure maybe u are getting attack? how much visitors u have?
     
  8. al0r

    al0r Member

    Joined:
    May 10, 2012
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Can u check "Daily Process Log" from your WHM?
    Which site loading server etc. (and which script)
     
  9. koolaquarian

    koolaquarian Member

    Joined:
    May 10, 2012
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    This was the daily process log :


    I have installed the caching plugin for wordpress but need help for my pligg site and also my.cnf only have 3 lines

     
  10. koolaquarian

    koolaquarian Member

    Joined:
    May 10, 2012
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Mysql is updated to the latest version and this is the result when i ran mysqltuner

    Can anyone please help me out ??


    Thanks


    Code:
    -------- 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: 364M (Tables: 472)
    [--] Data in InnoDB tables: 208K (Tables: 13)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 248K (Tables: 2)
    [!!] Total fragmented tables: 55
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 15h 25m 59s (3M q [60.947 qps], 53K conn, TX: 6B, RX: 294M)
    [--] Reads / Writes: 98% / 2%
    [--] 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% (377/3M)
    [!!] Highest connection usage: 99%  (150/151)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/235.9M
    [OK] Key buffer hit rate: 98.9% (2B cached / 28M reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 4% (6K temp sorts / 144K sorts)
    [!!] Joins performed without indexes: 4019
    [OK] Temporary tables created on disk: 25% (33K on disk / 130K total)
    [!!] Thread cache is disabled
    [!!] Table cache hit rate: 12% (400 open / 3K opened)
    [OK] Open file limit used: 56% (579/1K)
    [OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)
    [OK] InnoDB data size / buffer pool: 208.0K/128.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Enable the slow query log to troubleshoot bad queries
        Reduce or eliminate persistent connections to reduce connection usage
        Adjust your join queries to always utilize indexes
        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)
        join_buffer_size (> 128.0K, or always use indexes with joins)
        thread_cache_size (start at 4)
        table_cache (> 400)
    
     
  11. sardelich

    sardelich Well-Known Member

    Joined:
    Apr 28, 2010
    Messages:
    72
    Likes Received:
    1
    Trophy Points:
    8
    edit your /etc/my.cnf and just add query_cache_size directive so your my.cnf looks like this:

    [mysqld]
    set-variable = max_connections=500
    log-slow-queries
    safe-show-database
    query_cache_size=32M
    thread_cache_size=4
    table_cache=1024

    You can go up above recomended 8M for query cache size since I see you have 4 gigs of ram or something like that. I.ve added few more directives that should help reduce the load on mysql.Let it run for 24 hours before runnin mysqltuner again,it really helps to wait and get the right results. Query cache should help you a lot,but you will get more results after changing my.cnf and restarting MySQL and waiting for 24 hours.
     
  12. koolaquarian

    koolaquarian Member

    Joined:
    May 10, 2012
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    are you online now as i need to ask you something before i edit it..

    Waiting for your reply

    Thanks


     
  13. sardelich

    sardelich Well-Known Member

    Joined:
    Apr 28, 2010
    Messages:
    72
    Likes Received:
    1
    Trophy Points:
    8
    I'm here...
     
Loading...

Share This Page