[spip-dev] critère IN, SELECT FIELD et performances

Simon_Camerlo · Février 23, 2009, 8:57

Bonjour,

je me tourne aujourd'hui vers vous après avoir posé ma question sans succès sur forum.spip.org puis sur la liste spip@rezo.net.

J'ai un énorme problème de performances en raison de la manière dont spip gère les critères IN :
plutôt que de générer une requête SQL "IN" (ce qui serait logique), les fonctions "critere_IN_dist" et "calculer_critere_externe_init" de public.php génèrent des SELECT FIELD() qui n'utilisent pas les index dans la base (!!).

Mon site est bloqué sous spip 1.9.2 pour encore au moins 6 mois, donc est-ce que quelqu'un aurait une version de ces deux fonctions compatible avec la v1.9.2g qui génèrerait des requêtes SQL IN ? ou encore une méthode pour forcer l'optimiseur mysql à utiliser les index avec les SELECT FIELD ?

Pour répondre par avance aux questions / remarques :
- je sais pourquoi ce choix a été fait, j'ai lu les archives de la liste : conserver l'ordre des paramètres d'entrée dans le résultat
=> je n'ai pas besoin de cette particularité

- la structure de mon site est atypique par obligation (contraintes éditoriales) et utilise IN et les ARRAY intensivement pour palier
le manque de {branche IN a,b,c} et {!id_mot=x} de spip 1.9 avec des performances optimales (normalement du moins, si les index étaient utilisés)

- je ne connais malheureusement pas assez les entrailles de spip et n'ai pas le temps de m'y plonger suffisamment dans l'immédiat pour me lancer dans la modification
de ces fonctions moi-même car je maintiens un site à titre professionnel et ai de grosses contraintes d'emploi du temps par ailleurs.

- on m'a signalé sur spip@rezo.net que la version 2.x générait des requêtes SQL "IN" dès qu'un GROUP BY est aussi présent
    => je répète que je suis dans l'impossibilité de passer à la version 2 pour le moment
    => même si c'est mieux, ce choix me paraît aussi hasardeux et contre-intuitif : du point de vue utilisateur on s'attend à ce qu'un "IN" spip
         génère un "IN" SQL quel que soit le cas, l'exception où on veut conserver l'ordre des valeurs d'entrée faisant l'objet d'un traitement spécifique, par exemple avec un modificateur.
         (Sinon, si ce n'est pas pour maintenir la cohérence avec SQL et autres, pourquoi garder le critère en anglais plutôt que de l'appeler "PARMI" ?)
         Dans mon cas particulier, utiliser le critère en v2.0 tel qu'il est aujourd'hui me forcerait à trier les résultats pour éviter de réduire les performances, alors que je n'ai pas besoin du tri
        (création d'un champ de référence d'id_rubrique sur lequel je fais plusieurs post-traitements, le tri intervenant à la fin sur les articles contenus uniquement)

Merci d'avance à ceux qui pourront m'aider sur ce coup-là, j'aimerais autant ne pas avoir à réécrire plusieurs centaines de lignes sur les quelques milliers que comptent mes squelettes...
Et vivement que je puisse travailler avec la v2 qui m'a juste l'air énorme !

Simon Camerlo

cerdic · Février 23, 2009, 9:56

Bonjour,
les problèmes de performances sont en général une accumulation de pratiques peu idéales, et IN n'est en général que l'arbre qui cache la forêt.
En ce qui le concerne, cela se traite en 2min en mettant dans ton mes_fonctions quelques lignes de code pour redéfinir le critère IN, mais je doute vraiment que cela accélère soudainement ton site.

Cédric

cerdic · Février 23, 2009, 10:54

Je ne vais pas trop rentrer dans les détails car mon but n'est pas de troller, mais :

- les pratiques utilisées sont certes atypiques mais obligatoire du fait de la structure éditoriale que je coordonne

- le site fonctionnait plus vite avant que je bascule le code sur les critères IN pour l'optimiser (ironie ?)
  l'optimisation en question a réduit la complexité générale de mon algo principal de (6n + n log m) à (2n + log (m+n)), avec en plus un énorme impact sur l'optimalité des requêtes envoyées à la base :
  Les mesures expérimentales montrent une réduction de 50 % du nombre des requêtes envoyées à mysql, une réduction de 90% de la taille en octet de ces requêtes et un envoi de résultat de mysql vers
  php réduit de 30 % pour le même affichage => plus proche de l'optimum.
  Le seul point de blocage : les temps de latence entraînés par des parcours complets de (grosses) table à répétition à cause de SELECT FIELD, locks qui n'arriveraient pas si IN générait du IN
   (je maintiens que c'est un mauvais choix)

Peut-être ne ferions nous pas comme cela si l'on partait d'une feuille blanche, mais il faut assumer l'histoire et assurer la compatibilité ascendante qui évite à tout le monde de devoir recoder tous ses squelettes.
Ce soucis de ne pas oublier les utilisateurs et leurs problèmes concrets, et de ne pas les entraîner dans des opérations complexes hors de leur portée est ce qui fait l'essence même de SPIP, et probablement, sans vouloir troller, ce qui, il me semble, en a fait une grosse différence avec Agora (paix à l'ame de son code).

