Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

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

[download]
Is there a way to allow more connections?

Thank you much!!

John

Replies are listed 'Best First'.
Re: Help, please - MySQL database error - too many connections
by Joost (Canon) on Sep 25, 2007 at 13:36 UTC
[reply]
Re: Help, please - MySQL database error - too many connections
by andreas1234567 (Vicar) on Sep 25, 2007 at 13:39 UTC
    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.
    --
    Andreas
[reply]
      Is it safe to set this at 30500 max_connections?
[reply]
        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).
[reply]
Re: Help, please - MySQL database error - too many connections
by CountZero (Bishop) on Sep 25, 2007 at 19:57 UTC
    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

[reply]

      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]

Back to Seekers of Perl Wisdom