Performance problems and how we solved

Performance problems and how we solved

by Jose' Coelho -
Number of replies: 9

This message is to report performance problems and how we solved in Universidade Aberta (www.moodle.univ-ab.pt/moodle). The system was upgraded to 1.9 in July.

We have good hardware and all components of the system well optimized, and also we have made performance tests above the load that we expect, but in the first day of the semester, many students want to go to the courses and the system do not respond.

The database was doing about 3000 queries per second that use the table mdl_log, that has a very large table (we was maintaining records for 6 months), but even after cutting the table, the problem maintains. The 8 CPUs units of the database server were at 100%, and the two front-end servers were sleeping.

We turn off the block "Recent Activity", that was about 1700 instances (almost in all courses), and turn off the capability of the teacher and students to see reports, and the system recovered. In fact it is now answering very fast, was never before, and we have this year about 4 times the load that we have last year.

About the ability of teacher and students to see reports, the problem is that generates queries that can take 4 seconds of CPU, and depend of the size of mdl_log table. In those queries the WHERE condition starts with a time inequality, that probably would be better if it was in the last condition. This was not the main problem, but if it is turned on, the performance sometimes decreases considerably.

The block "Recent activity" it consumes too much CPU time in a heavy load situation, and when the mdl_log table is big. The difference was from night to day.

In http://docs.moodle.org/en/Performance#Performance_of_different_Moodle_modules there is no reference to this block, that was the root of our problems.

Our performance tests do not detect this situation because the block "Recent activity" and the reports was not on the pages tested. Also when we do the tests, the mdl_log table was smaller.

Regards,
José Coelho

Average of ratings: -
In reply to Jose' Coelho

Re: Performance problems and how we solved

by Gary Anderson -
Thanks Jose. I have filed a bug report in the tracker referencing your observation at MDL-16792
Average of ratings: -
In reply to Gary Anderson

Re: Performance problems and how we solved

by Martín Langhoff -
AFAIK, Petr worked quite a bit on getting the recent activity block to be efficient on the 1.9 branch.

Is this problem still observable on the latest 1.9.x?
Average of ratings: -
In reply to Martín Langhoff

Re: Performance problems and how we solved

by Jose' Coelho -

Our exact version is "Moodle 1.9.1+ (Build: 20080702)".

In the last month, we have 4 million page views, and we expect about 8 million this month. Before 29 of September, we have some activity going on, that was in some days more than half of the traffic of the starting day. I guess was because the number of courses that have "Recent activity", opening in the same time.

Average of ratings: -
In reply to Martín Langhoff

Re: Performance problems and how we solved

by ETH Zürich -

Indexing is not sufficient here, it is optimized and indexed to the max and we cant do anymore with the mdl_log..

Since latest versions of moodle do use mysql 5.1.x or newer, why dont we use mysql partitioning since it is already supported in mysql 5.1 and newer?
it will serve better and effecient, especially for mdl_log because it grows massively everyday. we can simply partition mdl_log by id range.. ie: adding number of partitions according to the number of records our systems can handle effeciently.

simply by adding there lins to the mdl_log creation:
.......
PARTITION BY RANGE(id) (
    PARTITION p0 VALUES LESS THAN(10000),
    PARTITION p1 VALUES LESS THAN(20000),
    PARTITION p2 VALUES LESS THAN(30000),
    PARTITION p3 VALUES LESS THAN(40000),
    PARTITION p4 VALUES LESS THAN(50000),
    PARTITION p5 VALUES LESS THAN(MAXVALUE)
);
these numbers can be changed based on some testing..

Average of ratings: -
In reply to Jose' Coelho

Ang: Performance problems and how we solved

by Morten Brydensholt -

Jose´,

This is valuable information!

I turned off the block 'recent activity' and some other blocks too and the page load is significant faster.

How do I turn off the capability of the teacher and students to see reports?

Morten

Average of ratings: -
In reply to Morten Brydensholt

Re: Ang: Performance problems and how we solved

by Jose' Coelho -

After login with the administrator:

Site Administration > Users > Premissions > Define roles > Edit(Student) > View reports = Prevent

The same for the teacher.

This is an important functionality, in my University professors used it a lot, but first of all comes the performance.

Regards,
José Coelho

Average of ratings: -
In reply to Jose' Coelho

Ang: Re: Ang: Performance problems and how we solved

by Morten Brydensholt -

Thanks  smile

Yes performance has higher weight. Luckily no-one uses 'recent activity'.

Morten

Average of ratings: -
In reply to Jose' Coelho

Re: Performance problems and how we solved

by Jason Cone -
The performance of our moodle server is rapidly declining. I would like to try the recommended actions you suggest. To turn off the recent activity block, does it need to be deleted from the block folder or just hidden? Same question for reports. Where/how exactly do you turn it off.

thanks
jc
Average of ratings: -
In reply to Jason Cone

Re: Performance problems and how we solved

by Jose' Coelho -

Hi Jason,

It is enouth to hidden the block.

After login with the administrator:

Site Administration > Modules > Blocks > Manage blocks > Recent Activity (hide)

Site Administration > Users > Premissions > Define roles > Edit(Student) > View reports = Prevent

The same for the teacher.

After the beginning of the semester, now we have about half of the traffic that we have in the first days, but is still considerable high comparing with the last year, so we cannot turn on neither the recent activity block nor the reports to professors.

Good luck.
José Coelho

Average of ratings: -