WeBWorK Main Forum

Error - too many connections

Error - too many connections

by Jason Aubrey -
Number of replies: 14
Hi All,

We (and our students) are experiencing the error below, apparently due to high demand. However, demand actually doesn't seem that high - I got the error just now, but counted about 50 active users.

Any ideas about the cause and/or possible solutions?

Thanks a lot,
Jason

(Some Specs: WW 2.2.1/mysql 3.23/freebsd)

Warning messages

Error messages

error instantiating DB driver WeBWorK::DB::Driver::SQL for table problem_user: DBI connect('webwork','webworkRead',...) failed: Too many connections at /usr/www/webwork/lib/WeBWorK/DB/Driver/SQL.pm line 62
at /usr/www/webwork/lib/WeBWorK.pm line 212

Call stack

The information below can help locate the source of the problem.

  • in Carp::croak called at line 221 of /usr/www/webwork/lib/WeBWorK/DB.pm
  • in WeBWorK::DB::new called at line 212 of /usr/www/webwork/lib/WeBWorK.pm

In reply to Jason Aubrey

Re: Error - too many connections

by Rich Winkel -
Let me add to this, I'm the sysadmin. The environment is:
Pentium 3 xeon with 512mb running freebsd 4.11, mysql 3.23 and WW 2.2.1. my.cnf reads:

set-variable = max_connections=200
innodb_data_file_path = ibdata1:10M:autoextend
set-variable = innodb_buffer_pool_size=256M
set-variable = innodb_additional_mem_pool_size=10M
set-variable = innodb_log_file_size=20M
set-variable = innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1

The swapping was off the charts, and the load occassionally reached peaks of over 30!

I've read about the wait_timeout variable but it's unclear to me what impact this would have on an existing session.

Please help, we have another test deadline coming up and students were understandably exasperated with performance.

In reply to Rich Winkel

Re: Error - too many connections

by William Wheeler -
Dear Rich,

Sometimes this problem arises because the "Refresh"
function on some operating systems and browser combinations
is a repeating key, so that a student who accidentally holds
down the F5 key (e.g., by resting a textbook on the keyboard)
can generate so many requests (6-10 per second) that the server
and Apache can't handle the load. In such cases, RAM is quickly
exhausted and swapping begins, which is untenable.

When using Apache 1, the solution involves using
Apache::SizeLimit and httpd.conf to limit
the size and number of Apache children.

Then WeBWorK can survive, but users will experience
a brief period of reduced service until the unintended
denial of service attack ends.

Your server (Pentium 3 Xeon, 512 MB RAM) may be a bit weak for a WeBWorK server.
We have 5-8 GB RAM on ours here at IU Bloomington.

Sincerely,

Bill Wheeler
In reply to William Wheeler

Re: Error - too many connections

by Davide Cervone -
There is a JavaScript file in webwork2/htdocs/js called limit-reloads.js that should help with the autorepeating F5 problem. There are comments in the file indicating how to use it.

The script traps the F5 keypresses and gives a warning after 5 have been seen. Other than that, it ignores all F5 key presses and only reloads on a key release (avoiding the autofiring F5).

Davide
In reply to Jason Aubrey

Re: Error - too many connections

by Sarunas Burdulis -
Hopefully someone with FreeBSD experience will chime in.

How did you count active users? Wheather the demand is "high" really depends on available system resources. How many Apache processes are there? How much memory are they using? How many MySQL connections/threads? (Perhaps one active user may be creating more than one connection.) If the system is swapping heavily, active connections will be tied up for much longer. What if you reduce the max number of Apache processes? --- Users may have to wait longer for webwork to respond, but at least their web page submission will go through, instead of running out of available MySQL resources. Sorry for the very genereic talk. I have found it useful to run ab (apache benchmark) and super-smack (MySQL load tester) to test the response of Apache+MySQL. That may help you to find out the limits for a given system on how many apache and MySQL processes can it support without going into swapping.

Again I have no FreeBSD experience, but 512MB of RAM looks on a low side to me. Your best solution here might be adding at least another 512MB.

