Moodle in English: Long time loading online assignment

I have a am trying to figure out why the online assignment (approximately 300 words per submission) for about 80 students is taking so long to load. I have

Ubuntu Linux (6.06), Apache 2.0.55, PHP 5.1.2, MySQL 5.0.22 - Moodle 1.6.3+ (2006050530).

running happily on a 3.0 GHz system with 4G of RAM. We have about 1000 students with about 30 concurrent users most of the time and performance is usually not an issue; however, when I go to grade the online assignment (http://moodle.jesuitcp.org/mod/assignment/submissions.php?id=10697&group=0), I get significant delays in loading times as demonstrated by the following from the performance info obtained from the bottom of the page (28.827939 secs RAM: 3.2MB Included 38 files ticks: 2883 user: 1138 sys: 7 cuser: 0 csys: 0 Load average: 2.15).

My suspicion is that I need to configure something in Apache or MySQL to allow for more memory but I am not certain. It seems that once the page loads if I want to view a group or sort the page that the reload is pretty quick provided it takes place within a short time (couple seconds) of the original load. For example, if I do a CTRL-F5 (hard refresh) right after loading the page I can get the performance info down to (2.618101 secs RAM: 2.8MB Included 28 files ticks: 262 user: 22 sys: 4 cuser: 0 csys: 0 Load average: 1.95)

Any suggestions as to how I might go about improving the performance of loading this page or where I might look to improve things?

Hi Anthony -

I'm sorry, I don't know the answer to your question, but the first thing I would do would be enable perf db. You've obviously enabled perf in general if you're seeing performance things in the footer, the perf db setting means that db queries are also counted.

Once you've done that, it should be easy to either blame or rule out excessive db queries

Thanks Penny. I attempted to do this by adding the following to my config.php and then turning debug on in admin but I did not see any additional information. What am I missing so that I can see the additional DB info?

define('MDL_PERF' , true); define('MDL_PERFDB' , true); define('MDL_PERFTOLOG' , true); define('MDL_PERFTOFOOT', true);

Are perftolog and perftofoot mutually exclusive?

no, and you have got MDL_PERFDB defined, which is the setting. It just generates one extra line in your performance stuff you had before:

28.827939 secs
RAM: 3.2MB
Included 38 files
ticks: 2883
user: 1138
sys: 7
cuser: 0
csys: 0
Load average: 2.15

You just get an extra line:

dbqueries: n

You're sure it's not there?

Penny - Thanks for confirming that I was on the right trail but as far as I can tell it is not there. I looked for the extra line but it is not showing up. I will have to troubleshoot why it is not showing up later. Peace.

Penny - Now that I've upgraded to 1.8 I went back and enabled perf db for the online assignment page (/mod/assignment/submissions.php). I have about 80 submissions each of about 300 words.

18.324313 secs RAM: 3.1MB Included 64 files DB queries 357 Log writes 1 ticks: 1832 user: 1743 sys: 8 cuser: 0 csys: 0 Load average: 0.29 Record cache hit/miss ratio : 0/0

Any thoughts?

Hi Anthony,

I suspect it is probably an issue with the database. Have you checked your slow queries log? Are you logging slow queries?

I just realized that I hadn't posted a bug about this. blush

The following applies to a 1.5.4 system. We did some database level upgrades a few weeks ago that resulted in some impressive changes in load average as well as the amount of queries showing up via mysql_slow_queries.

For the assignment module the following is what we did;

ALTER TABLE `mdl_assignment_submissions` ADD INDEX `userid` (`userid`);

ALTER TABLE `mdl_assignment_submissions` ADD INDEX `timemarked` (`timemarked`);

ALTER TABLE `mdl_assignment_submissions` ADD INDEX `timemodified` (`timemodified`);

ALTER TABLE `mdl_assignment_submissions` ADD INDEX `assignment` (`assignment`);

These commands will lock the assignment_submissions table so keep that in mind, and as always you should make a backup of your data first.

There were other index adds we did for other tables;

The log indexes will bar anyone from using the the site as this locks the log table while it is adding the index.

ALTER TABLE `mdl_log` ADD INDEX `userid` (`userid`);

ALTER TABLE `mdl_log` ADD INDEX `module` (`module`);

ALTER TABLE `mdl_log` ADD INDEX `action` (`action`);

ALTER TABLE `mdl_log` ADD INDEX `info` (`info`);

ALTER TABLE `mdl_log` ADD INDEX `time` (`time`);

If you are using db_sessions the following is critical

ALTER TABLE mdl_sessions CHANGE sesskey sesskey varchar(32) binary NOT NULL default '';

The query on db_sessions does a binary comparison (case-sensitive), but has an inappropriate index that can't be used. I wasn't sure that changing the query would be okay as adodb does a binary query on sessions so to be safe I adjusted the field to be appropriate for the query.

Added these as bugs;
MDL-7577
MDL-7578
MDL-7579

Before making changes to your database;

Please consider that the mdl_log alterations may not improve performance. Please review Martin's comments in the bug tracker for MDL-7579 he brings up some good points that you should be aware of before doing anything like this.

Uhm, sorry for the slight hijack, didn't want people to go off running database commands if they didn't understand the implications.

I don't see any comments from Martin (or anybody else

) in that bug. Maybe it's another bug number?

Saludos. Iñaki.

Doh!

I meant, MDL-7578

But as Samuli says below those are unecessary anyway. cool

Recent Moodle versions (1.6.2 or something and newer) have indexes "info" and "user" for the log table. The are other multicolumn indexes/indices for "time", "module" and "action", so these single indexes are not necessary.

I checked and the indexes were on the assignment_submissions table but it is still very slow pulling up. I have not checked slow queries yet but will look in to it. I opted not to apply the changes to mdl_logs. Peace.

Hardware and performance

Long time loading online assignment