From 5d911452cd9ef60f8f79ed9bd39fc8302211a96c Mon Sep 17 00:00:00 2001 From: Tyler Bannister Date: Fri, 11 May 2012 15:02:56 -0400 Subject: [PATCH] MDL-30643 - Fixed speed issues with stats generation. --- lib/statslib.php | 240 ++++++++++++++++++++++++++++------------------- 1 file changed, 143 insertions(+), 97 deletions(-) diff --git a/lib/statslib.php b/lib/statslib.php index 6b2220fbe25..936bb465743 100644 --- a/lib/statslib.php +++ b/lib/statslib.php @@ -80,12 +80,12 @@ function stats_daily_progress($ident) { static $init = 0; if ($ident == 'init') { - $init = $start = time(); + $init = $start = microtime(true); return; } - $elapsed = time() - $start; - $start = time(); + $elapsed = round(microtime(true) - $start); + $start = microtime(true); if (debugging('', DEBUG_ALL)) { mtrace("$ident:$elapsed ", ''); @@ -176,29 +176,27 @@ function stats_cron_daily($maxdays=1) { $daystart = time(); - $timesql = "l.time >= $timestart AND l.time < $nextmidnight"; - $timesql1 = "l1.time >= $timestart AND l1.time < $nextmidnight"; - $timesql2 = "l2.time >= $timestart AND l2.time < $nextmidnight"; - stats_daily_progress('init'); + if ( !stats_create_logs_view($timestart, $nextmidnight) ) { + $failed = true; + break; + } + + stats_daily_progress('gen'); + /// find out if any logs available for this day - $sql = "SELECT 'x' - FROM {log} l - WHERE $timesql"; + $sql = "SELECT 'x' FROM {tmp_stats_log1} l"; $logspresent = $DB->get_records_sql($sql, null, 0, 1); /// process login info first $sql = "INSERT INTO {stats_user_daily} (stattype, timeend, courseid, userid, statsreads) - SELECT 'logins', timeend, courseid, userid, count(statsreads) - FROM ( - SELECT $nextmidnight AS timeend, ".SITEID." AS courseid, l.userid, l.id AS statsreads - FROM {log} l - WHERE action = 'login' AND $timesql - ) inline_view - GROUP BY timeend, courseid, userid + SELECT 'logins', $nextmidnight AS timeend, ".SITEID." AS courseid, userid, count(id) as statsreads + FROM {tmp_stats_log1} l + WHERE action = 'login' + GROUP BY timeend, courseid, userid HAVING count(statsreads) > 0"; if ($logspresent and !$DB->execute($sql)) { @@ -210,13 +208,9 @@ function stats_cron_daily($maxdays=1) { $sql = "INSERT INTO {stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2) SELECT 'logins' AS stattype, $nextmidnight AS timeend, ".SITEID." as courseid, 0, - COALESCE((SELECT SUM(statsreads) - FROM {stats_user_daily} s1 - WHERE s1.stattype = 'logins' AND timeend = $nextmidnight), 0) AS stat1, - (SELECT COUNT('x') - FROM {stats_user_daily} s2 - WHERE s2.stattype = 'logins' AND timeend = $nextmidnight) AS stat2" . - $DB->sql_null_from_clause(); + COALESCE(SUM(statsreads), 0) as stat1, COUNT('x') as stat2 + FROM {stats_user_daily} + WHERE stattype = 'logins' AND timeend = $nextmidnight"; if ($logspresent and !$DB->execute($sql)) { $failed = true; @@ -237,15 +231,13 @@ function stats_cron_daily($maxdays=1) { $sql = "INSERT INTO {stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2) - SELECT 'enrolments', timeend, courseid, roleid, COUNT(DISTINCT userid), 0 - FROM ( - SELECT $nextmidnight AS timeend, e.courseid, ra.roleid, ue.userid - FROM {role_assignments} ra - JOIN {context} c ON (c.id = ra.contextid AND c.contextlevel = :courselevel) - JOIN {enrol} e ON e.courseid = c.instanceid - JOIN {user_enrolments} ue ON (ue.enrolid = e.id AND ue.userid = ra.userid) - ) inline_view - GROUP BY timeend, courseid, roleid"; + SELECT 'enrolments' as stattype, $nextmidnight as timeend, e.courseid, ra.roleid, + COUNT(DISTINCT ue.userid) as stat1, 0 as stat2 + FROM {role_assignments} ra + JOIN {context} c ON (c.id = ra.contextid AND c.contextlevel = :courselevel) + JOIN {enrol} e ON e.courseid = c.instanceid + JOIN {user_enrolments} ue ON (ue.enrolid = e.id AND ue.userid = ra.userid) + GROUP BY courseid, roleid"; if (!$DB->execute($sql, array('courselevel'=>CONTEXT_COURSE))) { $failed = true; @@ -263,15 +255,14 @@ function stats_cron_daily($maxdays=1) { WHERE ra.roleid = {stats_daily}.roleid AND e.courseid = {stats_daily}.courseid AND EXISTS (SELECT 'x' - FROM {log} l + FROM {tmp_stats_log1} l WHERE l.course = {stats_daily}.courseid AND - l.userid = ra.userid AND $timesql)) + l.userid = ra.userid)) WHERE {stats_daily}.stattype = 'enrolments' AND {stats_daily}.timeend = $nextmidnight AND {stats_daily}.courseid IN - (SELECT DISTINCT l.course - FROM {log} l - WHERE $timesql)"; + (SELECT DISTINCT course + FROM {tmp_stats_log2})"; if (!$DB->execute($sql, array('courselevel'=>CONTEXT_COURSE))) { $failed = true; @@ -282,13 +273,11 @@ function stats_cron_daily($maxdays=1) { /// now get course total enrolments (roleid==0) - except frontpage $sql = "INSERT INTO {stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2) - SELECT 'enrolments', timeend, id, nroleid, COUNT(DISTINCT userid), 0 - FROM ( - SELECT $nextmidnight AS timeend, e.courseid AS id, 0 AS nroleid, ue.userid - FROM {enrol} e - JOIN {user_enrolments} ue ON ue.enrolid = e.id - ) inline_view - GROUP BY timeend, id, nroleid + SELECT 'enrolments', $nextmidnight AS timeend, e.courseid AS courseid, 0 AS roleid, + COUNT(DISTINCT userid) AS stat1, 0 AS stat2 + FROM {enrol} e + JOIN {user_enrolments} ue ON ue.enrolid = e.id + GROUP BY courseid HAVING COUNT(DISTINCT userid) > 0"; if ($logspresent and !$DB->execute($sql)) { @@ -303,16 +292,16 @@ function stats_cron_daily($maxdays=1) { JOIN {user_enrolments} ue ON ue.enrolid = e.id WHERE e.courseid = {stats_daily}.courseid AND EXISTS (SELECT 'x' - FROM {log} l + FROM {tmp_stats_log1} l WHERE l.course = {stats_daily}.courseid AND - l.userid = ue.userid AND $timesql)) + l.userid = ue.userid)) WHERE {stats_daily}.stattype = 'enrolments' AND {stats_daily}.timeend = $nextmidnight AND {stats_daily}.roleid = 0 AND {stats_daily}.courseid IN (SELECT l.course - FROM {log} l - WHERE $timesql AND l.course <> ".SITEID.")"; + FROM {tmp_stats_log2} l + WHERE l.course <> ".SITEID.")"; if ($logspresent and !$DB->execute($sql, array())) { $failed = true; @@ -320,7 +309,7 @@ function stats_cron_daily($maxdays=1) { } stats_daily_progress('6'); - /// frontapge(==site) enrolments total + /// frontpage(==site) enrolments total $sql = "INSERT INTO {stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2) SELECT 'enrolments', $nextmidnight, ".SITEID.", 0, @@ -329,8 +318,8 @@ function stats_cron_daily($maxdays=1) { WHERE u.deleted = 0) AS stat1, (SELECT COUNT(DISTINCT u.id) FROM {user} u - JOIN {log} l ON l.userid = u.id - WHERE u.deleted = 0 AND $timesql) AS stat2" . + JOIN {tmp_stats_log1} l ON l.userid = u.id + WHERE u.deleted = 0) AS stat2" . $DB->sql_null_from_clause(); if ($logspresent and !$DB->execute($sql)) { @@ -360,8 +349,8 @@ function stats_cron_daily($maxdays=1) { WHERE u.deleted = 0) AS stat1, (SELECT COUNT(DISTINCT u.id) FROM {user} u - JOIN {log} l ON l.userid = u.id - WHERE u.deleted = 0 AND $timesql) AS stat2" . + JOIN {tmp_stats_log1} l ON l.userid = u.id + WHERE u.deleted = 0) AS stat2" . $DB->sql_null_from_clause();; if ($logspresent and !$DB->execute($sql)) { @@ -376,29 +365,17 @@ function stats_cron_daily($maxdays=1) { } - /// individual user stats (including not-logged-in) in each course, this is slow - reuse this data if possible list($viewactionssql, $params1) = $DB->get_in_or_equal($viewactions, SQL_PARAMS_NAMED, 'view'); list($postactionssql, $params2) = $DB->get_in_or_equal($postactions, SQL_PARAMS_NAMED, 'post'); $sql = "INSERT INTO {stats_user_daily} (stattype, timeend, courseid, userid, statsreads, statswrites) - SELECT 'activity' AS stattype, $nextmidnight AS timeend, d.courseid, d.userid, - (SELECT COUNT('x') - FROM {log} l - WHERE l.userid = d.userid AND - l.course = d.courseid AND $timesql AND - l.action $viewactionssql) AS statsreads, - (SELECT COUNT('x') - FROM {log} l - WHERE l.userid = d.userid AND - l.course = d.courseid AND $timesql AND - l.action $postactionssql) AS statswrites - FROM (SELECT DISTINCT u.id AS userid, l.course AS courseid - FROM {user} u, {log} l - WHERE u.id = l.userid AND $timesql - UNION - SELECT 0 AS userid, ".SITEID." AS courseid" . $DB->sql_null_from_clause() . ") d"; - // can not use group by here because pg can not handle it :-( + SELECT 'activity' AS stattype, $nextmidnight AS timeend, course as courseid, userid, + SUM(CASE WHEN action $viewactionssql THEN 1 ELSE 0 END) AS statsreads, + SUM(CASE WHEN action $postactionssql THEN 1 ELSE 0 END) AS statswrites + FROM {tmp_stats_log1} l + WHERE !(course = 0 AND userid = 0) + GROUP BY userid, courseid"; if ($logspresent and !$DB->execute($sql, array_merge($params1, $params2))) { $failed = true; @@ -411,18 +388,11 @@ function stats_cron_daily($maxdays=1) { $sql = "INSERT INTO {stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2) SELECT 'activity' AS stattype, $nextmidnight AS timeend, c.id AS courseid, 0, - (SELECT COUNT('x') - FROM {log} l1 - WHERE l1.course = c.id AND l1.action $viewactionssql AND - $timesql1) AS stat1, - (SELECT COUNT('x') - FROM {log} l2 - WHERE l2.course = c.id AND l2.action $postactionssql AND - $timesql2) AS stat2 - FROM {course} c - WHERE EXISTS (SELECT 'x' - FROM {log} l - WHERE l.course = c.id and $timesql)"; + SUM(CASE WHEN l.action $viewactionssql THEN 1 ELSE 0 END) AS stat1, + SUM(CASE WHEN l.action $postactionssql THEN 1 ELSE 0 END) AS stat2 + FROM {course} c, {tmp_stats_log1} l + WHERE l.course = c.id + GROUP BY courseid"; if ($logspresent and !$DB->execute($sql, array_merge($params1, $params2))) { $failed = true; @@ -435,9 +405,9 @@ function stats_cron_daily($maxdays=1) { $sql = "INSERT INTO {stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2) - SELECT 'activity', timeend, courseid, roleid, SUM(statsreads), SUM(statswrites) + SELECT 'activity', $nextmidnight AS timeend, courseid, roleid, SUM(statsreads), SUM(statswrites) FROM ( - SELECT $nextmidnight AS timeend, pl.courseid, pl.roleid, sud.statsreads, sud.statswrites + SELECT pl.courseid, pl.roleid, sud.statsreads, sud.statswrites FROM {stats_user_daily} sud, (SELECT DISTINCT ra.userid, ra.roleid, e.courseid FROM {role_assignments} ra @@ -466,9 +436,10 @@ function stats_cron_daily($maxdays=1) { $sql = "INSERT INTO {stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2) - SELECT 'activity', timeend, courseid, nroleid, SUM(statsreads), SUM(statswrites) + SELECT 'activity', $nextmidnight as timeend, courseid, $guestrole AS roleid, + SUM(statsreads), SUM(statswrites) FROM ( - SELECT $nextmidnight AS timeend, sud.courseid, $guestrole AS nroleid, sud.statsreads, sud.statswrites + SELECT sud.courseid, sud.statsreads, sud.statswrites FROM {stats_user_daily} sud WHERE sud.timeend = $nextmidnight AND sud.courseid <> ".SITEID." AND sud.stattype='activity' AND @@ -478,7 +449,7 @@ function stats_cron_daily($maxdays=1) { JOIN {enrol} e ON ue.enrolid = e.id WHERE e.courseid = sud.courseid)) ) inline_view - GROUP BY timeend, courseid, nroleid + GROUP BY timeend, courseid, roleid HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0"; if ($logspresent and !$DB->execute($sql, array())) { @@ -491,9 +462,10 @@ function stats_cron_daily($maxdays=1) { /// how many view actions for each role on frontpage - excluding guests, not-logged-in and default frontpage role $sql = "INSERT INTO {stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2) - SELECT 'activity', timeend, courseid, roleid, SUM(statsreads), SUM(statswrites) + SELECT 'activity', $nextmidnight AS timeend, courseid, roleid, + SUM(statsreads), SUM(statswrites) FROM ( - SELECT $nextmidnight AS timeend, pl.courseid, pl.roleid, sud.statsreads, sud.statswrites + SELECT pl.courseid, pl.roleid, sud.statsreads, sud.statswrites FROM {stats_user_daily} sud, (SELECT DISTINCT ra.userid, ra.roleid, c.instanceid AS courseid FROM {role_assignments} ra @@ -521,9 +493,10 @@ function stats_cron_daily($maxdays=1) { /// how many view actions for default frontpage role on frontpage only $sql = "INSERT INTO {stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2) - SELECT 'activity', timeend, courseid, nroleid, SUM(statsreads), SUM(statswrites) + SELECT 'activity', timeend, courseid, $defaultfproleid AS roleid, + SUM(statsreads), SUM(statswrites) FROM ( - SELECT sud.timeend AS timeend, sud.courseid, $defaultfproleid AS nroleid, sud.statsreads, sud.statswrites + SELECT sud.timeend AS timeend, sud.courseid, sud.statsreads, sud.statswrites FROM {stats_user_daily} sud WHERE sud.timeend = :nextm AND sud.courseid = :siteid AND sud.stattype='activity' AND @@ -533,7 +506,7 @@ function stats_cron_daily($maxdays=1) { WHERE ra.roleid <> $guestrole AND ra.roleid <> $defaultfproleid AND ra.contextid = :fpcontext) ) inline_view - GROUP BY timeend, courseid, nroleid + GROUP BY timeend, courseid, roleid HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0"; if ($logspresent and !$DB->execute($sql, array('fpcontext'=>$fpcontext->id, 'siteid'=>SITEID, 'nextm'=>$nextmidnight))) { @@ -545,9 +518,10 @@ function stats_cron_daily($maxdays=1) { /// how many view actions for guests or not-logged-in on frontpage $sql = "INSERT INTO {stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2) - SELECT 'activity', timeend, courseid, nroleid, SUM(statsreads), SUM(statswrites) + SELECT 'activity', $nextmidnight AS timeend, ".SITEID." AS courseid, $guestrole AS roleid, + SUM(statsreads), SUM(statswrites) FROM ( - SELECT $nextmidnight AS timeend, ".SITEID." AS courseid, $guestrole AS nroleid, pl.statsreads, pl.statswrites + SELECT pl.statsreads, pl.statswrites FROM ( SELECT sud.statsreads, sud.statswrites FROM {stats_user_daily} sud @@ -556,7 +530,7 @@ function stats_cron_daily($maxdays=1) { sud.stattype='activity' ) pl ) inline_view - GROUP BY timeend, courseid, nroleid + GROUP BY timeend, courseid, roleid HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0"; if ($logspresent and !$DB->execute($sql)) { @@ -573,6 +547,8 @@ function stats_cron_daily($maxdays=1) { $nextmidnight = stats_get_next_day_start($nextmidnight); } + stats_drop_logs_view(); + set_cron_lock('statsrunning', null); if ($failed) { @@ -1462,3 +1438,73 @@ function stats_check_uptodate($courseid=0) { //return error as string return get_string('statscatchupmode','error',$a); } + +/** + * Creates tmp log table + * + * @param timestart timestamp of the start time of logs view + * @param timeend timestamp of the end time of logs view + * @returns boolen success (true) or failure(false) + */ +function stats_create_logs_view ( $timestart, $timeend ) { + global $CFG, $DB; + + $dbman = $DB->get_manager(); // We are going to use database_manager services + + stats_drop_logs_view(); + + $table = new xmldb_table('tmp_stats_log1'); + + $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null); + $table->add_field('userid', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null); + $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null); + $table->add_field('action', XMLDB_TYPE_CHAR, '40', null, XMLDB_NOTNULL, null, null); + + $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); + $table->add_index('tmp_stats_log_course_ix', XMLDB_INDEX_NOTUNIQUE, array('course')); + $table->add_index('tmp_stats_log_act_ix', XMLDB_INDEX_NOTUNIQUE, array('action')); + $table->add_index('tmp_stats_log_user_ix', XMLDB_INDEX_NOTUNIQUE, array('userid')); + $table->add_index('tmp_stats_log_usecouact_ix', XMLDB_INDEX_NOTUNIQUE, array('userid','course','action')); + + $dbman->create_temp_table($table); + + $table->name = 'tmp_stats_log2'; + + $dbman->create_temp_table($table); + + $sql = "INSERT INTO {tmp_stats_log1} + SELECT id, userid, course, action FROM {log} l + WHERE l.time >= $timestart AND l.time < $timeend"; + + if (!$DB->execute($sql)) { + return false; + } + + $sql = "INSERT INTO {tmp_stats_log2} + SELECT * FROM {tmp_stats_log1}"; + + if (!$DB->execute($sql)) { + return false; + } + + return true; +} + +/** + * Deletes summary logs table for stats calculation + */ +function stats_drop_logs_view() { + global $DB; + + $dbman = $DB->get_manager(); + + if ( $dbman->table_exists('tmp_stats_log1') ) { + $table = new xmldb_table('tmp_stats_log1'); + $dbman->drop_temp_table($table); + } + + if ( $dbman->table_exists('tmp_stats_log2') ) { + $table = new xmldb_table('tmp_stats_log2'); + $dbman->drop_temp_table($table); + } +} -- 2.43.0