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.

Very slow site , high server load

Discussion in 'Workarounds and Optimization' started by minitech, Nov 28, 2011.

  1. minitech

    minitech Active Member

    Joined:
    Mar 14, 2011
    Messages:
    35
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Website Owner
    Hello
    Recently my site start to be very slow , and Server load went very high.
    Please help me solve this problem , its really effecting my site.
    Thanks in advance.
    and if you need any more information , please let me know and i will post.
    BTW , i am very newbie at this stuff, so please explain me slowly. ;)

    Code:
    Server load 	74.49 (4 CPUs) 	
    Memory Used 	26.37% (546,920 of 2,074,012) 	
    Swap Used 	        33.87% (1,398,340 of 4,128,664) 	
    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.56
    [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: 371M (Tables: 246)
    [--] Data in InnoDB tables: 160K (Tables: 10)
    [!!] Total fragmented tables: 47
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 3h 27m 23s (1M q [139.913 qps], 31K conn, TX: 5B, RX: 199M)
    [--] Reads / Writes: 99% / 1%
    [--] Total buffers: 34.0M global + 2.7M per thread (500 max threads)
    [OK] Maximum possible memory usage: 1.3G (68% of installed RAM)
    [OK] Slow queries: 0% (505/1M)
    [OK] Highest usage of available connections: 50% (252/500)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/96.0M
    [OK] Key buffer hit rate: 99.9% (87M cached / 87K reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 0% (38 temp sorts / 269K sorts)
    [!!] Temporary tables created on disk: 38% (90K on disk / 237K total)
    [!!] Thread cache is disabled
    [!!] Table cache hit rate: 0% (64 open / 8K opened)
    [OK] Open file limit used: 3% (95/2K)
    [OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
    [OK] InnoDB data size / buffer pool: 160.0K/8.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
        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)
        tmp_table_size (> 16M)
        max_heap_table_size (> 16M)
        thread_cache_size (start at 4)
        table_cache (> 64)
    
    My server:
    Code:
    Processor Information
    Total processors: 4
    Processor #1
        Vendor
            GenuineIntel
        Name
            Intel(R) Xeon(R) CPU X3210 @ 2.13GHz
        Speed
            2133.000 MHz
        Cache
            4096 KB
    Processor #2
        Vendor
            GenuineIntel
        Name
            Intel(R) Xeon(R) CPU X3210 @ 2.13GHz
        Speed
            2133.000 MHz
        Cache
            4096 KB
    Processor #3
        Vendor
            GenuineIntel
        Name
            Intel(R) Xeon(R) CPU X3210 @ 2.13GHz
       Speed
            1600.000 MHz
        Cache
            4096 KB
    Processor #4
        Vendor
            GenuineIntel
        Name
            Intel(R) Xeon(R) CPU X3210 @ 2.13GHz
        Speed
            1600.000 MHz
        Cache
            4096 KB
     
  2. minitech

    minitech Active Member

    Joined:
    Mar 14, 2011
    Messages:
    35
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Website Owner
    Please help , mysql is killing my site

    11468 mysql 15 0 121m 23m 3848 S 132.2 1.2 1:23.70 mysqld
     
  3. minitech

    minitech Active Member

    Joined:
    Mar 14, 2011
    Messages:
    35
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Website Owner
    Please , Someone?
     
  4. 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 in the command line to see what process is eating up the cpu
     
  5. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    142
    Likes Received:
    1
    Trophy Points:
    16
    Location:
    UK
    cPanel Access Level:
    Root Administrator
    Can you run the following command line:

    top -s -c

    That will basically list the processes that running and the file path to them. You'll also see the CPU usage from them.
     
  6. minitech

    minitech Active Member

    Joined:
    Mar 14, 2011
    Messages:
    35
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Website Owner
    Thanks for replay.
    here you go with what its shown me.

    2857 mysql 15 0 161m 39m 3644 S 115.8 1.9 1560:03 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/server.minitech.ws.err --pid-file=/var/lib/mysql/server.minitech.ws.pid
    31724 minitech 16 0 30656 20m 5100 R 13.5 1.0 0:00.41 /usr/bin/php /home/minitech/public_html/index.php
    31744 minitech 16 0 30428 20m 5084 R 13.2 1.0 0:00.40 /usr/bin/php /home/minitech/public_html/index.php
    31678 minitech 17 0 30428 20m 5084 R 9.5 1.0 0:00.39 /usr/bin/php /home/minitech/public_html/index.php
    31684 minitech 17 0 29908 20m 5080 R 7.6 1.0 0:00.24 /usr/bin/php /home/minitech/public_html/index.php
    31715 minitech 19 0 29092 18m 4860 R 6.3 0.9 0:00.19 /usr/bin/php /home/minitech/public_html/index.php
    31738 minitech 18 0 27748 17m 4832 R 5.6 0.9 0:00.17 /usr/bin/php /home/minitech/public_html/index.php
    31750 minitech 15 0 28020 17m 4848 S 5.6 0.9 0:00.17 /usr/bin/php /home/minitech/public_html/index.php
    31755 minitech 16 0 26452 16m 4800 R 4.9 0.8 0:00.15 /usr/bin/php /home/minitech/public_html/index.php
    31752 minitech 16 0 25908 15m 4752 R 4.6 0.8 0:00.14 /usr/bin/php /home/minitech/public_html/index.php
    31754 minitech 16 0 25640 15m 4736 R 4.3 0.7 0:00.13 /usr/bin/php /home/minitech/public_html/index.php
    31667 minitech 17 0 30164 20m 5092 R 3.3 1.0 0:00.31 /usr/bin/php /home/minitech/public_html/index.php
    31694 minitech 20 0 22212 12m 4752 R 3.0 0.6 0:00.09 /usr/bin/php /home/minitech/public_html/index.php
    31742 minitech 19 0 19804 9.8m 4736 R 1.6 0.5 0:00.05 /usr/bin/php /home/minitech/public_html/index.php
    31731 minitech 17 0 18240 8472 4752 R 1.3 0.4 0:00.04 /usr/bin/php /home/minitech/public_html/index.php
     
  7. storminternet

    storminternet Well-Known Member

    Joined:
    Nov 2, 2011
    Messages:
    462
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Hi,

    Though MySQL is consuming high cpu resources most of the processes were run by minitech. Perhaps it's databases might have consumed cpu resources. You need to optimize it asap
     
  8. minitech

    minitech Active Member

    Joined:
    Mar 14, 2011
    Messages:
    35
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Website Owner
    Hi storminternet
    I did optimize it , via ssh , few times the last days, but still , nothing changed.
    please help....
     
  9. pixeline

    pixeline Registered

    Joined:
    Dec 4, 2011
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Reseller Owner
    In your my.cnf file, i'd try upping the query_cache

    Code:
    query_cache_limit=4M
    query_cache_size=128M
    query_cache_type=1
    
    Please post your my.cnf for further suggestions.
     
  10. minitech

    minitech Active Member

    Joined:
    Mar 14, 2011
    Messages:
    35
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Website Owner
    [mysqld]
    set-variable = max_connections=500
    safe-show-database
     
  11. ruzbehraja

    ruzbehraja Well-Known Member

    Joined:
    May 19, 2011
    Messages:
    383
    Likes Received:
    7
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Reduce
    max_connections to 200
    wait_timeout = 250

    In SSH type in
    mysqladmin proc stat

    Paste the output here.
     
  12. minitech

    minitech Active Member

    Joined:
    Mar 14, 2011
    Messages:
    35
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Website Owner
    root@server [~]# mysqladmin proc stat
    +--------+------------------+-----------+-------------------+---------+------+-- -----+------------------+
    | Id | User | Host | db | Command | Time | S tate | Info |
    +--------+------------------+-----------+-------------------+---------+------+-- -----+------------------+
    | 607384 | leechprotect | localhost | leechprotect | Sleep | 3141 | | |
    | 617819 | eximstats | localhost | eximstats | Sleep | 0 | | |
    | 617825 | minitech_minitec | localhost | minitech_minitech | Sleep | 0 | | |
    | 617826 | root | localhost | | Query | 0 | | show processlist |
    +--------+------------------+-----------+-------------------+---------+------+-- -----+------------------+
    Uptime: 143653 Threads: 4 Questions: 34616925 Slow queries: 185 Opens: 7252 Flush tables: 1 Open tables: 64 Queries per second avg: 240.975
     
  13. minitech

    minitech Active Member

    Joined:
    Mar 14, 2011
    Messages:
    35
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Website Owner
    i tried to restart mysql and now i get:
    Starting MySQL......Manager of pid-file quit without updati[FAILED]
    and my site is down
    please help

    EDIT: nm. fixed it.
     
    #13 minitech, Dec 9, 2011
    Last edited: Dec 9, 2011
  14. minitech

    minitech Active Member

    Joined:
    Mar 14, 2011
    Messages:
    35
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Website Owner
    please help someone?
     
  15. alphawolf50

    alphawolf50 Well-Known Member

    Joined:
    Apr 28, 2011
    Messages:
    186
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    It appears to me that your system is swapping, which kills performance. Please post the results of this command to confirm:
    Code:
    vmstat 5 5
    Also, please post your entire my.cnf file. This is an easy way to see it:
    Code:
    cat /etc/my.cnf
    I'd also like to see "top" one more time, with all the info at the head this time.
    Code:
    top -n1
    If you could please post all the info inside of CODE tags like you did for your first post, that makes the data much much easier to read.
     
  16. faisikhan

    faisikhan Well-Known Member

    Joined:
    Dec 12, 2011
    Messages:
    88
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Islamabad, Pakistan
    cPanel Access Level:
    Root Administrator
    Hi,

    I have also experienced the same problem before many times just because of MySQL daemon consuming too much memory and it can be due to one of the following reasons:
    1. If there are huge databases involved then their population into the memory consumes much of the CPU resources and that is why website goes totally down.
    2. Check the hard disk space as well, it can be one of the reasons for MySQL to be failed while restarting.
    3. If swapping is involved then it is killing the memory resources of the server badly.

    Solution:
    1. Take the backup of the databases first, remove the unwanted ones and use indexing to reduce the memory consumption.
    2. Also check the server logs via tail -f /var/log/messages and I hope it will help you a lot to get the root cause of the issue.
    3. Make some free hard disk space and then restart the daemon but I don't think it may resolve the problem permanently.
    4.34616925 Slow queries, that is major problem so try to fix it using Indexing or please Google it.
    5. Try to extend the RAM of the server.
     
    #16 faisikhan, Dec 14, 2011
    Last edited: Dec 14, 2011
  17. faisikhan

    faisikhan Well-Known Member

    Joined:
    Dec 12, 2011
    Messages:
    88
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Islamabad, Pakistan
    cPanel Access Level:
    Root Administrator
    Hi,

    One more thing that if you do not succeed after doing all these steps, then last thing is Rebooting the server :)
     
    #17 faisikhan, Dec 14, 2011
    Last edited: Dec 14, 2011
  18. minitech

    minitech Active Member

    Joined:
    Mar 14, 2011
    Messages:
    35
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Website Owner
    Hi alphawolf50
    first of all thanks alot!
    here the info:

    Code:
    root@server [~]# vmstat 5 5
    procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
     r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
    16  0    112 784456  87952 858072    0    0     7    45  106  100 58  6 35  1  0
     2  0    112 830832  87968 847812    0    0     1   240 1405 4949 76  9 15  1  0
     3  0    112 836784  87980 847840    0    0     0    93 1446 3320 51  5 44  0  0
    26  0    112 695300  87992 847896    0    0     0    98 1559 5723 86 10  5  0  0
     9  0    112 759904  88004 847916    0    0     0   210 1375 4952 91  9  0  0  0
    Code:
    cat /etc/my.cnf
    [mysqld]
    set-variable = max_connections=250
    wait_timeout = 250
    safe-show-database
    
    Code:
     top -n1
    top - 21:38:32 up 1 day,  3:30,  1 user,  load average: 11.00, 11.62, 11.53
    Tasks: 326 total,   6 running, 316 sleeping,   0 stopped,   4 zombie
    Cpu(s): 57.9%us,  6.2%sy,  0.1%ni, 35.1%id,  0.6%wa,  0.0%hi,  0.1%si,  0.0%st
    Mem:   2074012k total,  1256928k used,   817084k free,    88444k buffers
    Swap:  4128664k total,      112k used,  4128552k free,   849036k cached
    
      PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
    28289 minitech  18   0 29916  19m 5080 R 50.6  1.0   0:00.26 php
     2858 mysql     15   0  164m  41m 4300 S 25.3  2.1   1109:17 mysqld
    28291 minitech  18   0 22212  12m 4736 R 19.5  0.6   0:00.10 php
    28292 minitech  24   0 19804   9m 4748 R  9.7  0.5   0:00.05 php
    22765 nobody    17   0  9224 2808 1336 S  1.9  0.1   0:00.04 httpd
    28288 root      15   0  2572 1060  704 R  1.9  0.1   0:00.01 top
        1 root      15   0  2160  676  584 S  0.0  0.0   0:01.19 init
        2 root      RT  -5     0    0    0 S  0.0  0.0   0:17.81 migration/0
        3 root      34  19     0    0    0 S  0.0  0.0   0:00.12 ksoftirqd/0
        4 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/0
        5 root      RT  -5     0    0    0 S  0.0  0.0   0:00.37 migration/1
        6 root      34  19     0    0    0 S  0.0  0.0   0:00.07 ksoftirqd/1
        7 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/1
        8 root      RT  -5     0    0    0 S  0.0  0.0   0:02.78 migration/2
        9 root      34  19     0    0    0 S  0.0  0.0   0:03.38 ksoftirqd/2
       10 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/2
       11 root      RT  -5     0    0    0 S  0.0  0.0   0:00.90 migration/3
    
     
  19. alphawolf50

    alphawolf50 Well-Known Member

    Joined:
    Apr 28, 2011
    Messages:
    186
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Hi minitech,

    The info you posted does not show any active swapping, so that is probably not your primary issue. The one thing that stands out is there is a PHP script using 50% of one CPU... that seems excessive for the typical PHP script. What kind of site are you running under user minitech? Blog/forum/e-commerce/reporting...?

    Do you know which MPM you're using for Apache?
    Which PHP are you running, suPHP, fcgid, ...?
    Are you using a PHP accelerator like eAccelerator or APC?

    Your my.cnf file definitely needs some work, but I noticed the mysqltuner output you provided earlier was only for 3 hours of operation. Could you provide the output of mysqltuner again, now that mysql has been running for over 24 hours? If it has been running less than a day, please wait until after 24 hours to provide this output.

    Thanks for providing everything in CODE tags, that was MUCH easier to read :)
     
  20. minitech

    minitech Active Member

    Joined:
    Mar 14, 2011
    Messages:
    35
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Website Owner
    Hello Thanks again.
    I'm running a blog , with 10000 daily users. its a WordPress.

    How can i check all this? (sorry for being newbie)
    Code:
    Do you know which MPM you're using for Apache?
    Which PHP are you running, suPHP, fcgid, ...?
    Are you using a PHP accelerator like eAccelerator or APC?


    Code:
    root@server [~]# perl mysqltuner.pl
    
     >>  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.56
    [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: 399M (Tables: 246)
    [--] Data in InnoDB tables: 160K (Tables: 10)
    [!!] Total fragmented tables: 24
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 2d 4h 24m 15s (47M q [253.249 qps], 831K conn, TX: 147B, RX: 5B)
    [--] Reads / Writes: 99% / 1%
    [--] Total buffers: 34.0M global + 2.7M per thread (250 max threads)
    [OK] Maximum possible memory usage: 705.9M (34% of installed RAM)
    [OK] Slow queries: 0% (253/47M)
    [OK] Highest usage of available connections: 34% (85/250)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/95.1M
    [OK] Key buffer hit rate: 100.0% (2B cached / 210K reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 0% (2K temp sorts / 7M sorts)
    [!!] Temporary tables created on disk: 37% (2M on disk / 6M total)
    [!!] Thread cache is disabled
    [!!] Table cache hit rate: 0% (64 open / 6K opened)
    [OK] Open file limit used: 7% (98/1K)
    [OK] Table locks acquired immediately: 99% (50M immediate / 50M locks)
    [OK] InnoDB data size / buffer pool: 160.0K/8.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Enable the slow query log to troubleshoot bad queries
        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)
        tmp_table_size (> 16M)
        max_heap_table_size (> 16M)
        thread_cache_size (start at 4)
        table_cache (> 64)
    
     
Loading...

Share This Page