Re: Problème de select suivant un update

Le Wednesday 04 June 2008, Guillaume Lelarge a écrit :
> Valérie SCHNEIDER a écrit :
> > Le mardi 03 juin 2008 à 23:49 +0200, Guillaume Lelarge a écrit :
> >> Valérie SCHNEIDER a écrit :
> >>> [...]
> >
> > [...]
> > La taille de la table sur disque est de 3 Go pour 5 millions de lignes
> > (en moyenne 700 octets par ligne).
> >
> > L'update porte sur 70000 lignes et met un certain temps. Le problème
> > pour moi n'est pas là.
>
> On est d'accord là-dessus.
>
> > Ce qui me dérange, c'est qu'ensuite, lorsque je
> > fais un accès select à la table sur ces mêmes lignes qui viennent d'être
> > mises à jour, avec les mêmes conditions, donc un select qui ramènera 0
> > ligne, et qui passe par l'index, cet accès devrait être immédiat, or il
> > met un temps catastrophique (de l'ordre de 300s) !
>
> Ça peut-être dû au bgwriter qui s'exécute, ou au postmaster qui écrit
> les tampons modifiés du cache pour récupérer de la place.
>
> > Si je relance le même
> > select (sur le même paquet de lignes), là le comportement sera celui
> > attendu : résultat quasi immédiat.
>
> Problème de cache à priori (voir mon explication plus détaillée
> ci-dessous).
>
> > Le problème semble bien porter sur les lignes qui viennent d'être mises
> > à jour. Un select du même type (utilisant l'index) et portant sur un
> > autre paquet de lignes, sera rapide (<1s).
>
> Cache trop petit ? (voir mon explication plus détaillée ci-dessous)
>
> > C'est réellement un pb pour nous. On est en train de mettre en place une
> > base à changement d'état, des tables journalières contenant 5 millions
> > de lignes, chacune sera mise à jour 2 fois dans la journée en moyenne,
> > donc 10 millions d'update par jour; en permanence une tâche recherchera
> > des lignes dans un état donné, donc ce que l'on teste reflétera ce que
> > l'on cherche à mettre en opérationnel.
>
> CHECKPOINT peut être votre solution (voir mon explication plus détaillée
> ci-dessous).
>
> > Remarque : sur la même machine on a fait une base oracle (10g)
> > identique, et exécuté les mêmes instructions : le temps d'update est
> > comparable, le select qui suit est immédiat.
>
> Cédric Villemain me dit qu'Oracle a un cache pour les résultats. J'en
> profite donc pour ajouter ici un point qui a son importance : PostgreSQL
> n'a pas de cache pour les résultats. Son cache est principalement un
> cache disque (shared_buffers pour les fichiers de données, temp_buffer
> pour les objets temporaires, wal_buffers pour les journaux de transaction).

ahem, je ne sais pas comment oracle gère son cache de résulats, mais il est
manifeste que pour Valérie (avec la conf d'oracle et de postgresql qui sont
propre à son install, et que nous ne connaissons pas) oracle donne de
meilleures perfs que postgresql.

>
> >> 1. Avant mise à jour, quel temps met cette requête à s'exécuter ?
> >> 2. Quelle est la taille de la table et de l'index ? (taille physique,
> >> pas le nombre de lignes)
> >> 3. Comment est construit l'index ? (à priori, je suppose qu'il utilise
> >> trois colonnes (state, channel, clef)
> >> 4. Avez-vous essayé de lancer un ANALYZE entre l'UPDATE et le SELECT ?
> >> Pourriez-vous le faire et nous indiquer ce que cela donne ?
> >
> > Autre test :
> > 1. un select sur un paquet de lignes toujours en passant par index (on
> > fait toujours un explain analyze pour en être sûr) -> comportement
> > normal (réponse immédiate)
> > 2. update sur ce même paquet de lignes -> un certain temps, supposé
> > normal (900 s)
> > 3. analyse verbose de la table -> 21s
> > 4. le select sur le même paquet de lignes (qui viennent d'être
> > modifiées) donc remonte 0 ligne -> 419 s !!!!
> > 5. un second select sur le même paquet de lignes (donc remonte 0 ligne)
> > -> immédiat
>
> Ce n'est pas un problème de l'optimiseur car vous avez mis à jour les
> statistiques avec l'étape 3 (ça se voit dans le explain du fichier,
> stats identiques pour le LIMIT comme pour le INDEX SCAN).

En fait, l'optimiseur ne rentre pas en ligne de compte pour comprendre
pourquoi le select apres update est bien long.

Cela n'empeche pas d'optimier index et tables.
1/ l'index obligeant à un filter n'est pas forcément optimum.
2/ les statistiques me semblent insuffisantes (avec un champs text un stat
target inférieur à 100 me semble tres optimiste)

>
> La différence de temps d'exécution entre les points 4 et 5 est
> clairement un problème de cache PostgreSQL. Voici mon hypothèse :
> * étape 1, le cache n'est pas rempli, le processus postgres lié à votre
> client peut lire rapidement le disque ;
> * étape 2, mise à jour des lignes, certaines des lignes sont écrites
> sur disque mais pas toutes ;
> * étape 3, analyze de la table, vu le nombre de lignes, ça impacte peu
> le cache ;
> * étape 4, le SELECT va vouloir charger une partie de la table et de
> l'index dans le cache... malheureusement, il n'y a pas assez de
> place... malheureusement encore, il doit écrire les pages disque
> modifiées dans le tampon sur disque, d'où le temps monstrueux de
> l'exécution.
> * étape 5, le deuxième SELECT est rapide car le cache contient déjà la
> majorité du contenu de la table et de l'index... et s'il a besoin de
> plus, l'étape 4 a déjà écrit une bonne partie sur le disque.
>
> Il y a deux moyens de vérifier mon hypothèse. La première est d'ajouter
> une étape 3.5 qui exécute un CHECKPOINT... à ce moment-là, vous serez
> certaine que le cache ne contient aucun tampon modifié et, du coup, le
> SELECT de l'étape 4 devrait être plus rapide (toujours d'après mon
> hypothèse). La seconde est d'utiliser vmstat pour vérifier si des
> écritures disque ont bien lieu pendant le SELECT.

iotop (ou une utilisation de libnotify) pourraient être utile pour déterminer
les processus créant de l'I/O (si vmstat confirme un acces io important).

>
> Si cela ne change rien, d'autres questions permettraient d'avancer :
> * de quel quantité de mémoire dispose le serveur ?
> * que vaut shared_buffers ? (s'il est possible d'avoir le fichier
> postgresql.conf complet, ce serait préférable... vous pouvez me
> l'envoyer directement si cela pose un problème qu'il devienne public)
> * et quel est la taille de la table (3 Go à priori) et des index ?
>
> Désolé pour toutes ces questions, mais c'est le seul moyen d'avancer :-/
>
>
> --
> Guillaume.
> http://www.postgresqlfr.org
> http://dalibo.com

--
Cédric Villemain
Administrateur de Base de Données
Cel: +33 (0)6 74 15 56 53
http://dalibo.com - http://dalibo.org