Forum d'entraide PHPFrance • Anatomie d'un forum

Page 1 sur 1

Anatomie d'un forum

Posté : 29 juil. 2007, 13:25

par Hubert Roksor

Suite à ce post, et puisque personne ne semble vouloir se lancer, je me suis décidé à me dévouer et poster quelque chose.

J'ai beaucoup de théories sur le design d'un forum (que je teste parfois sur vous à votre insu

), particulièrement de son aspect technique. Aujourd'hui, je vais partager avec vous quelques observations sur une des requêtes les plus exécutées par un logiciel de forum : récupérer la liste des sujets ("viewforum", ci-après). Pour les besoins de mes exemples, j'utiliserai le schéma de phpBB 2, mais le problème est commun à la plupart des logiciels de forum. De plus, j'utiliserai librement certains anglicismes (topic, post) parce que leur équivalent français m'irrite

Alors, cette requête viewforum, qu'est-ce qu'elle a de particulier ?

C'est l'une des pages les plus vues.
Je n'ai pas de stats récentes à ce sujet mais la dernière fois que j'ai vérifié elle représentait plus de 25% des impressions. C'était avant que Firefox (et IE ?) n'utilise systématiquement le cache lors du retour en arrière (bouton Retour) donc ce nombre a dû baisser, mais il est certainement resté conséquent.

Elle est pratiquement incachable
La table de topics est continuellement mise à jour par les nouveaux posts, voire même la lecture des anciens ! (conversion de la charge de lecture en charge d'écriture à cause de la mise à jour du compteur de vues... mais ce sera pour une autre fois) Résultat : vous pouvez oublier le query cache de MySQL.

Elle s'alourdit continuellement
La liste des topics s'allonge de jour en jour, à moins de régulièrement délester ou archiver ses sujets. La conséquence est qu'il faut trier de plus en plus de résultats. De plus, accèder aux plus anciens topics demander de passer (skip) de nombreux résultats.

En parlant de tri justement...
Récupérons la liste des topics du forum 3 pour voir

Code : Tout sélectionner

 EXPLAIN
  SELECT *
    FROM phpbb_topics
   WHERE forum_id = 3
     AND topic_type IN (0, 1)
ORDER BY topic_type DESC, topic_last_post_id DESC
   LIMIT 10;

Les topics sont triés par topic_type DESC pour placer les post-its (stickies) en haut de page, puis par topic_last_post_id pour les classer par ordre chronologique d'insertion dans la base. Que nous dit EXPLAIN ?

Code : Tout sélectionner

+----+-------------+--------------+------+---------------------+----------+---------+-------+------+-----------------------------+
| id | select_type | table        | type | possible_keys       | key      | key_len | ref   | rows | Extra                       |
+----+-------------+--------------+------+---------------------+----------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | phpbb_topics | ref  | topic_type,forum_id | forum_id | 2       | const | 2527 | Using where; Using filesort |
+----+-------------+--------------+------+---------------------+----------+---------+-------+------+-----------------------------+

Ouch, on trie ~2527 lignes. Heureusement, le tri s'effectue en mémoire et en 0.04s sur mon ordinateur. Qu'est-ce qu'on peut y faire ? Ben par exemple on pourrait utiliser un index qui couvre toute la clause WHERE :

Code : Tout sélectionner

ALTER TABLE phpbb_topics DROP INDEX forum_id, ADD INDEX viewforum (forum_id, topic_type);

Résultat :

Code : Tout sélectionner

+----+-------------+--------------+-------+----------------------+-----------+---------+------+------+-----------------------------+
| id | select_type | table        | type  | possible_keys        | key       | key_len | ref  | rows | Extra                       |
+----+-------------+--------------+-------+----------------------+-----------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | phpbb_topics | range | topic_type,viewforum | viewforum | 3       | NULL | 2308 | Using where; Using filesort |
+----+-------------+--------------+-------+----------------------+-----------+---------+------+------+-----------------------------+

Bon, c'est un peu mieux sur le papier et l'exécution est tombée à 0.03s, mais on continue à trier plusieurs milliers de lignes. On peut y remédier en étendant l'index pour qu'il couvre également la clause de tri :

Code : Tout sélectionner

ALTER TABLE phpbb_topics DROP INDEX viewforum, ADD INDEX viewforum (forum_id, topic_type, topic_last_post_id);

Code : Tout sélectionner

+----+-------------+--------------+-------+----------------------+-----------+---------+------+------+-------------+
| id | select_type | table        | type  | possible_keys        | key       | key_len | ref  | rows | Extra       |
+----+-------------+--------------+-------+----------------------+-----------+---------+------+------+-------------+
|  1 | SIMPLE      | phpbb_topics | range | topic_type,viewforum | viewforum | 3       | NULL | 2496 | Using where |
+----+-------------+--------------+-------+----------------------+-----------+---------+------+------+-------------+

Plus de tri du tout, et l'exécution descend à 0.00s (moins d'un centième de seconde). Est-ce la panacée ? Loin de là. C'est un index super-spécialisé, donc il ne vous servira probablement qu'à afficher viewforum. Ensuite, si vous voulez classer les topics par ordre chronologique normal les deux colonnes de tri ne seront pas dans le même sens (topic_type DESC, topic_last_post_ts ASC) et MySQL ne pourra pas utiliser l'ordre de l'index. En plus de ça il ne résoud pas le problème de l'accès aux plus anciennes page, il ne fait que diminuer la vitesse à laquelle les performances se dégradent. Quelques pistes : on remplace topic_last_post_id par topic_last_post_ts, un champs qui contient le timestamp du dernier post. Les topics restent triés par ordre chronologique et grâce au timestamp, on peut faire un "cut", c'est-à-dire limiter la période des topics, par exemple "n'afficher que les sujets datant de moins d'un mois". Ça aide pour les cas où MySQL a malgré tout à classer les topics par un filesort, mais ça ne règle pas le problème d'accès aux anciennes pages. On peut utiliser des tricks, comme par exemple scanner la table en partant de la fin. Ainsi, dans un forum comportant 3000 topics :

