Forum Rank Seo

Forum SEO pour webmasters et blogueurs !

 


  • Publicités

Les requêtes SQL lentes

Aide sur le codage maison.

Les requêtes SQL lentes

Message par Juliano » 24 Déc 2013, 11:16

Bonjour à tous,

J'aimerais vous partager une expérience qui m'est arrivée hier soir, et qui pourra peut-être aider quelques personnes ici qui aiment mettre les mains dans le code.

Pour situer le contexte, je possède un site qui est un moteur de recherche. La base de données est assez grande (bien que pas encore énorme, avec un peu plus d'un million d'entrées) et grandit au fil des jours.
Au début, le site était rapide, tout se passait bien. Plus le temps passe, plus la base de données se remplit et plus le site devient lent. Cette lenteur vient des requêtes SQL qui prennent de plus en plus de temps. Sur les pages en questions, deux requêtes de recherche sont présentes, et chacune prend plus d'une seconde, ce qui est assez catastrophique.

Ayant bien une petite idée d'où cela pouvait venir (un problème d'index qui rendrait la recherche ou le tri pas efficace), je lis pas mal d'articles, et sur certains d'entre eux, je vois des tableaux détaillés du temps de l'exécution de la requête. Comme celui-ci:

Code : Tout sélectionner
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000018 |
| Waiting for query cache lock   | 0.000004 |
| checking query cache for query | 0.000046 |
| checking permissions           | 0.000005 |
| Opening tables                 | 0.000012 |
| System lock                    | 0.000006 |
| Waiting for query cache lock   | 0.000015 |
| init                           | 0.000020 |
| optimizing                     | 0.000008 |
| statistics                     | 0.000029 |
| preparing                      | 0.000018 |
| FULLTEXT initialization        | 0.000453 |
| executing                      | 0.000004 |
| Sorting result                 | 1.274970 |
| Sending data                   | 0.000154 |
| Waiting for query cache lock   | 0.000003 |
| Sending data                   | 0.000061 |
| Waiting for query cache lock   | 0.000003 |
| Sending data                   | 0.000046 |
| Waiting for query cache lock   | 0.000002 |
| Sending data                   | 0.000045 |
| Waiting for query cache lock   | 0.000002 |
| Sending data                   | 0.000043 |
| Waiting for query cache lock   | 0.000002 |
| Sending data                   | 0.000042 |
| Waiting for query cache lock   | 0.000002 |
| Sending data                   | 0.000033 |
| end                            | 0.000004 |
| query end                      | 0.000003 |
| closing tables                 | 0.000007 |
| freeing items                  | 0.000008 |
| Waiting for query cache lock   | 0.000002 |
| freeing items                  | 0.000004 |
| Waiting for query cache lock   | 0.000003 |
| freeing items                  | 0.000002 |
| storing result in query cache  | 0.000003 |
| logging slow query             | 0.000002 |
| cleaning up                    | 0.000003 |
+--------------------------------+----------+


Autant dire qu'avec ce genre d'info, on voit directement où se trouve le problème ! Après quelques recherches, j'ai enfin trouvé comment obtenir ce tableau, et je vous propose de le découvrir dans le dernier article sur mon blog.


Mais si je vous parle de cela aujourd'hui, c'est parce que après avoir vu d'où venait le problème dans mes requêtes et après l'avoir corrigé, les 2 requêtes s'exécutaient en quelques ms. Génial.
Et vous savez quoi ?
Avant la correction, Google crawlait une page du site toutes les 2 ou 3 secondes.
Après la correction, il crawle environ 3 ou 4 pages par secondes.
Soit environ 10 fois plus qu'avant ! Tout ça en prenant soin d'optimiser ses requêtes.

On verra certainement dans quelques jours si cela a un impact positif sur le référencement du site, mais je suis convaincu que oui. Cela confirmera sans doute que je disais dans un précédent article, un site web rapide, c'est important pour les visiteurs et pour Google. Et pour moi, ce genre d'optimisations onsite sont bien plus importantes que du net linking, mais là, ça reste mon avis sans preuve :)
Avatar de l’utilisateur
Juliano
Membre Certifié
Membre Certifié
 
Message(s) : 186
Enregistré le: 24 Août 2013, 11:29
Localisation : BE
A remercié: 2 fois
Remercié: 21 fois

Publicités

Re: Les requêtes SQL lentes

Message par BrunoT » 24 Déc 2013, 11:26

