Database server of Moodle 2.3.1 is a bottleneck

Database server of Moodle 2.3.1 is a bottleneck

by CP Lau -
Number of replies: 14

We are running Moodle 2.3.1 with MySQL 5.5. In a stress test exercise (4 web servers and 1 db server), we find that the phsyical write IO of the MySQL database server is the bottleneck and the performance of 3 web servers is better than 4 web servers. Our moodle servers including db are all running in VMs with SAN storage. May i ask if there is any way to enhance the performance of Moodle? Do you suggest to use MySQL clusters?

 

Thanks

Average of ratings: -
In reply to CP Lau

Re: Database server of Moodle 2.3.1 is a bottleneck

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

I can't help thinking you have partially answered your own question. Faster write IO? There's another thread on the go at the moment with a similar topic - the disk IO on VMs can be slow. Consider not running your database on a VM.

I'm not much of an expert with MySQL clusters but, surely, as they all have to write the same data (as it's writing not reading that's a problem) a cluster will make no difference at best and probably make it worse.

Average of ratings: Useful (1)
In reply to Howard Miller

Re: Database server of Moodle 2.3.1 is a bottleneck

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
Hi CP

Read the discussion "Slow disk reads with Linux VMs, Moodle 1.9" http://moodle.org/mod/forum/discuss.php?d=211249. (Must be the thread Howard mentioned above.)

There many other similar discussions in this forum. Use the 'advanced search facility' mentioned in the introduction to this forum to find them.

OK, you run 2.3. There was a recent query in the "General Developer" forum: "2.3.2 slow", which ultimately came back to "Slow page loading in Moodle 2.3.1? Try this:" http://moodle.org/mod/forum/discuss.php?d=210777.

The compulsory reading is also linked to the intro. It also describes how you could dig deeper. Also note that you need to provide information about you platform (also in the intro), if you want others to answer your problem.
Average of ratings: -
In reply to CP Lau

Re: Database server of Moodle 2.3.1 is a bottleneck

by Matt Sharpe -
What type of storage is your VM using? I find that most commercial SANs are a little too abstracted away from the metal to be of use on a DB. I don't think MySQL clusters will help here because MySQL itself is not very good at this, and DB level clustering is usually of little benefit in this scenario. I would think improving your storage mechanism would be a bigger performance bump.

Likely the reason for your test results was that Moodle favours Apache (and I guess IIS) which both run PHP child processes for each request, causing too many MySQL connections on large numbers of servers. Connection pooling should get around that at least.

Matt
Average of ratings: -
In reply to Matt Sharpe

回應: Re: Database server of Moodle 2.3.1 is a bottleneck

by CP Lau -

We are using HDS AMS2500 SAN Storage with 650GB SAS 15K RPM disk, with volume virtualization by IBM SVC. And we are using Apache. Thanks for your great advice.

Average of ratings: -
In reply to CP Lau

Re: Database server of Moodle 2.3.1 is a bottleneck

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
Hi

It would also be nice if you could document the stress test you mentioned.
Average of ratings: -
In reply to Visvanath Ratnaweera

Re: Database server of Moodle 2.3.1 is a bottleneck

by CP Lau -

Dear Visvanath Ratnaweera, 

    Thank you very much for your help. In our stress test, we target to achieve 1500 active users actively using Moodle (login, view notes, quiz, assignment, reply to a discussion topics, view news forum, logout) in an hour with 4 apache web servers running on Linux and 1 single MySQL database. We find that the bottleneck is in the IO of the MySQL database.

    There are several ways to go, 1) use SSD SAN storage, 2) MySQL cluster, 3) setting up multiple read-only MySQL server (next to the single writable MySQL server) for further performance improvement using MySQL proxy or Scalebase MySQL load balancer.

    Our system team colleagues believe that option 2 and 3 should have performance speedup. However, i really don't know if Moodle can support them. May i have your valuable advice?

    Thank you very much

 

Average of ratings: -
In reply to CP Lau

Re: Database server of Moodle 2.3.1 is a bottleneck

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
Hi CP

You are welcome!

About the stress test: I hope, you'll document your method in detail. "Numbers" are very useful in the disucssions here rather than just saying "slow" or "fast".

I/O of the database as the bottleneck: This is an important finding. People often replace parts with "faster" components, without knowing whether they are critical or not. In your case, the result does not surprise anybody: see the post http://moodle.org/mod/forum/discuss.php?d=211249#p921345 (it was in the discussion I've linked in my earlier post).

