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.

find out server load and resource usage

Discussion in 'Workarounds and Optimization' started by hozyali, Mar 19, 2011.

  1. hozyali

    hozyali Well-Known Member

    Joined:
    Jan 24, 2007
    Messages:
    46
    Likes Received:
    0
    Trophy Points:
    6
    Hi,

    for more than 2 years, my linux server was working perfectly alright. I have about 60 hosting accounts on it. and about 80 domains.

    All of sudden from last week, the server has started to face difficulties and it goes down twice or thrice every 24 hours. I checked the theplanet support and they said the some processes or users are taking a lot of resources on the server at some times and then the server goes down as it cannot fulfill the resource requirements.

    My current WHM and OS info is

    WHM 11.28.86
    CENTOS 5.4 i686 standard on sh2

    I searched online to find this out, but the tips provided didn't help me. Can someone please help me how do I find who or which process is taking the resources?

    Also theplanet support said that I need to optimize PHP and Apache on my server as that is also using a lot of resources.

    Looking for help.

    Thanks
     
  2. dalem

    dalem Well-Known Member
    PartnerNOC

    Joined:
    Oct 24, 2003
    Messages:
    2,577
    Likes Received:
    40
    Trophy Points:
    48
    Location:
    SLC
    cPanel Access Level:
    DataCenter Provider
    run
    #top
    #ps aux
     
  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
    You can also check WHM > Daily Process Logs to see any users who have high CPU, memory or MySQL usage there.
     
  4. hozyali

    hozyali Well-Known Member

    Joined:
    Jan 24, 2007
    Messages:
    46
    Likes Received:
    0
    Trophy Points:
    6
    Hi

    Thanks for your help. I did that. I found mysql which is the most memory consuming user at 9.01%
    my server has 2Gb RAM installed. Is mysql using a lot at 9.01% if yes, is there a way to reduce is load on the server?

    Due to some reasons, my server is going down every 12-18 hours. I seriously need some help with this.

    thanks
     
  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
    What are the contents of your /etc/my.cnf file?

    Code:
    cat /etc/my.cnf
    Next, you can try running mysqltuner.pl to see what it recommends for the variables to change in that MySQL configuration file:

    Code:
    /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl
    Please post the results of both upon running each command.
     
  6. hozyali

    hozyali Well-Known Member

    Joined:
    Jan 24, 2007
    Messages:
    46
    Likes Received:
    0
    Trophy Points:
    6
    below is what I found in /etc/my.cnf
    Code:
    [mysqld]
    #set-variable = max_connections=500 # Commented per ticket 2694327
    set-variable = max_connections=250
    safe-show-database
    # # # # # # # # # # # # # #
    # Added per ticket 2694327
    # # # # # # # # # # # # # #
    query_cache_size = 16M
    join_buffer_size = 2M
    thread_cache_size = 8
    table_cache = 128
    innodb_buffer_pool_size = 384M
    key_buffer_size = 92M
    query_cache_limit = 4M
    # # # # # # # # # # # # # #
    
    regarding your 2nd step, I am a newbie, so can you be so kind to explain a bit more on how to do that?
     
  7. hozyali

    hozyali Well-Known Member

    Joined:
    Jan 24, 2007
    Messages:
    46
    Likes Received:
    0
    Trophy Points:
    6
    when I ran mysqltune.pl in ssh, it says
    Code:
    -bash: mysqltuner.pl: command not found
    please advise how to run it.
     
  8. 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
    You ran the full command that I provided then? It seems you likely didn't run that command exactly as I provided it:

    Code:
    /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl
     
  9. hozyali

    hozyali Well-Known Member

    Joined:
    Jan 24, 2007
    Messages:
    46
    Likes Received:
    0
    Trophy Points:
    6
    Oh sorry. I did that now. it worked. here is the output from it.

    Code:
     >>  MySQLTuner 1.1.2 - 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.0.91-community
    [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: 1G (Tables: 1964)
    [--] Data in InnoDB tables: 325M (Tables: 102)
    [!!] Total fragmented tables: 57
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1h 6m 6s (483K q [122.012 qps], 2K conn, TX: 1B, RX: 43M)
    [--] Reads / Writes: 58% / 42%
    [--] Total buffers: 510.0M global + 4.6M per thread (250 max threads)
    [OK] Maximum possible memory usage: 1.6G (81% of installed RAM)
    [OK] Slow queries: 0% (1/483K)
    [OK] Highest usage of available connections: 4% (11/250)
    [OK] Key buffer size / total MyISAM indexes: 92.0M/53.7M
    [OK] Key buffer hit rate: 99.4% (945K cached / 5K reads)
    [OK] Query cache efficiency: 81.5% (333K cached / 409K selects)
    [!!] Query cache prunes per day: 653947
    [OK] Sorts requiring temporary tables: 0% (1 temp sorts / 2K sorts)
    [!!] Joins performed without indexes: 327
    [!!] Temporary tables created on disk: 33% (1K on disk / 4K total)
    [OK] Thread cache hit rate: 99% (11 created / 2K connections)
    [!!] Table cache hit rate: 2% (128 open / 4K opened)
    [OK] Open file limit used: 19% (247/1K)
    [OK] Table locks acquired immediately: 99% (140K immediate / 141K locks)
    [OK] InnoDB data size / buffer pool: 325.8M/384.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
        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
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        query_cache_size (> 16M)
        join_buffer_size (> 2.0M, or always use indexes with joins)
        tmp_table_size (> 32M)
        max_heap_table_size (> 16M)
        table_cache (> 128)
    
    root@sh2 [/usr/local/cpanel/3rdparty/mysqltuner]#
    
     
  10. 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
    You could change to the following:

    Code:
    [mysqld]
    log-slow-queries=/var/lib/mysql/slow.log
    max_connections = 250
    query_cache_size = 24M
    join_buffer_size = 3M
    thread_cache_size = 8
    table_cache = 256
    innodb_buffer_pool_size = 384M
    key_buffer_size = 92M
    query_cache_limit = 4M
    tmp_table_size = 40M
    max_heap_table_size = 40M
    If you need the comments, simply keep them in the backup file you create of /etc/my.cnf as there is little point to wade through comments in the working copy. Of note, I've revised the max_connections to the right variable name (your syntax for it was old), and I removed safe-show-database as that's already the default now.

    You need to create the slow query log. I've used the syntax for MySQL 5.0, which is what you appear to be using on your machine. Here are the commands to create the slow query log:

    Code:
    cd /var/lib/mysql
    touch slow.log
    chown mysql:mysql slow.log
    chmod 660 slow.log
    After revising the /etc/my.cnf and creating the slow query log, then restart MySQL:

    Code:
    /etc/init.d/mysql restart
    Ensure MySQL is running after the restart by running mysqladmin's processlist:

    Code:
    mysqladmin proc
    If you get any errors for it showing the processlist, check the error log at /var/lib/mysql/hostname.err where hostname is the full hostname for the server.
     
  11. hozyali

    hozyali Well-Known Member

    Joined:
    Jan 24, 2007
    Messages:
    46
    Likes Received:
    0
    Trophy Points:
    6
    Hi,

    Thanks a lot for your help. Appreciate it.

    this is what I got after running the proc command



    +----+------+-----------+----+---------+------+-------+------------------+
    | Id | User | Host | db | Command | Time | State | Info |
    +----+------+-----------+----+---------+------+-------+------------------+
    | 9 | root | localhost | | Query | 0 | | show processlist |
    +----+------+-----------+----+---------+------+-------+------------------+

    does this mean mysql is running fine now?
     
  12. 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
    Yes, MySQL is currently running on the machine after restarting it. Now, wait 24 hours before running mysqltuner.pl again on the server.
     
  13. hozyali

    hozyali Well-Known Member

    Joined:
    Jan 24, 2007
    Messages:
    46
    Likes Received:
    0
    Trophy Points:
    6
    thank you so much for your help.

    Should I post the output of tuner again tomorrow?
    Or should I look for something specific in the output?
     
  14. 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
    You can post the output tomorrow along with the existing /etc/my.cnf as well. This way we know the current values along with the script's suggestions.
     
  15. hozyali

    hozyali Well-Known Member

    Joined:
    Jan 24, 2007
    Messages:
    46
    Likes Received:
    0
    Trophy Points:
    6
    thanks. just to confirm I updated the my.cnf before restarting mysql with the code you sent, ie;
    Code:
    [mysqld]
    log-slow-queries=/var/lib/mysql/slow.log
    max_connections = 250
    query_cache_size = 24M
    join_buffer_size = 3M
    thread_cache_size = 8
    table_cache = 256
    innodb_buffer_pool_size = 384M
    key_buffer_size = 92M
    query_cache_limit = 4M
    tmp_table_size = 40M
    max_heap_table_size = 40M
    I hope I did correct.
     
  16. 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
    Yep, looks good :)
     
  17. hozyali

    hozyali Well-Known Member

    Joined:
    Jan 24, 2007
    Messages:
    46
    Likes Received:
    0
    Trophy Points:
    6
    Thanks again. I hope this will fix my server issues. Will update you tomorrow with the results.

    bye for now.
     
  18. hozyali

    hozyali Well-Known Member

    Joined:
    Jan 24, 2007
    Messages:
    46
    Likes Received:
    0
    Trophy Points:
    6
    Hi friend, as you asked me to run the tuner again after 24 hours, I did and here is the result.

    Code:
     >>  MySQLTuner 1.1.2 - 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.0.91-community-log
    [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: 1G (Tables: 1964)
    [--] Data in InnoDB tables: 325M (Tables: 102)
    [!!] Total fragmented tables: 54
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 21h 25m 38s (6M q [85.999 qps], 31K conn, TX: 428M, RX: 605M)
    [--] Reads / Writes: 63% / 37%
    [--] Total buffers: 542.0M global + 5.6M per thread (250 max threads)
    [!!] Maximum possible memory usage: 1.9G (95% of installed RAM)
    [OK] Slow queries: 0% (39/6M)
    [OK] Highest usage of available connections: 48% (120/250)
    [OK] Key buffer size / total MyISAM indexes: 92.0M/53.7M
    [OK] Key buffer hit rate: 99.8% (8M cached / 18K reads)
    [OK] Query cache efficiency: 79.8% (4M cached / 5M selects)
    [!!] Query cache prunes per day: 485413
    [OK] Sorts requiring temporary tables: 0% (32 temp sorts / 58K sorts)
    [!!] Joins performed without indexes: 3672
    [!!] Temporary tables created on disk: 39% (16K on disk / 41K total)
    [OK] Thread cache hit rate: 98% (453 created / 31K connections)
    [!!] Table cache hit rate: 1% (256 open / 18K opened)
    [OK] Open file limit used: 39% (488/1K)
    [OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
    [OK] InnoDB data size / buffer pool: 325.8M/384.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
        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
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        query_cache_size (> 24M)
        join_buffer_size (> 3.0M, or always use indexes with joins)
        tmp_table_size (> 40M)
        max_heap_table_size (> 40M)
        table_cache (> 256)
    
    I also ran the mysqladmin proc command to see the process of mysql, its different than yesterday. please check this too

    Code:
    +-------+-----------------+-----------+---------------------+---------+------+--                         -----+------------------+
    | Id    | User            | Host      | db                  | Command | Time | S                         tate | Info             |
    +-------+-----------------+-----------+---------------------+---------+------+--                         -----+------------------+
    | 29927 | leechprotect    | localhost | leechprotect        | Sleep   | 4255 |                                |                  |
    | 31552 | utubecom_mshare | localhost | utubecom_mediashare | Sleep   | 311  |                                |                  |
    | 31600 | eximstats       | localhost | eximstats           | Sleep   | 151  |                                |                  |
    | 31645 | utubecom_mshare | localhost | utubecom_mediashare | Sleep   | 0    |                                |                  |
    | 31646 | root            | localhost |                     | Query   | 0    |                                | show processlist |
    +-------+-----------------+-----------+---------------------+---------+------+--                         -----+------------------+
    
    Please advise if the things are running normal now or I should do anything.

    Thanks again.
     
  19. 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
    It hasn't actually been 24 hours:

    You'll need to wait at least 3-4 more hours to run it as the posts yesterday show later timestamps and that script is even reporting it isn't 24 hours, which I can confirm is the case. I am going to hold off providing recommendations until it's actually been 24 hours, so please feel free to re-run it at that point and then provide the information.
     
  20. hozyali

    hozyali Well-Known Member

    Joined:
    Jan 24, 2007
    Messages:
    46
    Likes Received:
    0
    Trophy Points:
    6
    oh ok. my mistake. will do it after 4 hours and send you the output again.

    Thanks again for your help.
     
Loading...

Share This Page