Lenteur de MySQL, Drupal, Views & User Relationship

Maxime Topolov

Maxime Topolov

8 février 2009 | Catégorie: Drupal, Etude de cas, MySQL |

Suite à la mise en production d’une nouvelle version d’un gros site communautaire basé sur Drupal, le serveur ne répondait plus au bout d’une heure… Une rapide étude avec la commande top permet de voir que la base du site semble surchargée. Première décision prise avec le client : séparer la base MySQL et Apache sur deux serveurs. Deux heures plus tard, la base du site tombe à nouveau…

Dans cet article je vais retracer les étapes pour identifier et corriger le problème.

1. Activation du log des requêtes lentes MySQL

Alors qu’il est 2 heures du matin et que le trafic est assez faible, la base tombe. Le probleme vient donc forcement des changements de la nouvelle version. Toute fois, entre la version précédente et celle en ligne on a 200 revisions sur plusieurs dizaines de fichiers, sans parler des modifications en base de données sur les Panels et Views… Autant dire qu’il serait trop long d’analyser chaque révision SVN. On va donc aller identifier la ou les requêtes qui posent problème.

Premiere étape : activer le log des requêtes lentes sur MySQL. Pour cela, il faut aller éditer le fichier de configuration MySQL my.cnf. Il se trouve probablement dans

/etc/my.cnf

Dans la séction [mysqld], rajouter ou éditer les lignes suivantes :

log-slow-queries = /var/log/mysql-slow-queries.log
long_query_time = 1

MySQL va alors logguer toutes les requêtes dont le temps d’exécution est supérieur à 1 seconde. En fonction du site que vous avez ou des problèmes constatés, il serait peut-être judicieux d’augmenter ce paramètre.

Redémarrez MySQL avec la commande

/etc/init.d/mysql restart

Laissez tourner le site quelques instants histoire de remplir le log.

2. Étude du log des requêtes lentes

Une fois rempli, vous pouvez étudier les requêtes avec un vim ou en utilisant la commande mysql : mysqldumpslow

mysqldumpslow -s c -t 10

Chez nous, la requete suivante posait problème, avec plus de 300 secondes pour s’exécuter… !

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
SELECT DISTINCT(node.nid),
bio_node_data_field_photo.field_photo_fid AS bio_node_data_field_photo_field_photo_fid,
bio_node_data_field_photo.field_photo_title AS bio_node_data_field_photo_field_photo_title,
bio_node_data_field_photo.field_photo_alt AS bio_node_data_field_photo_field_photo_alt,
node_data_field_user_motto.field_user_motto_value AS node_data_field_user_motto_field_user_motto_value,
user_relationships.approved AS user_relationships_approved,
node.title AS node_title,
node.changed AS node_changed,
user_bio.uid AS node_uid,
user_relationships_add_friend.approved AS user_relationships_add_friend_add_friend_link,
bio.nid AS bio_nid
FROM node node
LEFT JOIN user_relationships user_relationships_to_user ON node.uid = user_relationships_to_user.requester_id
LEFT JOIN user_relationships user_relationships_to_user2 ON node.uid = user_relationships_to_user2.requester_id
LEFT JOIN node bio ON node.uid = bio.uid AND bio.type = 'bio'
LEFT JOIN content_type_bio bio_node_data_field_photo ON bio.vid = bio_node_data_field_photo.vid
LEFT JOIN content_type_bio node_data_field_user_motto ON node.vid = node_data_field_user_motto.vid
LEFT JOIN user_relationships user_relationships ON node.uid = user_relationships.requestee_id
LEFT JOIN bio user_bio ON node.nid = user_bio.nid
LEFT JOIN user_relationships user_relationships_add_friend ON node.uid = user_relationships_add_friend.requester_id
INNER JOIN node_access na ON na.nid = node.nid
WHERE
(na.grant_view >= 1
AND ((na.gid = 0 AND na.realm = 'all')
OR (na.gid = 1 AND na.realm = 'nodeaccess_rid')
OR (na.gid = 0 AND na.realm = 'nodeaccess_uid')
OR (na.gid = 0 AND na.realm = 'nodeaccess_author')
OR (na.gid = 0 AND na.realm = 'user_relationship_node_access_author')
OR (na.gid = 0 AND na.realm = 'og_public')))
AND ( (node.type IN ('bio'))
AND (user_relationships_to_user.requestee_id = 116)
AND (user_relationships_to_user.approved = 1) )
GROUP BY node.nid  LIMIT 0, 10;

L’autre moyen simple de surveiller l’état de MySQL est d’installer l’utilitaire mytop (yum install mytop / apt-get mytop….). En lancant mytop on voit la charge du serveur et, surtout, la liste des requêtes les plus lentes.

3. Rechercher la source de la requête.

Personne n’ayant écrit une pareille merveille il fallait donc rechercher dans le code la provenance de la requete. Un grep -R sur /sites/all n’a rien donné. Il faut donc étudier de plus près la requete, Drupal ayant plusieurs modules générant des requetes de maniere dynamique. Premiere constatation : il y a peu de logique dans l’aligement des JOINS, on peut donc penser à CCK ou Views, en effet les tables concernent uniquement le contenu et le module Node Access.

Les tables balayées appartenant a plusieurs types de contenus, on retient Views en éliminant CCK. On a donc une view qui génère une requete trop lente. Le site a des centaines de Views, autant chercher une aiguille dans une botte de foin… On revient sur le grep… En effet, on constate que parmi les tables de contenu on a Node Access et User relationship. Node Access override toutes les requêtes de manière automatique, il reste dont un handler de views proposé par User Relationship. Oui mais comment trouver ?

Un handler de view demander la déclaration de tableaux, chaque tableau est attribué d’un nom et qui recouvre plusieurs tableaux MySQL. Coté SQL, views va attribué des alias, tels que déclaré dans le handler. On peut donc rechercher dans le code un alias de la requête. J’ai pris celui qui me semblait le moins évident : user_relationships_add_friend_add_friend_link.

grep -R "user_relationships_add_friend_add_friend_link" /sites/all/*

Bingo, un seul module est pointé, mais pas User Relationship. C’est un module à nous, un handler de views qui permet d’ajouter automatiquement les liens “Ajouter à un ami” à tous les listings des membres du site.

4. Correction du bug dans le handler de views

En fait il ne s’agit pas vraiment d’un bug, disons d’une optimisation, pas évidente à trouver pour le développeur. En effet nous développons le site sur une version allégée, qui n’a pas le contenu de la production. La requête passait plutôt bien et on y a vu que du feu. Analysons le code de la version posant problème :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
function user_relationships_views_extends_arg_handler($op, &$query, $arg_type, $arg = '') {
  if ('filter' == $op) {
    switch ($arg_type['type']) {
     	case 'ur_uid_requestee':
     	  $arg = intval($arg);
 
          $query->add_table('user_relationships_to_user', true);
          $query->add_where('user_relationships_to_user.requestee_id = %d', $arg);     	  
     	break;
 
        case 'ur_approved_requestee':
          $arg = intval($arg);
 
          $query->add_table('user_relationships_to_user', true);
          $query->add_where('user_relationships_to_user.approved = %d', $arg);
        break;             	
    }    
  }
}

On remarque dans la requete que nous avons deux JOINs dont le second est complétement inutile, force MySQL a dupliquer les données pour chaque ligne retourner et provoque donc un full scan.

1
2
LEFT JOIN user_relationships user_relationships_to_user ON node.uid = user_relationships_to_user.requester_id
LEFT JOIN user_relationships user_relationships_to_user2 ON node.uid = user_relationships_to_user2.requester_id

On vois immédiatement que le probleme vient du fait que nous rajoutons deux fois la table “user_relationships_to_user” dans le handler d’argument.

La correction consiste en la vérification de l’existance de la table, grâce à la fonction de l’API views : ensure_table :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
function user_relationships_views_extends_arg_handler($op, &$query, $arg_type, $arg = '') {
  if ('filter' == $op) {
    switch ($arg_type['type']) {
     	case 'ur_uid_requestee':
     	  $arg = intval($arg);
          $query->ensure_table('user_relationships_to_user');
          $query->add_where('user_relationships_to_user.requestee_id = %d', $arg);     	  
     	break;
 
        case 'ur_approved_requestee':
          $arg = intval($arg);
 
          $query->ensure_table('user_relationships_to_user');
          $query->add_where('user_relationships_to_user.approved = %d', $arg);
        break;             	
    }    
  }
}

5. Pour aller plus loin…

Je profite de ce petit post pour vous conseiller d’utiliser la commande EXPLAIN de MySQL permettant d’analyser la maniere de gérer un requete par MySQL. Pour des requetes simples il est souvent simple de prévoir quels indexes MySQL va utiliser ou pas. Il en va autrement pour des requetes complexes construites par Views. En rajoutant EXPLAIN devant notre grosse requete, on obtient le résultat suivant :

Pour plus d’informations sur la syntaxe de EXPLAIN, visitez le site de MySQL.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
+----+-------------+-------------------------------+--------+-------------------------------------------------+--------------+---------+--------------------------------------------------+------+----------------------------------------------+
| id | select_type | table                         | type   | possible_keys                                   | key          | key_len | ref                                              | rows | Extra                                        |
+----+-------------+-------------------------------+--------+-------------------------------------------------+--------------+---------+--------------------------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | user_relationships_to_user    | ref    | relationship,requester_id,requestee_id,approved | requestee_id | 4       | const                                            |    6 | Using where; Using temporary; Using filesort | 
|  1 | SIMPLE      | node                          | ref    | PRIMARY,node_type,uid,nid                       | uid          | 4       | familler.user_relationships_to_user.requester_id |    1 | Using where                                  | 
|  1 | SIMPLE      | user_relationships_to_user2   | ref    | relationship,requester_id                       | requester_id | 4       | familler.user_relationships_to_user.requester_id |    8 | Using index                                  | 
|  1 | SIMPLE      | bio                           | ref    | node_type,uid                                   | uid          | 4       | familler.node.uid                                |    1 |                                              | 
|  1 | SIMPLE      | bio_node_data_field_photo     | eq_ref | PRIMARY                                         | PRIMARY      | 4       | familler.bio.vid                                 |    1 |                                              | 
|  1 | SIMPLE      | node_data_field_user_motto    | eq_ref | PRIMARY                                         | PRIMARY      | 4       | familler.node.vid                                |    1 |                                              | 
|  1 | SIMPLE      | user_relationships            | ref    | requestee_id                                    | requestee_id | 4       | familler.node.uid                                |    2 |                                              | 
|  1 | SIMPLE      | na                            | ref    | PRIMARY,nid                                     | nid          | 4       | familler.node.nid                                |    3 | Using where                                  | 
|  1 | SIMPLE      | user_bio                      | ref    | PRIMARY                                         | PRIMARY      | 4       | familler.na.nid                                  |   61 | Using index                                  | 
|  1 | SIMPLE      | user_relationships_add_friend | ref    | relationship,requester_id                       | requester_id | 4       | familler.node.uid                                |    8 |                                              | 
+----+-------------+-------------------------------+--------+-------------------------------------------------+--------------+---------+--------------------------------------------------+------+----------------------------------------------+
10 rows in set (0.01 sec)

Enfin, suite au commentaire d’Alexandre, je vous invite à lire l’excellent article de Maximilian sur le cache MySQL.



6 commentaires sur “Lenteur de MySQL, Drupal, Views & User Relationship”

  1. Alexandre Ouicher dit :

    Je suis le fameux client et l’analyse effectuée durant toute la nuit a démontrer une faiblesse du module “view”.
    Il n’y a pas de gestion d’optimisation des requettes sur sur la fonction add_table.
    Pourquoi ajouter un ensure_table alors que add_table pourrait être codé pour le faire automatiquement.
    Ceci est choix et je respecte.

    Quand à la configuration de ce site, après une division de la BDD sur un autre serveur, nous avons ajouter un gestion du cache MYSQL.

    Résultat, le site tourne à merveille et a depuis explosé les records d’inscriptions.

    Un grand merci aux équipes d’Adyax.

  2. Cédric dit :

    Bonjour,
    ça me laisse sans voix.

    Moi qui ait l’habitude de travailler avec l’archaïque SPIP en tombe à la renverse. Dans ce vieux CMS passé de mode :
    - les requêtes sont construites par le système de boucles à la compilation (et pas à l’exécution)
    - corrolairement elles sont optimisées (y compris dans la gestion des jointures), puisqu’on peut se permettre de payer le coût d’optimisation qui ne sera payé qu’une seule fois, à la compilation
    - il y a un outil intégré d’optimisation et de diagnostique directement utilisable sur le site, qui permet de voir en détail
    - l’ensemble des requêtes générées pour le calcul d’une page
    - classées par temps d’exécution * nombre d’exécutions
    - accompagnées du détail de la requête et la référence du code qui la génère

    Quand je lis tout cet article, je crois comprendre un peu mieux les retours d’expérience que j’entends ici ou là concernant les ressources machines consommées par les sites réalisés avec Drupal …
    Et les architectures serveurs déployées pour fournir …

    • Bonjour Cédric !
      Petits compléments d’informations, je ne voudrais surtout pas tomber dans le débat SPIP vs Drupal vs Typo3 vs ez…..

      1. Il faut bien garder à l’esprit que le choix d’un CMS se fait en premier lieu sur les fonctionnalités, puis sur la simplicité d’utilisation et seulement à la fin sur la vitesse d’exécution. L’optimisation, dans tout logiciel doit venir à la fin.
      2. La compilation des requêtes a du sens pour un site statique (qui peut être caché), encore que je n’en vois pas l’intérêt si un bon système de cache fonctionne derrière. N’oubliez pas que SPIP se base sur le système très propriétaire de squelettes, mais tu dois mieux savoir que moi, car j’ai jamais eu l’occasion de bosser avec SPIP.
      3. La structure modulaire et les fonctionnalités communautaires de Drupal fait que ce CMS a connu un tel succès, elle empêche aussi la compilation. Enfin je reste assez songeur quant à cette compilation… La puissance de Views est son niveau d’abstraction, tout module peut injecter du contenu pour Views, le templating est rendu très simple.
      4. Globalement Drupal consomme plus de ressources qu’un site sous SPIP, mais je dirais c’est normal, proposant beaucoup plus de fonctionnalités… Un peu comme Windows 3.11 consomme beaucoup moins que Windows XP….
      5. Il faut pas tomber à la renverse à cause de détails, il faut analyser la puissance structurelle d’un systeme. Le détails, optimisations se régleront avec le temps (c’est d’ailleurs déjà le cas avec Drupal 6 et Views 2), en revanche le design modulaire, un code très pur et bien commenté en ANGLAIS fait de Drupal une machine de guerre terrible. Les modules Views & CCK n’ont pas d’équivalent sur d’autres CMS.

      Donc globalement, Drupal reste loin devant SPIP sur la qualité du code et l’architecture globale. Il reste biensur, comme sur n’importe quel projet de grosses pistes d’amélioration et d’optimisation. Mais le combat n’est pas là. Il est sur l’ergonomie des interfaces et la simplicité d’utilisation. C’est, pour Drupal, le combat de demain, celui qu’il doit gagner pour continuer à être le CMS numéro 1 du marché.

      Maxime.

  3. Boyquotes dit :

    Bonjour,

    >>N’oubliez pas que SPIP se base sur le système très propriétaire de squelette
    La, je suis très surpris, soit j’ai pas la même définition que toi de propriétaire, soit il faut m’expliquer où les squelettes sont propriétaires ( format ?,…)

    Views & CCK sont des outils intéressants pour la faciliter qu’il amène à l’outils, mais l’effet papillon de cela et une lourdeur au niveau de l’exécution pour permettre toutes ces facilités.

    Ce que je trouve dommage dans Drupal est son aspect “tous” clicodrome ( voir aussi http://arnumeral.fr/articles/drupal/drupal-views-et-les-dangers-du-clickodrome), le fait de tous vouloir régler par cli, plus le fait de la quantité de “gros” modules qu’ils faut ajouter pour arriver à un site aussi fonctionnel qu’un spip ou joomla out of box est assez impressionnante (nombre lignes de codes ajoutés, plein de fonctionnalités qui ne servent pas dans tous les cas).
    On “simplifie” à l’extrême la vie du webmaster quitte à avoir un système énorme(et dur à comprendre) pour un petit besoin…

    Dans une prochaine mise à jour de mon article, je ferais le comparatif pour un site avec des fonctionnalité lambda pour voir la quantité de code à maintenir d’une solution à une autre. ( http://www.codes-libres.org/blog/?Comparaisons-des-CMS-SPIP-Joomla )

    Bonne journée.
    Nicolas.

    • Tout dépend. Si vous souhaitez mettre en place un petit site simple, un blog Drupal n’est peut être pas (plus?) la solution idéale, auquel cas il faudrait preferer un Wordpress. En revanche, le votre site doit prendre un traffic très important, avoir une partie communautaire importante, et un système très souple de gestion de contenu, adapté aux journalistes ou rédacteurs…

      Par ailleurs Drupal est un clickodrome pour les débutants, dans les configuration professionnelles on prefere “décendre” les Views dans du code. Enfin, les performances ne se jouent pas (en tous cas pour des sites statiques) sur Drupal / SPIP / Joomla / ezPublish / Typo ou je ne sais quoi d’autre… De toutes manieres au-delà d’un certain traffic, votre CMS, quelqu’il soit fera tomber vos machines. Sur ce genre de sites on va mettre en place des systèmes de cache performants (Varnish, Memcached, ….) intégrer dans certaines parties des bases de données non-relationnelles (MongoDB…) et c’est sur l’intégration de ce genre d’outil que Drupal offre toute sa puissance et la qualité de son API extensible, propre et surtout avec une communauté très importante de développeurs… ce que manque à SPIP ou à d’autres.

  4. Boyquotes dit :

    >>quelqu’il soit fera tomber vos machines.
    Quelqu’il soit oui, je suis d’accord sur le fonds, il faut les moyens de sa politique, mais si on a pas un serveur dédié ou si on approche des limites ,on est content d’avoir choisi l’un plutôt que l’autre à ce moment là:
    http://www.spip-blog.net/CMS-et-sites-a-fort-trafic-parlons-chiffres.html

    >>4. Globalement Drupal consomme plus de ressources qu’un site sous SPIP, mais je dirais >>c’est normal, proposant beaucoup plus de fonctionnalités… Un peu comme Windows 3.11 >>consomme beaucoup moins que Windows XP….
    Comme dis dans mon comparatif, drupal offre moins de fonctionnalités que SPIP, JOOMLA… , on a pas de gestion des images dans les articles, pas de commentaire, pas de statistique, pas d’éditeur wysiwig, pas de gestion des flux rss… et quand on commence à ajouter ça, je pense qu’on ai de suite moins performant non?

    Le problème du clicodrome et pas lié au fait d’être débutant, mais au fait d’ajouter les fonctionnalités pour un site avec des fonctionnalités( ce à quoi Drupal est destiné), tous ces modules ajoutes des entrées dans les menus, des pages de configuration( et donc toutes les lignes de codes qui vont avec…), à la fin on se retrouve avec 30 lignes de code(je suis gentils) pour permettre de cocher quelques cases dans un formulaire d’admin alors qu’éditez et modifier 2/3 lignes aurait suffit…

    Mais, je le répète, je cite les points faibles de Drupal, mais ça reste un outils intéressant comme dis plus haut, juste un avis personnel sur son utilisation…

    Bonne journée.
    Nicolas.

Laisser une réponse