Uploaded image for project: 'Plugins'
  1. Plugins
  2. CONTRIB-2001

Wiki changes/New pages page takes a long time to load

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 1.9.2
    • Fix Version/s: None
    • Component/s: None
    • Labels:
    • Environment:
      Moodle 1.9.2, MySQL 5.0.77, PHP 5.1.6, Apache 2.2.3, Centos 5 on VMWare
    • Database:
      MySQL
    • Affected Branches:
      MOODLE_19_STABLE

      Description

      If a user views a wiki and clicks on "Wiki changes" link and then the "New pages" tab, the page takes a long time to load. At the same time, the server load goes up (with most of the CPU time spent on MySQL) and so do the numbers of MySQL and Apache processes. We have tracked down the problem to a query inside the ouwiki_get_subwiki_recentpages function in mod/ouwiki/ouwiki.php. (I believe the function has been move to locallib.php in later versions of Moodle.) The query looks like this:

      SELECT p.id AS pageid, p.subwikiid, p.title, p.currentversionid, v.id AS versionid, v.timecreated, v.userid, u.firstname, u.lastname, u.username
      FROM mdl_ouwiki_versions v
      INNER JOIN mdl_ouwiki_pages p ON v.pageid = p.id
      LEFT JOIN mdl_user u ON v.userid = u.id
      WHERE v.id
      IN (
        SELECT MIN( v2.id )
        FROM mdl_ouwiki_pages p2
        INNER JOIN mdl_ouwiki_versions v2 ON v2.pageid = p2.id
        WHERE p2.subwikiid =790
        GROUP BY p2.id
      )
      ORDER BY v.id DESC
      LIMIT 51

      The query looks the same in the latest version of OU wiki in CVS. The MySQL EXPLAIN looks like this:

      id select_type table type possible_keys key key_len ref rows Extra
      1 PRIMARY v index mdl_ouwivers_pag_ix PRIMARY 8 NULL 11483 Using where
      1 PRIMARY p eq_ref PRIMARY PRIMARY 8 moodle.v.pageid 1
      1 PRIMARY u eq_ref PRIMARY PRIMARY 8 moodle.v.userid 1
      2 DEPENDENT SUBQUERY p2 index PRIMARY PRIMARY 8 NULL 3027 Using where; Using filesort
      2 DEPENDENT SUBQUERY v2 ref mdl_ouwivers_pag_ix mdl_ouwivers_pag_ix 8 moodle.p2.id 12

      11483 is the number of records in our mdl_ouwiki_versions table, so MySQL is effectively doing a table scan on the table, and this explains the high load.

      Has anyone else come across this problem?

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            wkchan Kai Chan
            Participants:
            Component watchers:
            Votes:
            1 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated: