mahara-contributors team mailing list archive

[Aaron Wells <1515053@xxxxxxxxxxxxxxxxxx>]

Public bug reported:

From:
https://mahara.org/interaction/forum/topic.php?id=7440&offset=0&limit=10#post29907

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.

** Affects: mahara
     Importance: Medium
         Status: Confirmed


** Tags: performance

-- 
You received this bug notification because you are a member of Mahara
Contributors, which is subscribed to Mahara.
Matching subscriptions: Subscription for all Mahara Contributors -- please ask on #mahara-dev or mahara.org forum before editing or unsubscribing it!
https://bugs.launchpad.net/bugs/1515053

Title:
  Refactor the query in get_artefactchooser_artefacts to use "Union"s
  instead of "Or"s

Status in Mahara:
  Confirmed

Bug description:
  From:
  https://mahara.org/interaction/forum/topic.php?id=7440&offset=0&limit=10#post29907

  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.

To manage notifications about this bug go to:
https://bugs.launchpad.net/mahara/+bug/1515053/+subscriptions