Code : Tout sélectionner

  SELECT *
    FROM phpbb_topics
   WHERE forum_id = 3
     AND topic_type IN (0, 1)
ORDER BY topic_type DESC, topic_last_post_id DESC
   LIMIT 10
  OFFSET 2970;

...et...

Code : Tout sélectionner

  SELECT *
    FROM phpbb_topics
   WHERE forum_id = 3
     AND topic_type IN (0, 1)
ORDER BY topic_type ASC, topic_last_post_id ASC
   LIMIT 10
  OFFSET 20;

...renverront les mêmes résultats (topics 2971 à 2980, sachant que le 20 de la première requête correspond à 3000 - 2970 - 10), mais pas dans le même ordre. On peut utiliser PHP ou une sous-requête (très rapide) pour les reclasser dans le bon ordre. À ma connaissance, la première personne a avoir parlé publiquement de cette technique était BartVB sur les forums phpBB. Mais là encore, ça ne fait que repousser le problème vers les pages du milieu, équidistantes du début et de la fin du forum.

Une autre façon de procéder serait d'abandonner le système de numérotation de page tel qu'il est utilisé sur la plupart des forums. D'ailleurs, sur les forums les plus actifs, ce système montre rapidement ses limites : le temps de lire la première page et cliquer sur le lien vers la page 2, de nouveaux topics ont été postés et vous retombez sur ceux que vous venez juste de lire ! À la place, on pourrait imaginer de naviguer par date. Ainsi, plutôt que de lister les sujets de "la page 2", vous demandez au forum de lister les topics "plus anciens que ceux en cours" (viewforum.php?page=2). Par exemple, "plus ancien que 29-07-2007 14:31:12" (viewforum.php?older=1185712272). Là, plus aucune chance de retomber sur les mêmes sujets. De plus, l'index couvre réduit encore plus les résultats possibles (key_len passe de 3 à 7). En revanche, il existe au moins un défaut à cette solution : un timestamp n'est pas unique, si deux sujets partagent le même timestamp, il se pourrait qu'un des deux se retrouve coincés "entre deux pages" si son jumeau se trouve être le dernier de la page. De plus, il devient impossible de numéroter les pages donc il faut trouver un autre repère pour les utilisateurs pour éviter qu'ils se sentent perdus.

