too many connections - Sleep problems (not me personally!)

[ProblemHelpPlease]

I sometime get a "too many connnections" error in the server logs. I know this means I have exceeded the number of permitted connections to the mysql server but comparing the error against the access logs shows that it is unlikely that this is just a high number of visitors. Visitor number are around 700 per day with 6500 page views.

 

I noticed from looking at the processlist for the mysql server that I am getting a lot of Sleep processes running(into several hundred seconds). The mysql.connect_timeout is set to 60 and I have read elsewhere that this can cause problems with "too many connections" on apache servers running PHP as CGI and reducing it helps sort the problem.

 

I dont have direct access to the php.ini file for the mysql server so cant change mysql.connect_timeout or the amount of allowed connections (which is set to 25).

 

I am wondering if the problem is related to another piece of code I added. I needed some content to refresh when pressing the back button so added session_start(); to the top of the page to force a reload. This worked as intended but I am now wondering if this is causing each page a users opens to generate a new connection rather than reusing the Sleep processes that are running. I am not sure if this is how the Sleep processes work or if a new connection would be used anyway.

 

I am also seeing pages that timeout before returning headers , but don't think this is related to the mysql problem.

 

Both problems only happen about 1 in every 500 page views or so.

 

Does anyone have any knowledge on this kind of problem.

 

 

[Mr Hyde]

Don't know if this will help or not, but I am kind of picky about my connections in my code. I always up up any function call like this:

sql_function($fake_var, $fake_var2, $conn = FALSE){
     if($conn==FALSE){$conn = conn();}
...

 

Where conn() is a mysqli connection helper function. This way, when I call an object it could have a million pages included, but I keep on passing the connection to each method or function individually so they will only use one connection. Php is supposed to manage connections real well... but I would rather avoid the whole problem in the first place.

 

Of course, in a php class, I usually create a single connection in the constructor, set it to be a class constant , then just access it, or pass it on to other called classes like $this->conn();

 

Don't forget that you can set php ini variables temporarily in your script like:

ini_set('mysql.connect_timeout', $int);

So if you think that REALLY is the problem, you can call that and straighten it out.

 

Hope that helps.

[ProblemHelpPlease]

Thanks for your reply.

 

I already use a very similar connection method like the one you suggested.

 

I have now managed to implement the changes to the server that I was hoping would fix the problem. Unfortunatley changing the timeout settings has not resulted in the error going away.

 

I am at a loss for what else to try other than a different server.

 

Does anyone have any idea what amount of traffic would be required to cause the "too many connections" problem when I am only allowed 25. The site does use quite a lot of mysql (including a livesearch that may be causing a lot of rapid mysql requests).

[nankoweap]

is your application one among many applications on the machine and mysql is a shared resource? if so, it may not be your particular application.

[ProblemHelpPlease]

Ok, I have new information on this problem so i have reopened this post in the hope someone knows something about it.

 

The "too many connection" issue is due to the entire server limit of 451 being reached. It normally operates at about 10 connections per second but over a 2-3 minute period is climed to reach 451, stayed there for about 1 min then gradually fell again. Duing this time the processlist for my site on the server (it is shared) remained at about 9 connections with all of them being in the Sleep state (at about 250).

 

I am guessing that all sites on the server that are opening connections are not closing for some reason resulting in the connections climboing rapidly and then hitting them limit.

 

At the same time the number of queries drops to 0 and the traffic to the mysql server drops to 0, probably because nobody can connect to it.

 

Does anyone have any ideas as to what might cause this (it only happens very rarely) or how to go about finding out what might be the cause.

[darkfreaks]

sometimes when you have a huge site the server becomes boggled down with too much traffic and you end up with "can't connect to session" errors. have you tried memcached? it helps speed up your MYSQL queries. and frees up valuable space on your servers.

memcache.inc.php

<?php
/**
*
* Setup:
*
    edit the singleton() metod 
    and define the list of memcached servers in a 2-d array
    in the format
    array(
        array('192.168.0.1'=>'11211'),
        array('192.168.0.2'=>'11211'),
    );
*
*
* Usage:
*
<?php
//include the class name
include ('memcache.class.php');

//store the variable
Cache::set('key','abc');

//increment/decrement the integer value
Cache::increment('key');
Cache::decrement('key');

//fetch the value by it's key
echo Cache::get('key');


//delete the data
echo Cache::delete('key');

//Clear the cache memory on all servers
Cache::flush();

?>

Cache::replace() and Cache::add are implemented also.

More information can be obtained here:
http://www.danga.com/memcached/
http://www.php.net/memcache

*/

/**
* The class makes it easier to work with memcached servers and provides hints in the IDE like Zend Studio
* @author Grigori Kochanov http://www.grik.net/
* @version 1
*
*/
class Cache {
/**
* Resources of the opend memcached connections
* @var array [memcache objects]
*/
protected $mc_servers = array();
/**
* Quantity of servers used
* @var int
*/
protected $mc_servers_count;

static $instance;

/**
* Singleton to call from all other functions
*/
static function singleton(){
    //Write here where from to get the servers list from, like 
    // global $servers
    $servers = _Config::$memcache_servers;
    
    self::$instance || 
        self::$instance = new Cache($servers);
    return self::$instance;
}

/**
* Accepts the 2-d array with details of memcached servers
*
* @param array $servers
*/
protected function __construct(array $servers){
    if (!$servers){
        trigger_error('No memcache servers to connect',E_USER_WARNING);
    }
    for ($i = 0, $n = count($servers); $i < $n; ++$i){
        ( $con = memcache_pconnect(key($servers[$i]), current($servers[$i])) )&& 
            $this->mc_servers[] = $con;
    }
    $this->mc_servers_count = count($this->mc_servers);
    if (!$this->mc_servers_count){
        $this->mc_servers[0]=null;
    }
}
/**
* Returns the resource for the memcache connection
*
* @param string $key
* @return object memcache
*/
protected function getMemcacheLink($key){
    if ( $this->mc_servers_count <2 ){
        //no servers choice
        return $this->mc_servers[0];
    }
    return $this->mc_servers[(crc32($key) & 0x7fffffff)%$this->mc_servers_count];
}

/**
* Clear the cache
*
* @return void
*/
static function flush() {
    $x = self::singleton()->mc_servers_count;
    for ($i = 0; $i < $x; ++$i){
        $a = self::singleton()->mc_servers[$i];
        self::singleton()->mc_servers[$i]->flush();
    }
}

/**
* Returns the value stored in the memory by it's key
*
* @param string $key
* @return mix
*/
static function get($key) {
    return self::singleton()->getMemcacheLink($key)->get($key);
}

/**
* Store the value in the memcache memory (overwrite if key exists)
*
* @param string $key
* @param mix $var
* @param bool $compress
* @param int $expire (seconds before item expires)
* @return bool
*/
static function set($key, $var, $compress=0, $expire=0) {
    return self::singleton()->getMemcacheLink($key)->set($key, $var, $compress?MEMCACHE_COMPRESSED:null, $expire);
}
/**
* Set the value in memcache if the value does not exist; returns FALSE if value exists
*
* @param sting $key
* @param mix $var
* @param bool $compress
* @param int $expire
* @return bool
*/
static function add($key, $var, $compress=0, $expire=0) {
    return self::singleton()->getMemcacheLink($key)->add($key, $var, $compress?MEMCACHE_COMPRESSED:null, $expire);
}

/**
* Replace an existing value
*
* @param string $key
* @param mix $var
* @param bool $compress
* @param int $expire
* @return bool
*/
static function replace($key, $var, $compress=0, $expire=0) {
    return self::singleton()->getMemcacheLink($key)->replace($key, $var, $compress?MEMCACHE_COMPRESSED:null, $expire);
}
/**
* Delete a record or set a timeout
*
* @param string $key
* @param int $timeout
* @return bool
*/
static function delete($key, $timeout=0) {
    return self::singleton()->getMemcacheLink($key)->delete($key, $timeout);
}
/**
* Increment an existing integer value
*
* @param string $key
* @param mix $value
* @return bool
*/
static function increment($key, $value=1) {
    return self::singleton()->getMemcacheLink($key)->increment($key, $value);
}

/**
* Decrement an existing value
*
* @param string $key
* @param mix $value
* @return bool
*/
static function decrement($key, $value=1) {
    return self::singleton()->getMemcacheLink($key)->decrement($key, $value);
}


//class end
}

?> 

 

for more information you can check out http://memcached.org/

[ProblemHelpPlease]

The problem is not related to high traffic, this is a shared server so any sites traffic high enough to cause an issue are detected and moved.

 

The servers connections are on average quite loe and only spike for about 2-3mins once a day or so.

[praveenhotha]

are u using mysql_pconnect? If so, the connection would still remains even after the script ends.

[ProblemHelpPlease]

No the connections do not use mysql_pconnect. It is a server wide issue that is causing all current connections and any new connections to stay in Sleep after they complete rather than closing for around 1-3mins.

[darkfreaks]

might want to check the list of possible reasons

 

Why can't i connect to MYSQL