Il est clair que plus un site est rapide à charger, mieux c'est ;)

Merci pour ton retour d'expérience.
Avatar de l’utilisateur
BrunoT
V.I.P
V.I.P
 
Message(s) : 3771
Enregistré le: 08 Juin 2013, 18:43
Localisation : GoDaddy
A remercié: 492 fois
Remercié: 362 fois

Re: Les requêtes SQL lentes

Message par Juliano » 27 Déc 2013, 11:59

Toujours pas d'augmentation du nombre de visites, mais au moins, maintenant Google sait que j'ai beaucoup de pages à lui montrer :siffle:

Image
Avatar de l’utilisateur
Juliano
Membre Certifié
Membre Certifié
 
Message(s) : 186
Enregistré le: 24 Août 2013, 11:29
Localisation : BE
A remercié: 2 fois
Remercié: 21 fois

Re: Les requêtes SQL lentes

Message par Nicolaseo » 28 Déc 2013, 16:52

J'ai eu des problèmes similaire ya quelques temps et c'est sûr que de créer les bons index ça change tout :)

Par contre j'ai une table mysql de plusieurs giga soit 5 millions de lignes et j'affiche des tableaux grâce à des select mais c'est super long à charger...

Des pistes pour un affichage progressif ou plus rapide ?

biensur j'ai créé tous les index...
Créateur de ghstools.fr et content-spinning.fr
Avatar de l’utilisateur
Nicolaseo
Membre Certifié
Membre Certifié
 
Message(s) : 229
Enregistré le: 14 Oct 2013, 16:17
Localisation : France / Suisse / Chine
A remercié: 11 fois
Remercié: 33 fois

Re: Les requêtes SQL lentes

Message par Juliano » 28 Déc 2013, 18:21

Si tu dois sélectionner beaucoup de lignes, une première chose à vérifier, c'est si tu fais bien un SELECT des champs nécessaires uniquement, et pas un SELECT *.

Pour un affichage progressif, si c'est destiné aux utilisateurs, l'idéal est la méthode du scroll infini: arrivé en bas du tableau, tu charges et affiches la suite.
Maintenant, si tout le contenu doit être là pour le crawler, c'est plus délicat.
Dans ce cas, il faut récupérer le contenu partie par partie, et flusher l'output après le traitement de chaque partie.

Un petit script qui fait un truc du genre:
Code : Tout sélectionner
<?php

header('Content-type: text/html; charset=utf-8');
echo 'Begin ...<br />';
for ($i = 0 ; $i < 10 ; $i++) {
    echo $i . '<br />';
    flush();
    ob_flush();
    sleep(1);
}

echo 'End ...<br />';

?>
Avatar de l’utilisateur
Juliano
Membre Certifié
Membre Certifié
 
Message(s) : 186
Enregistré le: 24 Août 2013, 11:29
Localisation : BE
A remercié: 2 fois
Remercié: 21 fois

Re: Les requêtes SQL lentes

Message par Nicolaseo » 28 Déc 2013, 19:30

Merci ;)

Je fais bien un select des infos nécessaire et le flush c'est pas bon je pense dans ce cas c'est le sql qui met du temps.

On m'avais parlé de NOSQL mais ca me semble compliqué à mettre en place :(
Créateur de ghstools.fr et content-spinning.fr
Avatar de l’utilisateur
Nicolaseo
Membre Certifié
Membre Certifié
 
Message(s) : 229
Enregistré le: 14 Oct 2013, 16:17
Localisation : France / Suisse / Chine
A remercié: 11 fois
Remercié: 33 fois

Re: Les requêtes SQL lentes

Message par Juliano » 28 Déc 2013, 19:38

Le NOSQL, c'est une autre façon de voir les bases de données. Ça a l'air super intéressant, et en tout cas très à la mode, mais je n'ai pas encore eu le temps de me pencher là-dessus :(

Pour ton problème, ça m'intrigue. Tu aurais des données chiffrées ?
- Temps de la requête (avec le détail)
- Temps du fetch des résultats
- Temps de l'affichage
Avatar de l’utilisateur
Juliano
Membre Certifié
Membre Certifié
 
Message(s) : 186
Enregistré le: 24 Août 2013, 11:29
Localisation : BE
A remercié: 2 fois
Remercié: 21 fois

Re: Les requêtes SQL lentes

Message par Nicolaseo » 28 Déc 2013, 20:36

