Requetes SQL un peu lente ... ~1 million d'enregistrements

[Ephaistos]

Bonsoir à tous.

J'édite un site ( voir mon WWW ) et je rencontre quelques ralentissements de chargement.
J'ai effectivement une base voyage avec environ 1.000.000 d'enregistrements ...

Mon index met 2.5 secondes à charger, alors que je fais que quelques appels.

Je ne suis pas expert en gestion de base de données, niveau Index et tout ... j'avoue galérer.
Je tourne sur un serveur dédié OVH. ( Core 2 Duo E6550 @ 2.33GHz / 2 Go de Ram)

Ma question est : Est-ce le nombre d'enregistrement qui peut ralentir ou est-ce un soucis de connaissances pour optimiser ?

Merci d'avance

 

[Ephaistos]

J'avais mis un FULLTEXT comme index sur le "nom".
J'ai mis un INDEX tout simple, et je passe de 2.5 sec à 1.2 sec déjà.

Un peu plus raisonnable je trouve.

 

[Zecat]

Au dela de ta demande sur les index, il me semble (j en suis meme certain) que tes varchar 100 sur pays villes etc .. pourraient être avantageusement remplacés par des entiers pointant vers une table ville et une table pays ...

Recherche sur google "modele entite association" ca devrait etre lumineux :wink:

Avec 1000 fiches, pourquoi pas mais avec un million ca vaut le coup de se casser un peu le c.. :wink:

 

[k2pi]

Bah ca dépend aussi beaucoup de la requete... La comme ca c'est difficile de t'aider..

Apres il faut savoir que rechercher sur tu texte c'est toujours plus long que sur des entiers. Donc effectivement comme le dis Zecat commence par factoriser ton contenu dans plusieurs table

 

[cvbperso]

Bonsoir,

Le temps de chargement peut être due à plusieurs facteurs, en plus de la base de données.
Quoique qu'il en soit 2,5 secondes pour une page d'accueil comme ça n'est pas normal...


1- Je t'invite à installer page speed sur Chrome et à l'utiliser.
Ta note est de 68/100, autrement dit trop peu pour un site comme le tien.


La base de données peut être aussi un facteur de ralentissement (Mauvaise analyse MERISE, pas de clé secondaire, mauvaise clé primaire, type de champs mal dimensionné ou mal choisi, doublons, produits cartésiens...). Les raisons sont nombreuses...

1- Vérifie bien si t'utilises les règles MERISE (une méthode bien utile quand on commence à conceptualiser une base de données)

2- Choisi les bon types de champs ! Une URL en varchar(255) suffit largement, les villes doivent pointer sur une table à part en utilisant un index (clé secondaire), pourquoi deux champs nommé ID et ID_H, qu'est-ce que c'est ?

3- Peux-tu nous donner ta requête, afin de voir s'il peut être optimisé ?

++

 

[Ephaistos]

Bonsoir.

Merci pour vos réponses !!!

Pour la ville, c'est galère, vu que j'importe depuis des fichiers XML. Il faudrait que je rajoute une condition dans mes imports pour enregistrer tout ça autrement. Je vais creuser.

Merci cvbperso, je vais regarder pour les règles MERISE.

Je vous tiens au courant

 

[cvbperso]

Pour la ville, c'est galère, vu que j'importe depuis des fichiers XML. Il faudrait que je rajoute une condition dans mes imports pour enregistrer tout ça autrement. Je vais creuser.

Au sujet de ton fichier XML ceci n'est pas très grave.

Pour chacune des communes, il faudra que tu requête afin de récupérer l'ID de la table ville, correspondant à la commune. Tu risques par contre d'avoir des noms de ville orthographié de façon différentes. A toi de voir, qu'elle est la bonne orthographe, de bien vérifier les données au préalable...

Tout ceci doit se faire depuis un backoffice, un script prévu pour ça. Un shell convient très bien (c'est même conseillé si t'as du gros volume, pour éviter des timeout de 30 secondes. Sans compter la vitesse de traitement qu'est bien plus importante). Par contre crée des fichiers logs (d'éventuelle erreurs, nom ville non trouvés,...)


Petit conseil construit les entités suivante, avant même de commencer l'intégration des données:

Entité Ville -> Entité Département -> Entité Région -> Entité Pays (pays si tu désires faire d'autre destination que la France). Tu as des listes complètes afin de créer cet ensemble d'entité sur le web ! Attention selon les pays, tu devras prendre ou non en compte spécificité géographique...

L'intégrité des données est essentiel dans une base de données, aucun doublon doit existé, tu ne peux pas avoir deux fois le même enregistrement, sauf cas très particulier et spécifique. Règle d'or !

++

 

[spout]

Tu peux aussi analyser tout ça avec :
MySQL Tuner Perl: https://github.com/rackerhacker/MySQLTuner-perl
ou
MySQL Performance Tuning Primer Script: http://www.day32.com/MySQL/

+ activer le slow query log.

 

[Rogers]

Sans vouloir dire de bêtises, il me semble que sur une même table, il ne faut pas mettre autant d'index sous peine de ralentir MySQL justement.

Essaie de mettre des marqueurs de temps dans ton code (juste avant et après chaque requête SQL de ta page) pour voir quelle est ou quelles sont les requêtes qui prennent trop de temps. Tu as peut être une ou plusieurs requêtes mal optimisées.

 

[cvbperso]

Sans vouloir dire de bêtises, il me semble que sur une même table, il ne faut pas mettre autant d'index sous peine de ralentir MySQL justement.

Essaie de mettre des marqueurs de temps dans ton code (juste avant et après chaque requête SQL de ta page) pour voir quelle est ou quelles sont les requêtes qui prennent trop de temps. Tu as peut être une ou plusieurs requêtes mal optimisées.

Dans une table tu n'as qu'un index. Par contre, tu peux avoir des index composés de plusieurs champs, afin d'augmenter la vitesse de recherche. Par contre, faut que les champs soient bien choisis...

Les marqueurs dans le code, ne donneront rien du tout, le temps sera faussé, car il comprendra également l'accès, la réponse du serveur. Réponse qui peut être variable en fonction des ressources utilisé à un instant "t"...

Par contre selon le type de connecteur (mysqli, PDO...) il y a moyen de connaître le temps de réponse d'une requête !
J'ai plus le nom de la fonction en tête... :mrgreen:

Avant tout ça faut surtout vérifier le modèle. Tu pourras avoir les meilleurs requêtes optimisés,..., si derrière les règles élémentaires d'analyse, de conceptualisation ne sont pas respectés, ça ne servira à rien ou presque.

 

[Rogers]

Les marqueurs dans le code, ne donneront rien du tout, le temps sera faussé, car il comprendra également l'accès, la réponse du serveur. Réponse qui peut être variable en fonction des ressources utilisé à un instant "t"...

En même temps, on est pas non plus sur de l'appli pro qui doit être ultra optimisée. Le but étant d'identifier le problème et le marqueur de temps au sein du code est la solution la plus simple pour voir à quel endroit dans le code, le temps est augmenté. Même si effectivement, d'autres variables entrent en compte, cela permet déjà de trouver le fautif. Quand tu passes de 0.03s à 2s d'un coup, tu sais donc que tu as un problème de taille à cet endroit.

Avant tout ça faut surtout vérifier le modèle. Tu pourras avoir les meilleurs requêtes optimisé,..., si derrière les règles élémentaire d'analyse, de conceptualisation ne sont pas respectés, ça ne servira à rien ou presque.

Oula, tu sais, moi je ne fais que donner des pistes. Je n'ai jamais eut ce genre de problématiques de tables si grosses, donc je l'informe juste sur le fait que pour moi sa table me semble mal construite et qu'en plus de ça, il doit vérifier si ses requêtes sont bien écrites. Les 2 conjugués peuvent faire un malheur au niveau temps de réponse.

 

[cvbperso]

@Rogers : Je suis d'accord avec tout ce que tu dis...
C'est un ensemble de chose qui font ralentir un site (requête, code, conceptualisation base,...).

Très souvent les bases de données sont les parents pauvres des sites !

 

[Rogers]

Bien d'accord avec toi.

 

[kazzar]

Il faudrait les requêtes pour t'aider efficacement, et le résultat des EXPLAIN sur ces requêtes.

A mon avis déjà tu as peut être trop d'index, et sur un seul champ, ce qui n'est pas forcement toujours utile suivant la requete.
Par exemple vu ta page d'accueil, un index destination + départ me parait obligatoire.
Et pas besoin d'index sur formule, dispo, programme : trop peu de champs.

J'ai aussi des réserves sur l'utilité de l'index doublon, qui en plus risque de pas mal ralentir les update.


Et bien sur faire les changements déjà indiqués par cvbperso sur les champs varchar !!!
Au minimum pour nom, dispo, programme, durée ...
Pour le XMl, en général plutôt que de le lancer depuis le backoffice, j'utilise un CRON pour lancer ce genre de traitement, en général vers 4h00 (si 1 maj/jour te convient).

 

[Julia41]

Je rejoins kazzar.
Sans avoir la gueule des requêtes et leur EXPLAIN...
Si tu fais du GROUP BY COUNT ORDER BY RAND etc... Tu vas pas aller loin.

Je trouve aussi que tu as trop d'INDEX, mais ça peut être nécessaire.

Pour tes champs date, ils pourraient être stockés en INTEGER (time()), surtout si tu les classes par ces valeurs.

Aussi si ton classement par défaut est par "update" (ton dernier champs), tu n'as pas d'INDEX dessus.

Mais bon, il faudrait tout de même la gueule des requêtes.