Forum d'entraide PHPFrance

hello à tous,

pour ceux qui avait suivi ma précedente discution faut t'il ranger les données en lignes ou en colonne pour un table de 200 colonnes (ou plus) et 10 millions de ligne on a décider pour l'instant de laisser en colonnes.

Mais on tombe rapidement sur un problème auquel (meme vous) n'aviez pas penser.

Il est possible d'effectuer des WHERE sur toutes les colonnes (le but final est en grande majorité de compter un nombre de ligne en fonction de condition)

Partont de cette exempe simple : SELECT count(*) FROM grosse_table WHERE (champ1 > 2007 and champ2='oui' ) or (champ1 > 2007 and champ3='SAT')
le probleme c'est que j'ai 10 million de ligne et effectuer des where sur des champs pas indexer pour une table aussi grande ben ca rame (environ 30 secondes, sur un machine très costaud). Si j'indexe les champ1, champ2, et champ3 , eh bien ca ne change rien au résultat. Un explain indique qu'il n'utilise qu'un seul indexe sur 3 trouvés.

1/ Vous comprenez ?

2/ Vous avez des idées pour booster ce genre de requete ?

on a essayé de mettre la table en memoire, on tombe à 10 seconde pour le requete ce qui est toujours trop long

3/ on utilise le moteur myisam. Mais on n'effectue que de la lecture sur cette table, aucune insert update ou delete. Y a t'il d'autre moteur meme non officiel plus rapide pour de la lecture seul ?

Merci pour vos idées et commentaires


(créer à la main juste pour l'exemple je pense pas que les 200 colonnes vous soit utile )

Bonjour,
Je n'ai pas lu l'ancien sujet, donc je risque de dire plus de bétise :
Ce temps varie-t-il après un OPTIMIZE ?
Obtenez-vous les mêmes temps avec un autre SGDB(R) ?

on pourrait voir ton "create index" ?

Et quel est l'intérêt de mettre 'oui' dans un champ, plutôt que de mettre un 1 ?

Si tu as un champs qui n'a que quelques valeurs possibles et différentes (genre oui/non/bof/demain), c'est une extrême perte de temps que de vouloir l'indexer. C'est logique, l'indice d'unicité doit être virtuellement nul. Il me semble que ça vaut pour les deux champs textuels.

La moindre des choses, ce serait d'utiliser des chiffres, et une table de correspondance pour la valeur.

0/1 pour oui/non

Et jouer avec des id's sur le second champ, et alors tu crées une table supplémentaire pour joindre ces id's a une valeur.

PRIMARY KEY (`id_client`),
KEY `champ1` (`champ1`),
KEY `champ2` (`champ2`),
KEY `champ3` (`champ3`)

Et quel est l'intérêt de mettre 'oui' dans un champ, plutôt que de mettre un 1 ?

je ne maitrise pas les type de données, je peut un peu les adapter mais pas "utiliser des chiffres, et une table de correspondance pour la valeur. " . c'est le client qui fournit la table.

pensez vous que je doit m'orienter vers ce genre de solution : http://en.wikipedia.org/wiki/Column-oriented_DBMS

Un petit tour également sur la partie 7 de la doc MySQL : http://dev.mysql.com/doc/refman/5.1/en/ ... ation.html
avec une lecture de l'optimisation du serveur à prendre en compte.

Une partie intéressante :
http://dev.mysql.com/doc/refman/5.1/en/ ... l-use.html

The volume of data was quite huge (about seven million summary transactions per month), and we had data for 4–10 years that we needed to present to the users. We got weekly requests from our customers, who wanted instant access to new reports from this data.
We solved this problem by storing all information per month in compressed “transaction tables.” We had a set of simple macros that generated summary tables grouped by different criteria (product group, customer id, store, and so on) from the tables in which the transactions were stored.

Pour pouvoir utiliser les "grosses tables", il y a quelques règles :
1) ce ne sont pas des tables sur lesquelles on travaille quotidiennement en insert et en update. Mais ce sont des tables uniquement faites pour la lecture et qu'on remplit en batch selon une fréquence à définir.
2) Ce sont des tables dans lesquelles on embarque la totalité de l'information pour éviter d'avoir à faire des jointures. Surtout pas de 0/1/2 pour faire une jointure avec une 2e table oui/non/nsp. Ce que tu dois faire, c'est avoir une colonne code pour l'index et une colonne libellé pour l'affichage.
3) Ce sont des tables dans lesquelles on "prémache" le travail en fonction des résultats souhaités : par exemple, on prévoit et on remplit une colonne 'SOMME' pour éviter d'avoir à faire des SUM() dans les requêtes, où dans lesquelles on ajoute le champ "DEPARTEMENT" pour éviter d'avoir à faire des SUBSTR(CODEPOSTAL, 0, 2).
4) Et enfin, comme indiqué dans le texte de MySQL, ce sont des tables que l'on segmente en fonction des requêtes.

Il y a des SGBDR (par exemple Oracle) qui intègrent ce genre de mécanisme. Par exemple, soit la table des communes de France avec les populations années par années. On aura une table du style (code_commune, nom_commune, annee, population). Avec Oracle, on créera un "index partionné" sur l'année qui de manière transparente pour l'utilisateur, va créer une "pseudo-table" par année. On utilisera ensuite un index sur le code_commune.
C'est très efficace quand tu veux chercher quelle est la population des communes du 92 en 2007 puisque le moteur Oracle détectera qu'il faut utiliser la pseudo-table 2007. Par contre, ça l'est beaucoup moins si tu veux chercher l'évolution de la population d'une commune au cours des années puisque le moteur devra parcourir plusieurs "pseudo-tables".
Et si on veut connaître la population d'un département, avec Oracle on va utiliser des "vues matérialisées" qui vont calculer, au moment de l'import, les sommes des populations des communes, département par département. Donc quand tu chercheras la population d'un département, au lieu de faire la somme, Oracle ira chercher dans cette vue matérialisée qui contient déjà le résultat.

Ce genre de fonctions implémenté dans Oracle et géré automatiquement par le moteur d'optimisation n'existe pas dans MySQL. Il va donc falloir que tu fasses comme le texte de MySQL l'explique : à la main !
Quelles sont les requêtes les plus courantes que tes utilisateurs vont faire ?
Sur quels champs cela va porter et sur quelles valeurs ?
Créer des tables partielles en fonction de ces requêtes.

Imaginons que ce soit ta grosse table soit une liste de clients et que ton interface permette de faire une recherche sur le début du nom. A ce moment-là, tu crées 26 tables clientA jusqu'à clientZ et en fonction de l'initiale, ton code PHP ira lire dans la bonne table. Gain statistique : 26 fois plus rapide.
Ca va grandement accélérer les requêtes qui portent sur le nom d'un client, mais cela va ralentir une requête du type "je veux tous les clients de 2007". Donc, pour ce type de requête, tu crées des tables client2004, client2005, client2006 et ce sont elles que tu attaques quand il y a des recherches sur l'année. Voire même si c'est encore trop gros, des tables clientA2006, clientB2006, clientZ2006, clientA2007, clientZ2007, ... Et puis des tables par département, ... De toute façon, si tu manques d'espace disque, tu peux en rajouter, ce n'est pas ça le facteur économique primordial. Il faut juste que le code PHP sache ensuite quelle table attaquer en fonction du ou des critères indiqués par l'utilisateur.

C'est ce que je disais dans mes précédentes réponses : pour ce genre de problème, il n'y a pas une réponse toute faite. Cela dépend énormément de ce que tu veux faire avec les données, comment elles vont être sélectionnées, sur quels critères, ... il faut organiser les données en fonction de la ou des requêtes les plus fréquentes, quitte à dupliquer les données dans autant de tables qu'il le faut.

Et bien sûr, il te faut un script de création qui a partir de la grosse table, va effacer toutes ces tables partielles, les remettre à zéro et réinjecter des données propres.

Merci caroube pour cette longue réponse très instructive.

Mais tout le raisonnement que tu m'explique part de l'hypothèse que je connais à l'avance la maniere dont les requetes seront écrite (en particulier les colonnes utilisées).
Et c'est bien là le problème, je n'en ai aucune idée, et à part faire des stats avec un temps de retard sur les requetes déjà ecrite je n'ai aucune moyen de le savoir. Les utilisateurs peuvent utilisés de manière totalement aléatoire tel ou tel champ de la base.

par contre je suis en accord avec les points 1 et 2 (une seule table, pas de modif). mais ca ne suffit pas visiblement


pour katagoto

Ce temps varie-t-il après un OPTIMIZE ?

non

Obtenez-vous les mêmes temps avec un autre SGDB(R) ?

pas testé mais mysql (avec myisam) est reconnu pour etre un des plus rapide en lecture


merci

Mais tout le raisonnement que tu m'explique part de l'hypothèse que je connais à l'avance la maniere dont les requetes seront écrite (en particulier les colonnes utilisées).

Si tu n'as vraiment aucune idée, c'est un peu embêtant. Mais c'est un peu étrange.
Quand je dis faire une segmentation en découpant les tables, celà ne veut pas dire faire des segmentations dans tous les sens.

Cela ne veut pas dire non plus que tu vas pouvoir optimiser toutes les requêtes, mais faire en sorte que les plus courantes le soient : si avant la situation est 100% des utilisateurs sont mécontents parce que les temps sont trop longs et après la situation est 50% des utilisateurs sont mécontents, c'est au moins ça de gagné.

Est-ce qu'il n'y a pas au minimum un champ discriminant qui te permettrait de découper tes tables ? Est-ce que ton client n'a pas une petite idée de la manière dont vont rechercher ses utilisateurs ?

Les utilisateurs peuvent utilisés de manière totalement aléatoire tel ou tel champ de la base.

J'ai déjà eu le cas d'un client qui m'a sorti ce genre d'argument sur une base de données de clientèle. En fait, en interviewant quelques utilisateurs, on s'est aperçu que c'était toujours les mêmes champs qui revenaient le plus souvent. Je ne sais pas quel est le domaine de ton application, mais ça demande confirmation.

Est-ce qu'il n'y a pas au minimum un champ discriminant qui te permettrait de découper tes tables ?

peut_etre

Est-ce que ton client n'a pas une petite idée de la manière dont vont rechercher ses utilisateurs ?

peut_etre