Ok je vais essayer de me repencher dessus asser rapidement je te dirais ici ;)
Créateur de ghstools.fr et content-spinning.fr
Avatar de l’utilisateur
Nicolaseo
Membre Certifié
Membre Certifié
 
Message(s) : 229
Enregistré le: 14 Oct 2013, 16:17
Localisation : France / Suisse / Chine
A remercié: 11 fois
Remercié: 33 fois

Re: Les requêtes SQL lentes

Message par jeromeweb » 05 Jan 2014, 16:16

@Juliano , tu ne peux pas logger les slow query sur ton serveur?

Tu devrais au moins mettre un systeme de cache de pages si ta BDD est vouée à énormément grossir
Mon blog web, seo et telephonie mobile : jeromeweb.net
Avatar de l’utilisateur
jeromeweb
Habitué
Habitué
 
Message(s) : 112
Enregistré le: 07 Nov 2013, 23:43
Localisation : IDF
A remercié: 14 fois
Remercié: 16 fois

Re: Les requêtes SQL lentes

Message par Juliano » 05 Jan 2014, 16:51

En effet, je n'ai pas pensé à logguer les slow queries à ce moment là. Je savais déjà quelles étaient les requêtes lentes, et je ne sais pas s'il y a le détail du temps d'exécution dans ce log.

Pour le système de cache, c'est exactement ce que j'ai fait. Données en cache avec memcache.
Avatar de l’utilisateur
Juliano
Membre Certifié
Membre Certifié
 
Message(s) : 186
Enregistré le: 24 Août 2013, 11:29
Localisation : BE
A remercié: 2 fois
Remercié: 21 fois

Re: Les requêtes SQL lentes

Message par jeromeweb » 06 Jan 2014, 00:00

Par défaut ça log les requêtes dont l’exécution est > 10 secondes (de tête) mais après tu peux le parametrer comme tu veux.
Ça permet d'avoir une idée des ralentissements BDD 24h sur 24, et parfois on a des surprises (la nuit par exemple, quand il y a des maintenances serveurs)

Si tes pages étaient déjà en cache, pourquoi l'optimisation te fait gagner autant de crawl?

Jerome
Mon blog web, seo et telephonie mobile : jeromeweb.net
Avatar de l’utilisateur
jeromeweb
Habitué
Habitué
 
Message(s) : 112
Enregistré le: 07 Nov 2013, 23:43
Localisation : IDF
A remercié: 14 fois
Remercié: 16 fois

Re: Les requêtes SQL lentes

Message par Juliano » 06 Jan 2014, 00:27

Parce que le site compte énormément de pages, générées au fur et à mesure de son utilisation.
Une fois qu'on charge la page, le contenu se met en cache pour un certain temps.

Mais au vu du nombre de pages à crawler, le cache miss est assez important. Quand Google visitait une page, elle n'avait pas spécialement été visitée ces derniers jours, donc plus en cache.
Avatar de l’utilisateur
Juliano
Membre Certifié
Membre Certifié
 
Message(s) : 186
Enregistré le: 24 Août 2013, 11:29
Localisation : BE
A remercié: 2 fois
Remercié: 21 fois

Re: Les requêtes SQL lentes

Message par jeromeweb » 06 Jan 2014, 00:45

Je comprends mieux alors.
+ de 3 millions de pages tu as, c'est ça?
Mon blog web, seo et telephonie mobile : jeromeweb.net
Avatar de l’utilisateur
jeromeweb
Habitué
Habitué
 
Message(s) : 112
Enregistré le: 07 Nov 2013, 23:43
Localisation : IDF
A remercié: 14 fois
Remercié: 16 fois

Re: Les requêtes SQL lentes

Message par Juliano » 06 Jan 2014, 10:55

Un peu plus de 3 millions indexées dans Google oui
Avatar de l’utilisateur
Juliano
Membre Certifié
Membre Certifié
 
Message(s) : 186
Enregistré le: 24 Août 2013, 11:29
Localisation : BE
A remercié: 2 fois
Remercié: 21 fois

Re: Les requêtes SQL lentes

Message par jeromeweb » 06 Jan 2014, 11:54

Ha oui du coup même si tu lances un xenu pour pre-charger le cache, ça va te prendre des heures pour tout crawler...

En tout cas interessant ton screenshot de GWT où l'on voit l'influence du temps de chargement sur le crawl de GoogleBot
Mon blog web, seo et telephonie mobile : jeromeweb.net
Avatar de l’utilisateur
jeromeweb
Habitué
Habitué
 