La suite plus tard...

Posté : 29 juil. 2007, 14:11

par Sékiltoyai

C'est très spécialisé ce dont tu nous parles là, spécialisé sur un script et sur une partie du script, et sur des points techniques très précis.
Ca rentre dans le cadre du sujet que tu as linké, bien entendu, mais cette réflexion n'est pas suffisante, loin s'en faut. Car le problème des forums n'est pas leurs performances, si les performances flanchent, on peut toujours prendre un hébergement plus puissant, mais l'architecture qui manque notamment de modularité. Donc, comme promis, j'ouvrirais dans les prochains jours un topic avec mes réflexions, que j'avais déjà préparé depuis longtemps mais mises de côté...

...la suite

Posté : 29 juil. 2007, 14:39

par Hubert Roksor

On a dit que notre index idéal est (forum_id, topic_type, topic_last_post_ts), même si celui-ci ne couvre pas tous les usages, et notamment l'affichage par ordre chronologique normal. Autre point un peu dommage, il ne peut pas être utilisé par un moteur de recherche pour trouver les sujets mis à jours récemment parce que la requête n'utiliserait que (forum_id, topic_last_post_ts) et MySQL n'utiliserait que la première colonne de l'index. On pourrait se contenter d'un index sur (forum_id, topic_last_post_ts) et filtrer le topic_type en PHP. Sur PHPFrance, 99.79% des sujets sont "normaux", on a 0.18% de "stickies" et 0.03% d'annonces, ce qui signifie que les chances de tomber sur un topic spécial sont très très faibles. Ainsi, on pourrait se contenter de

Code : Tout sélectionner

  SELECT *
    FROM phpbb_topics
   WHERE forum_id = 3
ORDER BY topic_last_post_id DESC
   LIMIT 10;

...tout en ignorant les topics spéciaux, et si dans le lot il y a des topics spéciaux, deux solutions :

tant pis, de toutes façons personnes ne fait la différence entre 24 topics par page et 25 topics par page
on faire une requête supplémentaire pour récupérer le topic qui manque

On sacrifie un peu de notre confort en échange d'un index plus utile (il permet de classer dans les deux sens).

Il nous reste malgré tout à afficher les stickies sur la première page, ainsi que les annonces sur toutes les autres, alors que faire ? On peut ajouter un index sur (topic_type) et l'utiliser à ces fins. L'inconvénient c'est qu'il n'est utile que pour 0.21% des enregistrements, et qu'il ne sert vraiment qu'à ça. D'un autre côté, un index est indispensable pour retrouver ces quelques enregistrements noyés dans la masse. Sous PostgreSQL, il est possible de créer des indices partiels, par exemple un index sur (topic_type) qui ne couvrirait que (topic_type > 0). De cette façon on ne s'encombre pas d'un index pour les enregistrements qui ne le nécessitent pas. Malheureusement, point d'index partiel sous MySQL. En échange, je vous propose de contourner le problème en créant une table spéciale (que j'appelerai "topic_type_table" pour l'exemple) qui contiendrait les valeurs de (forum_id, topic_id, topic_type) uniquement pour les enregistrements donc (topic_type > 0). Les puristes pourront se plaindre au bureau des réclamations que j'ai violé 3NF et toutes les saintes lois sur les dépendances transitives

