## Forum archive 2000-2006

### John Jones - mysql connections exhausted problem

by Arnold Pizer -
Number of replies: 0
 mysql connections exhausted problem topic started 5/27/2004; 3:27:39 PMlast post 6/8/2004; 10:13:03 PM
 John Jones - mysql connections exhausted problem  5/27/2004; 3:27:39 PM (reads: 3666, responses: 3) Hi, I was demonstrating webwork2 for some instructors and got "DBI - too many connection" errors in the middle of the demonstration.  I was accessing only a few mysql courses.  Aside from being embarrassing, it is a little worrying.  Almost no one is using the system yet and the mysql part is overloaded.  I restarted the web server and could get back in, but that isn't a long term solution. I checked mysql (with "show variables"); its connection limit is 100.  I then learned another mysql command "show processlist".  The number of connections started growing again.  There are many of the same type (e.g., webworkRead) to the same database.  I know that webwork leaves database connections open in the hope of reusing them.  Presumably, as a user accesses multiple pages, he gets a different mod_perl thread, each of which opens a connection and leaves it there for future requests. So, if I were to reset the number of connections mysql will allow, the right number should be at least (number of courses + epsilon) * 2 * (number of modperl instances) The epsilon is for other tables (ProblemLibrary, maybe a dvipng cache), and the 2 in the middle is for read and write connections.  The number of modperl instances is presumable MaxClients in http.conf?  Does this sound right? John<| Post or View Comments |>

 John Jones - Re: mysql connections exhausted problem  6/1/2004; 3:58:10 PM (reads: 3944, responses: 0) I have done more reading of the mysql manual.  I understand more, but I am also more confused.  If there are any mysql and/or unix experts out there, it would be great if they could comment on this problem. I restarted mysql with max_connections set much higher (5000).  For the moment, that "fixed" the problem.  For a fall semester, it would have to be bigger for us.  I can change it, but it looks like I am not out of the woods yet. The mysql manual says that I should also increase the size of the table_cache to "max_connections * N, where N is the maximum number of tables in a join".  Unfortunately, I don't know the value of N in spite of the explanation.  For WW, what should this be? I would be happy to go big on the size of the table_cache, but there are more warnings from the mysql manual.  Every "open table" might need 2 open file descriptors, and there is a danger of running out of open file descriptors from the system.  I think my system currently has a limit of 1024 per user (or maybe it is per process). John<| Post or View Comments |>