Sarunas

In reply to Sarunas Burdulis

Re: Error - too many connections

by Jason Aubrey -
Hi Sarunas,

Thanks for your reply and helpful suggestions. I counted active users in the courses by just listing all users in the Classlist Editor and then counting the users listed as "active".

Thanks again,
Jason
In reply to Sarunas Burdulis

Re: Error - too many connections

by Thomas Hagedorn -

We ran into similar problems last spring running Webwork 2.4.1 on a Red Hat Linux system (not sure what version) with 2gB. We have about 500 users overall, but not many at any given time. There appeared to be a memory leak somewhere in the system and the system would use up all memory after running for a few days. In the end, we are doing automatic restarts of Apache/Webwork every 12 hours (it takes about 10 seconds for the system to go down and come back up). We haven't had any problems since.

-Tom
In reply to Thomas Hagedorn

Re: Error - too many connections

by Marie-Claude Bonneau -

We also have a memory leak in our system, and we are also automatically restarting Apache/Webwork every 24 hours. It also solved our problem. We are running WebWork 2.2 on a Linux SuSE 102.

I always meant to check a better way of dealing with this memory leak. Is there a way to fix this memory leak?

Marie-Claude

In reply to Marie-Claude Bonneau

Re: Error - too many connections

by Danny Glin -
In regard to the apache memory leak, you can start by looking at the following thread.
http://wwrk.maa.org/moodle/mod/forum/discuss.php?d=129#p6963

We are currently running Webwork under Scientific Linux 5.1 (RHEL 5.1) with 4GB of RAM and apache 2.2, and are seeing the same memory leaks. Here's a bit more detail on how we've dealt with managing the amount of RAM used by apache.
First, here are our settings from httpd.conf. I'll talk about how they were arrived at afterward.
<IfModule prefork.c>
StartServers 8
MinSpareServers 5
MaxSpareServers 10
ServerLimit 15
MaxClients 15
MaxRequestsPerChild 100
</IfModule>

The big one is MaxRequestsPerChild. The default value is somewhere in the 4000-10000 range. This is the number of requests a httpd process serves before dying (and being respawned if necessary). Something in webwork is leaking memory, which causes httpd processes to grow quite quickly. You can monitor the size of your httpd processes using the 'top' or 'ps aux' commands in linux (the relevant column being 'RES' or 'RSS' respectively). On our servers, httpd processes start at around 40Mb, and quickly grew to 300Mb or more as they serve webwork requests. When we reduced MaxRequestsPerChild to 100, we found that when processes being killed after 100 requests, they tended to be in the 100-110Mb range.
Based on this, we took the amount of RAM we would like apache to use (2-3Gb, leaving some for the system and mysql), and divided it by the 110Mb that each process tended to use, giving us somewhere in the 15-25 range for MaxClients (this is the maximum amount of httpd processes that the system will spawn).
Using these settings we've kept apache from swapping. The small number of processes may lead to slower response times for requests, but so far our system has been stable, and students haven't complained.
I can't comment on the source of the memory leak, nor on whether this will solve your mysql connection issues, but getting these settings right should keep apache in check.
In reply to Danny Glin

Re: Error - too many connections

by Davide Cervone -
There definitely was a memory leak associated with the MathObjects library, which I eliminated last summer. Unfortunately, that patch did not make it into a public release until version 2.4.5 at the end of this summer, so anyone running older versions will have a significant memory leak associated with that. Since MathObjects underly both the fun_cmp and num_cmp answer checkers, you will experience this leak even if you aren't using newer problems that are written using MathObjects directly.

I've been using the updated version since last year, and my processes don't grow larger than about 120mb in general over a full day of activity (I do restart apache nightly for other reasons).

Those of you running older versions of webwork could update just the pg directory to rel-2-4-patches to be able to take advantage of the newer code. You can do that without updating the rest of webwork, as the two components are pretty independent. (Of course, it would be wise to make a backup of your pg directory first, just in case you want to go back.)

Hope that helps.

Davide
In reply to Davide Cervone

