slow front page and my page for a few users

slow front page and my page for a few users

by Rob Johnson -
Number of replies: 23

Hi Fellow Moodlers,

I recently upgraded from 2.6 to 2.8.7.  After, I have reports from a six users that the front page takes 40 to 50 seconds to load.   I have verified this using Log in as for one of the affected people.  For everyone else, performance is great.  Course pages are fast for everyone.   I don't see anything different about the affected accounts.  All are using manual authentication.  I am hoping someone can steer me to a solution.

So far, I have:

  • Removed all blocks from the home page.  This made no difference.
  • Switched to the Clean theme.  No difference.
  • Checked Apache error logs - Nothing there about the slow user.

When I turn show performance info at the bottom of the page, a slow front page account shows a 43 second load time and 42 second DB query time.  An test account show the front page loading in 1.2 seconds with .5231 seconds DB query.


The fall semester begins in a little over two weeks.  I am hoping to have it solved by then.
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

My guess would be they have turned some antivirus or anti-malware, or even an ad blocker, on in their browsers that others have managed not to. Or, there is another layer of java or flash or some other third-party tool that has become associated with their accounts. If they are using Internet Exploder, then it is likely to be the settings there, or if using chrome, the training wheels may have not been taken off. Outside of that, it is not likely that the server has singled these accounts out for special treatment, nor the PHP, and as it is not generic, it affects only a few users, I suspect it would be related to those accounts only. So what is different?  

Average of ratings: -
In reply to Colin Fraser

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

by Rob Johnson -

I cannot find anything different with those accounts.  I have logged in as two the slow users.  The front page is slow in IE, FF and Chrome.  I have reproduced the behavior on several devices.  Debugging turned on shows nothing.

I read another post about the mdl_cache_flags tables containing old data and causing slowness.  Is it OK to empty that table?

Average of ratings: -
In reply to Rob Johnson

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

by Conn Warwicker -
Picture of Core developers Picture of Plugin developers

It's easier to just go to Site Admin -> Purge Caches rather than mess around with the database tables.

Average of ratings: -
In reply to Conn Warwicker

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

by Rob Johnson -

Thank you, but I have purged caches several times.  Any time I make a change, I do so before trying an affected account again.  So far it has not made a difference.

Average of ratings: -
In reply to Rob Johnson

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

by Paul Verrall -

Hi Rob,

Your PERF results suggest the slowness is in the database and not the http daemon or PHP. 

First check that your DB has enough disk space (just a hunch)... 

I  would then check any database logs you have and look at enabling your databases 'slow-query-logging'. You don't say what DB you are using but this link covers postgresql and mysql in one smile

http://community.jaspersoft.com/wiki/logging-long-running-queries-postgres-and-mysql-databases

Paul

Average of ratings: -
In reply to Paul Verrall

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

by Rob Johnson -

Thank you Paul.  My prime suspect has been the database.  It is mysql 5.6.  This is a dedicated DB server that was recently upgraded to Suse 13.2.  I imported a backup of the database after the OS upgrade.  I will get slow query logging turned on and report back.

Average of ratings: -
In reply to Paul Verrall

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

by Rob Johnson -

Hi Paul and any other Moodler that can help interpreting this slow database query.  This is in my slow query log.

SET timestamp=1440631046;
SELECT * FROM mdl_event WHERE (timestart >= 1438412400 OR timestart + timeduration > 1438412400) AND timestart <= 1441090799 AND ( (userid = '55777' AND courseid = 0 AND groupid = 0) OR (groupid = 0 AND courseid IN ('2','4','7','8 [truncated, 351 bytes total]

The user ID is one of the slow accounts I have been using to test with.  It appeared in the log just after I loaded the front page from that account.  I would truly appreciate any help on what to do to clear this up.

Average of ratings: -
In reply to Rob Johnson

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

by Rob Johnson -

After seeing the slow query is with the events table, I hid both the upcoming events block and calendar block on the home page.  I had hidden them one at a time before.  This made the front page load normally for the slow accounts.  I have noticed that these accounts are slow in any course where those blocks are populated.  Also the calendar is slow for the affected accounts.  While I don't yet have a fix, I feel as though I am making progress.

Average of ratings: -
In reply to Rob Johnson

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

by Paul Verrall -

Hi Rob,

Shame the query log truncated, but what we have certainly does indicate an issue with querying the events. My first thought is an issue with the restored database perhaps missing an index. You can check and fix for this in Moodle via,

Site administration > Development > XMLDB editor

There will be an option in the header for 'Check indexes'

No idea if this will help, but it is certainly worth a punt!

Average of ratings: -
In reply to Paul Verrall

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

by Paul Verrall -

Also a CLI tool for this, ' check_database_schema.php'

Average of ratings: -
In reply to Paul Verrall

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

by Rob Johnson -

Here are the results from the index check.  Three tables came up as missing indexes.

  • Table: tag_instance. Index: unique (itemtype, itemid, tagid, tiuserid)
  • Table: grade_letters. Index: unique (contextid, lowerboundary, letter)
  • Table: block_instances. Index: not unique (parentcontextid)
Below was reported for the events table.

  • event
    • Key: primary (id) OK
    • Index: not unique (courseid) OK
    • Index: not unique (userid) OK
    • Index: not unique (timestart) OK
    • Index: not unique (timeduration) OK
    • Index: not unique (groupid, courseid, visible, userid) OK

Wouldn't a missing index as the cause affect all users?

Average of ratings: -
In reply to Rob Johnson

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

by Rob Johnson -

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: -
In reply to Rob Johnson

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

by Mark Sharp -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers

Hi Rob,

I found that the if you show the users' courses (Enrolled courses) on the frontpage that can cripple the page load, depending on how many courses they're enrolled on.

We now don't show that on the frontpage and direct people to the /my page as the course list there doesn't seem to slow things down.

Mark


Average of ratings: -
In reply to Mark Sharp

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

by Rob Johnson -

Thank you Mark.  We don't show courses on the front page.  Also, this is only happening to 6 identified accounts out of 27,000+.

I have enabled slow query logging in the database and loaded the front page with a slow account.  Nothing has been logged as a slow query so far.  I was given permission from one of the affected account to delete it and recreate as a new account.  It is still slow on the front page after recreation.  There must be something in the database tied to that username.

Average of ratings: -
In reply to Rob Johnson

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

by Mark Sharp -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers

Another thing we have come across is users who are enrolled via category enrolment. i.e. they are actually enrolled into all the courses in the category. If you've got someone who's enrolled this way I've seen page loading get crippled, though I think that might have been on their profile page.

The thing is it may not be an individual query that is slow, but the sheer volume of queries particular users trigger because of the way they are set up.

Average of ratings: -
In reply to Mark Sharp

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

by Rob Johnson -
I removed the account I have been testing with from all courses and the issue cleared up.  The slow query I posted above shows the course ID's it is trying to pull events from.  The user is only enrolled in a staff course that acts a meta course for a few other staff only courses.  All other staff are enrolled in these same courses and are not affected.
Average of ratings: -