SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict
    Join Date
    Nov 2001
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Too many MySQL connections in wait state. Linux + MySQL 5.0.45

    I recently began using a new database server (dual quad core Xeon running Debian Linux). It's running MySQL 5.0.45 and is configured for 500 connections.

    When web site traffic picks up during the day I start to get errors connecting to the database. It won't happen twice in a row, but will occur once every few minutes. The server has two aliases in the hosts file: db01 and dbserve. Dbserve is used by the application to make it easier to change database servers.

    #2003 - Can't connect to MySQL server on 'dbserve' (99)

    Error 99 apparently means the OS can't allocate any more addresses (ports?). A quick check on the web server shows a scary number of open connections:

    # netstat |grep db01:mysql |wc -l
    28219

    28,219 connections. Almost 27,000 are in TIME_WAIT.

    The application shouldn't be keeping these open. It's PHP and doesn't use persistent connections. Apache is configured to respawn processes after only 4000 requests and there are currently 163 Apache processes running.

    My.cnf had wait_timeout set to 10. I just changed it to 120 to match the old database server, but it doesn't appear to have helped.

    What could be keeping these connections open and how can I get them to close sooner?

    Thanks.

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    check to make sure DNS resolution still works on the DB server. or add skip-name-resolve to your mysql configuration file.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  3. #3
    SitePoint Addict
    Join Date
    Nov 2001
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck View Post
    check to make sure DNS resolution still works on the DB server. or add skip-name-resolve to your mysql configuration file.
    Interestingly, DNS is broken on the server, but all servers that connect to it are in the hosts file. That should be alright, shouldn't it?

    As an update, I was able to work around this problem by adjusting /proc/sys/net/ipv4/tcp_tw_reuse and /proc/sys/net/ipv4/tcp_tw_recycle each to a value of 1. That clearly isn't the best long-term solution because it only solves this symptom of the problem, but it does get the web site working normally again.

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    it should be, but i've seen stranger. i still suggest trying the skip-name-resolve option to see if that fixes it.

    is this server behind a load balancer?
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  5. #5
    SitePoint Addict
    Join Date
    Nov 2001
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck View Post
    it should be, but i've seen stranger. i still suggest trying the skip-name-resolve option to see if that fixes it.

    is this server behind a load balancer?
    I added that to the configuration, but don't see any change. The servers are behind an IPCop firewall, but there is no load balancing.

    FYI, I had to set /proc/sys/net/ipv4/tcp_tw_recycle back to 0 because it was really messing up connectivity after running for a while. Not only was connectivity to the database server slow, but so was ssh.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •