Le meilleur système de MP

Bonjour,

J'ai actuellement un site assez gros similaire à FB et +/- 800 connectés aux heures de pointes et le serveur est souvent dans les choux pendant cette période. Principalement à cause de mon système de recherche de membres et de MP. Le moteur de recherche je ne sais pas faire grand chose a part jouer avec les indexes, ce qui n'est pas gagné . Pour ce qui est du système de MP j'ai vu un peu trop gros. En effet j'ai copié Facebook +/- à 80% la dessus. Donc on a une colonne à gauche pour les conversations et un bloc à droite avec les messages. Au début j'utilisais Socket.io pour appeler le rafraîchissement Ajax. Mais suite à de nombreux problème de membres qui étaient sur des hotspots ou FAI qui rejetaient les ports autres que :80 et ssl. J'ai du actualisé en Ajax avec un setinterval ( ), donc je vous laisse imaginer toutes les ~5sec une centaine de clients faire des requêtes pour rafraîchir conversations et messages.

J'ai donc décidé de remanier le système de MP à la manière du Sdz ou Zds. Je pense que ça sucera beaucoup moins de ressources. Cependant avant de me lancer dans un long codage et vu que vous l'avez fait pour Zds je viens m’éclaircir les idées par vos lumières.

ps: je boss avec symfony2/mysql

TL;DR : Je dois refaire mon système de MP car mon site lag. Je pense donc à faire un système similaire au SdZ ou ZdS.

Donc pour les messages des MPs rien de bien compliqué :

Table : message

Je pense que le tableau parle de lui même, c'est le minimum syndical pour un message.

Et pour les conversations c'est la que je nage un peu.

Table : Thread

Les conversations avec un champ last_post_date pour les trier.

Table: X pour "lier"

Donc pour avoir toutes les conversations d'un membre et aussi pour savoir si le membre a lu la conversation.

Ou alors fait quelque chose du genre pour la table thread :

Donc avoir les deux users, avoir le dernier user pour savoir a qui appartient le statut à lu ou non lu. Mais ça implique de faire une requête par OR, WHERE user1_id = ? OR user2_id = ? . De part mon expérience le OR suce beaucoup de ressource.

Voila, comme vous le voyez je suis paumé, je cherche le schéma 'parfait' pour avoir quelque chose de rapide avant tout. Donc si vous avez des avis ou que vous voyez qu'un truc ne tiens pas la route ou serait plus rapide de tel ou tel manière je suis tout ouï. Autre chose à savoir, les conversations sont limités à deux participants.

Merci d'avoir lu mon pavé.

Si tu veux pour le code de ZdS c'est la pour les MPs : https://github.com/zestedesavoir/zds-site/tree/dev/zds/mp

et plus precisement le modele : https://github.com/zestedesavoir/zds-site/blob/dev/zds/mp/models.py

Oui j'ai vu avant de poster le topic, mais je suis pas sur de tout comprendre vu que je ne connais pas le python. Mais a ce que je vois c'est assez proche de ma première solution avec table X

Perso j'aurais utilisé une base NoSQL pour les messages.

Cette base est interrogée périodiquement par un serveur qui n'est pas ton serveur web et de préférence orienté asynchrone (nodejs, undertow, vertx, …) et récupère des nouveaux messages. Tu pousses le résultat dans une websocket.

Voilà dans les grandes lignes la solution qui me paraîtrait vraiment adresser le soucis et pas seulement "reculer pour mieux sauter".

NB : une requête Ajax qui fout ton serveur sous l'eau en setInterval toutes les 5s c'est une horrible mauvaise idée.

Question : il se passe quoi si ton serveur met plus de 5s à répondre ?

Un truc simple déjà c'est de remplacer ton setInterval par une setTimeout qui attend le résultat de la requête précédente avant de relancer un polling. Là c'est clair que tu vas écrouler ton serveur.

J'ai un flag, donc si la query lancé par un setInterval n'est pas fini, il stop et attend le prochain tour. Je suis pas fou non plus .

Il y a bien mongoDB qui est géré par symfony, mais bon je ne connais pas assez mongoDB pour me lancer la dedans et je ne fais pas confiance à symfony. Genre si tu fais confiance à symfony aveuglement il te fait des SELECT * FROM et ne met que des indexes au FK. Et surtout AUCUNE idée de comment faire bosser Mysql et Mongodb mains dans la mains sous symfony. De plus Mongodb n'a pas l'air non plus drastiquement plus rapide que Mysql.

Je sais que c'est reculer que de faire un système par MP basique, mais quand je vois mes concurrents (ceux à mon niveau) ils le font aussi. Donc voila je privilégie un site +/-rapide qu'a un site lent avec des fonctionnalités de foufou. Après rien m’empêche par la suite d'implémenter un rafraîchissement des messages via ajax ou socket si je vois que le serveur est soulagé.

Salut,

Même si tu ne comptes pas faire de conversations à plus que 2, la table avec user1_id, user2_id, c'est mauvais et clairement moins performant que des jointures en bonne et due forme.

Je te conseille donc la formule classique pour les relations many-to-many :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

create table users (
id int unsigned auto_increment,
...
primary key(id)
);

create table threads (
id int unsigned auto_increment,
...
primary key(id)
);

create table user_threads (
user int not null,
thread int not null,
...
index reversed(thread,user) # si besoin, peut-être ce n'est pas nécessaire selon ce que tu veux faire
primary key(user,thread) # Ou l'inverse selon le sens dans lequel tu as prévu de faire le plus de requêtes
);

En principe tu ne devrais pas avoir besoin d'un champ id en plus dans la table de jointure. Ca ne t'empêche pas d'avoir deux utilisateurs qui ont deux conversations distinctes en même temps.

Même si tu ne comptes pas faire de conversations à plus que 2, la table avec user1_id, user2_id, c'est mauvais et clairement moins performant que des jointures en bonne et due forme.

Oui je l'ai expérimenté et c'est caca. Je vais commencer demain ce truc alors. So much fun en perspective

Donc voila comment j'ai fait (je suis sous symfony2, mais bon c'est compréhensible si vous touchez au php et oo) :

Une entity/model Thread :

• id
• text : sorte de cache qui sert à stocker une partie du dernier message. Afin d'éviter de faire une query
• date : je vais trier par date donc pas oublié l'index
• lastSender : c'est l'id de la dernière personne a avoir posté, ATTENTION ce N'est PAS une relation c'est un simple integer.
• isRead : simple boolean pour savoir si le message a été lu ou non. Ca s’adresse à l'autre partie donc pas au lastSender

Les deux derniers champs permettre de faire quelque chose du genre : if(me != lastSender && !isRead){ message non lu } , Car forcément si vous êtes le dernier sender vous avez lu le thread.

Puis forcément mettre la relation :

1
2
3
4
5

<?
    /**
    * @ORM\ManyToMany(targetEntity="Acme\UserBundle\Entity\User", cascade={"persist"})
    */
    private $users;

Ensuite pour récupérer les threads donc conversations d'un user :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

<?
    public function getThreads($user) {
        $q = $this->createQueryBuilder('t')
                    ->leftJoin('t.users', 'u')
                        ->addSelect('partial u.{xxx,xxx,xxx,}')
                    ->where('u.id != :user')
                    ->setParameter('user', $user)
                    ->orderBy('t.date', 'DESC')
                    ->setMaxResults(20);

        return $q->getQuery()->getResult();
    }

Ce qui donne une query dont je ne pense pas pouvoir l'améliorer (des avis ?) :

1
2
3
4
5
6
7
8

    SELECT 
    ...
    FROM pmthread p0_
    LEFT JOIN thread_user t2_ ON p0_.id = t2_.thread_id
    LEFT JOIN fos_user f1_ ON f1_.id = t2_.user_id
    WHERE f1_.id <> 3
    ORDER BY p0_.date 
    DESC LIMIT 20

Ensuite pour la table message c'est super compliqué, faut s'accrocher :

• id
• user_id : qui a posté le message ?
• thead_id : de quel thread appartient le message ?
• text : le message
• date : la date

Je ne vous ai pas menti, c'est une table complètement folle et inattendu .

Donc voila si vous avez des avis sur la query, si elle est bonne bah je mettrai le topic comme résolu.

Merci

Bon je viens de tilt que ma query est mauvaise.

Actuellement j'ai cela :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

  <?
    public function getThreads($user) {
        $q = $this->createQueryBuilder('t')
                    ->leftJoin('t.users', 'u')
                        ->addSelect('partial u.{xxx}')
                    ->where('u.id = :user')
                    ->setParameter('user', $user)
                    ->orderBy('t.date', 'DESC')
                    ->setMaxResults(20);

        return $q->getQuery()->getResult();
    }

Donc ça me sort bien les threads du user, mais forcément le JOIN se fait sur moi. Ce qui donne quelque chose du genre :

1
2
3
4
5

 De       | Texte          | Date   
----------|----------------|--------
 Hotgeart | Hello world    | 31 jan 
 Hotgeart | Hello space    | 20 jan 
 Hotgeart | Hello zeste    | 10 jan

Alors que forcément j'ai besoin du nom de l'autre participant dans ma liste des mp :

1
2
3
4
5

 De       | Texte          | Date   
----------|----------------|--------
 Homer    | Hello world    | 31 jan 
 Bart     | Hello space    | 20 jan 
 Lisa     | Hello zeste    | 10 jan

Une idée de comment changer ce query builder ?

Salut !

Je pense que là, on va tomber dans un cas où il faudra faire une "double jointure" et utiliser WITH. J'essaierais ainsi :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

<?
    public function getThreads($user) {
        $q = $this->createQueryBuilder('t');
        $q          ->leftJoin('t.users', 'u', Doctrine\ORM\Query\Expr\Join::WITH, $q->expr()->eq('u.id', ':user'))
                        ->addSelect('partial u.{xxx}')
                    ->leftJoin('t.users', 'u2', Doctrine\ORM\Query\Expr\Join::WITH, $q->expr()->neq('u2.id', ':user'))
                        ->addSelect('partial u2.{xxx}')
                    ->where('u.id = :user')
                    ->setParameter('user', $user)
                    ->orderBy('t.date', 'DESC')
                    ->setMaxResults(20);

        return $q->getQuery()->getResult();
    }

Avec ça, je m'attendrais à ce que le premier élément de ta collection d'objets Utilisateurs dans l'objet Thread soit toi, et le suivant (ou les suivants) soit l'autre personne de la discussion.

Merci pour ta réponse, j'ai juste du rajouter un \ au début de Doctrine\ORM\Query\Expr\Join::WITH sinon il m’ennuyait comme quoi il ne trouvait pas la class.

Ça a bien le comportement que tu attendais, je suis bien le premier et le deuxième mon interlocuteur. Cependant, il sort des doublons. Donc pour reprendre mon super tableau, il fait :

1
2
3
4
5
6

 De       | Texte          | Date   
----------|----------------|--------
 Homer    | Hello world    | 31 jan 
 Homer    | Hello world    | 31 jan 
 Bart     | Hello space    | 20 jan 
 Bart     | Hello space    | 20 jan 

Merci

Mmm, je penserais à ajouter ->distinct(), mais là, du coup, je ne vois plus trop où le mettre… En général, c'est après ->select() ou ->addSelect(), mais je ne crois pas que ç'aurait le comportement voulu dans notre cas. Essaie éventuellement de le mettre par exemple après le ->where() ?

Avec le ->distinc()

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

   <?    $q = $this->createQueryBuilder('t');
                    $q->leftJoin('t.users', 'u', \Doctrine\ORM\Query\Expr\Join::WITH, $q->expr()->eq('u.id', ':user'))
                    ->leftJoin('t.users', 'u2', \Doctrine\ORM\Query\Expr\Join::WITH, $q->expr()->neq('u2.id', ':user'))
                        ->addSelect('partial u.{...}')
                    ->where('u.id = :user')
                    ->setParameter('user', $user)
                    ->distinct()
                    ->orderBy('t.date', 'DESC')
                    ->setMaxResults(20);

        return $q->getQuery()->getResult();

Ça donne le même résultat que cette query du dessus, donc mon nom et l'interlocuteur n'est pas dans l'objet/résultat.

Est ce que mon design de table/logique est a revoir ? Car pourtant j'essaye de faire un truc classique pour que justement ça soit simple a coder et rapide au niveau mysql (d'ou la raison que je re-code mon système de MP). Si tu devais faire un système de MP c'est aussi comme ça que tu le ferrais ? Sachant qu'il ne peut y avoir que deux interlocuteurs contrairement à ici.

S'il n'y a que deux interlocuteurs, je ferais deux relations entre User et Thread (c'est bien l'entité qui enregistre un message, au fait ?), une pour l'auteur, et l'autre pour le destinataire.

D'ailleurs, à la limite, si on étend à plusieurs destinataires, on a toujours un seul auteur.

Edit

Avec un peu plus de réflexion, on pourrait aussi n'avoir besoin que de l'expéditeur, le(s) destinataire(s) serai(en)t récupéré(s) depuis les participants au sujet de discussion en excluant l'auteur.

je ferais deux relations entre User et Thread

Tu veux dire crée un doublon ? Pas trop compris.

Je vais tout expliquer peut-être que tu y verras plus claire. En gros ce système de MP est pour un site de rencontre, donc il n'y aura toujours que 2 interlocuteurs, jamais 3. Et qu'une seule conversation est possible entre les deux interlocuteurs.

Par exemple ici, je pourrais t'envoyer un message de bonne année (ce qui crée un thread), et 2 mois plus tard un autre pour te souhaiter une bonne st-valentin (ce qui crée un autre thread). Sur mon site non, si tu cliques sur envoyer un MP, ça te redirige vers la conversation. Un peu comme facebook.

Actuellement j'ai ce système :

Si on était sur mon site et que je veux t'envoyer un message, il va me créer un thread (ou te rediriger vers celui qui existe, s'il en existe un) de la façon suivante :

• id
• user1 (int): Toi
• user2 (int): Moi
• text (var): text cache du dernier post
• isRead (bool): Si le thread a été lu ou non
• lastSender (int) : cache id du dernier sender
• date (datetime) : date cache du dernier post

Donc si j'envoie un message : 'Coucou Ymox', il va forcément créer une entrée dans post, mais update aussi le thread en conséquence.

• text : 'Coucou Ymox'
• isRead : 0 (car tu ne l'as pas encore lu, ça se mettra à 1 quand tu afficheras le thread)
• LastSender : mon id (c'est juste pour savoir a qui appartient le statut isRead, qui le "contrôle". Donc si toi tu affiches le thread et que celui-ci est à 0 et que tu n'es pas le lastSender il ferra un update pour le mettre à 1)
• date : un bête update NOW()

Cependant avec ce système quand je dois récupérer la liste des threads, c'est le caca pour 2 raisons :

1. Je fais un Join sur user1 et user2 car forcément je ne sais pas qui est qui. Tu pourrais très bien être l'user1 comme l'user2. Donc forcément j'ai un JOIN sur moi qui ne sert a rien, car je n'afficherai rien me concernant.

2. J'utilise un OR pour avoir tous les threads, car je suis user1 ou user2, car forcément je ne sais pas ou je suis SELECT * FROM t where user1 = :user OR user2 = :user.

D’où mon souhait de re-coder ça pour avoir une query efficace en temps d'exécution pour récupérer la liste des threads. Car forcément comme c'est un site de rencontre c'est la query la plus demandée, donc aux heures de pointes mon serveur est à genoux.

Essaie d'ajouter un group by sur les ID des threads à ta requête qui sort des doublons. C'est peut-être pas du SQL très clean mais ça marchera je pense.

JE ne peux pas t'aider plus, je n'utilise pas de framework.

En faisant un ->groupBy('t.id') je perds l'info sur l'autre user, tout ses champs sont à NULL

Et un regroupement sur l'ID des utilisateurs ?

Non plus, j'ai une perte de row et les champs de l'interlocuteur sont null.

Une idée tout à coup, pourquoi ne pas essayer ce genre de requête ?

1
2
3
4
5
6
7
8

select t.*, u.*, v.*
from threads_users j
join threads t on t.id=j.thread
join users u on u.id=j.user
join threads_users k on k.thread=t.id
join users v on v.id=k.user
where j.user = current_connected_user_id
group by t.id

Note bien ce que j'appellerais le « va-et-vient ». On commence avec la table de jointure, on récupère les threads intéressants, et on repasse par la table de jointure pour récupérer les infos sur l'autre utilisateur. Sinon, en fait, après réflexion, il me semble bien qu'il n'y a pas moyen de se débarrasser des doublons autrement sans perdre d'information.

Peut-être, je dois trouver comment sélectionner la table intermédiaire, car de base tu ne peux pas avec doctrine vu que theads_users n'est pas une entité/objet et "n'existe pas" à ses yeux. Surement possible en DQL.

Mais bon au pire la requête avec doublons n'était pas mauvaise, dans le sens ou je n'ai qu'a supprimer les doublons, donc n'afficher que 1/2. Car mon principal but c'est la rapidité de la query et non pas avoir une belle query. J'ai peur qu'avec ces 4 join ça va durer plus de temps que juste faire la query avec doublon qui n'a que 2 join puis la nettoyer avec PHP. Style stocker les ID des threads dans un tableau et si in_array bah tu passes à la row suivante.

Je vais voir si j'y arrive et faire un EXPLAIN des deux.