'Max Connections' already in use

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
AlexB
Forum Newbie
Posts: 6
Joined: Mon Apr 18, 2005 5:36 am
Location: Peterborough, UK

'Max Connections' already in use

Post 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?
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post 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
AlexB
Forum Newbie
Posts: 6
Joined: Mon Apr 18, 2005 5:36 am
Location: Peterborough, UK

Post by AlexB »

Is it too many page views (connections), or too many MySQL Queries? Bad SQL? or a mix of all three?
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post by malcolmboston »

too many connections to MySQL...as your error message implies :roll:
traherom
Forum Newbie
Posts: 13
Joined: Tue Mar 15, 2005 6:43 am

Post 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.
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post 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]
AlexB
Forum Newbie
Posts: 6
Joined: Mon Apr 18, 2005 5:36 am
Location: Peterborough, UK

Post by AlexB »

Cheers, I'll look into changing that sharpish!
Post Reply