slow front page and my page for a few users

Re: slow front page and my page for a few users

by Rob Johnson -
Number of replies: 7

I have added two of the three missing indexes using phpmyadmin.  When I try to fix the mdl_tag_instance index, I get the error below.  Fixing the two tables did not clear the slow query.

#1072 - Key column 'tiuserid' doesn't exist in table

 In a reply below, I commented that if I remove an affected account from all courses, the slow loading goes away.  I added an account back to the meta enrolled courses one by one using manual enrollment.  The slowness did not reappear until the account was in 7 courses.  Then the My page too about 15 seconds to load.  Adding an 8th course increased the load time to 35 seconds.  It did not matter what courses they were.  It just mattered how many.

I have more than 2000 staff that are enrolled in the same staff courses through the same meta course.  Only seven have reported experiencing the slow query.  Even If I delete an account, it is slow if I create a new one with the same username.  New account that have never been in the system work fine.

Average of ratings: -
In reply to Rob Johnson

Re: slow front page and my page for a few users

by Rob Johnson -

I am seeing other slow queries on the mdl_events table.  An example is below.

SET timestamp=1441032736;
SELECT * FROM mdl_event WHERE instance = '29234' AND modulename = 'quiz';

I cannot find a quiz with the ID 29334.  I would really appreciate help sorting this out.

Average of ratings: -
In reply to Rob Johnson

Re: slow front page and my page for a few users

by Colin Fraser -
Picture of Documentation writers Picture of Testers

That timestamp is the date 31/08/2015, or for you very strange people who like their dates backwards, 2015/08/31. I am pretty sure that you would know if there was someone running a quiz then.  So where is the query being made? Who is running it? Is it a cronjob?

Average of ratings: -
In reply to Colin Fraser

Re: slow front page and my page for a few users

by Rob Johnson -

Thank you for the reply Colin.  I ran the time stamp through a converter for unix time and it does correspond with the teacher of course editing a quiz.  I was able to determine the course by searching the mdl_events table for the "instance."  The teacher that generated the slow query in this case is not one of the users affected by the slow loading front page when the calendar block is populated.

I was able to get the slow query of an affected account without the truncation I had posted above.  It is:

SET timestamp=1441126734;
SELECT * FROM mdl_event WHERE (timestart >= 1441090800 OR timestart + timeduration > 1441090800) AND timestart <= 1444978799 AND ( (userid = '55777' AND courseid = 0 AND groupid = 0) OR (groupid = 0 AND courseid IN ('2','4','7','8','43','269','387','408','429','1638','1788','2578','1'))) AND visible = 1  ORDER BY timestart;

It just seems to be looking for upcoming events in the courses this user is enrolled in.  Hundreds of other staff accounts are enrolled in the same courses and not seeing the slow behavior.  How can I isolate why a very small number of users are affected?

Average of ratings: -
In reply to Rob Johnson

Re: slow front page and my page for a few users

by Rob Johnson -

Perhaps I am not asking the right question here in this discussion.  I would like to know why the slow query is triggered for a few users, but not others.  I ran the same query on a copy of my site, but substituted users ID's from accounts that do not have the front page slowness.  The query is slow no matter what.  This leads me to believe it does not run for most users. 

Classes begin in my district next week.  I really need help on this.  Thank you in advance.

Average of ratings: -
In reply to Rob Johnson

Re: slow front page and my page for a few users

by Colin Fraser -
Picture of Documentation writers Picture of Testers

Sorry Rob, but I am out of ideas, but the only thing I can think of is there is something associating with those users because they have done something, turned something on, or off, or not using the right browser or standing with their left foot on the keyboard or something usually not expected. I can't think of anything else that makes sense. 

Average of ratings: -
In reply to Rob Johnson

Re: slow front page and my page for a few users

by Paul Verrall -

I'm running also out of ideas, at least ones I can coherently suggest into the permanent on-line record  wink

That full query you have, does it run slow when you run it on it's own in the database (i.e. without all the other guff Moodle is doing when you actually load a page)? 

If the slowness of that particular query is reproducible I would move on to running an EXPLAIN on the query to work out why it is slow, It might be that you can add an index yourself to alleviate the problem.

Or maybe the mdl_event table is corrupted in some way in which case perhaps try running a repair on it? I should point out I am not an MySQL admin so take my suggestions in this area cautiously.

Average of ratings: -
In reply to Paul Verrall

Re: slow front page and my page for a few users

by Rob Johnson -

I have run that query on a copy of my site outside of Moodle using phpmyadmin.  It is slow then, and is slow when I change the user ID to one that is not experiencing the issue from within Moodle.  I will try the EXPLAIN tomorrow on the dev box with the copy of my site.

I truly appreciate the suggestion even if it is a stab in the dark.  It may lead me to a solution, even if it is in a round about way.  Thank you.

Average of ratings: -