Discussion :

[Hervé Saladin]

Bonjour,
j'ai développé une appli de gestion de documents numériques (pour ceux que ça intéresse voir ici), et je m'apprête à augmenter considérablement le volume de données de la base (le nb de document va passer en gros de 1500 à 15000 ...).
D'après la façon dont réagit mon serv de développement, il faut absolument que je trouve quelque chose pour que ça "rame" moins lorsque les données seront versées sur le serv de prod ...
J'ai déjà essayé d'optimiser autant que possible mes requêtes, ce qui a effectivement amélioré les choses, mais ce n'est pas suffisant.

Description (rapide et partielle) de ma base :
Les documents sont décrits par des "métadonnées" (auteur, titre, date, etc ...) dont la liste des champs est prédéfine. On apelle l'ensemble des métadonnées liées à un document sa "notice".

J'ai donc trois tables :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
document(id_document, id_utilisateur ...)
metadonnee(id_metadonnee, libelle, obligatoire, nb_max etc ...)

et la table d'association :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

notice(id_document#, id_metadonnee#, valeur)

C'est cette dernière table qui pose à mon avis problème : suite à l'ajout massif de documents qui est imminent, le nombre d'enregistrement de cette table passe à plus de 150 000 !!!
Or, les fonctionnalités de mon applis sont telles que j'ai besoin d'executer des bonnes grosses requêtes bien bourrines avec des sous requêtes imbriquées et tout (comme je l'ai dit plus haut, j'ai essayé d'optimiser le plus possible ces requêtes mais au vu des fonctionnalités, il y a un niveau de complexité sous lequel je ne peux pas descendre).
Du coup, avec tout ça, le serveur a du mal à suivre : certaines requêtes mettent 30sec à répondre en utilisant 70% du cpu) bref, les temps de réponses sont inacceptables pour l'utilisateur

Le serveur est une machine assez puissante, sous LAMP, logiciels plutôt à jour (mysql 5.1), environ 10 000 pages vues/jour, probablement bientôt le double.

D'où mes questions :
- comment configurer le serveur pour qu'il tourne le mieux possible ?
Quelles variables de conf peuvent être importantes ? Comment optimiser la structure de ma BD (indexes, partitionnement etc ... ).

- si ce n'est pas possible : quelle conf materielle faut-il ? mémoire, processeurs, disque etc ... que me conseillez-vous ?

Voilà, si vous avez ne serait-ce que des pistes à explorer pour résoudre mon pb, je suis prenneur, si vous avez besoin d'infos plus détaillées sur la conf de mon serveur ou la structure de ma BD aucun problème.

Merci d'avance

[Sivrît]

Voilà, si vous avez ne serait-ce que des pistes à explorer pour résoudre mon pb, je suis prenneur, si vous avez besoin d'infos plus détaillées sur la conf de mon serveur ou la structure de ma BD aucun problème.

Quelques précisions ne feraient pas de mal. Quel moteur ? Quels sont les types de données ? Et surtout, quels sont les index déjà en place ?

- comment configurer le serveur pour qu'il tourne le mieux possible ?
Quelles variables de conf peuvent être importantes ? Comment optimiser la structure de ma BD (indexes, partitionnement etc ... ).

Justement, ça va dépendre du moteur. Enfin, en règle générale, tout ce qui va permettre de faire tourner les requêtes sans toucher au disque.
Le partitionnement pourrait peut-être aider, mais à mon avis uniquement si certaines valeurs (par exemple un sous ensemble bien identifié des méta-données) ne sert presque pas.


Après, si ce sont bien les méta-données qui coincent et que ni configuration ni indexes n'aident, il faudra envisager des approches alternatives.

• Au niveau de mysql, peut-être serait-il possible de modifier les requêtes : passer par des tables temporaires explicites, etc.
• Ou alors placer les méta-données toutes ensembles dans des chaines de caractères (avec mots clefs) et utiliser un index fulltext, ou même un moteur d'indexation comme sphinx (ses diverses possibilités pourraient peut-être tout changer).
• La table n'est pas très large, donc même 150 000 enregistrements ne prennent pas tant de place que ça. La table pourrait être doublée avec le moteur MEMORY (aussi appelé HEAP) pour les requêtes.
• Dans le même genre d'idée, une partie de cette logique pourrait être faite côté client. Il faudrait voir si jouer avec quelques hashmap ne passerait pas mieux.
• MySQL n'est pas réputé pour son optimisation des sous-requêtes. Pourquoi ne pas tester d'autres serveurs ? Sans porter l'application, il "suffit" d'importer les méta-données et de tester quelques requêtes à la main. Je pense surtout à PostgreSQL.

[Hervé Saladin]

Tout d'abord merci d'avoir pris la peine de me répondre

Voilà, si vous avez ne serait-ce que des pistes à explorer pour résoudre mon pb, je suis prenneur, si vous avez besoin d'infos plus détaillées sur la conf de mon serveur ou la structure de ma BD aucun problème.

Quelques précisions ne feraient pas de mal. Quel moteur ? Quels sont les types de données ? Et surtout, quels sont les index déjà en place ?

Moteur : InnoDB (choisi pour l'implémentation des contraintes d'intégrité et support des transactions, contrairement à MyISAM trop "laxiste" à mon goût).
Types de données : mes clés primaires sont toutes des INT(10) en auto_increment, le champ "valeur" de ma table "notice" est de type "text", car parfois une métadonnéee peut être un texte (ex : résumé, description ...).
Indexes : euh, ben j'en ai mis un peu partout ... déjà il y en a sur chaque clé étrangère, mais concernant ma table "notice" mysql ne veut pas que j'indexe le champ "valeur" (qui est pourtant l'un de ceux qui sont le plus parcourus). Je crois que c'est parceque le champ est trop gros
Sachant qu'en tout j'ai 30 tables toutes plus ou moins liées les unes aux autres, au final ça fait pas mal d'indexes ... est-ce que cela peut plomber mes perfs ? (trop d'index tue-t-il l'index ???)

- comment configurer le serveur pour qu'il tourne le mieux possible ?
Quelles variables de conf peuvent être importantes ? Comment optimiser la structure de ma BD (indexes, partitionnement etc ... ).

Justement, ça va dépendre du moteur. Enfin, en règle générale, tout ce qui va permettre de faire tourner les requêtes sans toucher au disque.
Le partitionnement pourrait peut-être aider, mais à mon avis uniquement si certaines valeurs (par exemple un sous ensemble bien identifié des méta-données) ne sert presque pas.

Non, ce n'est pas le cas ... du coup, faute de mieux, j'ai tenté de partitionner de façon "arbitraire" (par tranche de 500 documents) mais ça n'a rien amélioré (j'ai pas mesuré au benchmark, mais j'avais l'impression que c'était même légèrement pire !).

Après, si ce sont bien les méta-données qui coincent et que ni configuration ni indexes n'aident, il faudra envisager des approches alternatives.

• Au niveau de mysql, peut-être serait-il possible de modifier les requêtes : passer par des tables temporaires explicites, etc.

J'ai déjà fait ce que je pouvais au niveau des requêtes, j'aurais du mal à faire mieux étant donné les fonctionnalités de l'appli.
Par contre qu'entends-tu par table temporaire "explicite" ? (table temporaire, je vois bien, mais "explicite" ... ???)

Ou alors placer les méta-données toutes ensembles dans des chaines de caractères (avec mots clefs) et utiliser un index fulltext, ou même un moteur d'indexation comme sphinx (ses diverses possibilités pourraient peut-être tout changer).

Euh, là je préfère pas partir dans ce genre de trucs : ça m'a l'air bien compliqué, et puis il faudrait quasiment tout reprendre !!!

La table n'est pas très large, donc même 150 000 enregistrements ne prennent pas tant de place que ça. La table pourrait être doublée avec le moteur MEMORY (aussi appelé HEAP) pour les requêtes.

Non, en effet, 3 champs ça n'est pas large, mais avec tous les enregistrements la table prend quand même 45 Mo (d'après phpMyAdmin), n'étant pas un expert et n'ayant pas trop de point de comparaison, je ne me rends pas bien compte si c'est beaucoup ou pas ?
Pour le moteur memory, je ne conaissais pas, et ça m'a l'air très interessant ! En gros, il s'agit de faire un cache mémoire pour la table, c'est bien ça ? Si ça accélere sensiblement les temps d'accès aux données j'achète tout de suite ! (d'autant que la RAM n'est pas un problème pour moi). Mais par contre comment on gère proprement la synchronisation entre la table "réelle" en InnoDB et sa copie MEMORY ??? T'aurais des liens là-desus, des tutos ? (y'a rien dans la FAQ de developpez )