Il est par ailleurs naturel de considérer qu'en cas d'arbitrage, il faut favoriser la vie des multiples utilisateurs qui utilisent SPIP pour faire des sites simples, sans ressources, car ceux qui utilisent SPIP pour faire des gros sites complexes sont supposés avoir un minimum de budget et de compétences à mettre en face pour traiter les problèmes plus complexes qui se posent à eux, comme l'optimisation des performances.

Le cas contraire que vous semblez représenter montre clairement que SPIP permet d'aller très loin avec vraiment peu de ressources (humaines et financières), ce que l'on peut considérer comme un succès donc

- ça se traite en 2 minutes pour quelqu'un qui connaît très bien le coeur de spip et les implications de ce qu'il code, ce qui n'est pas mon cas

Alors merci de rester constructif.

Oh, ce ne sont pas des paroles dans le vide, mais un retour d'expérience de l'analyse et de l'optimisation de gros sites usines a gaz "parce que l'éditorial ne permettait pas de faire autrement".

En ce qui concerne le patch, il consiste à copier/coller les lignes de définition du critère IN de spip 2.0, en commentant les 3 lignes qui ajoutent le tri par le FIELD(...)

// Si on a une liste de valeurs dans #ENV{x}, utiliser la double etoile
// pour faire par exemple {id_article IN #ENV**{liste_articles}}
// http://doc.spip.org/@critere_IN_dist
function critere_IN ($idb, &$boucles, $crit)
{

  list($arg, $op, $val, $col, $where_complement)= calculer_critere_infixe($idb, $boucles, $crit);
  $in = critere_IN_cas2($idb, $boucles, $crit->not ? 'NOT' : '', $arg, $op, $val, $col);
// inserer la condition; exemple: {id_mot ?IN (66, 62, 64)}
  $where = $in;
  if ($crit->cond) {
    $pred = calculer_argument_precedent($idb, $col, $boucles);
    $where = array("'?'",$pred, $where,"''");
    if ($where_complement) // condition annexe du type "AND (objet='article')"
      $where_complement = array("'?'",$pred, $where_complement,"''");
  }
  if ($crit->exclus)
    $where = array("'NOT'", $where);

  $boucles[$idb]->where = $where;
  if ($where_complement) // condition annexe du type "AND (objet='article')"
    $boucles[$idb]->where= $where_complement;
}

// http://doc.spip.org/@critere_IN_cas
function critere_IN_cas2 ($idb, &$boucles, $crit2, $arg, $op, $val, $col)
{
  static $cpt = 0;
  $var = '$in' . $cpt++;
  $x= "\n\t$var = array();";
  foreach ($val as $k => $v) {
    if (preg_match(",^(\n//.*\n)?'(.*)'$,", $v, $r)) {
      // optimiser le traitement des constantes
      if (is_numeric($r[2]))
        $x .= "\n\t$var" . "= $r[2];";
      else
        $x .= "\n\t$var" . "= " . sql_quote($r[2]) . ";";
    } else {
      // Pour permettre de passer des tableaux de valeurs
      // on repere l'utilisation brute de #ENV**{X},
      // c'est-a-dire sa traduction en ($PILE[0]).
      // et on deballe mais en rajoutant l'anti XSS
      $x .= "\n\tif (!(is_array(\$a = ($v))))\n\t\t$var" ."= \$a;\n\telse $var = array_merge($var, \$a);";
    }
  }

  $boucles[$idb]->in .= $x;

  // inserer le tri par defaut selon les ordres du IN ...
  // avec une ecriture de type FIELD qui degrade les performances (du meme ordre qu'un rexgexp)
  // et que l'on limite donc strictement aux cas necessaires :
  // si ce n'est pas un !IN, et si il n'y a pas d'autre order dans la boucle

/* ne pas trier selon l'ordre des arguments de IN pour des raisons de perfo
  if (!$crit2){
    $boucles[$idb]->default_order = "((!sql_quote($var) OR sql_quote($var)===\"''\") ? 0 : ('FIELD($arg,' . sql_quote($var) . ')'))";
  }
*/

  return "sql_in('$arg',sql_quote($var)".($crit2=='NOT'?",'NOT'":"").")";
}

Et pour reprendre vos arguments, croyez bien qu'ici chacun à également un emploi du temps professionnel chargé, avec ses propres contraintes, et qu'à ce titre l'argumentation du type

n'ai pas le temps de m'y plonger suffisamment dans l'immédiat pour me lancer dans la modification
de ces fonctions moi-même car je maintiens un site à titre professionnel et ai de grosses contraintes d'emploi du temps par ailleurs.

est toujours désagréable à lire, car elle sous entend que nous n'avons rien à faire, nous, si ce n'est à résoudre votre problème.
Le temps est compté pour tout le monde, et ce que nous en faisons est simplement une question de choix personnels.

Cordialement,
Cédric

3e5e4f242968084de82d · Février 23, 2009, 1:22

cedric.morin@yterium.com a écrit :

