Просмотрел логи за пол дня: в 10 сек попадают только следующее
Но т.к. нет ни одного запроса, который обращался бы к mdl_course_categories, то проблема не в продолжительности одного запроса.
Есть ли смысл long_query_time=5 или искать в другом направлении?
1) Связан с модулем проверки посещаемости
# Query_time: 19.098909 Lock_time: 0.000400 Rows_sent: 6 Rows_examined: 10775
# Rows_affected: 0 Bytes_sent: 6859
SET timestamp=1548248749;
SELECT u.* FROM mdl_user u JOIN (SELECT DISTINCT eu3_u.id
FROM mdl_user eu3_u
JOIN mdl_user_enrolments ej3_ue ON ej3_ue.userid = eu3_u.id
JOIN mdl_enrol ej3_e ON (ej3_e.id = ej3_ue.enrolid AND ej3_e.courseid = '1567')
JOIN mdl_role_assignments eu3_ra3
ON (eu3_ra3.userid = eu3_u.id
AND eu3_ra3.roleid IN (5,12)
AND eu3_ra3.contextid IN (1,39849,39851,39863,8447,33690))
WHERE 1 = 1 AND (eu3_u.id <> '1') AND eu3_u.deleted = 0) je ON je.id = u.id
WHERE u.lastaccess + ('10' * 60) < '1548248730'
AND ( NOT EXISTS (SELECT * FROM mdl_attendanceregister_session as3
WHERE as3.userid = u.id AND as3.register = '4' AND as3.onlinesess = 1)
OR NOT EXISTS (SELECT * FROM mdl_attendanceregister_aggregate aa4 WHERE aa4.userid=u.id AND aa4.register='4' AND aa4.grandtotal = 1 )
OR EXISTS (SELECT * FROM mdl_attendanceregister_aggregate aa2, mdl_logstore_standard_log l2
WHERE aa2.userid = u.id AND aa2.register = '4'
AND l2.courseid = '1567' AND l2.userid = aa2.userid
AND aa2.grandtotal = 1
AND l2.timecreated > aa2.lastsessionlogout) );
2) Не понимаю, с чем связан
# Query_time: 11.424725 Lock_time: 0.000065 Rows_sent: 0 Rows_examined: 29458
# Rows_affected: 0 Bytes_sent: 400
SET timestamp=1548246910;
SELECT mq.id quizid, mqa.id quiattemptid, mqa.userid, mq.course, mqa.uniqueid,
qna.id questionattemptid
FROM mdl_quiz mq
JOIN mdl_quiz_attempts mqa ON mqa.quiz = mq.id
JOIN mdl_question_attempts qna ON qna.questionusageid = mqa.uniqueid
JOIN mdl_user mu ON mu.id = mqa.userid
WHERE course = '10554'
AND behaviour = 'manualgraded'
AND mu.deleted = 0;