Moodle performance issues with mysql

Moodle performance issues with mysql

by Sreepathy T L -
Number of replies: 8

Hi,

We have a large moodle system in our organisation with about 34000 users consuming the e-learning content in the form of scorm courses. The moodle system is setup on a 32Core 128GB RAM Windows 2012 R2 VM with Moodle 3.4 version running on mysql 5.7. 

Towards the end of the course evaluation we had a spurt in traffic to our moodle site with almost 50% (17K) of the users trying to access the quiz modules. 

Though application ran without any visible decrease in speed, users got frequent DB connection failed popup messages and experienced DB errors during quiz also. Mysql was tuned using mysqltuner script. Still the error persisted. It seems like mysql is refusing further connections after a limit, though the total connections to mysql were less than max_connections limit.

Our organisation requires at least 16K people to be able to access the quiz concurrently. Can someone guide how to make this feasible.

Average of ratings: -
In reply to Sreepathy T L

Re: Moodle performance issues with mysql

by Ken Task -
Picture of Particularly helpful Moodlers

Is DB server in same VM as web/code?  IF so, might time to use another VM dedicated to just DB (nothing but the DB server).   And tune from web service to the db server.

Are the quizzes in the SCORM's?

Every students SCORM quiz do a 'network check' - which is something one cannot control.   From what you've described, a 'network check' failed.   Only logs might verify that but I could not tell you which log in/on Windows.

Comment: even though you've given/shared good info not at all sure anyone can give you very specific info.  Not all networks are the same ... and client locations come into play there as well.

'spirit of sharing', Ken


Average of ratings: -
In reply to Ken Task

Re: Moodle performance issues with mysql

by Sreepathy T L -
  • DB server is in same VM
  • Quizzes are not in the form of scorm. They are normal moodle quiz components taken from a question bank
  • What causes the DBCONNECTIONFAILED popup covering the screen? Is it the failed DB connection or non-receipt of ajax response from server?
  • During the choking period the site was not accessible from every network - especially the quiz module. We tried from various networks like leased lines with 32MBPS speed, 4G Mobile networks and also from even normal 3G networks and broadband connections. Everywhere the dbconnectionfailed popup came (which disappears on pressing escape key)
  • During the choking time CPU usage was about 70% and memory usage was about 20%. Mysql was using only 2GB of available 128GB RAM. Is it possible to make mysql cache tables in-memory (with ZERO DATA LOSS in case of crashes) to provide better performance making use of available RAM?
  • We have enabled opcache as recommended during PHP installation.
  • Will a separate DB server really helps?
Average of ratings: -
In reply to Sreepathy T L

Re: Moodle performance issues with mysql

by Ken Task -
Picture of Particularly helpful Moodlers

Thanks for additional info.   As we both know, networking comes before application.   Networking issues means no access to apps and therefore no metrics on web servers or db would be accurate ... couldn't get there.

So did you look at networking logs on server for other clues?

Do know this, MySQL runs best when most of It can be loaded into memory - less disk I/O, then.  Percona folks, well known for their DB expertise, blog that at least 80% of the DB in memory is a goal.   So on your current setup what is the memory usage for the web service and the DB server - separate and combined?

Does Windows have a 'top' command like in Linux?

A tool to help is mysqltuner.pl - a perl script.  Run with super user creds, it outputs info like this:

[OK] Maximum reached memory usage: 176.9M (2.26% of installed RAM)
[OK] Maximum possible memory usage: 338.9M (4.34% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available

On a dedicated DB server VM with no other connections allowed but maybe one or two admin level users, the only connectivity between web server and DB server is the app.  Might that fact help troubleshoot 'network' issues?

My suggestion easily tested with VM's, I think.

Alternative ... ie next step up is load balancing ... but that normally starts with multiple web service nodes back to a beefy single DB server.  Maybe all you need is a beefier dedicated DB server.

'spirit of sharing', Ken


Average of ratings: -
In reply to Ken Task

Re: Moodle performance issues with mysql

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
Ken, DB is in the same VM. Still the OP gets connection to the DB failed. Doesn't it mean that the machine is simply overloaded?
Average of ratings: -
In reply to Visvanath Ratnaweera

Re: Moodle performance issues with mysql

by Ken Task -
Picture of Particularly helpful Moodlers

Would think so ... but I don't use Windows!  Memory leaks?

On Linux localhost DB uses socket connections ... on Windows?

And we really don't have a picture/idea of just how much memory MySQL is grabbing on boot up.

?????

'spirit of sharing', Ken


Average of ratings: -
In reply to Sreepathy T L

Re: Moodle performance issues with mysql

by tommy lucy -

I am also using mysql server for my pc but its not opening msp 2 I am also using 

  • During the choking time CPU usage was about 70% and memory usage was about 20%. Mysql was using only 2GB of available 128GB RAM. Is it possible to make mysql cache tables in-memory (with ZERO DATA LOSS in case of crashes) to provide better performance making use of available RAM?
Average of ratings: -
In reply to Sreepathy T L

Re: Moodle performance issues with mysql

by Diane Soini -

We've seen a similar problem with even a smaller class than this. We've never been able to figure out how to reproduce the problem so that we can test solutions. However, since we moved our database to a percona cluster running on several separated systems we haven't seen this particular problem occur again. Our database cluster is a cluster of 5 with some SSDs and some spinning disks and it runs pretty fast and has some redundancy for failure. We have had several failures with some bad SSDs but those failures did not interrupt service.

We recently had a similar problem that had nothing to do with quizzes. We saw that we were hitting the limit of php threads. The problem ended up not being about php threads, though. It was about other scripts running out-dated optimize table commands on one of the tables in the database.

One thing we do is advise our instructors not to use moodle for high stakes testing. We still aren't convinced it will be able to handle it and would prefer not to have a failure occur for a high stakes test.

Average of ratings: Useful (2)