Bonjour,
La suppression du cours 441 a éliminé un problème, mais tout n'est pas fonctionnel ou optimal pour autant. Du coup, j'ai poursuivi mes recherches, en lançant quelques requêtes supplémentaires.
Liste des cours ayant un très grand nombre de logs :
SELECT courseid AS Cours, COUNT(1) AS Nb
FROM mdl_logstore_standard_log
GROUP BY courseid
ORDER BY Nb DESC
LIMIT 10;
+-------+----------+
| Cours | Nb |
+-------+----------+
| 0 | 13655627 |
| 441 | 3511715 |
| 275 | 2865744 |
| 327 | 1208524 |
| 301 | 722974 |
| 1463 | 436024 |
| 1255 | 431024 |
| 1461 | 429824 |
| 1464 | 428740 |
| 1468 | 427812 |
+-------+----------+
Répartition de ces logs pour ces cours :
SELECT courseid, eventname, COUNT(1) AS Nb
FROM mdl_logstore_standard_log
WHERE courseid IN (275, 327, 301, 1463, 1255, 1461, 1464, 1468)
AND component='core'
GROUP BY courseid, eventname
ORDER BY Nb DESC;
+----------+------------------------------------------+---------+
| courseid | eventname |
Nb |
+----------+------------------------------------------+---------+
| 275 | \core\event\user_enrolment_deleted |
1411756 |
| 275 | \core\event\user_enrolment_created |
1410710 |
| 301 | \core\event\course_viewed |
708145 |
| 327 | \core\event\user_enrolment_created |
665351 |
| 327 | \core\event\user_enrolment_deleted |
665320 |
| 1464 | \core\event\user_enrolment_deleted |
227621 |
| 1468 | \core\event\user_enrolment_deleted |
227620 |
| 1463 | \core\event\user_enrolment_deleted |
227617 |
| 1464 | \core\event\user_enrolment_created |
227587 |
| 1468 | \core\event\user_enrolment_created |
227586 |
| 1461 | \core\event\user_enrolment_created |
227586 |
| 1463 | \core\event\user_enrolment_created |
227584 |
| 1461 | \core\event\user_enrolment_deleted |
227582 |
| 1255 | \core\event\user_enrolment_created |
227573 |
| 1255 | \core\event\user_enrolment_deleted |
226998 |
+----------+------------------------------------------+---------+
Constatant que c'est toujours les mêmes éléments qui reviennent, je recherche tous les cours qui ont
manifestement ce problème de grand nombre
de log d'inscription/désinscriptions :
SELECT courseid, eventname, COUNT(1) AS Nb
FROM mdl_logstore_standard_log
WHERE component='core'
AND (eventname = '\\core\\event\\user_enrolment_created'
OR eventname = '\\core\\event\\user_enrolment_deleted')
GROUP BY courseid, eventname
HAVING Nb > 10000
ORDER BY Nb DESC;
+----------+------------------------------------+---------+
| courseid | eventname | Nb |
+----------+------------------------------------+---------+
| 441 | \core\event\user_enrolment_created | 1670712 |
| 441 | \core\event\user_enrolment_deleted | 1669610 |
| 275 | \core\event\user_enrolment_deleted | 1411756 |
| 275 | \core\event\user_enrolment_created | 1410710 |
| 327 | \core\event\user_enrolment_created | 666552 |
| 327 | \core\event\user_enrolment_deleted | 666520 |
| 1467 | \core\event\user_enrolment_deleted | 227939 |
| 1464 | \core\event\user_enrolment_deleted | 227939 |
| 1471 | \core\event\user_enrolment_deleted | 227938 |
| 1465 | \core\event\user_enrolment_deleted | 227938 |
| 1468 | \core\event\user_enrolment_deleted | 227938 |
| 1469 | \core\event\user_enrolment_deleted | 227937 |
| 1463 | \core\event\user_enrolment_deleted | 227935 |
| 1467 | \core\event\user_enrolment_created | 227907 |
| 1464 | \core\event\user_enrolment_created | 227905 |
| 1465 | \core\event\user_enrolment_created | 227905 |
| 1471 | \core\event\user_enrolment_created | 227905 |
| 1468 | \core\event\user_enrolment_created | 227904 |
| 1461 | \core\event\user_enrolment_created | 227904 |
| 1469 | \core\event\user_enrolment_created | 227904 |
| 1463 | \core\event\user_enrolment_created | 227902 |
| 1461 | \core\event\user_enrolment_deleted | 227900 |
| 1255 | \core\event\user_enrolment_created | 227891 |
| 1466 | \core\event\user_enrolment_deleted | 227599 |
| 1466 | \core\event\user_enrolment_created | 227566 |
| 1255 | \core\event\user_enrolment_deleted | 227316 |
| 1077 | \core\event\user_enrolment_created | 227267 |
| 1146 | \core\event\user_enrolment_deleted | 227000 |
| 1077 | \core\event\user_enrolment_deleted | 226998 |
| 448 | \core\event\user_enrolment_deleted | 226995 |
| 449 | \core\event\user_enrolment_deleted | 226993 |
| 917 | \core\event\user_enrolment_deleted | 226992 |
| 1146 | \core\event\user_enrolment_created | 226967 |
| 448 | \core\event\user_enrolment_created | 226962 |
| 449 | \core\event\user_enrolment_created | 226960 |
| 917 | \core\event\user_enrolment_created | 226960 |
| 2979 | \core\event\user_enrolment_created | 142790 |
| 2979 | \core\event\user_enrolment_deleted | 142772 |
+----------+------------------------------------+---------+
38 rows in set (6 min 2.26 sec)
J'ai donc maintenant la liste de tous les cours qui présentent ce problème.
Et pour connaitre leurs dernières dates représentant beaucoup de ces actions :
SELECT FROM_UNIXTIME(timecreated) AS Date, courseid, eventname AS Event, COUNT(1) AS Nb
FROM mdl_logstore_standard_log
WHERE courseid IN (275, 327, 1467, 1464, 1471, 1465, 1468, 1469, 1463, 1461, 1255, 1466, 1077, 1146, 448, 449, 917, 2979)
AND component='core'
AND (eventname = '\\core\\event\\user_enrolment_created'
OR eventname = '\\core\\event\\user_enrolment_deleted')
GROUP BY Date, courseid, event
HAVING Nb > 100
ORDER BY Date DESC
LIMIT 100;
+---------------------+----------+------------------------------------+-----+
| Date | courseid |
Event | Nb |
+---------------------+----------+------------------------------------+-----+
| 2015-11-16 15:17:56 | 448 |
\core\event\user_enrolment_deleted | 172 |
| 2015-11-16 15:17:54 | 917 |
\core\event\user_enrolment_deleted | 177 |
| 2015-11-16 15:17:52 | 1255 |
\core\event\user_enrolment_deleted | 104 |
| 2015-11-16 15:17:51 | 1255 |
\core\event\user_enrolment_deleted | 119 |
| 2015-11-16 15:17:42 | 1464 |
\core\event\user_enrolment_deleted | 143 |
| 2015-11-16 15:17:41 | 1464 |
\core\event\user_enrolment_deleted | 116 |
| 2015-11-16 15:17:15 | 1077 |
\core\event\user_enrolment_deleted | 177 |
| 2015-11-16 15:17:13 | 1468 |
\core\event\user_enrolment_deleted | 125 |
| 2015-11-16 15:17:12 | 1468 |
\core\event\user_enrolment_deleted | 102 |
| 2015-11-16 15:17:11 | 1469 |
\core\event\user_enrolment_deleted | 125 |
| 2015-11-16 15:17:10 | 1469 |
\core\event\user_enrolment_deleted | 154 |
| 2015-11-16 15:17:05 | 1471 |
\core\event\user_enrolment_deleted | 125 |
| 2015-11-16 15:17:04 | 1471 |
\core\event\user_enrolment_deleted | 112 |
| 2015-11-16 15:17:02 | 1466 |
\core\event\user_enrolment_deleted | 107 |
| 2015-11-16 15:17:01 | 1466 |
\core\event\user_enrolment_deleted | 110 |
| 2015-11-16 15:16:43 | 327 |
\core\event\user_enrolment_created | 251 |
| 2015-11-16 15:16:42 | 327 |
\core\event\user_enrolment_created | 262 |
| 2015-11-16 15:16:40 | 327 |
\core\event\user_enrolment_created | 211 |
| 2015-11-16 14:03:38 | 1461 |
\core\event\user_enrolment_deleted | 107 |
| 2015-11-16 14:03:37 | 1461 |
\core\event\user_enrolment_deleted | 101 |
| 2015-11-16 14:03:31 | 448 |
\core\event\user_enrolment_deleted | 202 |
| 2015-11-16 14:03:30 | 917 |
\core\event\user_enrolment_deleted | 121 |
| 2015-11-16 14:03:29 | 917 |
\core\event\user_enrolment_deleted | 182 |
| 2015-11-16 14:03:28 | 1255 |
\core\event\user_enrolment_deleted | 108 |
| 2015-11-16 14:03:27 | 1255 |
\core\event\user_enrolment_deleted | 119 |
| 2015-11-16 14:03:25 | 449 |
\core\event\user_enrolment_deleted | 118 |
| 2015-11-16 14:03:24 | 449 |
\core\event\user_enrolment_deleted | 113 |
| 2015-11-16 14:03:22 | 1146 |
\core\event\user_enrolment_deleted | 152 |
| 2015-11-16 14:03:21 | 1146 |
\core\event\user_enrolment_deleted | 101 |
| 2015-11-16 14:03:20 | 1464 |
\core\event\user_enrolment_deleted | 158 |
| 2015-11-16 14:03:19 | 1464 |
\core\event\user_enrolment_deleted | 114 |
| 2015-11-16 14:02:57 | 1077 |
\core\event\user_enrolment_deleted | 121 |
| 2015-11-16 14:02:56 | 1077 |
\core\event\user_enrolment_deleted | 172 |
| 2015-11-16 14:02:55 | 1468 |
\core\event\user_enrolment_deleted | 148 |
| 2015-11-16 14:02:54 | 1468 |
\core\event\user_enrolment_deleted | 167 |
| 2015-11-16 14:02:53 | 1469 |
\core\event\user_enrolment_deleted | 146 |
| 2015-11-16 14:02:52 | 1469 |
\core\event\user_enrolment_deleted | 158 |
| 2015-11-16 14:02:51 | 1465 |
\core\event\user_enrolment_deleted | 112 |
| 2015-11-16 14:02:50 | 1465 |
\core\event\user_enrolment_deleted | 125 |
| 2015-11-16 14:02:48 | 1471 |
\core\event\user_enrolment_deleted | 160 |
| 2015-11-16 14:02:47 | 1471 |
\core\event\user_enrolment_deleted | 106 |
| 2015-11-16 14:02:46 | 1466 |
\core\event\user_enrolment_deleted | 162 |
| 2015-11-16 14:02:45 | 1466 |
\core\event\user_enrolment_deleted | 114 |
| 2015-11-16 14:02:30 | 327 |
\core\event\user_enrolment_created | 121 |
| 2015-11-16 14:02:29 | 327 |
\core\event\user_enrolment_created | 231 |
| 2015-11-16 14:02:28 | 327 |
\core\event\user_enrolment_created | 339 |
| 2015-11-16 14:02:27 | 327 |
\core\event\user_enrolment_created | 145 |
| 2015-11-16 14:02:26 | 327 |
\core\event\user_enrolment_created | 145 |
| 2015-11-16 13:01:17 | 1461 |
\core\event\user_enrolment_deleted | 128 |
| 2015-11-16 13:01:16 | 1461 |
\core\event\user_enrolment_deleted | 134 |
| 2015-11-16 13:01:11 | 448 |
\core\event\user_enrolment_deleted | 195 |
| 2015-11-16 13:01:10 | 448 |
\core\event\user_enrolment_deleted | 123 |
| 2015-11-16 13:01:09 | 917 |
\core\event\user_enrolment_deleted | 217 |
| 2015-11-16 13:01:08 | 1255 |
\core\event\user_enrolment_deleted | 138 |
| 2015-11-16 13:01:07 | 1255 |
\core\event\user_enrolment_deleted | 143 |
| 2015-11-16 13:01:05 | 449 |
\core\event\user_enrolment_deleted | 129 |
| 2015-11-16 13:01:03 | 1146 |
\core\event\user_enrolment_deleted | 155 |
| 2015-11-16 13:01:02 | 1146 |
\core\event\user_enrolment_deleted | 135 |
| 2015-11-16 13:01:01 | 1464 |
\core\event\user_enrolment_deleted | 179 |
| 2015-11-16 13:01:00 | 1464 |
\core\event\user_enrolment_deleted | 117 |
| 2015-11-16 13:00:42 | 1467 |
\core\event\user_enrolment_deleted | 107 |
| 2015-11-16 13:00:41 | 1467 |
\core\event\user_enrolment_deleted | 112 |
| 2015-11-16 13:00:40 | 1077 |
\core\event\user_enrolment_deleted | 185 |
| 2015-11-16 13:00:39 | 1077 |
\core\event\user_enrolment_deleted | 133 |
| 2015-11-16 13:00:38 | 1468 |
\core\event\user_enrolment_deleted | 181 |
| 2015-11-16 13:00:37 | 1469 |
\core\event\user_enrolment_deleted | 110 |
| 2015-11-16 13:00:36 | 1469 |
\core\event\user_enrolment_deleted | 173 |
| 2015-11-16 13:00:35 | 1465 |
\core\event\user_enrolment_deleted | 101 |
| 2015-11-16 13:00:34 | 1465 |
\core\event\user_enrolment_deleted | 147 |
| 2015-11-16 13:00:32 | 1471 |
\core\event\user_enrolment_deleted | 187 |
| 2015-11-16 13:00:31 | 1466 |
\core\event\user_enrolment_deleted | 138 |
| 2015-11-16 13:00:30 | 1466 |
\core\event\user_enrolment_deleted | 180 |
| 2015-11-16 13:00:15 | 327 |
\core\event\user_enrolment_created | 240 |
| 2015-11-16 13:00:14 | 327 |
\core\event\user_enrolment_created | 371 |
| 2015-11-16 13:00:12 | 327 |
\core\event\user_enrolment_created | 212 |
| 2015-11-16 11:47:08 | 1461 |
\core\event\user_enrolment_deleted | 122 |
| 2015-11-16 11:47:07 | 1461 |
\core\event\user_enrolment_deleted | 123 |
| 2015-11-16 11:47:01 | 448 |
\core\event\user_enrolment_deleted | 169 |
| 2015-11-16 11:47:00 | 448 |
\core\event\user_enrolment_deleted | 102 |
| 2015-11-16 11:46:59 | 917 |
\core\event\user_enrolment_deleted | 194 |
| 2015-11-16 11:46:57 | 1255 |
\core\event\user_enrolment_deleted | 137 |
| 2015-11-16 11:46:55 | 449 |
\core\event\user_enrolment_deleted | 121 |
| 2015-11-16 11:46:54 | 449 |
\core\event\user_enrolment_deleted | 122 |
| 2015-11-16 11:46:52 | 1146 |
\core\event\user_enrolment_deleted | 150 |
| 2015-11-16 11:46:50 | 1464 |
\core\event\user_enrolment_deleted | 159 |
| 2015-11-16 11:46:29 | 1077 |
\core\event\user_enrolment_deleted | 214 |
| 2015-11-16 11:46:28 | 1468 |
\core\event\user_enrolment_deleted | 120 |
| 2015-11-16 11:46:27 | 1468 |
\core\event\user_enrolment_deleted | 186 |
| 2015-11-16 11:46:26 | 1469 |
\core\event\user_enrolment_deleted | 153 |
| 2015-11-16 11:46:25 | 1469 |
\core\event\user_enrolment_deleted | 165 |
| 2015-11-16 11:46:24 | 1465 |
\core\event\user_enrolment_deleted | 134 |
| 2015-11-16 11:46:23 | 1465 |
\core\event\user_enrolment_deleted | 140 |
| 2015-11-16 11:46:22 | 1471 |
\core\event\user_enrolment_deleted | 125 |
| 2015-11-16 11:46:21 | 1471 |
\core\event\user_enrolment_deleted | 180 |
| 2015-11-16 11:46:20 | 1466 |
\core\event\user_enrolment_deleted | 154 |
| 2015-11-16 11:46:19 | 1466 |
\core\event\user_enrolment_deleted | 164 |
| 2015-11-16 11:46:08 | 327 |
\core\event\user_enrolment_created | 114 |
| 2015-11-16 11:46:07 | 327 |
\core\event\user_enrolment_created | 300 |
| 2015-11-16 11:46:06 | 327 |
\core\event\user_enrolment_created | 313 |
| 2015-11-16 11:46:05 | 327 |
\core\event\user_enrolment_created | 230 |
+---------------------+----------+------------------------------------+-----+
100 rows in set (4 min 29.20 sec)
Je constate donc que ces cours continuent de produire régulièrement de très grands nombres de lignes de log.
Par ailleurs, en cherchant le point commun entre ces cours, il semble que ce soit la présence de liens méta-cours masqués (la méthode d'inscription correspondant à ce lien méta-cours est cachée) qui génère ces problèmes.
En effet, si on supprime ces méthodes d'inscriptions masquées, il n'y a plus de génération de lignes d'inscription/désinscription dans les historiques
Je vais donc, pour ces différents cours, supprimer ces méthodes d'inscription masquées, puis supprimer toutes les lignes correspondantes dans les tables d'historiques !
Séverin