19 October 2011, 10:53

We've recently encountered a problem with opening some blocks for editing, for example it takes several minutes for the Profile Information block to open. After investigating I've discovered that a few database queries are taking an awful long time to run, in some cases over a minute. We've tried copying it all onto new servers that are about to be deployed, it sped up slightly but nowhere near enough. Tried MyISAM and INNODB, doesn't seem to make any difference.
As this was only reported a couple of weeks ago I'm assuming it's since we upgraded to 1.4. It wasn't reported immediately, but that's the only thing which had changed recently.

Could anybody tell me if any of the db queries were re-written for 1.4?
And if not any pointers to why it might have suddenly slowed down so much?

Mahara 1.4
RHEL 5.6  PHP 5.2 & MySQL 5.0
RHEL 6.1: PHP 5.3 & MySQL 5.5

This is an example of a query that's now running really slowly: (71 seconds)

SELECT a.* FROM mah_artefact AS a
LEFT OUTER JOIN mah_artefact_access_usr AS aau ON (a.id = aau.artefact AND aau.usr = 8)
LEFT OUTER JOIN mah_artefact_parent_cache AS apc ON (a.id = apc.artefact)
LEFT OUTER JOIN (
SELECT
aar.artefact, aar.can_republish, m.group
FROM
mah_artefact_access_role AS aar
INNER JOIN mah_group_member AS m ON aar.role = m.role
WHERE
m.member = 8
AND aar.can_republish = 1
) ra ON (a.id = ra.artefact AND a.group = ra.group) WHERE (
"owner" = 8
OR ra.can_republish = 1
OR aau.can_republish = 1
OR a.institution IN ('mahara')
) AND artefacttype IN('firstname','lastname','studentid','preferredname','introduction','officialwebsite','personalwebsite','blogaddress','address','town','city','country','homenumber','businessnumber','mobilenumber','faxnumber','icqnumber','msnnumber','aimscreenname','yahoochat','skypeusername','jabberusername','occupation','industry')

19 October 2011, 15:42

Hi Gary, I'm not sure why it would have suddenly got slower, but I can tell you I ran into the same problem myself recently.  It's actually a lot worse in 1.5, because textbox configuration now calls the function that does the same query, and textboxes are edited a lot more often than profile blocks.

I found that on our site, it's slowest for users who are in a lot of groups.  I made a couple of changes that have sped things up quite a bit for us (this is for Postgres, I haven't timed them on MySQL yet).

These are the commits:

http://gitorious.org/mahara/mahara/commit/d38f9f11cb4cd775174df6e416957f0923bbb2cd

http://gitorious.org/mahara/mahara/commit/b49a1353cafdb635bac19fedfa88de63b5911dbb

http://gitorious.org/mahara/mahara/commit/e825502db66d93ddcbd58123876e503c0c850ee0

I suspect they won't apply cleanly to 1.4, though it probably wouldn't be too hard to pick out the relevant bits.

20 October 2011, 0:04

I should also mention that when I was looking at those slow queries, I noticed that most of the slowness was due to missing indexes.  For some reason our site did not have indexes on the "group" and "institution" columns of the "artefact" and "view" tables.

I don't know how that happened, and at the time I actually assumed it was because we have such an old site, and perhaps those indexes were never added when the columns were initially introduced.

But it would be worth checking for those indexes on your database in case there's some bug in an upgrade that removes them.

20 October 2011, 5:14

I should have said I'd already checked that the indexes exist and optimized the tables, that hadn't helped. And when I said it had suddenly got slower, we were told it had suddenly got slower, but this is a place where somebody can take months to get around to reporting that they can't log in. So it may have just happened, or may have been over a period of months.

I haven't incorporated the placeholder changes, wasn't sure of any knock on effects, but I've used the other two and it appears to have made a huge difference. It's now taking seconds instead of minutes to open the edit boxes. I need to do a bit more testing, but that appears to have done the trick. Thank you! 

Has this gone into 1.4.1, or just 1.5? Only asking so I know whether to copy the changes when we next upgrade.

20 October 2011, 17:05

It will be in 1.5 but not in 1.4.

Cheers,

Francois