En utilisant cette technique (uniquement pour les topics spéciaux, comme je l'ai expliqué) la requête pour récupérer les topics du forum 3 dont le topic_type serait 1 ou 2 ressemblerait à

Code : Tout sélectionner

SELECT t.*
  FROM topic_type_table tt
  JOIN phpbb_topics USING (topic_id)
 WHERE tt.forum_id = 3
   AND tt.topic_type IN (1, 2)

J'ai parlé de dépendance transitive, il nous faut en effet nous assurer que la table contient toujours la bonne valeur de forum_id pour chaque topic_id, toujours le bon topic_type, supprimer les entrées ne possédant plus de contrepartie dans la table principale, etc... On peut en régler une partie par des références/contraintes (InnoDB), et gérer le reste en PHP, "à la main". Ou encore si vous avez la chance d'utiliser MySQL 5, par des triggers. Par exemple

Code : Tout sélectionner

CREATE TABLE "topic_type_table" (
	"topic_id" mediumint unsigned NOT NULL,
	"forum_id" smallint unsigned NOT NULL,
	"topic_type" tinyint unsigned NOT NULL,
	PRIMARY KEY ("topic_id"),
	KEY "topic_type_per_forum" ("forum_id","topic_type"),
	KEY "topic_type" ("topic_type")
);

DELIMITER //

CREATE TRIGGER ai_phpbb_topics AFTER INSERT ON phpbb_topics
FOR EACH ROW
BEGIN
	IF (NEW.topic_type > 0)
	THEN
		INSERT INTO topic_type_table (topic_id, forum_id, topic_type)
		VALUES (NEW.topic_id, NEW.forum_id, NEW.topic_type);
	END IF;
END
//

CREATE TRIGGER ad_phpbb_topics AFTER DELETE ON phpbb_topics
FOR EACH ROW
BEGIN
	IF (OLD.topic_type > 0)
	THEN
		DELETE FROM topic_type_table
		WHERE topic_id = OLD.topic_id;
	END IF;
END
//

CREATE TRIGGER au_phpbb_topics AFTER UPDATE ON phpbb_topics
FOR EACH ROW
BEGIN
	IF (NEW.forum_id <> OLD.forum_id)
	THEN
		UPDATE topic_type_table
		   SET forum_id = NEW.forum_id
		 WHERE topic_id = OLD.topic_id;
	END IF;

	IF (NEW.topic_type <> OLD.topic_type)
	THEN
		/**
		* Note : ne gère pas les changements de topic_id, ne changez jamais
		*        la valeur d'une clé primaire !
		*/
		IF (OLD.topic_type = 0)
		THEN
			/**
			* Topic normal devient spécial
			*/
			INSERT INTO topic_type_table (topic_id, forum_id, topic_type)
			VALUES (NEW.topic_id, NEW.forum_id, NEW.topic_type);

		ELSEIF (NEW.topic_type = 0)
		THEN
			/**
			* Topic spécial devient normal
			*/
			DELETE FROM topic_type_table
			WHERE topic_id = OLD.topic_id;

		ELSE
			/**
			* Simple changement
			*/
			UPDATE topic_type_table
			   SET forum_id = NEW.forum_id,
			       topic_type = NEW.topic_type
			 WHERE topic_id = OLD.topic_id;
		END IF;
	END IF;
END
//

Et zou, index partiel !

Posté : 29 juil. 2007, 14:42

par naholyr

Hubert, y a pas que les perfs dans la vie, ça s'optimise après tout ça

là on parlait vraiment de la structure en elle-même je crois

Posté : 29 juil. 2007, 14:49

par Hubert Roksor

si les performances flanchent, on peut toujours prendre un hébergement plus puissant

Le dernier gars qui m'a dit ça paie 250€/mois d'hébergement pour un forum avec 300 utilisateurs en ligne...

[...] mais l'architecture qui manque notamment de modularité.

Quelque chose me dit que l'on va être profondément en désaccord sur cet aspect.

comme promis, j'ouvrirais dans les prochains jours un topic avec mes réflexions, que j'avais déjà préparé depuis longtemps mais mises de côté...

N'hésite pas à copier/coller même si ce n'est pas mis en forme. Si tu le souhaites, poste ton fichier texte tel quel et je vérouillerai le sujet en attendant que tu finisses tes corrections.

Posté : 29 juil. 2007, 14:58

par Hubert Roksor

Hubert, y a pas que les perfs dans la vie, ça s'optimise après tout ça là on parlait vraiment de la structure en elle-même je crois

Je confirme qu'il n'y a pas que les perfs qui importent, en revanche je suis très circonspect concernant l'idée d'optimiser un programme après l'avoir terminé...

Un bon design doit impérativement concilier fonctionnalité et performance. J'ai posté un message concernant la mise au point d'un bon index parce que c'était une bonne occasion de promouvoir l'utilisation de triggers pour émuler un index partiel, et parce que j'aime tout ce qui touche au performance, mais aussi parce que je pense que 1 bon post sur les performances vaut bien 13 excellents posts inexistents sur le design parfait... Mais je suis impatient de vous lire.

Re: ...la suite

Posté : 31 juil. 2007, 00:06

par Theri le Vorace

Sous PostgreSQL, il est possible de créer des indices partiels, par exemple un index sur (topic_type) qui ne couvrirait que (topic_type > 0). De cette façon on ne s'encombre pas d'un index pour les enregistrements qui ne le nécessitent pas. Malheureusement, point d'index partiel sous MySQL. [...] En échange, je vous propose de contourner le problème en créant une table spéciale (que j'appelerai "topic_type_table" pour l'exemple) qui contiendrait les valeurs de (forum_id, topic_id, topic_type) uniquement pour les enregistrements donc (topic_type > 0). Les puristes pourront se plaindre au bureau des réclamations que j'ai violé 3NF et toutes les saintes lois sur les dépendances transitives

Je découvre tout juste les vues de MySQL 5.0, et cela permet de gérer des "tables virtuelles" sans te prendre de l'espace de stockage ou violer ta sacro-sainte règle..

Cela ne répondrait-il en partie à ton souhait, ou fais-tu passer les performances de réponse avant celles de stockage ?

/* Note : prendre en compte le fait que les ayant découvertes hier, je n'ai qu'une connaissance fooort limitée de leurs caractéristiques en termes de vitesse, à part qu'au niveau de l'algorithme utilisé : MERGE c'est bien, TEMPTABLE c'est moins bien ; et qu'une vue ne gère pas les index directement.. */

Posté : 31 juil. 2007, 06:35

par Hubert Roksor

Cela ne répondrait-il en partie à ton souhait

Non, mais c'est perspicace d'avoir fait le rapprochement avec les vues

Tu as pu lire dans le manuel que MySQL implémentait deux algorithmes différents pour la gestions des vues : MERGE et TEMPTABLE. La technique que j'ai présentée plus haut s'apparente à un troisième algorithme que l'on peut trouver chez d'autres SGBD sous le terme MATERIALIZED, une vue matérialisée. Les algorithmes de MySQL ne font que réécrire ta requête SQL, et donc sont soumis exactement aux mêmes problèmes. Si tu cherches à en savoir plus sur le fonctionnement des vues, essaie EXPLAIN EXTENDED suivi d'un SHOW WARNINGS (idéalement, fais-le en local avec le client MySQL fourni avec le serveur. Astuce #2 : tape \W pour activer l'affichage automatique des warnings).

