Wikini

OptimisationWikiniEtudeSQL

PagePrincipale :: DerniersChangements :: DerniersCommentaires :: ParametresUtilisateur :: Vous êtes ec2-54-221-69-42.compute-1.amazonaws.com

Etude des optimisations possible des requêtes SQL




Ressources pour l'optimisation










Systéme de purge


Voir OptimisationWikiniEtudeCode



Défragmentation des tables (StephaneAulery?)


Le code suivant optimise la taille des tables de MySQL pour améliorer les temps de réponse du serveur.

<?php
    
// Optimize tables size
    
function OptimizeTables()
    {
        
// List tables
        
if($tables $this->Query("SHOW TABLES "
                    
."FROM ".$this->GetConfigValue("mysql_database")." "
                    
."LIKE '".$this->GetConfigValue("table_prefix")."%'")) {

            
// Optimize every table
            
while ($table mysql_fetch_assoc($tables))
                
$this->Query("OPTIMIZE TABLE "
                    
.$table["Tables_in_".$this->GetConfigValue("mysql_database")
                    .
" (".$this->GetConfigValue("table_prefix")."%)"]);

            
// Free ressources
            
mysql_free_result($tables);
        }
    }
?>


Mon erreur a été d'appeler ce code dans la fonction maintenance qui est appelé trop souvent pour une fonction aussi lourde (bloquante ?). Néanmoins je pense que les gains occasionnés par cette fonction autorisent à en faire une fonctionnalité intégré à WikiNi. Je ne pense pas que l'intégration à un outils d'administration manuelle réponde correctement au problème de la fragmentation des tables à cause de la fréquence avec laquelle les pages d'un Wiki peuvent être éditées. L'optimisation devrait ce déclencher après l'édition et/ou la suppression de N pages par exemple.



Optimisation de la fonction LoadRecentlyCommented (Voir Bug #2217)


Un seul petit coup d'oeil pour voir que cette fonction est épouvantable au niveau consommation de requête. Il y a une solution en cours de développement sur le gestionnaire d'anomalies de Gna! Mais aujourd'hui elle a l'air KO...

Proposition à étudier :

<?php
function LoadRecentlyCommented($limit 50) {
    
$pageTable "`".$this->config["table_prefix"]."pages`";

    
$sql "SELECT p1.comment_on as tag, p1.tag as comment_tag, p1.time as comment_time, p1.user as comment_user ";
    
$sql .= "FROM $pageTable p1 ";
    
$sql .= "WHERE p1.time = (SELECT max(p2.time) FROM $pageTable p2 ";
    
$sql .=                  "WHERE p2.comment_on = p1.comment_on ";
    
$sql .=                  "AND p2.time = (SELECT min(p3.time) FROM $pageTable p3";
    
$sql .=                                  "WHERE p3.tag = p2.tag)) ";
    
$sql .= "AND p1.comment_on <> \"\" ";
    
$sql .= "ORDER BY p1.time DESC LIMIT 0, $limit";

    
$pages $this->LoadAll($sql);
    return 
$pages;
}
?>


Rermarques :

Voir le résultat sur (i.e. pas de différence d'un point de vue rendu [inclus une comparaison à l'ancienne version]) : Le Wiki de tests de VirtualDust.

Difficile de vérifier qu'il n'y a pas de différence au point de vue rendu puisque tu n'as pas laissé l'ancienne version pour comparer... Il est certain que graphiquement c'est la même chose, mais rien ne "prouve" que les pages listées sont les mêmes... Si possible essaie de mettre les deux en parallèle (par exemple pluto que de remplacer la méthodes actuelle, tu en fais une nouvelle appelée par une nouvelle action, et tu mets les deux appels d'actions en parrallèle dans un tableau) -- LordFarquaad

Résumé de la discussion sur Gna! à propos du Bug #2217 :

DidierLoiseau

J'ai réécrit totalement ce méthode, mais j'ai deux possibilités :


Les deux possibilités ne nécessitent aucun traitement en php !

On notera le Bug #2234 qui est à peu près équivalent à la question qui se pose ici, cependant dans le cas présent les deux possibilités ont du sens: le fait d'éditer un commentaire doit bien apparaître quelque part dans les révisions, par ailleurs l'ActionRecentlyCommented donne le lien vers le commentaire modifié grâce à l'ancre "#" (tout comme l'ActionRecentComments, à ne pas confondre...)

Je me rends compte que si pour le bug #2234 on opte pour la troisième solution, la deuxième possibilité que je propose peut se faire également en seulement deux requêtes. (ceci renforce mon idée selon laquelle la solution 3 pour ce problème la est la meilleure...)

Tempest69

Bonjour,
Je propose la requete suivante pour remplacer le fonction et de même corriger ce bug :
<?php
function LoadRecentlyCommented($limit 50) {
return(
$this->LoadAll("SELECT comment_on as tag,
user as comment_user,
time as comment_time,
tag as comment_tag
FROM "
.$this->config["table_prefix"]."pages
WHERE comment_on <> '' and
id IN (
SELECT MAX(id)
FROM "
.$this->config["table_prefix"]."pages
WHERE comment_on <> ''
GROUP BY comment_on)
ORDER BY id DESC
LIMIT "
.$limit));
}
?>


