CODEWITHSUNDEEP
phpmysql
Naresh Chauhan
Naresh Chauhan

Reputation: 79

Too many open MySQL connections

I am developing a project in which two portions of webpage frequently change and fetch recent data. I have some confusion about whether to use mysql_connect or mysql_pconnect? I have one config file that is being included in every page. There is one database connection object which I use for queries. Even when approximately 70 users are online it shows 20,000 connections on my server. Please suggest me the best way to keep a single connection alive from a single user, so there should be 70 connections when there are 70 users online. Currently I'm not using mysql_close method to close connection. Is this the reason it shows these many connections? Your advice will really be appreciated.

Upvotes: 1

Views: 974

Answers (3)

Mike Purcell
Mike Purcell

Reputation: 19979

Interstellar_Coder is correct, you want to use a singleton/factory solution for your db connection handling. We use here at work and it serves us well.

While Interstallar_Coder's solution is valid, I wrote up a more flexible solution in response to another post.

Destroy db connections:

public function __destruct()
{
    foreach (self::$dbos as $key => $dbo) {
        self::$dbos[$key] = null;
    }
}

More info about PDO connection management.

Upvotes: 0

Jakob Alexander Eichler
Jakob Alexander Eichler

Reputation: 3056

Here you have my implementation maybe it is useful for you 

<?php
    class Utils_Sql{
        private $count = 0;
        private static $sqlObj = null;
        /** 
         * @return Utils_Sql 
         */
        public static function getSql(){
            if(self::$sqlObj===null){self::$sqlObj = new Utils_Sql();}
            return self::$sqlObj;
        }
        private $db;
        private function __construct(){
            $this->db = mysql_connect(MYSQL_SERVER,DB_LOGIN,DB_PASS);
            if($this->db === false){
                Utils_Logging_Logger::getLogger()->log(
                    array("Unable to connect to DB on Mysql_Server:".MYSQL_SERVER." with login:".DB_LOGIN." and pass:".DB_PASS."!")
                    ,Utils_Logging_Logger::TYPE_ERROR
                 );
            }else{
                if (!mysql_select_db ( DB_NAME , $this->db )) {
                    $sql = "CREATE DATABASE " . DB_NAME;
                    $this->qry($sql);
                    if (!mysql_select_db ( DB_NAME , $this->db )) {
                        Utils_Logging_Logger::getLogger()->log(
                            array("DB: ".DB_NAME." not found"),
                            Utils_Logging_Logger::TYPE_ERROR
                        );
                    }
                }
            }
        mysql_set_charset  ('utf8',$this->getConnection());     
        }    
        public function getConnection(){return $this->db;}
        public function qry($sql,$errType,$errMsg=""){
            $this->count++;
    //      Utils_Logging_Logger::getLogger()->log("<br>$sql<br>",Utils_Logging_Logger::TYPE_LOG);
            $ret = mysql_query($sql,$this->getConnection());
            if(mysql_error($this->getConnection())){
                //Error
                $msgs = array(
                    "mysql_error: (".mysql_error($this->getConnection()).")",
                    "qry: \"$sql\""
                );
                if($errMsg!==""){$msgs[]="$errMsg";}
                Utils_Logging_Logger::getLogger()->log($msgs,$errType);
            }
            return $ret;
        }
        public function getData($sql,$errType=Utils_Logging_Logger::TYPE_ERROR){
            $r = $this->qry($sql,$errType);
            if($r === false){
                Utils_Logging_Logger::getLogger()->log("No Sql Resource, Illegal Query!",$errType);
                return false;
            }
            $ret = array();
            while(($data = mysql_fetch_assoc($r))!==false){
                $ret[] = $data;
            }
            if(count($ret)===1){return $ret[0];}
            else if(count($ret)>1){return $ret;}
            else{
                $msgs = array(
                    "No resulset found.",
                    "qry: \"$sql\""
                );
                Utils_Logging_Logger::getLogger()->log($msgs,$errType|Utils_Logging_Logger::TYPE_WARNING);
                return false;
            }
        }
        public function getInsertId($sql,$errType=Utils_Logging_Logger::TYPE_ERROR){
            $this->qry($sql,$errType);
            $ret = mysql_insert_id($this->getConnection());
            if(!is_numeric($ret)){Utils_Logging_Logger::getLogger()->log("mysql_insert_id is not numeric!",$errType);}
            return $ret;
        }
        public function getDbName(){return DB_NAME;}
        public function __destruct(){
    //        Utils_Logging_Logger::getLogger()->log("Querys count: '$this->count'",Utils_Logging_Logger::TYPE_LOG);
        }
    }

Upvotes: 0

aziz punjani
aziz punjani

Reputation: 25766

A common pattern used in this case is the singleton pattern, here's some rough code. 

class DB_Instance
{
    private static $db;

    public static function getDB()
    {
        if (!self::$db)
                self::$db = new Database(); 

        return self::$db;
    }
}


function getSomething()
{
    $conn = DB_Instance::getDB();
    .
    .
    .
}

Some examples/references

http://tutorialpedia.org/tutorials/Singleton+pattern+in+PHP.html http://www.ricocheting.com/static/code/php/mysql-v3/Database.singleton.phps http://netlinxinc.com/netlinx-blog/53-php/7-applying-the-singleton-pattern-to-database-connections-in-php.html

Upvotes: 2

Related Questions