Re: Error - too many connections

by Jason Aubrey -
Hi Davide,

Thanks for your replies. Just to clarify - you do think it's ok to update pg without updating WW? (I.e. nothing will break?). We'll be upgrading WW after our server is upgraded, but right the main thing preventing us from upgrading WW is that we have mysql 3.23. (Sam said that we would need at least mysql 4 for a WW upgrade). So, given that, do you think it's ok to update pg?

Thanks,
Jason
In reply to Jason Aubrey

Re: Error - too many connections

by Davide Cervone -
WEll, WW2.2.1 is pretty old, so I set up a test installation to try it out. You do need to make one change to pg/macros/dangerousMacros.pl.

Around line 133 change 

    $macrosPath               = eval('$main::envir{pgDirectories}{macrosPath}');
to 
    $macrosPath               = eval('$main::envir{macrosPath}');
(that is, remove the {pgDirectories} part). This has to do with the interface between the WeBWorK code and the PG code, so this is the one place where there is some cross over between the two.

Alternatively, you could edit webwork2/lib/WeBWorK/PG.pm to include passing the pgDirectories variable, but I didn't test that. I did take a quick look at the differences between 2.2.1 and 2.4.5 and while there are a couple of other new symbols that have been added since 2.2.1, they shouldn't be anything that is a problem for you.

So it looks to me like you should be able to update just PG, provided you make the one change I indicate above. Of course, I recommend backing up your pg directory first. For example, cd to the parent directory of your pg directory and do 

    tar vfcz pg-2-2-1.tgz pg
to make a backup file. If you need to recover it, remove the new pg directory and then 
  tar vfxz pg-2-2-1.tgz

Hope that helps.

Davide

In reply to Danny Glin

Re: Error - too many connections

by Arnold Pizer -
Here is the general guidance we give in the Ubuntu installation instructions (http://webwork.maa.org/wiki/Installation_Manual_for_2.4_on_Ubuntu_8.04#Configuring_Apache):

Next we will make a few changes to Apache's default configuration. First we backup the configuration file

# cd /etc/apache2/
# cp apache2.conf apache2.conf.bak1
# gedit apache2.conf

Search for the line

Timeout 300

and replace it by

Timeout 1200

Next search for the lines

MaxClients 150
MaxRequestsPerChild 0

Which occur under <IfModule mpm_prefork_module> and replace them by

# For WeBWorK a rough rule of thumb is 20 MaxClients per 1 GB of memory
MaxClients 20
MaxRequestsPerChild 100

where you should set MaxClients depending on the amount of memory your server has.

Note that the default MaxRequestsPerChild 0 means children never die. We would certainly appreciate guidance on the "For WeBWorK a rough rule of thumb is 20 MaxClients per 1 GB of memory".

Arnie
In reply to Thomas Hagedorn

Re: Error - too many connections

by William Wheeler -
I think one origin of the memory leak may be that a persistent perl interpreter
does not do garbage collection on its parse tree, only on its data structures.
Processing problems requires parsing their perl code, so the parse tree
would grow monotonically.

For Apache 1, one can use the perl module Apache::SizeLimit to kill automatically
Apache child processes when they reach a prescribed size. Then one doesn't have
to restart Apache in order to reclaim leaked memory.

Sincerely,

Bill Wheeler, Indiana University Bloomington
In reply to Jason Aubrey

Re: Error - too many connections

by Michael Gage -

(Some Specs: WW 2.2.1/mysql 3.23/freebsd)

Warning messages

Error messages

error instantiating DB driver WeBWorK::DB::Driver::SQL for table problem_user: DBI connect('webwork','webworkRead',...) failed: Too many connections at /usr/www/webwork/lib/WeBWorK/DB/Driver/SQL.pm line 62
at /usr/www/webwork/lib/WeBWorK.pm line 212
Call stack
 I'm wondering if the "users" referred to here are connections to the mysql database? the sequence of posts here: http://wwrk.maa.org/moodle/mod/forum/discuss.php?d=89#309 is related Hope this helps.