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.

Too Many Connections / MySQL Instable

Discussion in 'General Discussion' started by AlaskanWolf, Mar 25, 2003.

  1. AlaskanWolf

    AlaskanWolf Well-Known Member

    Joined:
    Aug 11, 2001
    Messages:
    537
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Fremont CA
    hopefully someone in here knows mysql

    Got a server running mysql, running into instablity issues with it, about 300 customers on it, server gets "too many connections" daily, to the point, even root cant even login to mysql to see who the offending user is, even though plain jane on MySQL, sql is suppose to leave 1 connection avail for root

    Any way to track down why mysql is dying and/or reaching this point of connections issue? (i already got logging enabled, edited the my.cnf as much as i think i can, no help)...

    atm im stuck (since its network wide...we have about 6 servers and i seemd to fix this in the past by running corruption check / fix but so far, this server has been very instable last few days) any way i can configure mysql to say...be in safe mode or something so i can find out where / how / what to fix??


    Today SQL crashed to the point where, it wasnt getting too many connections error, rather you couldnt even get INTO mysql, its like it was overloaded, I couldnt even kill -9 or skill it, i had to reboot the machine
     
    #1 AlaskanWolf, Mar 25, 2003
    Last edited: Mar 26, 2003
  2. MN-Robert

    MN-Robert Well-Known Member

    Joined:
    Feb 19, 2003
    Messages:
    203
    Likes Received:
    0
    Trophy Points:
    16
    We had the same problem gary
     
  3. Admin356

    Admin356 Active Member

    Joined:
    Feb 19, 2003
    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    1
    2 servers today, even after upgrading to 3.23.56 - I am beat - It seems to be when cpanellogd runs - but not always... the latest crash saw loads of 30 + , one server was reporting 500% memory usage - Anyone else experiencing this?

    I've tried testing db's for corruption, logging, disabling certain clients db's - Drawn a blank, seems to just have occured since the upgrade to cpanel 6, but its getting worse lately..
     
  4. MN-Robert

    MN-Robert Well-Known Member

    Joined:
    Feb 19, 2003
    Messages:
    203
    Likes Received:
    0
    Trophy Points:
    16
    Some of ours are saying 600% memory usage
     
  5. AlaskanWolf

    AlaskanWolf Well-Known Member

    Joined:
    Aug 11, 2001
    Messages:
    537
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Fremont CA
    Yes, we see the same thing, and its *impossible* to find out what db's are causing it. I just wish Nick can help us in this, as i have bugged him in the past, to see that not just me is having such a problem, would be benificial to everyone if someone can assist us non-mysql gurus in troubleshooting MySQL

    Turning on logs is one thing, but thats really no help, since that doesnt tell you anything, the mysql error log doesnt help much either.

    It seems the major problems (everyone) is seeing in a lump sum is the inablity to troubleshoot MySQL proactively or even after the fact, with either corrupt db's, max connections, or high loads, and even to the point where regarding high loads, its impossible to see what db is causing the high load, all you ever see in PS or top is the path to mysql
     
  6. MN-Robert

    MN-Robert Well-Known Member

    Joined:
    Feb 19, 2003
    Messages:
    203
    Likes Received:
    0
    Trophy Points:
    16
    Gary, I am starting to believe it maybe a cpanel problem. It's true mroe info is needed to catch these people as with the current situation mysql procinfo does not work.
     
  7. Curious Too

    Curious Too Well-Known Member

    Joined:
    Aug 31, 2001
    Messages:
    427
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    For the record I am not seeing this problem. I have 10 servers, average 250 sites per, 6-7 extremely active VBulletin sites including one with a 500MB database pulling 78GB Bandwidth per month.
     
  8. alwaysweb

    alwaysweb Well-Known Member

    Joined:
    Mar 8, 2002
    Messages:
    97
    Likes Received:
    0
    Trophy Points:
    0
    Location:
    Dallas, TX
    cPanel Access Level:
    Root Administrator
    Hi Gary, we used to have similar MySQL issues in the past. Likewise, we couldn't see mysql processes when it gave 'too many connections'...One quick monitoring tool we set up was this (not using it anymore, no longer needed). First, sut up a cron to run frequently (every 10 min, 5 min, or even every 1 minute if you really wanted, depending on your needs). Have the cron we used:

    mysqladmin processlist | mail -s "MySQL" mysql@hostname.com

    This will grab processlist and mail the output to you or root. (Instead of your regular mailbox, we set up a 'mysql' mailbox on the server for it...) If MySQL gives you issues, check the local 'mysql' mailbox you created on server and look through last 5 or 10 processlist emails... It should be pretty obvious what site is loading up the processes, or using persistent DB connections, etc. And purge periodically.

    Until Cpanel can help us leave one connection open for the root user, I hope this does you some good to track the problem user/site down! As well, we have highly tuned our my.cnf file to our environment. If you'd like assistance, let me know and I'd be glad to try and help.

    P.S. You could always store the processlist output in a MySQL database and save all that mail activity, but that feels silly to me! :)
     
  9. LS_Drew

    LS_Drew Well-Known Member

    Joined:
    Feb 20, 2003
    Messages:
    187
    Likes Received:
    0
    Trophy Points:
    16
    For the record, we are having this problem as well. It is EXTREMELY annoying.

    I have to reboot the server in order to kill the mysql pid that is taking up 99% processor.

    We have reinstalled, upgraded, tweaked settings, turned on logging, watched the processlist. We just can't pinpoint the problem.

    I don't like rebooting servers. :(
     
  10. mesranet

    mesranet Well-Known Member

    Joined:
    May 6, 2002
    Messages:
    126
    Likes Received:
    0
    Trophy Points:
    16
    Hi All,

    I have same problem too, but when cpanel upgrade to new version 3.23.56, i have no more problem on mysql.
     
  11. MN-Robert

    MN-Robert Well-Known Member

    Joined:
    Feb 19, 2003
    Messages:
    203
    Likes Received:
    0
    Trophy Points:
    16
    I have been monitoring our server. It seems to occur when mailman is running, could be related
     
  12. LS_Drew

    LS_Drew Well-Known Member

    Joined:
    Feb 20, 2003
    Messages:
    187
    Likes Received:
    0
    Trophy Points:
    16
    Logging was off when it started and we put it on to try and help pinpoint the cause of the issue, however it didn't do a damn thing.
     
  13. Admin356

    Admin356 Active Member

    Joined:
    Feb 19, 2003
    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    1
    Found our issue, it was a database causing the problem.

    A db was locking up Mysql (Creating many Lock tables) and writing to /tmp - Filling /tmp up completely until Mysql was restarted, then all was good again until that process was repeated again.

    Logging the "mysqladmin processlist" made it fairly simple to see where that one originated from.
     
  14. LS_Drew

    LS_Drew Well-Known Member

    Joined:
    Feb 20, 2003
    Messages:
    187
    Likes Received:
    0
    Trophy Points:
    16
    Mysqladmin processlist was the first thing I did. Didn't help one bit.
     
    #14 LS_Drew, Mar 30, 2003
    Last edited: Mar 30, 2003
  15. shaun

    shaun Well-Known Member

    Joined:
    Nov 9, 2001
    Messages:
    698
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    San Clemente, Ca
    Since their where enough of you complaining about this i wrote a script for you guys...

    http://cplicensing.net/extras/scripts.php

    it's called watchmysql

    Run it in the background (./watchmysql &)

    and it will connect and stay connected (unless you restart mysql or somthing) and it will check mysql every xx seconds and check to see if a user has abused his/her limits. I ran this script and it seamed to work. Let me know if you guys have any problems with it.
     
  16. sexy_guy

    sexy_guy Well-Known Member

    Joined:
    Mar 19, 2003
    Messages:
    848
    Likes Received:
    0
    Trophy Points:
    16
    Nice script! Hmm, you looking in root for my.cnf? I thought it was read from /etc/my.cnf? At least my.cnf is configured and being read from /etc/ on my servers.
     
  17. shaun

    shaun Well-Known Member

    Joined:
    Nov 9, 2001
    Messages:
    698
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    San Clemente, Ca
    All my cPanel machines use /root/.my.cnf

    It's looking for the mysql root password.


    If yours for some reason is in /etc/.my.cnf then change it in the script... I'm running unmodified cpanel installs so all yours should be the same (with redhat... i dont knwo about other distros).
     
  18. sexy_guy

    sexy_guy Well-Known Member

    Joined:
    Mar 19, 2003
    Messages:
    848
    Likes Received:
    0
    Trophy Points:
    16
    Ok, so should one run this script if we are not having connection problems just to prevent one in the event?
     
  19. shaun

    shaun Well-Known Member

    Joined:
    Nov 9, 2001
    Messages:
    698
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    San Clemente, Ca
    I dont see a need to...


    I Could write a better one and actually have it kill the threads rather than restart mysql if you guys really wanted to run it 24/7.

    But that would take more time... I think this will work to help trouble shoot a problem. Maybe nick will see this and write a better one that works with cpanel and then maybe he can setup limits with in cpanel. I dunno.

    Theirs suppose to be a max connections per user setting you can set in the .my.cnf


    I never tryed to use it though. I dotn have mysql connection problems and when/if i ever do i usually find the user right away. Keep in mind i manage alot of cpanel servers... so this is not a normal problem. Probably sombody with a misconfigured script or a flood calling that script or somthing.
     
  20. thomas

    thomas Well-Known Member

    Joined:
    Mar 31, 2002
    Messages:
    70
    Likes Received:
    0
    Trophy Points:
    6
    Thanks Shaun!!


    What do you guys consider a fair amount of connections per user?
     
Loading...

Share This Page