database access performance

[digitalenviron]

Hello, I have a question regarding a php/mysql/fc7 application architecture for improving database access performance. The application I am maintaining has high usage (about 25k unique visitors per day) and my purpose is to scale it out. I've set it up with remote database access and have run into intermittent problems with database connections and open files limitation.

 

My question is specifically about the architecture of accessing the database via a class which establishes the connection link. The db object code is like this:

class dbd {
    var $db;								// Database type.
    var $dsn;								// Datasource.
    ...etc
  
    /* Constructor. */
  function dbd($dsn, $dbLink = NULL, $path = "") {   
  
  $this->conn = mysql_connect($this->db[4], $this->db[2], $this->db[3]);
                mysql_select_db($this->db[6]);
                if(mysql_errno()) {                 	
                	die("Database '".$this->db[6]."' connection failed."); 
                }
      if ($this->conn) {         	
        	return 1;
        }
        return 0;
    }
/* Destructor. */
    function __destruct() {
    mysql_close($this->conn);
    }
    
}

   

The db class is then accessed in many functions in the application, always this way:

$_DB =  new dbd(CFG_DSN);
$_DB->query(...);

 

What happens effectively is that for each page request, hundreds of db connections are opened and then closed. Logging mysql I see this happening about 200 times per page:

 

  nnn Connect    user@host on

nnn Init DB    my_db

nnn Quit     

 

But in fact the page may only perform 10-20 queries, so a lot of open connections are wasteful.

 

This is no problem when running mysql locally, but when running mysql on a dedicated server, soon the connections start to fail under load ( I get client error code 2003, cannot connect. Also  I see in the webserver logs that often it is unable to open any more files). I've played with system open files limits (this is on fedora core 7) and also tried some hacks to retry the open when it fails, but really I am not sure that the db access code is soundly implemented in the application.

 

The problem that I need to address is:

 

1. is the db object implementation visibly wrong?

2. how can db access be performed more efficiently? What I'd like to see is that only one (or just a few) connection is open per session, destroyed at session end.

3. Is there a way to pool these connections so that if there is a burst of new sessions, the old ones can be destroyed automatically?

 

If someone has some quick pointers, please let me know. Otherwise if there is someone with concrete experience in this area, please contact me so we can work out a solution.

 

TIA

[nankoweap]

regarding database access specifically... i've settled on the singleton approach. this singleton provides application-wide information plus encapsulates a connection which is lazily created. the base class of the business objects that make up the modeling layer of the application ask the singleton for its instance as well as the connection.

 

here's a stripped down version of the singleton, AppInfo.php:

 

<?php

class AppInfo {
  private $mysqlHost = 'someHost';
  private $mysqlUser = 'someUser';
  private $mysqlPassword = 'somePass';
  private $mysqlDatabase = 'someDB';
  private $pageSize = 20;
  private $con;

  private function __construct() {}

  private function __clone() {}

  public static function getInstance() {
    if(self::$_instance === null) {
      self::$_instance = new self();
    }
    self::$_instance->count += 1;
    return self::$_instance;
  }

  public function getMysqlHost() { return $this->mysqlHost; }
  public function getMysqlUser() { return $this->mysqlUser; }
  public function getMysqlPassword() { return $this->mysqlPassword; }
  public function getMysqlDatabase() { return $this->mysqlDatabase; }
  public function getPageSize() { return $this->pageSize; }
  public function getConnection() {
    if (is_null($this->con)) {
      $this->con = new mysqli($this->getMysqlHost(), $this->getMysqlUser(), $this->getMysqlPassword(), $this->getMysqlDatabase());
      if ($this->con->connect_errno) {
        printf("Cannot connect to MySQL Server: Error Code: %s\n", $this->con->connect_error);
      }
    }
    return $this->con;
  }

  function __desctruct() {
    $this->con->close();
  }

} // AppInfo

?>

 

And here's the base business object class, BaseBo.php:

 

<?php

include_once('classes/AppInfo.php');

class BaseBo {

  protected $appInfo;
  protected $con;
    
  function BaseBo() {
    $this->appInfo = AppInfo::getInstance();
    $this->con = $this->appInfo->getConnection();
  }
  
}
?>

 

And to round it all out, here's a snippet from a business object class, UserManager.php:

 

<?php

include_once('classes/bean/User.php');
include_once('classes/bo/ReportManager.php');
include_once('classes/bo/BaseBo.php');

class UserManager extends BaseBo {

  function UserManager() {
    $this->BaseBo();
  }

  // bunch of user-related functions in here for logging in/out, registering, updating profiles...
}
?>

 

it's important to note that only business objects in the model access the database. anytime they need access to the connection available for servicing the current request, it's merely:

 

$this->con

 

the next code iteration will address and improve error/exception handling.

 

given my rudimentary understanding of php and its architecture, this approach provides an optimal connection solution - create it lazily and share the same connection across all objects that service a request.

 

regarding connection pooling... sorry. that's outside the realm of a php application given its inherent stateless architecture and no simple, effective way to create and share resources like connections and open files and across multiple requests.

 

jason

[digitalenviron]

Yes, this looks a lot like what I've been looking for. I'll start by using this and will see if I can get further. Obviously I'm inexperienced with OOP which is why I asked this way. Thank you very much for your help. Don't mind about connection pooling, that was something I was wondering about, not really important here.

 

Thanks again.

[nankoweap]

no need to go full-blown oop to get this to work. using the singleton as-is and changing the connection from "new msqli..." to "mysqli_connect..." would be a simple way to gain the advantages of a single, shared connection. in the pages that need it, just include the singleton class, get its instance and use the connection directly.

 

jason

[Acs]

You could also try to cache the page and serve that instead of connecting to mysql again or use something like memcache