Page 1 of 1

'Max Connections' already in use

Posted: Thu Apr 21, 2005 5:36 am
by AlexB
Hello!
My first post in a code forum, be gentle. ;)

I've written a bit of forum software, currently in use here and here.

When the sites began, it worked fine, really quick, simple to use etc, but now the sites have grown the forum is running really slow. The first link is the worst.

Every now and then we get HTTP 500 errors, but more oftern than not, we get 'Max Connections' limit already in use / Lost connection to MySQL server during query.

Now, I'm not sure I understand the problem. The way it says Connections makes me think that the sites are just too big, as we only connect once per page view? - Or is it the way I do the MySQL queries which is killing it?

We connect at the top of each PHP script with the following code:

Code: Select all

$mysql["server"]         = '127.0.0.1';             // Mysql Server (i.e. localhost)
$mysql["database"]       = 'xx';             // Mysql database
$mysql["username"]       = 'xx';             // Mysql username
$mysql["password"]       = 'xx';             // Mysql password
	

// connect to MySQL
$conn = mysql_connect ($mysql["server"], $mysql["username"], $mysql["password"]);
        mysql_select_db($mysql["database"],$conn);
and use this little function to do the queries:

Code: Select all

function MyQ($sqlString)
        {
                echo('<!--Excecuting Query: '.$sqlString.'--><img src="reddot.gif" border="0">');
                if($sqlString)
                {
                        $result = mysql_query($sqlString)
                                        or die('<b>MySQL Error</b><br>Function: mysqlQuery();<br>Error Description: '.mysql_error().'<br>Sql ran: '.$sqlString.'<br>');
                        return $result;
                }
                else
                {
                        echo('mysqlQuery() called with no SQL defined');
                        exit;
                }
        }
and we call that with this sort of code.

Code: Select all

$RS = MyQ("select * from table");
  while($Row = mysql_fetch_array($RS))
  {
      ...
  }
  unset($Row);
  mysql_free_result($RS);

Sorry if this is the worst post ever.
Any advice / suggestions?

Posted: Thu Apr 21, 2005 7:58 am
by malcolmboston
the max connections issue is a MySQL related problem, not PHP, and to be honest its not really a problem, you are exceeding the total simultaneous connections to MySQL as defined in the MySQL config

If you are hosted by someone, ask the company to heighten it abit

Also you could try and slimline your code to use as little MySQL access requests as possible, this will speed up your site and stop those pesky 500 errors

Hope that helps

Posted: Thu Apr 21, 2005 8:29 am
by AlexB
Is it too many page views (connections), or too many MySQL Queries? Bad SQL? or a mix of all three?

Posted: Thu Apr 21, 2005 9:21 am
by malcolmboston
too many connections to MySQL...as your error message implies :roll:

Posted: Thu Apr 21, 2005 12:35 pm
by traherom
AlexB wrote:Is it too many page views (connections), or too many MySQL Queries? Bad SQL? or a mix of all three?
Too many page views, so the total number of connections to the MySQL server is exceeding the limit set in the msyql config file.

Posted: Thu Apr 21, 2005 1:44 pm
by magicrobotmonkey
just a security note, its probably a bad idea to print queries to the screen. Letting everyone know your db structure makes it easier for someone to attack it. On this page: http://no-rice.com/forum/index.php?p=vf&fid=26 I found this:

Code: Select all

<!-- forum code -->
                                                <!-- SQL RUN: SELECT count(id) FROM &#1111;omitted]

Posted: Fri Apr 22, 2005 5:24 am
by AlexB
Cheers, I'll look into changing that sharpish!