De plus pour ce qui est du bug 2234 il suffirait d'utiliser à bon escient la zone latest pour les commentaires (elle ne semble pas utilisée en ce moment). Il suffit de gerer la zone latest à Y sur la dernière modif d'un commentaire et ensuite si l'on veut ordonner suivant la date originale de création d'un commentaire ou bien la date de dernière modif d'un commentaire on peut très bien utiliser la même technique que la requête du dessus qui d'ailleurs pourrait être encore beaucoup plus simple si le latest était gérée!

DidierLoiseau

Cette solution ne me paraît pas très performante vu l'utilisation de deux SELECT imbriqués. Par ailleurs, cette utilisation n'est possible que sous MySQL4?, ce qui n'est pas un PrerequisWikiNi et ne peut donc être employé.

Je pense qu'en utilisant une solution similaire à celle choisie pour le bug #2234, on devrait arriver à quelque chose de bon. Je vais essayer de voir ce que je peux faire dès que j'ai le temps. (ce qui ne devrait pas tarder car tu m'as redonné l'envie d'essayer de corriger ce bug... enfin surtout améliorer les perf de cette fonction)

J'ai essayé de chercher une solution à ce problème mais je dois dire que le fait de trier par première révision est vraiment plus une contrainte qu'autre chose. (sans compter qu'elle masque les éditions multiples d'un même commentaire, et que les dates [et parfois utilisateur] ne correspondent pas à ce qui sera affiché une fois qu'on aura cliqué sur le lien...)
Alors j'ai deux solutions.
NB.: quand je parle de performances, les tests ont été effectués sur mon ordinateur en local, où les temps de connexion à MySQL sont négligeable, ce qui permet au requêtes telles que des chargement de pages de s'effectuer en quelques dixièmes de millisecondes... En règle général je pense qu'on peut dire que moins de requêtes = plus performant
La première, d'après mes tests aussi performante que la solution actuelle au niveau SQl (mis à part qu'elle s'effectue en deux requêtes au lieu d'un nombre proportionnel au nombre de commentaires du wiki entier), mais plus performante au niveau PHP (aucun traitement après requête).
Le seul inconvéniant de cette solution est qu'elle n'est pas tout à fait déterministe: on ne sait pas vraiment prévoir qui sera le user qui sera affiché (en principe ce sera celui de la dernière édition). Ceci est dû à la clause GROUP BY, je n'ai jamais su comment spécifier qu'on voulait la valeur d'un champ de la ligne qui contenait le minimum (ou le maximum) d'un autre champ...
Cette solution consiste à remplir une table temporare avec la liste des tables qui ont été récemment commentées, ainsi que le numéro XY du commentaire CommentXY?. Ensuite, une seconde requêts sert à retrouver quel est la date de première révision de ce commentaire :

<?php
$sql 
'CREATE TEMPORARY TABLE ' $recentlyCommented
' SELECT page.*, MAX(SUBSTRING(comment.tag, 8) + 0) AS comment_id'
' FROM ' $prefix 'pages AS comment, ' $prefix 'pages AS page'
' WHERE comment.comment_on != "" AND comment.latest = "Y" AND page.latest = "Y" AND page.tag = comment.comment_on'
' GROUP BY comment.comment_on'
' ORDER BY comment_id DESC'
' LIMIT ' $limit;
$this->Query($sql);
$sql2 'SELECT rc.*, min(comment.time) AS comment_time, comment.user AS comment_user, comment.tag AS comment_tag'
' FROM ' $temp ' rc, ' $prefix 'pages comment'
' WHERE rc.tag = comment.comment_on AND CONCAT("Comment", rc.comment_id) = comment.tag'
' GROUP BY rc.comment_id'
' ORDER BY rc.comment_id DESC';
return 
$this->LoadAll($sql2);
?>


La deuxième solution semble même moins performante (3 requêtes, 2 tables temporaires), mais je vais tout de même vous l'exposer:
Elle se passe en 3 étapes (3 requêtes), et contrairement à la première solution, est totalement déterministe.
1) on construit une table qui contient la date de première révision de tous les commentaires
2) on construit une table qui contient toutes les pages récemment commentées
3) on fait la correspondance entre ces deux tables, et on combine le tout avec wikini_pages afin de récupérer le user:

<?php
// first we create a table containing all comments and their first revision time
$sql 'CREATE TEMPORARY TABLE ' $firstRevisions
' SELECT tag, MIN(time) AS time'
' FROM ' $prefix 'pages'
' WHERE comment_on != ""'
' GROUP BY tag';
$this->Query($sql);
// then we create a table containing the $limit recently commented pages, and the id XY of their last CommentXY
$sql 'CREATE TEMPORARY TABLE ' $recentlyCommented
' SELECT page.*, MAX(SUBSTRING(comment.tag, 8) + 0) AS comment_id'
' FROM ' $prefix 'pages AS comment, ' $prefix 'pages AS page'
' WHERE comment.comment_on != "" AND comment.latest = "Y" AND page.latest = "Y" AND page.tag = comment.comment_on'
' GROUP BY comment.comment_on'
' ORDER BY comment_id DESC'
' LIMIT ' $limit;
$this->Query($sql);
// now all we have to do is combine these two tables with wikini_pages and we'll have got it !
$sql 'SELECT rc.*, comment.time AS comment_time, comment.user AS comment_user, comment.tag AS comment_tag'
' FROM ' $firstRevisions ' AS fr, ' $recentlyCommented ' AS rc, ' $prefix 'pages AS comment'
' WHERE fr.tag = comment.tag AND fr.time = comment.time AND CONCAT("Comment", rc.comment_id) = fr.tag'
' ORDER BY rc.comment_id DESC';
return 
$this->LoadAll($sql);
?>


Voilà. Je dois dire que l'histoire du "first revisions of latest comments" est vraiment agaçante et que je n'en vois pas l'intérêt. S'il n'y avait pas ça, la première solution serait parfaite, et encore plus performante puisqu'il ne faudrait pas faire de GROUP BY dans la deuxième requête (ou peut-être même ne pas faire la deuxième requête du tout...)

Tempest69

Deuxième tentative : la même que l'autre fois mais avec un fichier de travail à la place de la sous requête...

<?php
function LoadRecentlyCommented($limit 50) {
$prefix=$this->config["table_prefix"];

// récup des 'id' des x (=$limit) derniers commentaires
$sql="CREATE TEMPORARY TABLE recentlyCommented
SELECT max( id ) AS maxid
FROM "
.$prefix."pages
WHERE comment_on <> '' and latest='Y'
GROUP BY comment_on
ORDER BY maxid desc
LIMIT 
$limit";
$this->Query($sql);

// récup des info sur ces commentaires grace aux id
$sql="SELECT comment_on AS tag, user AS comment_user, time AS comment_time, tag AS comment_tag
FROM "
.$prefix."pages,
recentlyCommented
WHERE id = maxid
ORDER BY id DESC"
;
return (
$this->LoadAll($sql));
}
?>


Je ne suis pas certain que ce bug soit lié au bug #2234

PS : On peut aussi finalement corriger le bug en ajoutant au début de la fonction 'originale' le code suivant :
"$page=array();"

DidierLoiseau

En fait, si on veut reproduire le comportement exact de la version actuelle, on ne peut pas faire de tri sur les id car une édition d'un vieux commentaire le fait passer devant un commentaire plus récent. Donc, quand on prend le max(id), ça sera peut-être l'id du Comment1? qui a été modifié après la création du Comment2?.
La spécification de RecentlyCommented? (d'après les commentaires) est:
load ids of the first revisions of latest comments
Donc, si sur une page on a Comment1? et Comment2?, il faut la première révision de Comment2?, même si Comment1? a été édité plus récemment. Prendre max(id) en groupant sur comment_on retournera l'id de Comment1? si on a eu une révision depuis Comment2?.

Il est vrai que pour moi il serait plus logique de trier par la dernière révision de n'importe quel commentaire, ce qui ferait remonter dans la liste un commentaire qui a été édité il y a longtemps. (RecentlyCommented? afficherait alors non seulement les nouveaux commentaires mais aussi les commentaires modifiés).
Si on faisait cela, ça simplifierait considérablement le problème et je pense que ta solution serait tout à fait applicable.

J'aimerais avoir l'avis des autres développeurs sur le sujet...

Bon j'ai fait la correction pour supprimer la notice, mais sans aucune optimisation...



Optimisation de l'utilisation de la base de données


Actuellement, WikiNi est assez lourd au niveau de la base de données. Une page moyenne prend facilement 10 à 20 ko. Chaque version est une sauvegarde complète de cette page, ce qui signifie qu'il faut multiplier la taille de la page par le nombre de versions pour obtenir une approximation de la taille prise. Actuellement, la seule chose qui est faite pour éviter de faire trop gonfler la base de données est la purge, mais si on considère que chaque page a en moyenne 5 révisions au cours des 90 derniers jours, et qu'un wiki moyen a minimum 100pages, cela fait déjà plus de 5 à 10 Mo. (tous ces chiffres ne sont que des estimations...) Cela peut vraiment poser problème à certains, beaucoup d'hébergeurs bon marché (ou gratuits) ne proposant pas plus de 50 à 100 Mo, et beaucoup moins pour la base de données.

Je propose donc d'envisager différentes possibilités pour réduire la taille de la base de données (sans perdre pour autant des informations)


-- LordFarquaad


Pourtant actuellement la page est "réenrégistrée" à chaque fois... Je pense qu'il est tout de même bon de faire une sauvegarde complète de temps en temps pour éviter de potentiels problèmes de corruption à cause de diff, ou de pertes de données... -- LordFarquaad
Commentaires [Cacher commentaires/formulaire]