Dans le même genre d'idée, une partie de cette logique pourrait être faite côté client. Il faudrait voir si jouer avec quelques hashmap ne passerait pas mieux.

Mmmh, je ne pense pas : les requêtes qui posent problème sont celles qui moulinent toute la table notice pour en extraire les identifiants d'une poignée de documents qui correspondent à une liste de critères portant sur les métadonnées ... donc pour les remplacer, il faudrait fetcher toute la table et réécrire les algos de recherche ...

MySQL n'est pas réputé pour son optimisation des sous-requêtes. Pourquoi ne pas tester d'autres serveurs ? Sans porter l'application, il "suffit" d'importer les méta-données et de tester quelques requêtes à la main. Je pense surtout à PostgreSQL.

Ok, je vais essayer ça. Porter mon appli n'est pas un problème puisqu'elle est basée sur une couche d'abstraction de base de données censée supporter la plupart des SGBD, donc théoriquement j'ai juste à ajouter un driver et changer 2-3 lignes dans les fichiers de conf .
Je peux aussi tester sur un serveur Oracle ... penses-tu que ça peux améliorer les perfs aussi (j'ai lu que les perfs d'Oracle et MySQL étaitent - de nos jours - sensiblement équivalentes, mais cela ne tenait peut-être pas compte du cas particulier des sous-requêtes).


