Re: Help, please - MySQL database error

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.

CountZero

A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

Comment on Re: Help, please - MySQL database error - too many connections

Replies are listed 'Best First'.
Re^2: Help, please - MySQL database error - too many connections by fraktalisman (Hermit) on Nov 18, 2009 at 11:07 UTC
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. _{Fraktalisman / Ingo Steinke}	[reply]

Replies are listed 'Best First'.

Re^2: Help, please - MySQL database error - too many connections
by fraktalisman (Hermit) on Nov 18, 2009 at 11:07 UTC

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.

_{Fraktalisman / Ingo Steinke}

[reply]