Max connections? Am I going wrong somewhere

[chris_stfc]

Hello All,

I'm looking for some advice with my connection script to my database.

In my content management system I have a page (initial_connect.php) that is included on all my pages in my content management system.

It contains two connections to different mysql databases via functions (but those databases sit on the same server but I can't have them together in one DB). One to get the initial data from a function called initial_connect and one to get all my news items called news_connect

<?php

/*****************************************************************
 - INITIAL CONNECTION
******************************************************************/

function initial_connect() {

$result = mysql_connect("localhost", "usernamehere", "passwordhere");
    
    if (!$result) {    return FALSE; }
    
    if (!mysql_select_db("1STdatabasename")) { return FALSE; }
    
    return $result;
}


/*****************************************************************
 - GET INDIVIDUAL SITE DETAILS FROM connections TABLE
******************************************************************/
initial_connect();

$SQL = "SELECT * FROM connection_details'";
$result = mysql_query($SQL) or die (mysql_error());

$siteDetails = mysql_fetch_array($result);
 
$sitename = $siteDetails['name'];
$googleCode = $siteDetails['google'];
//etc


/*****************************************************************
 - CONNECT TO THE NEWS DATABASE
******************************************************************/

function news_connect() {

$result = mysql_connect("localhost", "usernamehere", "passwordhere");
    
    if (!$result) {    return FALSE; }
    
    if (!mysql_select_db("2NDdatabasename")) { return FALSE; }
    
    return $result;
}
?>

Now, thoughout all my pages in my content management system I include initial_connect.php like so, and also open the connection to the news_connect() function and have about 25 pages in my CMS.

<?php

include ('initial_connect.php'); 

// Connect to news database
news_connect();

//Rest of code

But i'm getting several errors on my site saying I have Too many connections

Is there anything I can do, or change in my code to fix this, or make it work more efficiently?

Thanks

Chris

[Dan Grossman]

The most common cause of "too many connections" errors is unoptomized queries that take very long to resolve. As other people hitting your site generate new queries waiting on tables locked by the slow queries, the backlog builds up until you exceed your connection limit.

Turn on slow query logging with a low threshold to identify the problematic queries, and take a look at mytop which can show you what the server is doing in realtime.

[chris_stfc]

Cheers Dan,

I'll take a look at that, so there's nothing blatently obvious on the code that is a no-no then? i.e. opening those two connections on each page eveytime a page in my CMS is loaded?

[Dan Grossman]

If you think that may be the cause, then explicitly call mysql_close() on the first connection when you're done with it. I doubt the fraction of a second is an issue unless your max connections is set very low.

[j3ph]

At the end of your code just put mysql_close() like Dan said. If that doesn't fix the issue you might make your mysql connection variable global and reference it in every mysql_query(). That way you are guaranteed to only use that connection.