En tout cas MERCI pour ton aide !!!

[SQLpro]

j'ai lu que les perfs d'Oracle et MySQL étaitent - de nos jours - sensiblement équivalentes,

J'espère que cette une plaisanterie ?
A plus de 4 millions de transactions pas minute aucune batterie même de quelques centaines de MySQL ne serait en mesure de le faire !

Pour votre changement de SGBDR, vous pouvez aussi essayer MS SQL Server. Très performant en matière de requêtes complexes (c'est l'un des optimiseurs de requêtes les mieux foutu du marché) et bien moins cher qu'Oracle !

Types de données : [...] le champ "valeur" de ma table "notice" est de type "text", car parfois une métadonnéee peut être un texte (ex : résumé, description ...).

C'est là que le bât blesse. Ce qu'il vous faut c'est faire de l'indexation textuelle, prévue par la norme SQL avec le prédicat CONTAINS notamment.
Intéressez vous a l'article que j'ai écrit à ce sujet : http://sqlpro.developpez.com/cours/indextextuelle/

A +

[Hervé Saladin]

j'ai lu que les perfs d'Oracle et MySQL étaitent - de nos jours - sensiblement équivalentes

J'espère que cette une plaisanterie ?
A plus de 4 millions de transactions pas minute aucune batterie même de quelques centaines de MySQL ne serait en mesure de le faire !

