Installation

Database Limits - where in conf?

Database Limits - where in conf?

by Wesley Burr -
Number of replies: 10

Sorry for the dumb question, but I don't see where to set the database limits in the .conf files. Recently upgraded to 2.18, and migrated all our old courses. Things have been running fine, but today after the students hammered th server, we got:

DBI connect('database=webwork','webworkWrite',...) failed: Too many connections at /opt/webwork/webwork2/lib/WeBWorK/Utils/CourseManagement.pm line 92.

and the interface wasn't loading for users. I poked at the usual spots (localOverrides and site) and didn't see anywhere to set the number of database connections? I'm serving over hypnotoad, with 1 client, 250 workers and 40 spares. The server has 128GB of RAM, so I figured this was a reasonable amount. Is this low? Or is this related at all? 

Thanks for your brain power, everyone. I appreciate advice.


In reply to Wesley Burr

Re: Database Limits - where in conf?

by Wesley Burr -
Note: just checked, and MySQL has been running with 151 connections, the default max, for 4 years. I just bumped that up in case that was the issue, but anyone who's run into this before, clarification would be very welcome.
In reply to Wesley Burr

Re: Database Limits - where in conf?

by Andrew Parker -

I have also encountered this issue recently (also after upgrading to v2.18)

To answer your question, maxConnections is set in your MySQL configs and not in WeBWorK itself. Your response seems to indicate you figured that out already...

I would hypothesize that perhaps the latest version of WeBWorK has introduced code that is failing to close DB connections -- causing this issue? 


In reply to Andrew Parker

Re: Database Limits - where in conf?

by Wesley Burr -
Really good to know someone else experienced this, thanks, Andrew.
In reply to Wesley Burr

Re: Database Limits - where in conf?

by Nathan Wallach -

I think it might be useful to check on the mySQL side what it shows in terms of open connections.
It could be that Mojolicious is leaving many connections open ready for reuse, in which case the limit on the DB side should be increased.
The setting to consider changing in the DB settings is max_connections.

  • show status where variable_name = 'threads_connected';
  • show processlist;
  • select id, user, host, db, command, time, state, info from information_schema.processlist;
Refs:

In reply to Andrew Parker

Re: Database Limits - where in conf?

by Glenn Rice -

After looking at this closely, I don't think that anything has actually significantly changed on this.  WeBWorK has always used cached database connections that are never really closed (until they go stale).  When a network request is received by a worker process it uses its cached connection.  If that connection is stale it creates a new one, and the stale connection is closed.  Each worker will have its own cached connection.  However, note that the library browser uses a separate connection which is also cached.  So there will be two database connections per worker.  Each of these connections will appear when a worker makes a connection to the database for the first time.

That should be all the same as it was with previous versions of webwork2 and mod_perl2.  Previously the above would have applied to each apache2 worker process.  Now it applies to each hypnotoad worker process.

One thing that has changed is the number of workers.  Previously with apache2 you probably used less workers. With hypnotoad you now typically need more workers than before. This is probably what is causing the problem.  So you will need to ensure that max_connections is set to at least twice the number of workers (plus some extras to account for stale worker turn around time).

Note that according to https://www.ionos.com/digitalguide/websites/web-development/solve-mysqlmariadb-too-many-connections-error/, most linux systems should be able to support 500 to 1000 connections without difficulty (although the mysql documentation it links doesn't seem to actually say anything about this?).

In reply to Glenn Rice

Re: Database Limits - where in conf?

by Wesley Burr -

This makes sense. I was running apache for the older version of WW, and with the update to 2.18 decided to switch to hypnotoad (which is lovely). I cranked the number of workers there, but didn't scale the database limits. 

I'll bump this to 500 and then keep an eye on it through the term. And I'll make a proposed edit to the 2.18 install instructions to add this suggestion so someone else doesn't get caught out in the future. 

In reply to Glenn Rice

Re: Database Limits - where in conf?

by Glenn Rice -
I am seeing something now that I missed before. What I said was accurate for the usual database connection obtained for regular course access, it is not for the library browser connections. The call there is not a connect_cached call. It is a straight connect call. This is consistent with what I am seeing in the connection count. Each time that I access the library browser I see the connection count go up one. What is happening is that each time the library browser is accessed a completely new connection is created. All previous connections remain active and idle (it is never accessed again) until the worker that made that connection is recycled since they are never closed. I suspect that what is happening is that with previous versions of webwork (using mod_perl1 and with the pg memory leak in effect) workers were recycled frequently. So all of these idle library browser connections were not kept around that much. However, now that the pg memory leak is not an issue the hypnotoad workers are probably recycled much less frequently. As such, these idle library browser database connections remain alive and build up to a much greater number.

This all assumes that you have a significant number of faculty that are accessing the library browser. If that is not the case, then this is not relevant.
In reply to Glenn Rice

Re: Database Limits - where in conf?

by Wesley Burr -
Jumping back into this thread, Glenn. We just had a gateway/quiz running with 110 simultaneous users, plus miscellaneous other courses doing assignments. And hit the database limits with 500. A faculty member did a lot of library work this morning fine-tuning his midterm.

So something about the library database accesses is lingering for longer than it should, and that plus the standard users was way more connections than we'd ever seen in the past. I went up to 1500 simultaneous, triple the 500, and it seems to be holding steady now. Just thought I'd throw that into the hive mind so maybe some thoughts follow up on it later in the future.
In reply to Wesley Burr

Re: Database Limits - where in conf?

by Glenn Rice -

Have you pulled recent hotfixes to the webwork2 code from Github? The library browser database connection was fixed in https://github.com/openwebwork/webwork2/pull/2207.

In reply to Glenn Rice

Re: Database Limits - where in conf?

by Wesley Burr -

I have not! I'll do that this week. Thanks for the reminder. I don't tend to track the repo through the semester - too many other things pulling my attention away.