Repeated flood of requests causes all database connections to be used up?

Repeated flood of requests causes all database connections to be used up?

by Mathew Gancarz -
Number of replies: 6
Picture of Core developers

Hi all, I had posted about this issue before in the quiz forum, but it seems to be unrelated to the specific activity as I have seen it happen with a quiz attempt, a course view and a forum post. For reference, this is my previous thread on it: https://moodle.org/mod/forum/discuss.php?d=268753

The symptom is that all of a sudden we get notifications: "WARNING: Database connection error". When I investigate the apache/php logs, I see them filled with about 200-250 errors. in the most recent one it was about 200 messages of the below. I've removed the domain name and replaced it with <snip> and also removed the IP address.

[Fri Mar 27 10:23:19 2015] [error] [client x.x.x.x] PHP Warning:  mysqli::mysqli() [<a href='mysqli.mysqli'>mysqli.mysqli</a>]: (00000/1040): Too many connections in /var/web/<snip>/lib/dml/mysqli_native_moodle_database.php on line 384, referer: http://<snip>/mod/quiz/attempt.php?attempt=134243&page=16

[Fri Mar 27 10:23:47 2015] [error] [client x.x.x.x] PHP Warning:  mysqli::mysqli() [<a href='mysqli.mysqli'>mysqli.mysqli</a>]: (08004/1040): Too many connections in /var/web/<snip>/lib/dml/mysqli_native_moodle_database.php on line 384, referer: http://<snip>/mod/quiz/attempt.php?attempt=134243&page=16


Other ones I've receive has been from a course view, for example:

[Thu Feb 12 07:13:26 2015] [error] [client x.x.x.y] PHP Warning:  mysqli::mysqli() [<a href='mysqli.mysqli'>mysqli.mysqli</a>]: (08004/1040): Too many connections in /var/web/<snip>/lib/dml/mysqli_native_moodle_database.php on line 384, referer: http://<snip>/course/view.php?id=959

When I cross check the time against the site logs, it just shows a barrage of the same request repeated 200+ times, from the same user. When I've checked in with users they indicate they were just doing things normally, clicked on something, and then after a little bit of while got the error no database connection error message shortly after. Once the site errors out, the mysql connections clear themselves up fairly quickly and everything goes back to normal. The total site outage lasts for no more than 20-30 seconds.


I'm curious if anyone has had anything similar happen on their site? Line 384 of mysqli_native_moodle_database.php is the mysql connection creation:         $this->mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname, $dbport, $dbsocket); 

So I don't think it's a coding issue in that file itself, it just seems either a bug or something on the network causes the request to be repeated hundred of times until the max number of connections is consumed. I'm reluctant to just raise the maximum number of mysql connections since I think then it will just take longer for the queue to fill up and the repeating request to be ended, but won't actually solve the problem.

I'm going to ask our network group to see if they saw anything strange during that time. On the user end, there is no consistency. It happens once every few months or so but different users in different geographic locations, using different browsers, doing a different activity.

Average of ratings: -
In reply to Mathew Gancarz

Re: Repeated flood of requests causes all database connections to be used up?

by Ken Task -
Picture of Particularly helpful Moodlers

Am no DB admin, but ....

The 'devil' might be in the details as to site configuration/setup.

Is the moodle app using a dedicated backend DB server? or is the DB on the web server itself?

Make a difference in certain settings for the DB and possibly apache/moodle.

It clearly says: Too many connections.

The default number of connections is 151 in MySQL.   Suggest installing something like mysqltuner.pl and check the DB server/services settings and performance.

http://mysqltuner.com/

If the DB is on a dedicated server, install mysqltuner.pl on the web server and run it from there.  That will test the networking to the DB box as well.

If one were to also install mysqltuner.pl ON the DB server and run it, one will see different recommended values.

In my.cnf one might need to:

Set max connections higher than default (151) to ???

max_connections=251 (that value is a guess)

And if the MySQL server is a dedicated machine might also need to set

'skip networking' ... MySQL checks the host accessing it each attempt to access.

'spirit of sharing', Ken

Average of ratings: -
In reply to Mathew Gancarz

Re: Repeated flood of requests causes all database connections to be used up?

by Ken Task -
Picture of Particularly helpful Moodlers

One more thing:

'... attempt.php?attempt=134243&page=16' indicates a quiz(s) is set to show X number of questions at a time ... i.e., not all questions.  So when a taker of the quiz finishes say page 1 of the quiz(s)/test, they see the next set of questions (next page) ... but they have NOT submitted the quiz for grading.  That does equate to more connections on the DB as students 'page through' the quiz.
3 pages to a quiz means at least 3 connections.

So let's say a quiz has been set to allow taking on a particular day for 150 users in a course.  Those users could come from any geographic location on the globe if your course is setup like that.  So there would be a spike on that day as opposed to other days when a quiz is not being taken/due.

Could it be a bot?  Can't rule anything out for anything online these days, but checking Moodle logs for activity - setting the report the quiz being taken on that day - might show a range of IP addresses ... but if one investigates those, one might find valid IP's (ie, a student making connection, and not a bot).

'spirit of sharing', Ken


Average of ratings: -
In reply to Ken Task

Re: Repeated flood of requests causes all database connections to be used up?

by Mathew Gancarz -
Picture of Core developers

Hi Ken, thank you for the suggestions so far. I've investigated with our IT team, and it looks like on the network side, we did record that there was actually about 200+ network requests from the user's machine within the space of less than a minute. This corresponds with the Moodle logs for that activity and user too. The site wasn't particularly busy.. it's just that one browser sent many requests.

It looks to me that what is happening is that correctly, Moodle is answering these 200+ queries until it runs out of DB connections to satisfy the requests. I was able to get in contact with the student and they mentioned receiving a popup that they dismissed for a while by clicking X, then finally clicking No on it at which point they got the db connection error message.

My best guess is that a script or their browser went wrong in some way and caused the multitude of requests.

Every time that we've had this happen, it has been from one IP, corresponding to a legitimate learner with their own login. Though each time it happens to someone else.. We haven't had any repeats, and it only happens once every few months.

Average of ratings: -
In reply to Mathew Gancarz

Re: Repeated flood of requests causes all database connections to be used up?

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

We have seen this occasionally at the OU, and what can happen is that something ends up on the keyboard, on the F5 key, holding it down. On one occasion, it was actually the student's cat! (I wish I could remember how we found that out, there was a good reason to believe that is really what happened.) So, anyway, it is not necessarily malicious (unless you think the cats are out to get you.)

Average of ratings: -
In reply to Tim Hunt

Re: Repeated flood of requests causes all database connections to be used up?

by Ken Task -
Picture of Particularly helpful Moodlers

Guess much depends upon 'clients' when it comes to 'malicious' ... turn a bunch of Middle Schools kids loose on a Moodle that has a 'button' or 'link' they can play with ... ie, launch, close, launch, close, launch, close, etc.. or launch multiples ... like a single page with 20 embedded videos coming from your Moodle server ... and they will 'stress test' your Moodle for you! ;\ and, hopefully, the entity will provide a digital lesson in 'denial of service' consequences.

Been there ... seen that!

'spirit of sharing', Ken


Average of ratings: -
In reply to Mathew Gancarz

Re: Repeated flood of requests causes all database connections to be used up?

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

Yes - we've seen it to. Usually in class quizzes. We see hundreds of requests for the same page from the same IP over a short period. 

In some respects its a denial-of-service attack and should probably be dealt with as such (at the web server level). 

As you say, when tracked down the student always denies everything but I'm not convinced wink

Average of ratings: -