About your strategy:
> 1) use SSD SAN storage

Theoretically yes. Practically I don't know.

> 2) MySQL cluster

People have come up with various architectures, but this forum lacks concrete numbers and a clear documentation on which implementation. Others suggest goint to PostgreSQL, still no further information. You can use the 'Advanced serach' in the intro to find them.

> 3) setting up multiple read-only MySQL server (next to the single writable MySQL server)

Never heard. Still the writable database will dictate the speed, won't it?

More possibilites:

4) Physical database server: See http://moodle.org/mod/forum/discuss.php?d=211249#p921345 (the same link as above).

5) Debug the database queries and give feed back to the developers. See P.S. in http://moodle.org/mod/forum/discuss.php?d=211679#p922637. Optimising the application itself has the biggest impact.

6) Revert to 2.2, or to 1.9 or even to 1.6 if you use quizzes in a massive scale.

You are going to need some drastic measures since you are still far away from the target.
Average of ratings: -
In reply to CP Lau

Re: Database server of Moodle 2.3.1 is a bottleneck

by Matt Sharpe -
Following on from what Visvanath has said regarding MySQL Clustering, that is generally not a good idea. Something that most people ignore though is a connection pooler and sharder. PostgreSQL has a lot more of this technology but there is a decent software package for MySQL I've been meaning to test called Spock Proxy. What it does is splits your data across multiple servers and then shards your query. I'm not sure how this would work for Moodle but a scheme like logs on one server, everything else on another would probably be fine as we've been talking about that in HQ for a while.

If you want to try reading and writing off multiple servers for the DB without worrying about replication, see about having an SSD server for writes and a regular server for reads but run LVM and DRBD on them. That way the disks are the same size and any writes in the kernel to one are automatically pushed to the other, without the usual overhead of a SAN. This sounds a bit complicated but it's a good way to get a reasonable performance bump on a 2-server system. The other advantage is that because both servers are potentially the same, if one goes down you can just fallback to the other using LVS. I would usually just store the DB itself on the DRBD disk though.

Matt
Average of ratings: Useful (1)
In reply to Matt Sharpe

Re: Database server of Moodle 2.3.1 is a bottleneck

by CP Lau -

Thanks for your suggestion.

We are using 5 web servers and 1 mysql server for the stress test. The Moodle version being tested is v2.3.1+. All of them are running on VMs.

During the stress test, 1500 users would actively use Moodle in an hour, including quiz submission. During the stress test, select queries are fast as we have used MySQL query cache. However, the write queries are slow although the phsyical IO is not busy at all. Thus, we would like to use MySQL Cluster to improve the write queries performance. Otherwise, we cannot further scale out our Moodle system.

 

Thanks a lot

 

 

 

Average of ratings: -
In reply to CP Lau

Re: Database server of Moodle 2.3.1 is a bottleneck

by Pedro Marques -

Hi I do you make the simulation of quiz submission for that amount of users?

 

thanks

 

Pedro

Average of ratings: -
In reply to CP Lau

Re: Database server of Moodle 2.3.1 is a bottleneck

by E. L. Cooper -

I have over 1,000 students in 40 classes with one school with no need to do anything oddball- unless you consider two plain dedicated vanilla servers odd.  The 2nd server is overkill. Of course the 1,500 users per hour you postilate would actually be 15,000 enrollments

Personal advice- start with where you are- hardware and software will out strip your needs in the blink of an eye.

Average of ratings: Useful (1)
In reply to CP Lau

Re: Database server of Moodle 2.3.1 is a bottleneck

by Wen Hao Chuang -

May I ask how many users you are serving right now? During peak hours, how many concurrent users and how many simultanenous users taking the quizzes at the same time? Thanks!

Average of ratings: -
In reply to Wen Hao Chuang

Re: Database server of Moodle 2.3.1 is a bottleneck

by E. L. Cooper -

We are totally asycronis so it may not fit your class idea. Most users at one time ever was 200 ish. We only use quiz for exams- most ever was 28 but only 2 of them were taking the same exam. Lessons we use a lot and I can get you that.

Curious too- are you testing with https on all pages? And what are your current real numbers?

My understanding is that VMS slows databases so what would be the point of using it?

Average of ratings: Useful (1)