Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-8138

Long Query on Login - takes up to 20 seconds for user to log in.

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Duplicate
    • Affects Version/s: 1.7
    • Fix Version/s: None
    • Component/s: Authentication
    • Labels:
      None
    • Environment:
      FreeBSD, PHP 4.4.2, MySQL 4.1.18. Moodle 1.7+ (2006101009)
    • Database:
      MySQL
    • Affected Branches:
      MOODLE_17_STABLE

      Description

      I am getting a lot (7 in the last 5 hours) of slow queries.

      I have upgraded to the latest version of 1.7 and saw the reports in Moodle.org about the database missing some of the indexes. I compared my database with the one created with a new install and they are now identical.

      This has been going on since the upgrade.

      I have been monitoring server performance to see if that was an issue and it does not seem to be related to limited resources at the server.

      All the queries are similar to this one with ranges of 14 to 22 seconds.

      1. Time: 070110 12:23:13
      2. User@Host: ioc_moodleuser[ioc_moodleuser] @ localhost []
      3. Query_time: 20 Lock_time: 0 Rows_sent: 78162 Rows_examined: 391167
        SELECT rc.capability, c1.id as id1, c2.id as id2, (c1.contextlevel * 100 + c2.contextlevel) AS aggrlevel,
        rc.permission AS sum
        FROM
        role_assignments ra,
        role_capabilities rc,
        context c1,
        context c2
        WHERE
        ra.contextid=c1.id AND
        ra.roleid=rc.roleid AND
        ra.userid=95 AND
        rc.contextid=c2.id AND
        c1.id IN (127,7,5,2,4,117,169) AND
        rc.contextid != 1

      AND ((ra.timestart = 0 OR ra.timestart < 1168453373) AND (ra.timeend = 0 OR ra.timeend > 1168453373))

      GROUP BY
      rc.capability, (c1.contextlevel * 100 + c2.contextlevel), c1.id, c2.id, rc.permission
      ORDER BY
      aggrlevel ASC;

      Let me know if there is anything else I can do to help with this.

        Attachments

          Issue Links

            Activity

              People

              • Votes:
                1 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: