Help, please - MySQL database error - too many connections

[Anonymous Monk]

Ok, I've never had a website kill MySQL, but it did:

Software error:
DBI connect('host=10.45.28.5;database=dbnamehere','dbuserhere',...) fa
+iled: Too many connections at /home/path/user/mod/sys/Sess.pm line 22

Is there a way to allow more connections?

Thank you much!!

John

[Joost]

Update your mysql server configuration to allow more connections (i.e. set/change the max_connections option)

[andreas1234567]

Too many connections:

The number of connections allowed is controlled by the max_connections system variable. Its default value is 100. If you need to support more connections, you should restart mysqld with a larger value for this variable.

[Anonymous Monk]

Is it safe to set this at 30500 max_connections?

[cowboy]

It depends on your system. I can't see anyone possibly needing 30500 connections though (or finding a server capable of handling that many either).

[CountZero]

I had the same problem too and adding more connections is definitely NOT the way to solve this.

What you need is Apache::DBI which will manage a pool of persistent connections for you. Now every new request will not start a new connection but will re-use an existing connection, which will cut down your number of connections to the maximum number of concurrent requests which access the database, rather than adding a new connection for each such request (and they will only disappear once the time-out value expires, meaning you will quickly run out of free connections).

Another solution is set the time-out value to a very short period of time (thus freeing more quickly your connections) but that has the drawback that you need to re-connect frequently and that will cost precious time.

[fraktalisman]

Now this really seems to be a complex and tricky issue with mySQL / database programming and configuration in general.
I never used to care a lot as long as I had to deal with smaller web applications on default shared hosting servers. Usually, anything left open would time or be automatically closed, and there was some "too many connections" very rarely, but also for out-of-the box software like bulletin boards and content management systems.

After installing XAMPP (lampp) on a local linux server and working with mySQL settings myself to optimize the system for more complex queries, I had to figure out the best way to set mysql query cache size, allowed number of maximum connections etc. Simply setting everything to very high values is definitely not the smartes way to do this. You have to think, watch your database design and queries, keep testing and watch the SQL status variables to find settings that match the project's requirements and hardware possibilities in the best possible way.