voila c'est à peu pret tout ce que je sais sur l'utilisation de la base. De plus ce projet doit pouvoir etre réutiliser pour des donnees provenant de source totalement diverse et variée. Un jour on va compter des clients (age, sexe, chiffre d'affaire, ...etc) un autre jour ca sera peut etre des puiguins.

mais je vais continuer à fouiller dans toutes les option d'optimization de mysql. J'ai déjà mis à jour en mysql 5.1 qui ma fait passer de 31 sec à 28 sec, puis utiliser FORCE INDEX qui à fait passer ma requete de 28sec à 22sec (mais mon nez me dit que cette option va ecrouler le temps de réponse dans certain cas)

ensuite si vraiment il faut j'ai suffisament de RAM sur le serveur et je met la table en memoire, cela gagne fait tomber le temps d'execution à 12 sec


peut etre que locker une table rend les select plus rapide aussi .... je vais tester


merci pour ces infos, mais si vous en avez d'autre ca m'interesse toujours

PRIMARY KEY (`id_client`),
KEY `champ1` (`champ1`),
KEY `champ2` (`champ2`),
KEY `champ3` (`champ3`)

J'y pense, mais la tu fais des clés, pas des index..

La clé primaire, c'est pour identifier le champ par un identifiant unique. C'est vraiment pas le cas pour tes champs.

Ce serait intéressant de comparer ta façon de faire avec celle des index (create index ...)
http://dev.mysql.com/doc/refman/5.0/fr/ ... index.html

Un index par colonne je dirais, je vois pas l'intérêt de les concaténer. Dommage que Tracker n'est pas la.

Et j'y pense aussi, mais utiliser des champs de type 'text', pour des valeurs du type 'oui', c'est du suicide, non ? prévoir 65535 caractères juste pour un 'oui'.. m'étonne que ça dure des plombes. varchar(3) ou même tintytext me semblent plus indiqués.

Encore un édit, mais si tu as quand même du texte considérable dans tes champs, regarde du côté des index FULLTEXT. C'est mieuw.

Un jour on va compter des clients (age, sexe, chiffre d'affaire, ...etc) un autre jour ca sera peut etre des puiguins.

Alors là, c'est différent : un outil adapté pour tous les types d'utilisation n'est en fait adapté pour aucun. Tu ne peux pas concevoir quelque chose qui sera optimisé à la fois pour des clients ou pour des pingouins, à moins de mettre ça dans deux tables et de faire une optimisation spécifique pour chacune.

McCall a montré dans ses études sur les qualités d'un logiciel qu'on pouvait privilégier soit la réutilisabilité, soit l'efficacité. Mais pas les deux en même temps.
Soit tu mets en place une structure qui s'adapte aux clients et aux pingouins, mais elle ne sera pas efficace.
Soit tu mets en place une structure qui booste les performances pour les clients, mais dans ce cas tu ne peux pas l'utiliser telle quelle pour les pingouins.
Il suffit juste au départ de savoir quel facteur tu veux privilégier.

Il suffit juste au départ de savoir quel facteur tu veux privilégier.

le plus important c'est la réutilisabilité, mais je ne doit pas pourtant pas laisser tomber la rapidité. Elle n'est pas l'argument premier mais je dois tout de même arrivé à une solution rapide.
Un CMS est très personnalisable (c'est l'argument premier), mais la vitesse reste toujours importante.

c'est pour cela que je me rend compte qu'il faut que je m'oriente vers des solutions générique d'optimisation. tunning de mysql, augmenter les caches, key_buffer, etc ... peut etre aussi au niveau de linux ...
en me basant sur les 2 seuls éléments qui sont générique : 1 seule table et en lecture seul

a+

Bonjour,
je me demande si une représentation par méta données ne serait pas plus judicieuse dans votre cas.
Quand vous dite que vous ne savez pas sur combien de colonnes vont porter les requêtes pour moi cela veut potentiellement dire qu'une recherche pourra porter sur l'ensemble des colonnes.

Avec un système par méta données pour certain champs, une seule colonne serait indexé et balayer pour une recherche, de plus les champs vides ne seront même pas insérés dans cette table ce qui réduira le nombre d'enregistrement (pour le cas ou certaines colonnes peuvent être vide ou null)

Après il est vrai qu'il y a un nombre conséquent de ligne, mais balayer 200 colonnes de type text pour une recherche, je ne pense pas que vous pourrez optimiser énormément.

c'est un debat que j'avais lancé ici : http://www.phpfrance.com/forums/voir_sujet-243063.php

et le débat reste ouvert ...

mais les arguments de transposer une table (ranger en ligne plutot qu'en colonne) sont en général quand la structure change continuellement. De mon coté la table peut etre considéré comme etant read only, c'est pour cela que j'avais écarté cette solution. De plus on trouvait bizare de ranger dans une meme colonne dufférent type de données. J'ai en effet souvent du texte, mais parfois ce sont des int, voir des tinyint sur 1 octet et parfois des dates.

Pour info mon collegue de bureau qui aime bien les defis, s'essaye à coder un moteur mysql optimiser pour notre cas avec ce cahier des charges :
- 1 seul table
- lecture seul
- pas de sous requete, pas d'union
- table chargé en memoire
- seul des selects avec count et sum


cela correspond à nos besoins, (peut etre à d'autre aussi), si on arrive à quelque chose qui tient la route je vous tiens au courant.

a+

S'il n'est pas docteur en bases de données, je pense qu'il va dans le mur…

Même avec des centaines de millions de rangées, MySQL peut donner des performances satisfaisantes.

Cette page explique comment gérer de gros contenus.

Et tu parlais de 200 colonnes.. tu rigoles ? Quid d'un peu de normalisation dans tout ça ?
En tout cas, il va falloir bosser MySQL..