Error: Too many connections in ...

[moleproductions]

Hi,

I've got a mySQL database on a UNIX server and I'm using PHP to talk to it.

Every now and then the entire site does down and I get the message on screen and in the error log which says "PHP Warning: mysql_connect(): Too many connections in /home/public_html/index.php on line 14"

It's happened twice now (for about 2 or 3 hours each time) and then goes back to normal.

Any ideas?
Is it the server or the script?
Usage is currently approx 50 visitors per day.

Thanks.

 

[Wishdiak]

Every now and then the entire site does down

Does this mean that the box hosting the site goes down, and you get the error when it boots back up?

Wishdiak

http://www.wishdiak.com

 

[moleproductions]

No - the rest of the site is fine. In fact, I've just had this message back from the server technical support so it seems that it's been solved. If anyone else gets this problem it would be worth asking technical support to do this:

"There was too many requsets for mysql server to handle, so the server load became little bit high. And I reconfigured and restarted the mysql server"

Thanks for your help though.

 

[Wishdiak]

moleproductions,

Glad to hear that the problem's fixed.

Cheers,

Wishdiak

http://www.wishdiak.com

 

[mattlacey]

We have had this in the past when someone had used a persistent database connect rather than a standard database connection.

 

[moleproductions]

Mat,

Not sure what you mean by this? Was this an error in the script or an error in the database set-up/server?

If I'm talking to the person who wrote that script, what should I get them to check? I'm not a PHP programmer!

Many thanks

 

[ingresman]

Depending on how you run PHP (either CGI or apace module) you may be able to have persistent connections.
Actually getting a connection to a database is probably the single most expensive thing you can do (except for awful queries), so PHP allows you to hold open connections and let them be reused by any connection that comes along. It will be alot quicker but it can give problems if administed correctly.
If the code says mysql_connect your not using persistent, if it says mysql_pconnect you might be (I dont know if it would error or just revert back to a standard mysql_connect)

 

[DRJ478]

A note on that:
We have four machines running the web servers, a proxy front end distributes the load. Some parts on the site have PHP, most do not. Most PHP scripts use database servers.
mysql_pconnect() has a persistent connection for the life of the child process (in our case Apache). In a setup like ours persistent connections were prone to actually create the same scenario you described.
The likelyhood for a child process on any of the 4 servers to hit a PHP script that would reuse the persistent connection was just too low, so we changed to using mysql_connect() which eliminated all possible problems. In our setup (4 quad processor solaris boxes) mysql_connect() has practically no determinable disadvantage over persistent connections. As said, persistent connections clogged up the database servers.

 

[ingresman]

That's interesting. Do you connect to the db using different usernames ?. The PHP manual does state the creditionals must be the same (this is the same in CICS, MTS, Tuxedo etc) and so the nice security stuff you get for free you have to replicate in the application.

 

[DRJ478]

We have a lot of distributed computing solutions. Departments write their own PHP scripts. They are assigned specific usernames and passwords that are restricted to the databases they own. Data is sensitive and therefore each department is isolated as to access.
So, in other words, there is no way around having each application authenticate to the database servers using distinct usernames and passwords. BTW, all connections are restricted to be originating from the core servers. There is no outside access to the MySQL servers.

 

[ingresman]

Yep see what your saying. A lot of apps do authentication them self (from the db etc) and handle all the security themselfs.
Mysql does seem quite quick to connect but the likes of Oracle and Ingres can take a while as they sort out permissions character sets etc but they were desinged for fat client 2-tier apps which stay connected all day rather than web apps in and out like fiddlers elbow !