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/