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 stuck, Site goes down in every few minutes, high disk IO waiting :(

Discussion in 'Workarounds and Optimization' started by rohan_, Jan 9, 2012.

  1. rohan_

    rohan_ Member

    Joined:
    Jun 4, 2011
    Messages:
    18
    Likes Received:
    0
    Trophy Points:
    1
    Hello,

    I have a Premium VPS with 3GB Ram and 500GB HDD, and a Xeon Processor with 1GBPS port

    I was using my vps for my scripts and everything was working superfast and fine, after some days i deleted everything, installed fresh centos 5 64bit with cpanel and whm.

    now hosting only 1 site in it, site have high traffic, ( 20k UV per day )

    now the problem is, everything works fine for some hours but after them things gets stuck, mysql is eating all ram and making high disk %wa

    I reboot my server multiple times but things doesnt work, things come back to normal after some hours when there is not much traffic.

    so the questing is, what is making high disk %wa ?

    why my site eats all 3GB ram only when the downtime but only 400MB when normal.

    what actually causes this and what can be the solution.

    Please Help. I also have tried different servers from different providers but same prob :(
     
  2. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    14,448
    Likes Received:
    194
    Trophy Points:
    63
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
    Re: MySQL stuck, Site goes down in every few minutes, high disk IO waiting

    Sounds more like problems with your website than the server.
     
  3. alphawolf50

    alphawolf50 Well-Known Member

    Joined:
    Apr 28, 2011
    Messages:
    186
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Re: MySQL stuck, Site goes down in every few minutes, high disk IO waiting

    Are you sure it's MySQL that's using all the RAM?

    I/O wait % increases when memory is tight because the system starts swapping. That is a very bad thing.

    If MySQL is indeed using that much more RAM when it is very busy, then you've likely got the per-thread buffers set way too high. Please post your my.cnf and the results of mysqltuner here. Please place them in CODE tags. Make sure MySQL has been running for at least 24 hours before getting the mysqltuner results.

    Do you know if you're using mpm_prefork in Apache? This spawns several processes to handle each connection, and can eat a lot of memory on a busy site. MPM_worker is often a better choice on such servers, as it spawns lighter-weight threads. Just something to look into.
     
  4. 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
    Re: MySQL stuck, Site goes down in every few minutes, high disk IO waiting

    Could you also check your MySQL error log in case there are any errors showing? Wait until the issue occurs again, then tail the log during that time to see the results and provide the output:

    Code:
    tail -fn0 /var/lib/mysql/hostname.err
    Please replace hostname.err with the full hostname and .err at the end. If you aren't certain the error log name, you can always try to find the files name using this command:

    Code:
    ls -l /var/lib/mysql/*.err
     
  5. rohan_

    rohan_ Member

    Joined:
    Jun 4, 2011
    Messages:
    18
    Likes Received:
    0
    Trophy Points:
    1
    Re: MySQL stuck, Site goes down in every few minutes, high disk IO waiting

    how can it be my site's problem, my site works great even with high traffic but it goes down some times :(

    what can be the reason of tight memory ? my software or the hardware ? also how do I check if i am using MPM prefork or MPM worker ?


    did that but nothing is coming, SSH just waiting for the output, waited 10 minutes then cancelled by pressing CTRL+C

    --------

    my.cnf file details

    Code:
    [mysqld]
    set-variable = max_connections=300
    log-slow-queries
    safe-show-database
    wait_timeout=100
    interactive_timeout=84100
    table_cache=512
    mysqltuner result

    Code:
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.56-log
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 1G (Tables: 120)
    [--] Data in InnoDB tables: 144K (Tables: 9)
    [!!] Total fragmented tables: 20
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1m 5s (3K q [56.046 qps], 214 conn, TX: 13M, RX: 877K)
    [--] Reads / Writes: 74% / 26%
    [--] Total buffers: 34.0M global + 2.7M per thread (300 max threads)
    [OK] Maximum possible memory usage: 859.0M (85% of installed RAM)
    [OK] Slow queries: 0% (1/3K)
    [OK] Highest usage of available connections: 2% (7/300)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/769.4M
    [OK] Key buffer hit rate: 98.5% (327K cached / 4K reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 5% (2 temp sorts / 34 sorts)
    [!!] Temporary tables created on disk: 43% (7 on disk / 16 total)
    [!!] Thread cache is disabled
    [OK] Table cache hit rate: 90% (64 open / 71 opened)
    [OK] Open file limit used: 7% (108/1K)
    [OK] Table locks acquired immediately: 99% (4K immediate / 4K locks)
    [OK] InnoDB data size / buffer pool: 144.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
        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
    Variables to adjust:
        query_cache_size (>= 8M)
        tmp_table_size (> 16M)
        max_heap_table_size (> 16M)
        thread_cache_size (start at 4)
    
    not 24h old as the system is not stable for 24h to keep it online.

    Tried different settings with my.cnf, also tried different server with different config, but still same prob, site goes down because of overload frequently, also the other thing is when the server load goes over 100 and 150 i see no high load for mysql in "top" command, but when i restart mysql service then server comes back to normal.

    can this be any other issue ? PLEASE HELP, I am really stuck in problems
     
  6. alphawolf50

    alphawolf50 Well-Known Member

    Joined:
    Apr 28, 2011
    Messages:
    186
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Re: MySQL stuck, Site goes down in every few minutes, high disk IO waiting

    I can say definitively from what you've posted that MySQL is not using all your RAM. Run this command while the server is under high load, and it will tell me if you have high I/O wait because of swapping or poor MySQL configuration.

    Code:
    vmstat 5 5
    Please post the results in CODE tags.

    Your my.cnf definitely needs an overhaul, but I don't want to make MySQL use more RAM until I know if there is RAM to give it.

    Also, run this and post the results:
    Code:
    httpd -l | grep -E -i "worker|prefork|event"
     
  7. rohan_

    rohan_ Member

    Joined:
    Jun 4, 2011
    Messages:
    18
    Likes Received:
    0
    Trophy Points:
    1
    Re: MySQL stuck, Site goes down in every few minutes, high disk IO waiting

    vmstat result

    Code:
    procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
     r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
     1  1  84392   8244   3524 897904  125   45   633   150   88   43  3  2 78 17  0
     1  1  84392   9032   3636 896480    0    0 11691  8575 1986  292 16 22 43 19  0
     0  3  84392   8572   3740 897244    0    0 11830 14813 2134  316 16 21 40 23  0
     1  1  84392   9028   3848 895988    0    0 10898  8213 1803  272 15 19 42 25  0
     0  2  84392   8704   3724 897160    0    0 11952 13993 2221  333 15 21 38 26  0
    
    httpd -l | grep -E -i "worker|prefork|event" result

    Code:
    -bash: httpd: command not found
    Please help :( I am in serious problems and tired of this :(

    I got another thing too, may this also can help. when the server was overloaded i ran mysqladmin pr and got this

    Code:
                                    FROM db_members |
    | 1948 | mydb_db | localhost | mydb_db | Query   | 64   | Locked                                                             | SELECT
                                    c.ID_CAT, b.name AS bname, b.description, b.numT                                              opics, b.memberGroups,
                                    b.ID_PARENT, c |
    | 1949 | mydb_db | localhost | mydb_db | Query   | 63   | Locked                                                             | SELECT
                            c.name AS catName, c.ID_CAT, b.ID_BOARD, b.name AS board                                              Name, b.description,
                            b.numPosts |
    | 1950 | mydb_db | localhost | mydb_db | Query   | 62   | Locked                                                             | SELECT mem.*, IFNULL(a.ID_ATTACH, 0) AS ID_ATTACH, a.filename, a.attach                                              mentType
                                    FROM db_members |
    | 1951 | mydb_db | localhost | mydb_db | Query   | 62   | Locked                                                             | SELECT
                                    c.ID_CAT, b.name AS bname, b.description, b.numT                                              opics, b.memberGroups,
                                    b.ID_PARENT, c |
    | 1952 | mydb_db | localhost | mydb_db | Query   | 61   | Locked                                                             | SELECT
                                    c.ID_CAT, b.name AS bname, b.description, b.numT                                              opics, b.memberGroups,
                                    b.ID_PARENT, c |
    | 1953 | mydb_db | localhost | mydb_db | Query   | 59   | Locked                                                             | SELECT mem.*, IFNULL(a.ID_ATTACH, 0) AS ID_ATTACH, a.filename, a.attach                                              mentType
                                    FROM db_members |
    | 1954 | mydb_db | localhost | mydb_db | Query   | 59   | Locked                                                             | SELECT
                                    c.ID_CAT, b.name AS bname, b.description, b.numT                                              opics, b.memberGroups,
                                    b.ID_PARENT, c |
    | 1956 | mydb_db | localhost | mydb_db | Query   | 57   | Locked                                                             | SELECT
                                    c.ID_CAT, b.name AS bname, b.description, b.numT                                              opics, b.memberGroups,
                                    b.ID_PARENT, c |
    | 1957 | mydb_db | localhost | mydb_db | Query   | 55   | Locked                                                             | SELECT
                                    c.ID_CAT, b.name AS bname, b.description, b.numT                                              opics, b.memberGroups,
                                    b.ID_PARENT, c |
    | 1959 | mydb_db | localhost | mydb_db | Query   | 53   | Locked                                                             | SELECT passwd, ID_MEMBER, ID_GROUP, lngfile, is_activated, emailAddress                                              , additionalGroups, memberNam |
    | 1960 | mydb_db | localhost | mydb_db | Query   | 53   | Locked                                                             | SELECT mem.*, IFNULL(a.ID_ATTACH, 0) AS ID_ATTACH, a.filename, a.attach                                              mentType
                                    FROM db_members |
    | 1961 | mydb_db | localhost | mydb_db | Query   | 51   | Locked                                                             | SELECT
                                    c.ID_CAT, b.name AS bname, b.description, b.numT                                              opics, b.memberGroups,
                                    b.ID_PARENT, c |
    | 1964 | mydb_db | localhost | mydb_db | Query   | 51   | Locked                                                             | SELECT passwd, ID_MEMBER, ID_GROUP, lngfile, is_activated, emailAddress                                              , additionalGroups, memberNam |
    | 1965 | mydb_db | localhost | mydb_db | Query   | 50   | Locked                                                             | SELECT mem.*, IFNULL(a.ID_ATTACH, 0) AS ID_ATTACH, a.filename, a.attach                                              mentType
                                    FROM db_members |
    | 1966 | mydb_db | localhost | mydb_db | Query   | 47   | Locked                                                             | SELECT
                            c.name AS catName, c.ID_CAT, b.ID_BOARD, b.name AS board                                              Name, b.description,
                            b.numPosts |
    | 1967 | mydb_db | localhost | mydb_db | Query   | 42   | Locked                                                             | SELECT
                                    c.ID_CAT, b.name AS bname, b.description, b.numT                                              opics, b.memberGroups,
                                    b.ID_PARENT, c |
    | 1969 | mydb_db | localhost | mydb_db | Query   | 41   | Locked                                                             | SELECT
                                    c.ID_CAT, b.name AS bname, b.description, b.numT                                              opics, b.memberGroups,
                                    b.ID_PARENT, c |
    | 1971 | mydb_db | localhost | mydb_db | Query   | 38   | Locked                                                             | SELECT passwd, ID_MEMBER, ID_GROUP, lngfile, is_activated, emailAddress                                              , additionalGroups, memberNam |
    | 1972 | mydb_db | localhost | mydb_db | Query   | 36   | Locked                                                             | SELECT mem.*, IFNULL(a.ID_ATTACH, 0) AS ID_ATTACH, a.filename, a.attach                                              mentType
                                    FROM db_members |
    | 1973 | mydb_db | localhost | mydb_db | Query   | 36   | Locked                                                             | SELECT
                                    c.ID_CAT, b.name AS bname, b.description, b.numT                                              opics, b.memberGroups,
                                    b.ID_PARENT, c |
    | 1974 | mydb_db | localhost | mydb_db | Query   | 36   | Locked                                                             | SELECT
                                    c.ID_CAT, b.name AS bname, b.description, b.numT                                              opics, b.memberGroups,
                                    b.ID_PARENT, c |
    | 1975 | mydb_db | localhost | mydb_db | Query   | 36   | Locked                                                             | SELECT
                                    c.ID_CAT, b.name AS bname, b.description, b.numT                                              opics, b.memberGroups,
                                    b.ID_PARENT, c |
    | 1976 | mydb_db | localhost | mydb_db | Query   | 33   | Locked                                                             | SELECT
                                    c.ID_CAT, b.name AS bname, b.description, b.numT                                              opics, b.memberGroups,
                                    b.ID_PARENT, c |
    | 1977 | mydb_db | localhost | mydb_db | Query   | 30   | Locked                                                             | SELECT
                                    c.ID_CAT, b.name AS bname, b.description, b.numT                                              opics, b.memberGroups,
                                    b.ID_PARENT, c |
    | 1978 | mydb_db | localhost | mydb_db | Query   | 29   | Locked                                                             | SELECT
                                    c.ID_CAT, b.name AS bname, b.description, b.numT                                              opics, b.memberGroups,
                                    b.ID_PARENT, c |
    | 1979 | mydb_db | localhost | mydb_db | Query   | 29   | Locked                                                             | SELECT
                                    c.ID_CAT, b.name AS bname, b.description, b.numT                                              opics, b.memberGroups,
                                    b.ID_PARENT, c |
    | 1980 | mydb_db | localhost | mydb_db | Query   | 25   | Locked                                                             | SELECT
                                    c.ID_CAT, b.name AS bname, b.description, b.numT                                              opics, b.memberGroups,
                                    b.ID_PARENT, c |
    | 1982 | mydb_db | localhost | mydb_db | Query   | 20   | Locked                                                             | SELECT passwd, ID_MEMBER, ID_GROUP, lngfile, is_activated, emailAddress                                              , additionalGroups, memberNam |
    | 1983 | mydb_db | localhost | mydb_db | Query   | 20   | Locked                                                             | SELECT
                                    c.ID_CAT, b.name AS bname, b.description, b.numT                                              opics, b.memberGroups,
                                    b.ID_PARENT, c |
    | 1984 | mydb_db | localhost | mydb_db | Query   | 20   | Locked                                                             | SELECT
                                    c.ID_CAT, b.name AS bname, b.description, b.numT                                              opics, b.memberGroups,
                                    b.ID_PARENT, c |
    | 1985 | mydb_db | localhost | mydb_db | Query   | 19   | Locked                                                             | SELECT
                                    c.ID_CAT, b.name AS bname, b.description, b.numT                                              opics, b.memberGroups,
                                    b.ID_PARENT, c |
    | 1988 | mydb_db | localhost | mydb_db | Query   | 16   | Locked                                                             | SELECT passwd, ID_MEMBER, ID_GROUP, lngfile, is_activated, emailAddress                                              , additionalGroups, memberNam |
    | 1989 | mydb_db | localhost | mydb_db | Query   | 16   | Locked                                                             | SELECT
                                    c.ID_CAT, b.name AS bname, b.description, b.numT                                              opics, b.memberGroups,
                                    b.ID_PARENT, c |
    | 1991 | mydb_db | localhost | mydb_db | Query   | 13   | Locked                                                             | SELECT passwd, ID_MEMBER, ID_GROUP, lngfile, is_activated, emailAddress                                              , additionalGroups, memberNam |
    | 1992 | mydb_db | localhost | mydb_db | Query   | 13   | Locked                                                             | SELECT mem.*, IFNULL(a.ID_ATTACH, 0) AS ID_ATTACH, a.filename, a.attach                                              mentType
                                    FROM db_members |
    | 1993 | mydb_db | localhost | mydb_db | Query   | 12   | Locked                                                             | SELECT
                                    c.ID_CAT, b.name AS bname, b.description, b.numT                                              opics, b.memberGroups,
                                    b.ID_PARENT, c |
    | 1994 | mydb_db | localhost | mydb_db | Query   | 11   | Locked                                                             | SELECT
                                    c.ID_CAT, b.name AS bname, b.description, b.numT                                              opics, b.memberGroups,
                                    b.ID_PARENT, c |
    | 1995 | mydb_db | localhost | mydb_db | Query   | 11   | Locked                                                             | SELECT
                                    c.ID_CAT, b.name AS bname, b.description, b.numT                                              opics, b.memberGroups,
                                    b.ID_PARENT, c |
    | 1996 | mydb_db | localhost | mydb_db | Query   | 10   | Locked                                                             | SELECT
                                    c.ID_CAT, b.name AS bname, b.description, b.numT                                              opics, b.memberGroups,
                                    b.ID_PARENT, c |
    | 1998 | mydb_db | localhost | mydb_db | Query   | 7    | Locked                                                             | SELECT
                                    c.ID_CAT, b.name AS bname, b.description, b.numT                                              opics, b.memberGroups,
                                    b.ID_PARENT, c |
    | 1999 | mydb_db | localhost | mydb_db | Query   | 7    | Locked                                                             | SELECT
                                    c.ID_CAT, b.name AS bname, b.description, b.numT                                              opics, b.memberGroups,
                                    b.ID_PARENT, c |
    | 2001 | mydb_db | localhost | mydb_db | Query   | 6    | Locked                                                             | SELECT
                                    c.ID_CAT, b.name AS bname, b.description, b.numT                                              opics, b.memberGroups,
                                    b.ID_PARENT, c |
    | 2002 | mydb_db | localhost | mydb_db | Query   | 5    | Locked                                                             | SELECT
                                    c.ID_CAT, b.name AS bname, b.description, b.numT                                              opics, b.memberGroups,
                                    b.ID_PARENT, c |
    | 2003 | mydb_db | localhost | mydb_db | Query   | 3    | Locked                                                             | SELECT
                                    c.ID_CAT, b.name AS bname, b.description, b.numT                                              opics, b.memberGroups,
                                    b.ID_PARENT, c |
    | 2004 | mydb_db | localhost | mydb_db | Query   | 2    | Locked                                                             | SELECT
                                    c.ID_CAT, b.name AS bname, b.description, b.numT                                              opics, b.memberGroups,
                                    b.ID_PARENT, c |
    | 2006 | mydb_db | localhost | mydb_db | Query   | 1    | Locked                                                             | SELECT passwd, ID_MEMBER, ID_GROUP, lngfile, is_activated, emailAddress                                              , additionalGroups, memberNam |
    | 2007 | mydb_db | localhost | mydb_db | Query   | 1    | Locked                                                             | SELECT
                                    c.ID_CAT, b.name AS bname, b.description, b.numT                                              opics, b.memberGroups,
                                    b.ID_PARENT, c |
    | 2008 | mydb_db | localhost | mydb_db | Query   | 1    | Locked                                                             | SELECT passwd, ID_MEMBER, ID_GROUP, lngfile, is_activated, emailAddress                                              , additionalGroups, memberNam |
    | 2009 | mydb_db | localhost | mydb_db | Query   | 1    | Locked                                                             | SELECT
                                    c.ID_CAT, b.name AS bname, b.description, b.numT                                              opics, b.memberGroups,
                                    b.ID_PARENT, c |
    | 2010 | mydb_db | localhost | mydb_db | Query   | 1    | Locked                                                             | SELECT
                            IFNULL(lo.logTime, 0) AS isOnline, IFNULL(a.ID_ATTACH, 0                                              ) AS ID_ATTACH, a.filename, a.atta |
    | 2011 | root        | localhost |             | Query   | 0    |                                                                    | show processlist                                                                                                                                   |
    +------+-------------+-----------+-------------+---------+------+---------------                                              -------+------------------------------------------------------------------------                                              ------------------------------+
    
    when the server is normal i only see root and leechprotect but when it's overloading i see to many things like pasted above.. is there any issue on these ? locking causing issues ?
     
    #7 rohan_, Jan 10, 2012
    Last edited: Jan 10, 2012
  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
    Re: MySQL stuck, Site goes down in every few minutes, high disk IO waiting

    Some systems do not have /usr/sbin/httpd symlinked to /usr/local/apache/bin/apachectl, so you would want to run this instead of httpd:

    Code:
    /usr/local/apache/bin/apachectl -l | grep -E -i "worker|prefork|event"
     
  9. rohan_

    rohan_ Member

    Joined:
    Jun 4, 2011
    Messages:
    18
    Likes Received:
    0
    Trophy Points:
    1
    Re: MySQL stuck, Site goes down in every few minutes, high disk IO waiting

    ^^ Did that, nothing came out
     
  10. alphawolf50

    alphawolf50 Well-Known Member

    Joined:
    Apr 28, 2011
    Messages:
    186
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Re: MySQL stuck, Site goes down in every few minutes, high disk IO waiting

    Darnit...

    I wrote out a bunch of recommendations, and then I noticed one thing. You SAID you had 3GB of RAM, but mysqltuner reports that mysql's current maximum memory usage is 859.0M (85% of installed RAM)... that means you only have 1GB of RAM. You need to find out why you don't have the 3GB of RAM that you think you do.

    In the meanwhile, you might get better performance just by adding these three lines to your my.cnf:
    Code:
    thread_cache_size=8
    sort_buffer_size=256K
    concurrent_insert=2
    Also replace "table_cache=512" with "table_open_cache=512".

    Setting sort_buffer_size to 256K will reduce MySQL's overall footprint, which should stop any swapping and save on memory allocation time. The concurrent_insert setting will help prevent table locking, at the expense of more fragmented tables. Changing table_cache to table_open_cache is just a semantics thing, as the former variable has been deprecated.

    Let me know what you find out about the RAM situation, and if the above settings help at all. I'm hoping they make your system stable enough to get a 24 hour mysqltuner report. We'll worry about apache tuning later.
     
  11. rohan_

    rohan_ Member

    Joined:
    Jun 4, 2011
    Messages:
    18
    Likes Received:
    0
    Trophy Points:
    1
    Re: MySQL stuck, Site goes down in every few minutes, high disk IO waiting

    Thanks Alpha, Did the changes u told me to do.

    and about the RAM, that is because I switched my site to different server recently with better processor.

    and normally in stable times my ram usage is never more than 400-500MB, but when my site gets stuck then ram usage starts increasing slowly slowly like 20MB per 3 second and when it goes to max server gets crashed.. I even tried 8GB server but ram isnt a problem i think, its usage just gets increasing.

    lets see now if it's stable.
     
  12. rohan_

    rohan_ Member

    Joined:
    Jun 4, 2011
    Messages:
    18
    Likes Received:
    0
    Trophy Points:
    1
    Re: MySQL stuck, Site goes down in every few minutes, high disk IO waiting

    last day passed without any downtime :) I hope the server will be stable forever like this... Thanks a Ton for the help.

    I will report here if any overload causes again.
     
  13. alphawolf50

    alphawolf50 Well-Known Member

    Joined:
    Apr 28, 2011
    Messages:
    186
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Re: MySQL stuck, Site goes down in every few minutes, high disk IO waiting

    Glad that made the server stable. MySQL could still use some optimization though -- if you want more help just post a mysqltuner report with at least 24 hours of runtime.

    If possible to upgrade your ram, I would suggest at least 2GB, preferably 4GB.
     
  14. rohan_

    rohan_ Member

    Joined:
    Jun 4, 2011
    Messages:
    18
    Likes Received:
    0
    Trophy Points:
    1
    Re: MySQL stuck, Site goes down in every few minutes, high disk IO waiting

    it's overloaded again :( I have tried 4GB ram server but nothing worked.. can u tell me any way which can restart mysql automatically when server crosses 5 load ?

    also please help me fix this permanently
     
  15. 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
    Re: MySQL stuck, Site goes down in every few minutes, high disk IO waiting

    You probably would want to hire someone if you cannot optimize it with suggestions here. If this is a very important site or server, the extra cost to hire someone to do it right would be well worth it.

    We have providers listed at this location:

    Dev & Sys Admin Services « Application Catalog
     
  16. rohan_

    rohan_ Member

    Joined:
    Jun 4, 2011
    Messages:
    18
    Likes Received:
    0
    Trophy Points:
    1
    Re: MySQL stuck, Site goes down in every few minutes, high disk IO waiting

    new report

    Code:
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.56-log
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 1G (Tables: 120)
    [--] Data in InnoDB tables: 144K (Tables: 9)
    [!!] Total fragmented tables: 22
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 13h 3m 38s (8M q [66.655 qps], 558K conn, TX: 32B, RX: 2B)
    [--] Reads / Writes: 54% / 46%
    [--] Total buffers: 818.0M global + 1.0M per thread (300 max threads)
    [!!] Maximum possible memory usage: 1.1G (111% of installed RAM)
    [OK] Slow queries: 0% (1K/8M)
    [!!] Highest connection usage: 86%  (259/300)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/788.9M
    [OK] Key buffer hit rate: 99.2% (1B cached / 10M reads)
    [OK] Query cache efficiency: 57.5% (3M cached / 5M selects)
    [OK] Query cache prunes per day: 0
    [!!] Sorts requiring temporary tables: 118% (56K temp sorts / 47K sorts)
    [!!] Joins performed without indexes: 1119
    [!!] Temporary tables created on disk: 48% (10K on disk / 21K total)
    [OK] Thread cache hit rate: 99% (2K created / 558K connections)
    [!!] Table cache hit rate: 3% (512 open / 14K opened)
    [OK] Open file limit used: 35% (531/1K)
    [OK] Table locks acquired immediately: 99% (8M immediate / 8M locks)
    [OK] InnoDB data size / buffer pool: 144.0K/8.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Reduce your overall MySQL memory footprint for system stability
        Reduce or eliminate persistent connections to reduce connection usage
        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 ***
        max_connections (> 300)
        wait_timeout (< 84100)
        interactive_timeout (< 84100)
        sort_buffer_size (> 256K)
        read_rnd_buffer_size (> 256K)
        join_buffer_size (> 128.0K, or always use indexes with joins)
        tmp_table_size (> 200M)
        max_heap_table_size (> 200M)
        table_cache (> 512)
    
    few hours ago somebody said me to turn on persistent connections and i think this overload was caused by too many connections.. I have disabled persistent connection now. let's see if something happens again.

    Also please check and see if u think i need more edits in my.cnf

    Thank you
     
  17. alphawolf50

    alphawolf50 Well-Known Member

    Joined:
    Apr 28, 2011
    Messages:
    186
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Re: MySQL stuck, Site goes down in every few minutes, high disk IO waiting

    Hi Rohan,

    Persistent connections are just... no. Please don't take advice from multiple people at the same time, as it makes it impossible to nail down which change helped/hurt performance.

    Do you restart MySQL when you make changes to my.cnf? Mysqltuner is showing very odd (impossible) results... like 118% of sorts requiring temporary tables. It looks like you flushed status at some point, but didn't restart. Please restart mysql and send a new mysqltuner after 24 hours. In the meanwhile, please post your my.cnf.

    Regarding more RAM: you might have tried 4GB of RAM before, but you never tuned mysql properly to use it. 1GB is far too little RAM for your amount of data and number of connections.
     
  18. rohan_

    rohan_ Member

    Joined:
    Jun 4, 2011
    Messages:
    18
    Likes Received:
    0
    Trophy Points:
    1
    Re: MySQL stuck, Site goes down in every few minutes, high disk IO waiting

    yes I restarted mysql just after u told me to do changes yesterday
     
    #18 rohan_, Jan 12, 2012
    Last edited: Jan 12, 2012
  19. rohan_

    rohan_ Member

    Joined:
    Jun 4, 2011
    Messages:
    18
    Likes Received:
    0
    Trophy Points:
    1
    Re: MySQL stuck, Site goes down in every few minutes, high disk IO waiting

    oh man down again :(

    once again what i see is a lot of connections.. is my site having too much connections or the freezing of mysql cause it to make more connection ?

    EDIT: Just did a reboot, worked for 2 min then got stuck again, now there were not much traffic or connections.. what can be the reason ? I see too many locked tables........
     
    #19 rohan_, Jan 12, 2012
    Last edited: Jan 12, 2012
  20. alphawolf50

    alphawolf50 Well-Known Member

    Joined:
    Apr 28, 2011
    Messages:
    186
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Re: MySQL stuck, Site goes down in every few minutes, high disk IO waiting

    Post your my.cnf so I can see what's in there.
     
Loading...

Share This Page