Bonjour,
les problèmes de performances sont en général une accumulation de pratiques peu idéales, et IN n'est en général que l'arbre qui cache la forêt.
En ce qui le concerne, cela se traite en 2min en mettant dans ton mes_fonctions quelques lignes de code pour redéfinir le critère IN, mais je doute vraiment que cela accélère soudainement ton site.

heu, si quand meme, j'ai déjà croisé certains cas ou c'etait assez visible.
Bien sur, on peut toujours s'en sortir en segmentant en plusieurs boucles et en utilisant les doublons, mais il faut avouer que le critère IN qui ne produit pas de IN, c'est pas super intuitif pour un developpeur.
Mais j'ai en plus un autre cas ou l'absence de clause IN pose carrement probleme, c'est avec une jointure.
Je n'ai plus la boucle sous la main, mais en gros l'absence de clause IN me faisait remonter la bonne ligne de la table principale (les éléments ayant au moins un enregistrement correspondant au IN dans la table de jointure), mais pas la bonne ligne de jointure.
de tete ca devait etre un truc du genre BOUCLE(AUTEURS spip_forum){id_article IN #ENV**{les_id_article}>
la ca retourne bien les auteurs ayant un message dans un des articles, mais le message remonté pouvait correspondre à d'autres articles.
C'est un poil tordu puisqu'à priori on remonte un message quelconques, mais c'est quand meme plus logique de remonter une information correspondant à la recherche...
J'imagine qu'il y a des structures ou le probleme sera plus évident.

Bref, j'ai donc ajouté : au critere_IN :
642a644,645
$boucles[$idb]->where= "calcul_mysql_in('$arg', join(',',array_map('_q', $var)) , '" . ($crit->not ? 'NOT' : '') . "')";

qui place la clause IN *en plus* du FIELD

J'imagine que dans le cas de Simon, ca devrait permettre l'exploitation de l'index et que seuls les lignes concernées seront parcourues pour calculer le Field.

Enfin, il faut tester et voir si c'est mieux au niveau des perfs.

@++

Simon_Camerlo · Février 23, 2009, 1:32

Peut-être ne ferions nous pas comme cela si l’on partait d’une feuille blanche, mais il faut assumer l’histoire et assurer la compatibilité ascendante qui évite à tout le monde de devoir recoder tous ses squelettes.
Ce soucis de ne pas oublier les utilisateurs et leurs problèmes concrets, et de ne pas les entraîner dans des opérations complexes hors de leur portée est ce qui fait l’essence même de SPIP, et probablement, sans vouloir troller, ce qui, il me semble, en a fait une grosse différence avec Agora (paix à l’ame de son code).

Il est par ailleurs naturel de considérer qu’en cas d’arbitrage, il faut favoriser la vie des multiples utilisateurs qui utilisent SPIP pour faire des sites simples, sans ressources, car ceux qui utilisent SPIP pour faire des gros sites complexes sont supposés avoir un minimum de budget et de compétences à mettre en face pour traiter les problèmes plus complexes qui se posent à eux, comme l’optimisation des performances.

D’accord pour l’argument des arbitrages en raison de l’existant (j’en sais quelque-chose), cependant une mise en garde claire dans la doc du critère sur spip.net aurait été vraiment bienvenue pour un problème de cette ampleur. On ne parle pas d’un petit souci de manque d’optimisation, mais de l’absence d’utilisation des index dans la base, ce qui peut être catastrophique dans certains cas.
Ca m’a pris énormément de temps pour détecter la source de mon problème de performances, tout simplement parce que ça me paraissait impossible qu’une telle « erreur » ait été faite dans spip, donc je l’avais écartée dès le départ et ai testé des dizaines d’autres possibilités avant de trouver.

Le cas contraire que vous semblez représenter montre clairement que SPIP permet d’aller très loin avec vraiment peu de ressources (humaines et financières), ce que l’on peut considérer comme un succès donc

Je confirme pour le peu de ressources dans mon cas (vraiment TRES peu en regard du projet qui n’est pas que technique)
Pour ce que j’en pense, Spip est définitivement une réussite technique majeure : simplicité et souplesse, espace privé excellent et très fonctionnel moyennant quelques plugins, avec la v2.0, il devient une véritable API web puissante, bref du très bon sur ce plan-là.
Par contre, là où ça coince, c’est la documentation technique : spip.net est une excellente doc pour les débutants qui veulent faire du code simple et des petits sites (la vocation première de spip), mais ensuite on passe directement à l’historique des commits, il y a très peu de documentation technique « high level » (si j’ai l’occasion de l’améliorer moi-même maintenant que j’ai une expérience suffisante de spip, je le ferai avec plaisir).

Oh, ce ne sont pas des paroles dans le vide, mais un retour d’expérience de l’analyse et de l’optimisation de gros sites usines a gaz « parce que l’éditorial ne permettait pas de faire autrement ».

Et évidemment on me reproche aussi de trop contraindre l’éditorial à la logique spip… la boucle est bouclée
(j’assume, je suis obligé de faire un peu des deux, comme n’importe quel webmestre avec n’importe quel CMS dès que le site prend un peu d’ampleur)

En ce qui concerne le patch, il consiste à copier/coller les lignes de définition du critère IN de spip 2.0, en commentant les 3 lignes qui ajoutent le tri par le FIELD(…)

Merci beaucoup, je vais tester ça dès demain.
Je n’ai pas eu le temps de me pencher sur spip 2.0 pour le moment, qui plus est sur son code.
Et ça m’aurait pris plusieurs jours d’essais/erreurs pour me rendre compte de ce fait et être sûr que ça n’impactait pas le fonctionnement général du site.

Et pour reprendre vos arguments, croyez bien qu’ici chacun à également un emploi du temps professionnel chargé, avec ses propres contraintes, et qu’à ce titre l’argumentation du type

n’ai pas le temps de m’y plonger suffisamment dans l’immédiat pour me lancer dans la modification
de ces fonctions moi-même car je maintiens un site à titre professionnel et ai de grosses contraintes d’emploi du temps par ailleurs.

est toujours désagréable à lire, car elle sous entend que nous n’avons rien à faire, nous, si ce n’est à résoudre votre problème.
Le temps est compté pour tout le monde, et ce que nous en faisons est simplement une question de choix personnels.

Je n’ai jamais sous-entendu que vous n’aviez rien à faire, et c’est bien pour ça que je me suis tourné vers vous en tout dernier recours (une semaine de posts infructueux en remontant la chaîne depuis les forums).
Il se trouve simplement que les participants à cette liste sont les seuls à détenir les connaissances nécessaires à la résolution rapide du problème sans risquer de se perdre (le moteur de spip est très complexe, et autant je commence à maîtriser son utilisation, autant mettre ses doigts dans le code me demanderait BEAUCOUP de temps pour être sûr de ne pas tout casser et comprendre ce que je fais).

Merci encore, je posterai un [Résolu] si les tests s’avèrent concluants demain.

fil · Février 23, 2009, 1:35

Ca m'a pris énormément de temps pour détecter la source de mon problème de
performances, tout simplement parce que ça me paraissait impossible qu'une
telle "erreur" ait été faite dans spip, donc je l'avais écartée dès le

SPIP peut te montrer les requêtes qu'il fait pour telle ou telle
boucle si tu lui passes ?var_mode=debug

-- Fil

RealET · Février 23, 2009, 2:38

* simon camerlo tapuscrivait, le 23/02/2009 14:32:

Par contre, là où ça coince, c'est la documentation technique : spip.net est
une excellente doc pour les débutants qui veulent faire du code simple et
des petits sites (la vocation première de spip), mais ensuite on passe
directement à l'historique des commits, il y a très peu de documentation
technique "high level" (si j'ai l'occasion de l'améliorer moi-même
maintenant que j'ai une expérience suffisante de spip, je le ferai avec
plaisir).

http://programmer.spip.org/ devrait contribuer à ton bonheur.

Simon_Camerlo · Février 23, 2009, 3:19

cedric.morin@yterium.com a écrit :

Bonjour,

les problèmes de performances sont en général une accumulation de pratiques peu idéales, et IN n’est en général que l’arbre qui cache la forêt.
En ce qui le concerne, cela se traite en 2min en mettant dans ton mes_fonctions quelques lignes de code pour redéfinir le critère IN, mais je doute vraiment que cela accélère soudainement ton site.

heu, si quand meme, j’ai déjà croisé certains cas ou c’etait assez visible.
Bien sur, on peut toujours s’en sortir en segmentant en plusieurs boucles et en utilisant les doublons, mais il faut avouer que le critère IN qui ne produit pas de IN, c’est pas super intuitif pour un developpeur.

En l’occurrence, mes boucles sélectionnant des articles utilisaient anciennement des {doublons} / {!doublons}, ce qui générait des requêtes monstrueuses associé aux {branche}, requêtes que le serveur mysql avait bien du mal à ingurgiter.
L’optimisation a consisté à passer sur une boucle récursive créant un champ de rubriques valides et le plaçant dans un array (1 passe pour le tout), je récupère ensuite tous les articles présents dans les rubriques en question.
=> Normalement beaucoup plus léger sans le problème d’index car réduction de la complexité : à affichage égal les tests montrent un nombre de requêtes réduit de 50 %, la réduction de leur taille moyenne en octet de 90% et un envoi de résultat de mysql vers php réduit de 30 %

Bref, j’ai donc ajouté : au critere_IN :
642a644,645
$boucles[$idb]->where= « calcul_mysql_in(‹ $arg ›, join(‹ , ›,array_map(‹ _q ›, $var)) , ' » . ($crit->not ? ‹ NOT › : ‹ ›) . « ') »;

qui place la clause IN en plus du FIELD

J’imagine que dans le cas de Simon, ca devrait permettre l’exploitation de l’index et que seuls les lignes concernées seront parcourues pour calculer le Field.

Exactement, je pense qu’on remplit les deux contraintes dans ce cas, je teste ça rapidement (faut juste que je trouve où exactement je dois insérer ça
Dans tous les cas, merci.

Ca m’a pris énormément de temps pour détecter la source de mon problème de
performances, tout simplement parce que ça me paraissait impossible qu’une
telle « erreur » ait été faite dans spip, donc je l’avais écartée dès le

SPIP peut te montrer les requêtes qu’il fait pour telle ou telle
boucle si tu lui passes ?var_mode=debug

Certes, encore faut-il savoir que SELECT FIELD n’utilise pas les index, ce qui n’est pas évident et que spip ne peut montrer…

3e5e4f242968084de82d · Février 23, 2009, 4:11

simon camerlo a écrit :

    Bref, j'ai donc ajouté : au critere_IN :
    642a644,645
    $boucles[$idb]->where= "calcul_mysql_in('$arg',
    join(',',array_map('_q', $var)) , '" . ($crit->not ? 'NOT' : '') .
    "')";

    qui place la clause IN *en plus* du FIELD

    J'imagine que dans le cas de Simon, ca devrait permettre
    l'exploitation de l'index et que seuls les lignes concernées
    seront parcourues pour calculer le Field.

Exactement, je pense qu'on remplit les deux contraintes dans ce cas, je teste ça rapidement (faut juste que je trouve où exactement je dois insérer ça

c'est dans /ecrire/public/criteres.php, dans la fonction critere_IN_dist (j'ai posé ca juste avant $arg = "FIELD...)
tu peux copier la fonction dans ton mes_fonctions en la renommant fonction critere_IN si tu veux faire ca proprement.

@++

Simon_Camerlo · Février 26, 2009, 7:39

Stephane a écrit :

    Bref, j'ai donc ajouté : au critere_IN :
    642a644,645
    $boucles[$idb]->where= "calcul_mysql_in('$arg',
    join(',',array_map('_q', $var)) , '" . ($crit->not ? 'NOT' : '') .
    "')";

    qui place la clause IN *en plus* du FIELD

    J'imagine que dans le cas de Simon, ca devrait permettre
    l'exploitation de l'index et que seuls les lignes concernées
    seront parcourues pour calculer le Field.

Bon, après deux jours de tests, voici mon retour sur l'opération :

- j'ai commencé par ajouter la ligne ci-dessus dans critère_IN_dist
- j'en ai profité pour dégager le bloc de code qui génère le FIELD vu que je n'en ai pas besoin (à modifier pour optimiser...)
- après une trace des requêtes, je me rends compte qu'il reste des FIELD, que je retrouve dans boucle_HIERARCHIE_dist
- j'ajoute la ligne suivante :
$boucle->where= "calcul_mysql_in('". $id_table . ".id_rubrique'" . ', $hierarchie)';
=> génération de IN + FIELD pour la boucle hiérarchie (là, on ne peut pas s'en passer)
- je tune bien mes index pour coïncider avec mes boucles

=> belle augmentation apparente de performances, le site semble plus réactif, l'utilisation du CPU semble décroître, et mysql truste moins souvent la tête du htop, donc le but est atteint.
Faut que je confirme ça sur plusieurs jours de charge, une fois que le cache se sera stabilisé, etc.

Quelques bricoles subsistent :
- parfois, quand la liste de paramètres du IN est trop longue, les index ne sont pas utilisés *si l'index qu'on vise n'est pas la clé primaire*, merci l'optimiseur de mysql (ça arrive souvent sur id_parent dans spip_rubriques, le nombre de paramètres limite change d'un serveur à l'autre et d'une requête à l'autre...). Néanmoins, c'est beaucoup mieux qu'avec FIELD seul qui, lui, n'utilise jamais les index

- idem quand on fait un select * : les index ne sont parfois pris en compte que si toutes les colonnes renvoyées sont correctement indexées
    par exemple :
        SELECT * FROM spip_mots_rubriques WHERE id_mot IN 1,2,3;
    n'utilisera pas un index sur id_mot seul, il faut indexer impérativement sur le couple (id_mot, id_rubrique)

- ces soucis sont présents sur mes deux serveurs mysql (exploitation en v5.0.45, test sur easyphp embarquant un mysql 5.1.30)

- Question accessoire : impossible de surcharger critere_IN_dist par critere_IN dans mes_fonctions.php (placé dans mon home/squelettes).
   J'ai tout essayé, relancé apache et tout, mais il ne prend jamais le code en compte (oui, j'ai vidé tmp, désactivé le couteau suisse, etc.).
   Et d'ailleurs je n'ai jamais réussi à surcharger la moindre fonction là-dedans, j'ai toujours dû taper dans le fichier d'origine (ce qui est mal), alors que les fonctions custom présentes dans mes_fonctions.php et que j'utilise dans mes squelettes sont bien prises en compte, elles.
   Quelqu'un a une idée ?

Dans tous les cas, merci à vous pour l'aide apportée, ça m'aurait pris BEAUCOUP plus de temps pour m'en sortir tout seul.
Si ce retour peut aider à améliorer les perfs d'une future distrib, alors tant mieux
J'en aurai peut-être d'autres à l'avenir, donc à bientôt.

Simon

cerdic · Février 26, 2009, 8:35

Stephane a écrit :

   Bref, j'ai donc ajouté : au critere_IN :
   642a644,645
   $boucles[$idb]->where= "calcul_mysql_in('$arg',
   join(',',array_map('_q', $var)) , '" . ($crit->not ? 'NOT' : '') .
   "')";

   qui place la clause IN *en plus* du FIELD

   J'imagine que dans le cas de Simon, ca devrait permettre
   l'exploitation de l'index et que seuls les lignes concernées
   seront parcourues pour calculer le Field.

Bon, après deux jours de tests, voici mon retour sur l'opération :
...
- Question accessoire : impossible de surcharger critere_IN_dist par critere_IN dans mes_fonctions.php (placé dans mon home/squelettes).
J'ai tout essayé, relancé apache et tout, mais il ne prend jamais le code en compte (oui, j'ai vidé tmp, désactivé le couteau suisse, etc.).
Et d'ailleurs je n'ai jamais réussi à surcharger la moindre fonction là-dedans, j'ai toujours dû taper dans le fichier d'origine (ce qui est mal), alors que les fonctions custom présentes dans mes_fonctions.php et que j'utilise dans mes squelettes sont bien prises en compte, elles.
Quelqu'un a une idée ?

bizarre, en effet, car ça marche très bien

Dans tous les cas, merci à vous pour l'aide apportée, ça m'aurait pris BEAUCOUP plus de temps pour m'en sortir tout seul.
Si ce retour peut aider à améliorer les perfs d'une future distrib, alors tant mieux

Comme je te l'indiquais dans mon mail, le code que je t'ai fourni provient de la 2.0 qui est déjà optimisée sur ce point (notamment) par rapport à la 1.9.x en generant un critère IN dans tous les cas, accompagné d'un FIELD uniquement si aucune clause order n'est demandée (dans ce cas, tri dans l'ordre des éléments du IN, pour compatibilité ascendante).

J'en aurai peut-être d'autres à l'avenir, donc à bientôt.

SPIP 2.0 comprend un profileur de requetes qui permet de visualiser toutes les requetes sql executées pour la construction d'une page, et, pour chaque, le temps consommé.
C'est un outil indispensable pour qui veut optimiser un site.
A tel point que pour l'optimisation d'un site 1.9.2 en production, il m'est arrivé de monter une version upgradée en 2.0 (pas parfaitement fonctionnelle) pour pouvoir profiler toutes les pages et les optimiser.
Ca va dix fois plus vite.

Cédric

fil · Février 26, 2009, 8:45

bizarre, en effet, car ça marche très bien

non les opérateurs <>=IN etc ne sont pas surchargeables

-- Fil

cerdic · Février 26, 2009, 8:56

Pardon ?
http://trac.rezo.net/trac/spip/browser/branches/spip-1.9.2/ecrire/public/criteres.php#L612

Je l'ai déjà fait plusieurs fois en ce qui concerne le IN pour l'optimiser en 1.9.2 justement
(ce qui a entraîné l'optimisation dans le core sur la 2.0, au passage)
Cédric

fil · Février 26, 2009, 8:59

non les opérateurs <>=IN etc ne sont pas surchargeables

Pardon ?
http://trac.rezo.net/trac/spip/browser/branches/spip-1.9.2/ecrire/public/criteres.php#L612
Je l'ai déjà fait plusieurs fois en ce qui concerne le IN pour l'optimiser
en 1.9.2 justement

Alors il y a un truc qui doublonne ici non ?
ecrire/public/phraser_html.php:
')[[:space:]]*(\??)(!?)(<=?|>=?|==?|\b(?:IN|LIKE)\b)(.*)$,is', $param,
$m))

-- Fil

cerdic · Février 26, 2009, 9:01

la syntaxe IN n'est pas modifiable, mais sa transposition en requete se fait par la fonction surchargeable, il est donc possible de modifier son interpretation
(mais pas de le renommer en INTRUC, effectivement)

Cédric

fil · Février 26, 2009, 9:03

la syntaxe IN n'est pas modifiable, mais sa transposition en requete se fait
par la fonction surchargeable, il est donc possible de modifier son
interpretation
(mais pas de le renommer en INTRUC, effectivement)

OK donc je complète ma phrase qui était juste à un détail près : les
opérateurs < <= = > >= IN LIKE etc ne sont pas surchargeables SAUF IN
et LIKE

-- Fil, de mauvaise foix

cerdic · Février 26, 2009, 9:08

En l'occurrence, la discussion portait sur le seul critère IN.
On appelle ça un hors sujet ?

Cédric

fil · Février 26, 2009, 9:11

OK donc je complète ma phrase qui était juste à un détail près : les
opérateurs < <= = > >= IN LIKE etc ne sont pas surchargeables SAUF IN
et LIKE

En l'occurrence, la discussion portait sur le seul critère IN.
On appelle ça un hors sujet ?

T'es vraiment méchant avec moi

-- Fil

3e5e4f242968084de82d · Février 26, 2009, 10:13

Simon Camerlo a écrit :

Stephane a écrit :

    Bref, j'ai donc ajouté : au critere_IN :
    642a644,645
    $boucles[$idb]->where= "calcul_mysql_in('$arg',
    join(',',array_map('_q', $var)) , '" . ($crit->not ? 'NOT' : '') .
    "')";

    qui place la clause IN *en plus* du FIELD

    J'imagine que dans le cas de Simon, ca devrait permettre
    l'exploitation de l'index et que seuls les lignes concernées
    seront parcourues pour calculer le Field.

Bon, après deux jours de tests, voici mon retour sur l'opération :

- j'ai commencé par ajouter la ligne ci-dessus dans critère_IN_dist
- j'en ai profité pour dégager le bloc de code qui génère le FIELD vu que je n'en ai pas besoin (à modifier pour optimiser...)

ca normalement, c'est pas un gros gain si ca utilise les indexes, le calcul n'etant fait que sur les lignes effectivement remontées

- après une trace des requêtes, je me rends compte qu'il reste des FIELD, que je retrouve dans boucle_HIERARCHIE_dist
- j'ajoute la ligne suivante :
$boucle->where= "calcul_mysql_in('". $id_table . ".id_rubrique'" . ', $hierarchie)';
=> génération de IN + FIELD pour la boucle hiérarchie (là, on ne peut pas s'en passer)

ah oui effectivement, j'avais pas regardé de ce coté.

- je tune bien mes index pour coïncider avec mes boucles

=> belle augmentation apparente de performances, le site semble plus réactif, l'utilisation du CPU semble décroître, et mysql truste moins souvent la tête du htop, donc le but est atteint.
Faut que je confirme ça sur plusieurs jours de charge, une fois que le cache se sera stabilisé, etc.

Quelques bricoles subsistent :
- parfois, quand la liste de paramètres du IN est trop longue, les index ne sont pas utilisés *si l'index qu'on vise n'est pas la clé primaire*, merci l'optimiseur de mysql

bizarre ca, tu pourrais faire passer une de ces requete et la structure de ta table ?

(ça arrive souvent sur id_parent dans spip_rubriques, le nombre de paramètres limite change d'un serveur à l'autre et d'une requête à l'autre...). Néanmoins, c'est beaucoup mieux qu'avec FIELD seul qui, lui, n'utilise jamais les index

- idem quand on fait un select * : les index ne sont parfois pris en compte que si toutes les colonnes renvoyées sont correctement indexées
   par exemple :
       SELECT * FROM spip_mots_rubriques WHERE id_mot IN 1,2,3;
   n'utilisera pas un index sur id_mot seul, il faut indexer impérativement sur le couple (id_mot, id_rubrique)

ben celle la, elle risque pas de marcher...
fait passer les requetes effectivement générées.
La dans ce cas, il y a jointure sur spoip_mots_rubriques et c'est cette clause de jointure qui necessite l'indexation de la clé double (qui devrait normalement déclarée en primary et la deuxieme clé en index)

- ces soucis sont présents sur mes deux serveurs mysql (exploitation en v5.0.45, test sur easyphp embarquant un mysql 5.1.30)

il faudrait voir les requetes exactes qui posent probleme pour voir si c'est un probleme résolu par mysql ou si certaines indexation peuvent etre améliorées.

- Question accessoire : impossible de surcharger critere_IN_dist par critere_IN dans mes_fonctions.php (placé dans mon home/squelettes).
J'ai tout essayé, relancé apache et tout, mais il ne prend jamais le code en compte (oui, j'ai vidé tmp, désactivé le couteau suisse, etc.).

ca c'est vraiment pas normal, ce critère n'est pas appelé d'une facon differente des autres.
Ca voudrait dire que ton mes_fonctions n'est pas chargé au moment de la compilation...
quelle version exacte de Spip ? est-ce du mutualisé ?
j'ai eu il y a un moment un pb dans les chemin, c'etait une vieille version du couteau suisse qui cassait les chemin avec la mutualisation (mais il y a longtemps que ca ne le fait plus)

si tu veux comprendre, regarde dans public/composer la fonction public_composer_dist commence par inclure mes_fonctions si il existe avant de lancer la mécanique, est-ce que tu as bien le bon repertoire squelette à ce niveau ?

Et d'ailleurs je n'ai jamais réussi à surcharger la moindre fonction là-dedans, j'ai toujours dû taper dans le fichier d'origine (ce qui est mal), alors que les fonctions custom présentes dans mes_fonctions.php et que j'utilise dans mes squelettes sont bien prises en compte, elles.
Quelqu'un a une idée ?

pas plus...

Dans tous les cas, merci à vous pour l'aide apportée, ça m'aurait pris BEAUCOUP plus de temps pour m'en sortir tout seul.
Si ce retour peut aider à améliorer les perfs d'une future distrib, alors tant mieux

ben ca c'est fait, mais des gens voulant profiter de ces optimisation en restant en 1.9.2, il y en aura sans doute d'autres donc c'est jamais du temps perdu.

@++

Simon_Camerlo · Mars 2, 2009, 4:34

Désolé de ne pas avoir pu répondre plus tôt, *beaucoup* de boulot...

Stephane a écrit :

Simon Camerlo a écrit :

- parfois, quand la liste de paramètres du IN est trop longue, les index ne sont pas utilisés *si l'index qu'on vise n'est pas la clé primaire*, merci l'optimiseur de mysql

bizarre ca, tu pourrais faire passer une de ces requete et la structure de ta table ?

SELECT id_rubrique
FROM `mabase`.spip_rubriques
WHERE id_parent IN ('-1','730','721','720','787','722','1141','743','751','779','798','732','727','846','910','726','728','716','723','724','714','725','960','979','718','729','767','695','696','693','697','715','731','713','717')

=> en fait je modifie ma remarque : cette requête a un comportement erratique si un index sur id_parent seul est présent, elle se comporte un peu mieux avec un index couplé sur (id_parent, id_rubrique) alors que normalement ça n'a aucun impact sur le tri. Ce n'est donc visiblement pas lié au fait que la clé soit primaire ou non, mais toujours au fait que l'index soit "complet" (voir ci-dessous)
De plus elle utilisait l'index id_parent seul jusqu'à un certain nombre de valeurs dans le IN (24 sur mon serveur de test, 26 sur le serveur d'exploitation)...

(ça arrive souvent sur id_parent dans spip_rubriques, le nombre de paramètres limite change d'un serveur à l'autre et d'une requête à l'autre...). Néanmoins, c'est beaucoup mieux qu'avec FIELD seul qui, lui, n'utilise jamais les index

- idem quand on fait un select * : les index ne sont parfois pris en compte que si toutes les colonnes renvoyées sont correctement indexées
   par exemple :
       SELECT * FROM spip_mots_rubriques WHERE id_mot IN 1,2,3;
   n'utilisera pas un index sur id_mot seul, il faut indexer impérativement sur le couple (id_mot, id_rubrique)

ben celle la, elle risque pas de marcher...

oui, désolé c'était juste un raccourci pour expliquer le principe car je n'ai plus la requête en question sous la main.

fait passer les requetes effectivement générées.
La dans ce cas, il y a jointure sur spoip_mots_rubriques et c'est cette clause de jointure qui necessite l'indexation de la clé double (qui devrait normalement déclarée en primary et la deuxieme clé en index)

Pour le coup, je parlais bien d'une requête simple sur la table spip_mots_rubriques, sans jointure donc.
J'ai fait des tests pour savoir pourquoi mes jointures n'utilisaient pas d'index, et je me suis rendu compte de ce fait en tentant la requête simple ci-dessus.

Autre exemple :
SELECT * FROM `mabase`.spip_auteurs
WHERE id_auteur NOT IN (2419)
AND statut!='5poubelle'
AND statut!='6forum'
AND statut!='nouveau'
ORDER BY statut, nom;

Celle-là, impossible de lui faire prendre un index sur cette table, j'en ai créé un sur (statut, nom(30), id_auteur), rien à faire...
Je n'ai pas réussi à reproduire ça aujourd'hui, mais j'ai eu des cas la semaine dernière où le simple fait de changer le SELECT * en SELECT a,b,c (où a,b,et c sont les valeurs indexées) entraînait soudainement l'utilisation de l'index en question.

- Question accessoire : impossible de surcharger critere_IN_dist par critere_IN dans mes_fonctions.php (placé dans mon home/squelettes).
J'ai tout essayé, relancé apache et tout, mais il ne prend jamais le code en compte (oui, j'ai vidé tmp, désactivé le couteau suisse, etc.).

ca c'est vraiment pas normal, ce critère n'est pas appelé d'une facon differente des autres.
Ca voudrait dire que ton mes_fonctions n'est pas chargé au moment de la compilation...

Mon mes_fonctions.php est apparemment chargé puisque les fonctions custom que j'utilise dans les squelettes passent sans problème.
Apparemment c'est juste la surcharge qui pose problème.

quelle version exacte de Spip ? est-ce du mutualisé ?

spip 1.9.2g sur serveur dédié

j'ai eu il y a un moment un pb dans les chemin, c'etait une vieille version du couteau suisse qui cassait les chemin avec la mutualisation (mais il y a longtemps que ca ne le fait plus)

Mon couteau suisse est en 1.7.20.03 révision 24011
Le problème persiste quand je le désactive et quand je vide /tmp (je l'ai eu aussi sur d'autres fonctions, comme celle qui permet de changer la taille de spip.log que j'ai dû forker aussi, par exemple)

si tu veux comprendre, regarde dans public/composer la fonction public_composer_dist commence par inclure mes_fonctions si il existe avant de lancer la mécanique, est-ce que tu as bien le bon repertoire squelette à ce niveau ?

à priori oui...

Dans tous les cas, merci à vous pour l'aide apportée, ça m'aurait pris BEAUCOUP plus de temps pour m'en sortir tout seul.
Si ce retour peut aider à améliorer les perfs d'une future distrib, alors tant mieux

ben ca c'est fait, mais des gens voulant profiter de ces optimisation en restant en 1.9.2, il y en aura sans doute d'autres donc c'est jamais du temps perdu.

En effet (j'avais lu un peu vite les messages précédents).

Merci et à bientôt.
Simon Camerlo