En admettant que je crée une vue

Code : Tout sélectionner

CREATE VIEW topic_type_view AS
SELECT * FROM phpbb_topics WHERE topic_type > 0

...la requête

Code : Tout sélectionner

SELECT * FROM topic_type_view WHERE forum_id = 3

...deviendra l'une ou l'autre de celles-ci, suivant l'algorithme utilisé, TEMPTABLE ou MERGE

Code : Tout sélectionner

SELECT *
FROM (
    SELECT * FROM phpbb_topics WHERE topic_type > 0
) AS topic_type_view
WHERE forum_id = 3

Code : Tout sélectionner

SELECT *
FROM phpbb_topics
WHERE forum_id = 3
  AND topic_type > 0

Donc les vues sous MySQL ne serve qu'à des questions de conforts, elles ne peuvent pas améliorer les performances.

fais-tu passer les performances de réponse avant celles de stockage ?

Oui, et surtout sur un site dynamique comme un forum. Les habitués d'internet font la différence (consciemment ou pas) entre une page qui se charge en 0.3s et une page qui se charge en 0.9s, donc le gain en confort utilisateur justifie le coût en stockage supplémentaire (et je parle en général, l'exemple ci-dessus ne prends que quelques KiB de stockage). Même d'un point de vue technique c'est justifié, les ressources CPU coûtent plus chers que les ressources de stockage, tu trouves facilement des hébergeurs mutualisés proposant des centaines de MiB de stockage pour quelques euros par mois, mais si tu t'accapares le CPU du serveur tu risques de te faire virer :]