Aborted Connection (Unknown Error) - Memory Related?

Aborted Connection (Unknown Error) - Memory Related?

by Stephen Teichgraeber -
Number of replies: 5

For a while now, our mysqld.err has a large number of entries for Aborted Connection (Unknown Error).  These occur frequently, often 2 - 10 minutes apart 24/7.  Doing some basic research into the mysql Aborted Connection error, a different message would display if the problem was, for example, having too small a packet size set in my.cnf.  Looking at the error log, these entries started appearing immediately after an upgrade from 3.4 to our current version of 3.6.4 but we haven't noticed until recently.

I'm struggling to identify a cause. One idea I have is that mysql's memory buffer is full 100% of the time.  With its reserved memory at capacity, and swap at capacity, old connections are terminated before cleanly closed to make space for new connections. 

Another memory related issue we have is with the Automated Backups task.  Every day at the same time, this task runs and results in a large memory spike.  Sometimes there isn't enough free memory and the mysqld service crashes and auto-restarts into crash recovery.  Our site will be inaccessible for only a few minutes.  A prior sys admin configured this task to run hourly.  Realistically, how frequently should this be run?  I'm thinking about changing it to once or twice a day.  This task could also be failing because we have a very large number of courses (probably in the tens of thousands) still on the site going back several years.  No course clean up has ever been attempted.  Could the Automated Backups task be iterating through too many courses at this point?  If so, what are the best methods for bulk course removal, if any?

Our configuration is as follows:

  • Moodle and MariaDB are on the same RHEL system.
  • Moodle 3.6.4+ (Build: 20190524)
  • MariaDB 10.1.12
  • PHP 7.0.6
Average of ratings: -
In reply to Stephen Teichgraeber

Re: Aborted Connection (Unknown Error) - Memory Related?

by Ken Task -
Picture of Particularly helpful Moodlers

From what you've described, sounds like a major clean up + some tuning needed.

Some command line scripts to help assess.

Number of courses:

getcourses

mysql -u root -p -e "use moodle;select id,fullname,shortname,timecreated,visible from mdl_course;" > courses.txt;cat courses.txt
wc -l courses.txt

Looks like:
id    fullname    shortname    timecreated    visible
1    Cxxxxxx University LMS    CLMS    0    1
2    Admin Sanbox    Sandbox    1217427527    0
3    NATE Core Review    NATE    1217428217    1
8    LEED Accreditation Training    LEED    1094240775    0


Number of courses that are invisible:

getcoursesinv

mysql -u root -p -e "use moodle;select id,fullname,shortname,timecreated,visible from mdl_course where visible='0';" > invcourses.txt;cat invcourses.txt
wc -l invcourses.txt

Looks like:
id    fullname    shortname    timecreated    visible
2    Admin Sanbox    Sandbox    1217427527    0
8    LEED Accreditation Training    LEED    1094240775    0
9    Practice Course    Practice    1218730104    0
18    Andy Sandbox Course - Testing    Andy Sandbox Course    1221783489    0

getallusers

mysql -u root -p -e "use moodle;select id,auth,username,firstname,lastname,email,lastip,timecreated,timemodified from mdl_user order by timecreated asc;" > allusers.txt;cat allusers.txt
wc -l ./allusers.txt

Also suggest installing and running MySQLTuner.

https://github.com/major/MySQLTuner-perl

Run with superuser creds.

Will help with aborted connections and tweaks to my.cnf for DB server.

And one is probably/also looking at upgrade to PHP ... 7.0.x -> 7.2.x

Please see: https://docs.moodle.org/dev/Moodle_3.6_release_notes

Automated backup issues could be cleaned up *some* depending upon number of active courses and how many.

Obviously, no sense in keeping a course that is invisable and inactive for some time on productions server.   And archive (backup) of course and archiving that backup will help reduce on server.

A lot to do ... above just a start! smile

'SoS', Ken

Average of ratings: Useful (2)
In reply to Ken Task

Re: Aborted Connection (Unknown Error) - Memory Related?

by Stephen Teichgraeber -
Thanks for your reply, Ken. Going on 6 days now we haven't had a DB crash. The only thing that changed was a setting with automated backups to skip courses not modified in 14 days. It was set to the default of 30. I also purged site caches when troubleshooting a different issue around the same time. It's likely caches had not been purged in a very long time. Now the automated backup scheduled task no longer results in a memory spike that can sometimes crash the DB. Definitely keeping an eye on it.

Thank you also for the query suggestions. We have far too many past users and past courses that do not need to be in Moodle. Using the course size plugin (https://moodle.org/plugins/report_coursesize) I now have a better idea about which courses are taking up the most space. I'm wondering what the best way to delete a large amount of courses/users is. I feel like it would be safer through the UI by moving courses into a new category and then deleting that category. This will be rough as I expect this number to be in the thousands (22,000 courses total on the site). I don't know if there are any better/preferred methods.

Ran mysqltuner after 5 days of uptime for the DB. One thing that's odd is even though mysql.err has the "Adborted Connection (Unknown error)" messages, mysqltuner reported 0 aborted connections. I definitely trust the log over the script but I wonder why it didn't catch these. "Unknown error" maybe? It also seems to confirm my suspicion that the error is memory related. Current buffer is 48 GB and the system has 62 GB of memory. Script recommends a buffer of at least 60 GB and warns about mysql memory usage. I hope I'm not wrong in thinking that reducing the number of courses and users on the site might also reduce the script's recommendation.
Average of ratings: Useful (1)
In reply to Stephen Teichgraeber

Re: Aborted Connection (Unknown Error) - Memory Related?

by Ken Task -
Picture of Particularly helpful Moodlers

You might to install moosh.

https://moosh-online.com/

check out what it can do:

https://moosh-online.com/commands/

Look for course-delete - which deletes courses by course id numbers.

Start with a single course id.  Might be aware of a single user in that course and follow up with what one can find for that user after deletion of course in which user was enrolled.

I would think that reduced number of courses and users has to reduce size of tables and thus should/would make it easier on DB server with queries that involved courses/users.

'SoS', Ken

Average of ratings: -
In reply to Stephen Teichgraeber

Re: Aborted Connection (Unknown Error) - Memory Related?

by Ken Task -
Picture of Particularly helpful Moodlers

In addition to questions asked by Visvanath ...

Tuner

In Performance Metrics
What does it show for:
Physical Memory
Max MySQL memory
Other process memory
Maximum reached memory usage: 2.0G (26.83% of installed RAM)
Maximum possible memory usage: 2.2G (28.90% of installed RAM)
Overall possible memory usage with other process is compatible with memory available

InnoDB Metrics
InnoDB buffer pool / data size:

Is server an all in one box? code/db/moodledata on same server?

Since you will be culling courses, good idea, me thinks, maybe for records retention, to archive courses you will be deleting
to a storage device.

Would also recommend auto backups be stored outside of moodledata/filedir/
Designated directory option in set up of auto backups.
Among reasons ... designated directory will store backups by humanly recognizable names and true admin of sever can find them without DB queries and the need of meta data in DB.

'SoS', Ken

Average of ratings: -
In reply to Stephen Teichgraeber

Re: Aborted Connection (Unknown Error) - Memory Related?

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
How much RAM? How many (v)CPUs? What is the disc system? Any network mounted disks?
Average of ratings: -