10 November 2015, 22:32

Hello,

We have a mahara install with about 40k users (200/11k daily/all-time active) and are facing performance issues which we think are related to the nature of the queries created by mahara.

Database size: 544.8MB
Disk usage: 50.3GB
Mahara version: 1.10.2


Typical slow query :

# User@Host: maharauser[maharauser] @ [172.x.x.x]
# Query_time: 5.269038 Lock_time: 0.000127 Rows_sent: 6 Rows_examined: 684791
SET timestamp=1445254360;
SELECT a.*, (a.owner IS NOT NULL AND a.owner = '17792') AS editable FROM "artefact" a WHERE (
a.owner = '17792'
OR a.id IN (
SELECT id
FROM "artefact"
WHERE (path = '/17' OR path LIKE '/17/%') AND institution = 'mahara'
)
OR a.id IN (
SELECT aar.artefact
FROM "group_member" m
JOIN "artefact" aa ON m.group = aa.group
JOIN "artefact_access_role" aar ON aar.role = m.role AND aar.artefact = aa.id
WHERE m.member = '17792' AND aar.can_republish = 1
)
OR a.id IN (SELECT artefact FROM "artefact_access_usr" WHERE usr = '17792' AND can_republish = 1)
OR a.institution IN ('exampleuniversity')
) AND artefacttype IN('firstname','lastname','studentid','preferredname','introduction','officialwebsite','personalwebsite','blogadd
ress','address','town','city','country','homenumber','businessnumber','mobilenumber','faxnumber','occupation','industry','socialprofile') LIMIT
655360;


our dba, after considering the usual optimization cache/buffer size and re-indexing techniques has suggested an equivalent query :


select a.*,
      (a.owner IS NOT NULL AND a.owner = '17792') AS editable
from (SELECT a.*
      FROM   artefact a
      WHERE  a.owner = '17792'
      union
      SELECT a.*
      FROM   artefact a join (SELECT id
                              FROM   artefact
                              WHERE  (path = '/17' OR path LIKE '/17/%')
                              AND institution = 'mahara'
                              union
                              SELECT aar.artefact
                              FROM   group_member m JOIN artefact aa
                                       ON  m.group = aa.group
                                     JOIN artefact_access_role aar
                                       ON  aar.role = m.role
                                       AND aar.artefact = aa.id
                              WHERE  m.member            = '17792'
                              AND    aar.can_republish = 1
                              union
                              SELECT artefact
                              FROM   artefact_access_usr
                              WHERE  usr  = '17792'
                              AND    can_republish = 1) b
               on a.id = b.id
      union
      SELECT a.*
      FROM   artefact a
      where  a.institution IN ('exampleuniversity')) a
where  artefacttype IN('firstname','lastname','studentid','preferredname',
                       'introduction','officialwebsite','personalwebsite','blogaddress','address',
                       'town','city','country','homenumber','businessnumber','mobilenumber',
                       'faxnumber','occupation','industry','socialprofile') limit 655360;

which takes 0.263 seconds. The mysql database version we are running on is Server version: 5.1.73

We have replicated on Server version: 5.5.41-MariaDB on the same dataset. Although the gains were not as dramatic from 1.2 seconds to 0.1 seconds. Even so total page load times are an important usability consideration.

Is this improved in 15.10.x ? or any chance in fixing this in 1.10.2

Regards,

Tom

12 November 2015, 12:25

Hi Fabio,

Thanks for the analysis! Mahara's speed is definitely something we'd like to work on. It looks like this query in particular is generated by the get_artefactchooser_artefacts() method in htdocs/lib/view.php, and as of Mahara 16.04dev it's still present in its original form:

https://github.com/MaharaProject/mahara/blob/master/htdocs/lib/view.php#L3289

Unfortunately it's a difficult query to refactor, because it is generated dynamically based on the parameters passed to it. But I've filed a launchpad tracker bug with your revised query, so we'll see if anyone can reorganize the function to produce something more in line with that: https://bugs.launchpad.net/mahara/+bug/1515053

Or if you've got some PHP development resources in your organization, and you'd like to crack at it, we'd gladly welcome your patch. :)

Cheers,

Aaron