Message(s) : 112
Enregistré le: 07 Nov 2013, 23:43
Localisation : IDF
A remercié: 14 fois
Remercié: 16 fois

Re: Les requêtes SQL lentes

Message par YannOuch » 30 Jan 2014, 19:17

Bonsoir,
J'ai pu remarqué sur l'un de mes sites que certaines requetes coincé et surchargé le serveur.

J'en connait la cause : Rafraichissement par F5 des utilisateurs (pour aller plus vites).
J'aimerais connaître la ou les requetes qui coinces.

Un tel tableau s'obtient pour une requete ou plusieurs ?

Devons nous tester toutes les requetes une par une ?

De plus, tu dis je cite :
En un coup d’oeil, vous voyez directement d’où vient votre problème: table verrouillée, jointures, tris, … ?


Mais je t'avouerais que moi non. ^^
je remarque bien le sorting result, cependant quels sont les correspondance ?

Merci de ton aide Juliano, j'aimerais vraiment alléger tout ça !

Amicalement, Yann.

PS : je peux fournir le fameux suivi système de mon hébergement si cela peut aider. :s
Enfile ton costume de Noob et viens découvrir mon petit Blog SEO pour les amateurs !
Avatar de l’utilisateur
YannOuch
Modérateur
Modérateur
 
Message(s) : 890
Enregistré le: 23 Août 2013, 18:20
Localisation : Toulouse
A remercié: 107 fois
Remercié: 162 fois

Re: Les requêtes SQL lentes

Message par Juliano » 30 Jan 2014, 21:59

En effet, la technique montrée permet d'avoir le résultat requête par requête. Il est peut-être possible de logguer ce résultat pour chaque requête, mais je n'ai pas fait de recherches à ce sujet.

Effectivement, si une requête est fort lente, et verrouille la table pendant longtemps, les autres requêtes devront attendre que la première soit terminée pour pouvoir s'exécuter, d'où les lenteurs.

Assez difficile de t'aider comme ça à l'aveugle, mais n'hésite pas à venir me voir sur Skype (julienmahin) et on erra ce qu'on peut faire pour résoudre le problème.
Avatar de l’utilisateur
Juliano
Membre Certifié
Membre Certifié
 
Message(s) : 186
Enregistré le: 24 Août 2013, 11:29
Localisation : BE
A remercié: 2 fois
Remercié: 21 fois

Re: Les requêtes SQL lentes

Message par jeromeweb » 30 Jan 2014, 22:17

@YannOuch, si tu à accès à ça, active les "slow queries"
http://dev.mysql.com/doc/refman/5.0/fr/ ... y-log.html
Mon blog web, seo et telephonie mobile : jeromeweb.net
Avatar de l’utilisateur
jeromeweb
Habitué
Habitué
 
Message(s) : 112
Enregistré le: 07 Nov 2013, 23:43
Localisation : IDF
A remercié: 14 fois
Remercié: 16 fois

Re: Les requêtes SQL lentes

Message par YannOuch » 31 Jan 2014, 00:45

jeromeweb a écrit:@YannOuch, si tu à accès à ça, active les "slow queries"
http://dev.mysql.com/doc/refman/5.0/fr/ ... y-log.html


Bonsoir,

Je n'ai pas accès à ces logs.
J'ai accès aux logs du site (accès/erreurs) c'est tout.

Les erreurs les plus récurrentes sont ces deux :

[Thu Jan 30 04:03:52 2014] [error] [client 184.75.216.159] client denied by server configuration: /var/www/default_vds/codes-faciles/index.cgi
[Thu Jan 30 04:03:52 2014] [error] [client 184.75.216.159] client denied by server configuration: /var/www/default_vds/codes-faciles/index.pl


Juliano je vais voir de trouver du temps ce WE !

Amicalement, yann.
Viens faire le tour du Monde de RankSEO avec nous !
Avatar de l’utilisateur
YannOuch
Modérateur
Modérateur
 
Message(s) : 890
Enregistré le: 23 Août 2013, 18:20
Localisation : Toulouse
A remercié: 107 fois
Remercié: 162 fois


Retour vers Code Maison



Qui est en ligne

Utilisateurs parcourant ce forum : Aucun utilisateur inscrit et 1 invité

  • Publicités

Webmaster Jimcail.fr

Propulsé par phpBB® Forum Software © phpBB Group • Traduit par phpBB-fr.com
phpBB SEO
Theme created by StylerBB.net
cron