I am trying to figure out why we sometimes get "Too many connections" in /var/log/apache2/error-webwork.log with a maximum of 45 students that opens a problem set at about the same time.
When studying the variable Threads_connected in MySQL when the students clicks on the problem set in their web browsers it seems like WeBWorK opens one connection to MySQL for each problem in the problem set and then closes those connections with about ten seconds. Is that correct?
Example: If we have 45 students that opens a problem set containing 6 problems within a ten second interval then the MySQL database needs to be able to handle 45 * 6 = 270 connections at the same time.
Is it correct that WeBWorK closes all those connections except one for each student within about ten seconds after a problem set has been opened?
Thanks
When a student clicks on the problem set I think all of the 6 problems for the student's problem set are randomly selected from the problem library which is done by opening one database connection for each problem, i.e., 6 database connections in our case, and then it takes 10-30 seconds for each of those database connections to close which means the MySQL database needs to be able to handle [nr of students] * [nr of problems in the problem set] connections at the same time if all students logs in at about the same time.
If above is correct, would it be possible to configure WeBWorK to use the same database connection for selecting all of the 6 problems instead of using one separate database connection for each problem?
If above is correct, would it be possible to configure WeBWorK to use the same database connection for selecting all of the 6 problems instead of using one separate database connection for each problem?
Do you have MaxConnectionsPerChild set to 1 in your apache configuration? If so, this could explain the large number of mysql connections.
On my server it looks like the database connections are not being closed, but it also looks like a given apache process reuses the same connection. Based on this I suspect that the DB server ends up seeing roughly one connection per apache process.
If you kill your apache processes after each request, then when a new process starts, it initiates a new connection to the database. I'm guessing that the 10-30 seconds is the time it takes the DB server to clean up orphaned connections from the killed apache processes, so you're looking at basically one connection per apache request during peak load times.
Thanks a lot for your reply!
It looks like I have MaxConnectionsPerChild set to 0. This is how my /etc/apache2/mods-enabled/mpm_prefork.conf looks like:
<IfModule mpm_prefork_module>
StartServers 5
MinSpareServers 5
MaxSpareServers 10
MaxRequestWorkers 150
MaxConnectionsPerChild 0
</IfModule>
How can you see that your Apache process reuses the same connection?
Do you also see a lot of "Aborted connection [nr] to db: 'webwork' user: 'webworkWrite' host: 'localhost' (Got an error reading communication packets)" in your /var/log/mysql/error.log? I guess that might be those orphaned connections that takes some time for the MySQL server to close.
It looks like I have MaxConnectionsPerChild set to 0. This is how my /etc/apache2/mods-enabled/mpm_prefork.conf looks like:
<IfModule mpm_prefork_module>
StartServers 5
MinSpareServers 5
MaxSpareServers 10
MaxRequestWorkers 150
MaxConnectionsPerChild 0
</IfModule>
How can you see that your Apache process reuses the same connection?
Do you also see a lot of "Aborted connection [nr] to db: 'webwork' user: 'webworkWrite' host: 'localhost' (Got an error reading communication packets)" in your /var/log/mysql/error.log? I guess that might be those orphaned connections that takes some time for the MySQL server to close.
Hi Alex,
You can find recommended setting at http://webwork.maa.org/wiki/Installation_Manual_for_2.13_on_Ubuntu_16.04#Configuring_Apache
I don't see any aborted connections in my log files, but that could just be because they're not being logged. Also, my system is running on CentOS. If you're running on Ubuntu, then perhaps other people here using Ubuntu can chime in with whether they have similar log entries.
I'm actually just guessing that each apache process reuses the same connection. You can log in to the mysql console and run 'show connections' to see which processes currently have open connections. When I do this, I see that even idle apache processes stay connected to the database.
Based on your apache settings I have a revised theory similar to what I originally suggested:
With MaxSpareServers set to 10, apache will only keep 10 idle processes running, and kill any additional idle processes. Since MaxRequestWorkers is 150, apache is allowed to start up to 150 processes to serve concurrent requests. This means that at the start of a quiz, apache has to start a large number of processes to serve all of the requests. As soon as these requests are complete, it kills off all but 10 of these, and possibly does not properly close the database connection, which means that mysql has to wait for them to time out.
It's also possible that there is something in your mysql (or possibly apache) configuration which is causing the server to wait some amount of time before closing these connections, but I'm not sure where you would start looking for that.
What OS and version are you using?