too many connections

[ProblemHelpPlease]

I am having a problem with "too many connections" on my mysql server.I want to make sure that the structure of the code is not creating the problem.I have a file with all my connection settings in it that contains the database connection

$con = mysql_connect("$host","$usr","$pwd");if (!$con) { die('Could not connect: ' . mysql_error()); }mysql_select_db("$db", $con);

but does not close the connection in this file.This file is only ever used as a require() at the top of each new file that needs a database connection and then I run a mysql_close($con) at the end of each file that I require() at the top.Should this close all connections or are connections being left open. I have checked show processlist after navigating around my site and there are no open connections.Thanks in advance

[Synook]

Connections are always closed when the script ends, so that shouldn't be a problem. Does your site get a lot of traffic?

[birbal]

after executing your page mysql connection close automatically. but you can also close your connection after your database work evreytime. both are same more or less. i think your host has some limitation about mysql connection. and you have more user who are using more connection than last limit.

[ProblemHelpPlease]

The limit is set to 25 connections at the same time but I don't think the site gets enough traffic to reach this, especially looking at the access logs for the same period of time that the error occurs. The site traffic is around 12,000 visitors per month. No scripts are running continously, and no scripts take more than a second to complete, so all connections should be closed within a second of the connection opening. The probelm occurs about once a day for anything between a few seconds and a few minutes.I am unable to use show processlist to view activity when the error occurs as I have yet to be viewing the website in the short period when the problem occurs. Does anyone know if it is possible to capture show processlist automatically and store it somehow?

[boen_robot]

If you have direct access to the MySQL server instance (via MySQL Workbench or something like that; more than phpMyAdmin), you can configure log file writing, and then review the logs to see how many connections are there when, and to which users.If you don't, you'll have to speak to your host if they have some kind of MySQL logging capabilities for you.If you're running PHP as an Apache module or FCGI, you can use persistant connections to connect to the server. PHP will then reuse a MySQL connection across HTTP requests, thereby nearly eliminating the problem. Note that if your PHP scripts are running as CGI, you won't notice any difference, and the database will still be unaccessible at those times.

[End User]

I doubt that explicitly closing the connection is going to make much of a difference. You can try it but I doubt that will "fix" this problem.Most default configurations of mySQL set the maximum number of (simultaneous) connections to 100. To increase this you'll need to edit the 'my.cnf' file. On some platforms the file may have a different name, but that's usually it. In 'my.cnf', look for this line (mySQL 4.x and 5.x):

max_connections = 100

Change it to:

max_connections = 200

Restart mySQL after editing this value. You could use a higher number (i.e. 250), but that's rarely needed unless you have major, major traffic or some extremely convoluted SQL going on. However...before increasing the connection limit, you ought to try and find out why you're reaching the connection limit. Oftentimes a mySQL server that's hitting the connection limit has some kind of performance issue that could be corrected instead.EDIT: I should add that there is an additional situation where the increase is needed, even though your site doesn't see heavy traffic. If you host a number of domains on a single server, the traffic across all of the domains may exceed the shared connection limit, event though no single domain does. I have one server with ~100 active domains and although only a few see significant traffic if there's a spike in usage across a bunch of the domains, sometimes it will hit the connection limit (or it did until I increased the limit).