Ce n'est pas moi qui le dit, c'est juste ce que j'ai lu (je ne sais plus ou d'ailleurs) ...
Mais l'article précisait qu'il s'agissait de MySQL 5, et expliquait les cas de figure dans lesquels l'un des deux SGBD était plus performant que l'autre, ou l'inverse, ou a peu près équivalents, le tout avec benchmarks à l'appui. Ce que j'ai retenu c'est que sauf cas de figure très spécifique, les perfs n'étaient plus (de nos jours) un argument très pertinent en faveur d'Oracle comme cela avait pu l'être par le passé. Encore une fois, c'est ce que j'ai lu, c'est donc à prendre avec prudence.
De toutes façons, je n'ai pas 4 millions de transactions par minutes, pour vous donner une idée, je dois avoir (sur le serveur web de production) environ 20 000 pages vues par jour, et environ (à la louche) une dizaine de requetes par page en moyenne, le tout est quand même sur un serveur assez costaud.

Pour votre changement de SGBDR, vous pouvez aussi essayer MS SQL Server. Très performant en matière de requêtes complexes (c'est l'un des optimiseurs de requêtes les mieux foutu du marché) et bien moins cher qu'Oracle !

Eventuellement, pourquoi pas s'il gère bien les transactions et les contraintes d'intégrité (j'immagine que oui, quand même) et surtout si je n'ai pas à reprendre la structure de ma BD ni mes requêtes.
Concernant les licences, le prix n'est pas un critère rédhibitoire d'autant que j'ai déjà des serveur Win (2000 et 2003) Serveur (avec IIS et SQL Serveur) et également Oracle en production. Si je n'arrive pas à avoir des perfs acceptables sous MySQL, j'essaierais Oracle et MS SQL Serveur, mais si je pouvais garder la BD sur la même machine que mon serveur web (LAMP), je préfère ...

Types de données : [...] le champ "valeur" de ma table "notice" est de type "text", car parfois une métadonnéee peut être un texte (ex : résumé, description ...).

C'est là que le bât blesse. Ce qu'il vous faut c'est faire de l'indexation textuelle, prévue par la norme SQL avec le prédicat CONTAINS notamment.
Intéressez vous a l'article que j'ai écrit à ce sujet : http://sqlpro.developpez.com/cours/indextextuelle/

Interessant, je vais regarder ça de plus près.
Merci pour l'info (et aussi pour la rédaction du tuto).

[Antoun]

Au passage, pour la version MySQL (totalement spécifique) de l'indexation textuelle : http://omiossec.developpez.com/mysql/fulltext/

[Hervé Saladin]

Merci, malheureusement mes tables sont en InnoDB, on ne peut donc pour le moment pas créer d'index FULLTEXT dessus

[Antoun]

Rien n'empêche de mélanger InnoDB et MyISAM dans la même base. L'astuce classique consiste à garder la table de base en InnoDB et déplacer ou dupliquer les colonnes à indexer en FULLTEXT dans des tables MyISAM. Bien sûr, ça demande un peu de vues et/ou de triggers, donc ça n'est pas aussi clean qu'avec SQL Server ou Oracle.

[Sivrît]

Ce n'est pas moi qui le dit, c'est juste ce que j'ai lu (je ne sais plus ou d'ailleurs) ...
Mais l'article précisait qu'il s'agissait de MySQL 5, et expliquait les cas de figure dans lesquels l'un des deux SGBD était plus performant que l'autre, ou l'inverse, ou a peu près équivalents, le tout avec benchmarks à l'appui. Ce que j'ai retenu c'est que sauf cas de figure très spécifique, les perfs n'étaient plus (de nos jours) un argument très pertinent en faveur d'Oracle comme cela avait pu l'être par le passé. Encore une fois, c'est ce que j'ai lu, c'est donc à prendre avec prudence.

Je serais curieux de lire cet article
Je n'ai pas d'expérience avec Oracle, mais sur certains points MySQL est notoirement faible. Il est possible que pour des requêtes simples il soit plus efficace qu'Oracle, mais :

• MySQL est mauvais sur l'optimisation des requêtes imbriquées, ce qui ici peut beaucoup jouer.
• Ses algorithmes de jointure ont leurs limites. L'utilisation conjointe de deux indexes différents est récente et donne des résultats moyens.
• Oracle est heureux sur des machines avec 64 processeurs. Pour MySQL, 8 est plutôt un maximum, et une requête donnée ne peut être parallélisée sur plusieurs processeurs.

