Too many open connections to mySQL

Discussion in 'MySQL' started by shergill, Aug 14, 2007.

0
  1. #1
    Hi guys.. my host is telling me that I am leaving too many open connections to mysql. This loads up the server and causes mysql to restart. I am trying to figure out where these open connections are. I am using phpMyAdmin 'status' and I see this info

    Threads_connected 1 The number of currently open connections.
    Threads_created 1,081 The number of threads created to handle


    Doesn't that mean that there is only 1 open connections? My host is saying that I have over 200++

    please advice.
     
    shergill, Aug 14, 2007 IP
  2. shergill

    shergill Peon

    Messages:
    138
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Anyone got any input?
     
    shergill, Aug 14, 2007 IP
  3. gweb

    gweb Well-Known Member

    Messages:
    1,167
    Likes Received:
    20
    Best Answers:
    0
    Trophy Points:
    130
    #3
    do you mean too many queries are occuring - use a robots.txt file in your root directory..
     
    gweb, Aug 14, 2007 IP
  4. Estevan

    Estevan Peon

    Messages:
    120
    Likes Received:
    8
    Best Answers:
    1
    Trophy Points:
    0
    #4
    Estevan, Aug 15, 2007 IP
  5. supperman

    supperman Peon

    Messages:
    22
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #5
    maybe you use mysql_pconnect() in your php(forever connected)

    try mysql_connect()
    but kill those thread first

    hope this helps

    /s
     
    supperman, Aug 17, 2007 IP
  6. Roido

    Roido Active Member

    Messages:
    273
    Likes Received:
    5
    Best Answers:
    0
    Trophy Points:
    60
    #6
    This is probably down to persistent data connections being set to 'on' in your php.ini.

    What happens when this is on is - a visitor accesses your site and your script makes a connection to your database for that visitor. If persistence is turned on then this connection is kept alive in case the visitor wants to run another script which will access the database - this is to improve speed as if the connection is kept alive then it takes less time to get the data from the db.

    This is all very well but on busy sites if you have too many people all with persistent kept alive connections to your database then things can get congested and you will get the problem you are facing.

    You need to edit your php.ini and find the SQL section.

    If your running mysql then change the line:

    [MySQL]
    mysql.allow_persistent = On ; allow or prevent persistent link

    to

    [MySQL]
    mysql.allow_persistent = Off ; allow or prevent persistent link

    This means when a visitor accesses your site the script will make a connection to your database get the data then close the connection which will free it up for someone else. The only drawback is when the same visitor accesses another script then the connection has to be re-made which has a slight delay but your visitors will not notice it.
     
    Roido, Aug 17, 2007 IP