Re: Problème de select suivant un update

[Guillaume Lelarge <guillaume(at)lelarge(dot)info>]

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).

> > 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).

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.

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