Après, quand on n'a pas un serveur expérimental ou des requêtes à 10 étages, MySQL s'en sort certainement tout à fait bien.


Enfin bref, il est probable qu'ici Oracle puisse faire mieux. J'avais plutôt suggéré Postgres parce que quand on utilise MySQL, c'est généralement qu'Oracle est trop $$$.

Types de données : mes clés primaires sont toutes des INT(10) en auto_increment, le champ "valeur" de ma table "notice" est de type "text", car parfois une métadonnéee peut être un texte (ex : résumé, description ...).

J'aurais dû tiquer immédiatement. Je pense que le type "text" doit faire mal.

Les requêtes complexes impliquent généralement la création de tables temporaires pour les étapes intermédiaires (ça devrait apparaitre dans les EXPLAIN). Dans la mesure du possible, ces tables sont de type HEAP/MEMORY. Si ce n'est pas possible, le serveur se rabat sur MyIsam, mais c'est sur le disque et c'est bien plus lent. Les deux limites à l'utilisation du moteur HEAP sont la taille (si c'est trop gros on passe sur le disque), et les types de colonnes qu'ils ne supporte pas... BLOB et TEXT

Il serait intéressant de tester en passant en VARCHAR (juste pour le test), et si c'est concluant d'envisager de séparer données "normales" et données TEXTuelles. D'autant plus que ces dernières risquent de ne pas être manipulées de la même façon ("LIKE '%motclef%'" ?).

Indexes : euh, ben j'en ai mis un peu partout ... déjà il y en a sur chaque clé étrangère, mais concernant ma table "notice" mysql ne veut pas que j'indexe le champ "valeur" (qui est pourtant l'un de ceux qui sont le plus parcourus). Je crois que c'est parce que le champ est trop gros

C'est dommage, c'est celui qu'il faut vraiment. MySQL ne peut pas indexer intégralement une colonne TEXT (hors index FULLTEXT, mais leur fonctionnement est différent), il faut obligatoirement n'en indéxer qu'une longueur donnée. Dans le genre :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CREATE INDEX index_notice_valeur ON notice(valeur (255));

Ça, ou comme je le suggérais, séparer TEXT et VARCHAR. Je pense que ça va tout changer.
Si les critères de recherche sont du type "valeur='toto'", c'est vraiment l'index qu'il faut. Si c'est plutôt du mot clef façon "valeur LIKE '%toto%'", alors il faudrait plutôt le FULLTEXT suggéré par Antoun.

Sachant qu'en tout j'ai 30 tables toutes plus ou moins liées les unes aux autres, au final ça fait pas mal d'indexes ... est-ce que cela peut plomber mes perfs ? (trop d'index tue-t-il l'index ???)

Ça ralentie les modifications et prend de la place sur le disque et dans les caches. Mais dans l'ensemble c'est un moindre mal. Les champs sur lesquels on fait beaucoup de recherches et les clefs étrangères (pour elles c'est d'ailleurs obligatoire il me semble) sont une bonne première approche.

Il faudrait juste veiller à ce que innodb_buffer_pool_size soit assez grand, surtout qu'ici tout doit pouvoir tenir dedans (mais dépasser sa valeur par défaut). Le but est d'arriver à y faire tenir les index (vraiment), et si possible les données (ici ça semble possible pour l'essentiel).

J'ai déjà fait ce que je pouvais au niveau des requêtes, j'aurais du mal à faire mieux étant donné les fonctionnalités de l'appli.
Par contre qu'entends-tu par table temporaire "explicite" ? (table temporaire, je vois bien, mais "explicite" ... ???)

Ce sont les tables intermédiaires que je mentionnais plus tôt. Il est possible fragmenter une grosse requête en plusieurs plus simples, en conservant les résultats intermédiaires dans des tables temporaires (que l'on aura créées manuellement, donc explicitement, cette fois ci). Il y a même moyen de placer des index dessus et de tenter plusieurs approches. Ça peut (mais ce n'est pas une promesse) être plus efficace.

Non, en effet, 3 champs ça n'est pas large, mais avec tous les enregistrements la table prend quand même 45 Mo (d'après phpMyAdmin), n'étant pas un expert et n'ayant pas trop de point de comparaison, je ne me rends pas bien compte si c'est beaucoup ou pas ?

Disons que c'est plutôt à comparer à la RAM disponible. Aujourd'hui, au royaume des BDD, ce n'est rien du tout. "Beaucoup" se compte plutôt en Go.

Pour le moteur memory, je ne conaissais pas, et ça m'a l'air très interessant ! En gros, il s'agit de faire un cache mémoire pour la table, c'est bien ça ? Si ça accélere sensiblement les temps d'accès aux données j'achète tout de suite ! (d'autant que la RAM n'est pas un problème pour moi). Mais par contre comment on gère proprement la synchronisation entre la table "réelle" en InnoDB et sa copie MEMORY ??? T'aurais des liens là-desus, des tutos ? (y'a rien dans la FAQ de developpez )

Les triggers seraient à mon sens l'outil à utiliser pour maintenir la synchronisation. Mais ça tient du dernier recourt. En plus je ne sais pas si ce moteur est transactionnel et surtout... il n'aime pas les champ TEXT.

Mais une fois l'indexation revue, je pense que ce genre de bidouille ne sera pas nécessaire (et autant sortir Oracle).

[Hervé Saladin]

J'ai réduit la taille de mon champ "valeur" qui était de type "TEXT" => je l'ai passé en VARCHAR(2047) ce qui est juste suffisant pour les données qui sont actuellement dans la BD (le plus gros était un résumé de 2028 octets) et j'ai ainsi pu mettre un index dessus.
Je vais rajouter un avertissement sur l'interface utilisateur pour indiquer cette limite, et si un jour y'a vraiment besoin d'augmenter la taille et que c'est indispensable et qu'on peut pas faire autrement, j'aurais qu'à faire un p'tit ALTER
D'ailleurs quelqu'un sait jusqu'à quelle taille on peut faire un idex en innodb ?
J'ai également configuré la variable "innodb_buffer_pool_size" dans mon my.cnf : j'ai 512M (plus ou moins au pif) histoire de prévoir large (le serv à 4G de RAM) ainsi que quelques autres variables concernant le cache.
Et ben avec tout ça, ça va BEAUCOUP mieux !!!
La plus "bourrine" de mes pages qui mettait avant plusieurs minutes à s'afficher met maintenant quelques secondes lors du premier chargement (je pense que c'est grâce à la modif du champ "valeur" et à son index) et s'affiche quasi-instantanément les fois d'après (je pense que c'est grâce au cache).
Me voilà soulagé : si ça tourne bien sur mon serv de développement, ça devrait envoyer du gros sur la machine de prioduction qui est plus puissante.
En plus, j'aurais appris deux ou trois trucs sur l'optimisation d'une BD mysql ce qui n'est pas ma spécialité à la base.

Donc un grand MERCI à vous qui m'avez conseillé, ça fait plaisir de voir qu'il y a des pros prêts à aider leur collègues même sur des problèmes un peu pointus

[Jester]

J'ai également configuré la variable "innodb_buffer_pool_size" dans mon my.cnf : j'ai 512M (plus ou moins au pif) histoire de prévoir large (le serv à 4G de RAM) ainsi que quelques autres variables concernant le cache.
Et ben avec tout ça, ça va BEAUCOUP mieux !!!

C'est la variable la plus importante pour les tables innodb. On y met en général 75% de la RAM attribué à Mysql. Si toute la base peut tenir en mémoire, les select ne génèrent pratiquement plus d'accès au disque.