Re: Problème d'update et de performance

Bonjour,

Nous observons un comportement curieux d'une série d'update sur une base
PG. Je suis preneur d'explication si vous en avez ...

Voilà : il s'agit d'une base PG 8.3.1 sur serveur linux RedHat 5.1 64
bit avec 4 Go de RAM :

Date: mer mai 14 09:38:14 GMT 2008
Système Linux: Linux TDIFINTG 2.6.18-53.el5xen #1 SMP
Wed Oct 10 16:48:44 EDT 2007 x86_64 x86_64 x86_64
GNU/Linux
Redhat-Release: Red Hat Enterprise Linux Server release 5.1 (Tikanga)
Version Postgresql: 8.3.1

Au niveau de postgresql .conf :
# - Memory -
shared_buffers = 1024MB # min 128kB or
max_connections*16kB

# - Checkpoints -
checkpoint_segments = 10 # in logfile segments, min 1,
16MB each

# pour les vacuum
maintenance_work_mem = 256MB # min 1MB

# Pour les operations de tri
work_mem = 16MB # min 64kB

# memoire partagee utilisee par une transaction typique.
wal_buffers = 1024kB # min 32kB

autovacuum = off # enable autovacuum subprocess?

Un cron effectue des analyze sur les tables à intervalles choisis.

On effectue un update sur une table de 5 millions de lignes, de taille
environ 3Go, portant sur environ 50000 lignes, selon des critères
utilisant un index.
En exécutant plusieurs fois à la suite le même update (donc à partir du
second plus aucune ligne n'est mise à jour) on observe des temps très
longs pour finalement tomber à quelques millisecondes (qui est le
résultat attendu).

Que se passe-t'il d'après vous ?

Ci-dessous en pièce jointe la description de la table, des index, et une
série d'explain analyze update.

Merci !
Valérie.

--

********************************************************************
* Les points de vue exprimes sont strictement personnels et *
* n'engagent pas la responsabilite de METEO-FRANCE. *
********************************************************************
* Valerie SCHNEIDER Tel : +33 (0)5 61 07 81 91 *
* METEO-FRANCE / DSI/DEV Fax : +33 (0)5 61 07 81 09 *
* 42, avenue G. Coriolis Email : Valerie(dot)Schneider(at)meteo(dot)fr *
* 31057 TOULOUSE Cedex 1 - FRANCE http://www.meteo.fr *
********************************************************************

On Fri, 16 May 2008 09:15:51 +0000
Valérie SCHNEIDER <valerie(dot)schneider(at)meteo(dot)fr> wrote:

> Bonjour,
>
> Nous observons un comportement curieux d'une série d'update sur une base
> PG. Je suis preneur d'explication si vous en avez ...
>
> Voilà : il s'agit d'une base PG 8.3.1 sur serveur linux RedHat 5.1 64
> bit avec 4 Go de RAM :
>
> Date: mer mai 14 09:38:14 GMT 2008
> Système Linux: Linux TDIFINTG 2.6.18-53.el5xen #1 SMP
> Wed Oct 10 16:48:44 EDT 2007 x86_64 x86_64 x86_64
> GNU/Linux
> Redhat-Release: Red Hat Enterprise Linux Server release 5.1 (Tikanga)
> Version Postgresql: 8.3.1
>
> Au niveau de postgresql .conf :
> # - Memory -
> shared_buffers = 1024MB # min 128kB or
> max_connections*16kB
>
> # - Checkpoints -
> checkpoint_segments = 10 # in logfile segments, min 1,
> 16MB each
>
> # pour les vacuum
> maintenance_work_mem = 256MB # min 1MB
>
> # Pour les operations de tri
> work_mem = 16MB # min 64kB
>
> # memoire partagee utilisee par une transaction typique.
> wal_buffers = 1024kB # min 32kB
>
> autovacuum = off # enable autovacuum subprocess?
>
>
> Un cron effectue des analyze sur les tables à intervalles choisis.
>
> On effectue un update sur une table de 5 millions de lignes, de taille
> environ 3Go, portant sur environ 50000 lignes, selon des critères
> utilisant un index.
> En exécutant plusieurs fois à la suite le même update (donc à partir du
> second plus aucune ligne n'est mise à jour) on observe des temps très
> longs pour finalement tomber à quelques millisecondes (qui est le
> résultat attendu).
>
> Que se passe-t'il d'après vous ?
>
>
> Ci-dessous en pièce jointe la description de la table, des index, et une
> série d'explain analyze update.
>
>
> Merci !
> Valérie.
>
> --
>
Bonjour,

Voilà une question intéressante, en résumé :
mise à jour de 1% d'une table de 5 millions de lignes soit environ 3Go,
temps de traitement de < 15mn à < 3ms.

Vu les temps, ce doit être du batch : jamais vu personne capable de faire ça.
Tous les SGBD ont tendance à privilégier le conversationnel et c'est normal.
1/ En batch, le temps ne parait pas très important ...
2/ À quel intervalle de temps (!) avez vous lancé vos mises à jour ?

Je suis loin d'être un gourou, mais quelques pistes à explorer :
- mettre en début de procédure un "begin work" et en fin un "commit work"
juste pour lui dire que c'est validé,
- PG stocke temporairement les modifs en mémoire, puis ensuite dans /tmp,
combien de temps ? existe-t'il quelque chose style "sync" ?
AMHA juste VACUUM, mais il faudrait en mettre un AU DÉBUT de votre transaction
pour voir.

Espérant vous aider,
--
Alain <eurlix(dot)alain(at)free(dot)fr>

Alain a écrit :
> [...]
> - PG stocke temporairement les modifs en mémoire, puis ensuite dans /tmp,
> combien de temps ? existe-t'il quelque chose style "sync" ?

Excuse-moi mais tu tiens ça d'où ?

Les modifications réalisées par des opérations de type
INSERT/UPDATE/DELETE sont immédiatement faite dans le cache disque de
PostgreSQL, donc en mémoire. S'il n'a pas assez de place dans ce cache
disque, les tampons de cache les moins utilisées sont immédiatement
enregistrées dans les journaux de transaction. Les tampons en question
sont ensuite libérés puis réutilisés pour d'autres pages disque. Jamais
(à ma connaissance) PostgreSQL ne stocke des choses dans /tmp (en dehors
de la socket Unix par exemple).

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com

Rebonjour,
Je crois que je me suis mal exprimée : le comportement que je voudrais
comprendre, c'est pourquoi, en exécutant plusieurs fois le même update
(à la suite, l'un après l'autre, dans la même session psql), le temps
d'exécution ne soit pas quasi-immédiat à partir du second update
(puisque plus aucune ligne à updater et qu'on passe par l'index -ce que
confirme les explain analyze).
Valérie.

Le vendredi 16 mai 2008 à 09:15 +0000, Valérie SCHNEIDER a écrit :
> Bonjour,
>
> Nous observons un comportement curieux d'une série d'update sur une base
> PG. Je suis preneur d'explication si vous en avez ...
>
> Voilà : il s'agit d'une base PG 8.3.1 sur serveur linux RedHat 5.1 64
> bit avec 4 Go de RAM :
>
> Date: mer mai 14 09:38:14 GMT 2008
> Système Linux: Linux TDIFINTG 2.6.18-53.el5xen #1 SMP
> Wed Oct 10 16:48:44 EDT 2007 x86_64 x86_64 x86_64
> GNU/Linux
> Redhat-Release: Red Hat Enterprise Linux Server release 5.1 (Tikanga)
> Version Postgresql: 8.3.1
>
> Au niveau de postgresql .conf :
> # - Memory -
> shared_buffers = 1024MB # min 128kB or
> max_connections*16kB
>
> # - Checkpoints -
> checkpoint_segments = 10 # in logfile segments, min 1,
> 16MB each
>
> # pour les vacuum
> maintenance_work_mem = 256MB # min 1MB
>
> # Pour les operations de tri
> work_mem = 16MB # min 64kB
>
> # memoire partagee utilisee par une transaction typique.
> wal_buffers = 1024kB # min 32kB
>
> autovacuum = off # enable autovacuum subprocess?
>
>
> Un cron effectue des analyze sur les tables à intervalles choisis.
>
> On effectue un update sur une table de 5 millions de lignes, de taille
> environ 3Go, portant sur environ 50000 lignes, selon des critères
> utilisant un index.
> En exécutant plusieurs fois à la suite le même update (donc à partir du
> second plus aucune ligne n'est mise à jour) on observe des temps très
> longs pour finalement tomber à quelques millisecondes (qui est le
> résultat attendu).
>
> Que se passe-t'il d'après vous ?
>
>
> Ci-dessous en pièce jointe la description de la table, des index, et une
> série d'explain analyze update.
>
>
> Merci !
> Valérie.
>
--

********************************************************************
* Les points de vue exprimes sont strictement personnels et *
* n'engagent pas la responsabilite de METEO-FRANCE. *
********************************************************************
* Valerie SCHNEIDER Tel : +33 (0)5 61 07 81 91 *
* METEO-FRANCE / DSI/DEV Fax : +33 (0)5 61 07 81 09 *
* 42, avenue G. Coriolis Email : Valerie(dot)Schneider(at)meteo(dot)fr *
* 31057 TOULOUSE Cedex 1 - FRANCE http://www.meteo.fr *
********************************************************************

Valérie SCHNEIDER a écrit :
> Bonjour,
>
> Nous observons un comportement curieux d'une série d'update sur une base
> PG. Je suis preneur d'explication si vous en avez ...
>
> Voilà : il s'agit d'une base PG 8.3.1 sur serveur linux RedHat 5.1 64
> bit avec 4 Go de RAM :
>
> Date: mer mai 14 09:38:14 GMT 2008
> Système Linux: Linux TDIFINTG 2.6.18-53.el5xen #1 SMP
> Wed Oct 10 16:48:44 EDT 2007 x86_64 x86_64 x86_64
> GNU/Linux
> Redhat-Release: Red Hat Enterprise Linux Server release 5.1 (Tikanga)
> Version Postgresql: 8.3.1
>
> Au niveau de postgresql .conf :
> # - Memory -
> shared_buffers = 1024MB # min 128kB or
> max_connections*16kB
>
> # - Checkpoints -
> checkpoint_segments = 10 # in logfile segments, min 1,
> 16MB each
>
> # pour les vacuum
> maintenance_work_mem = 256MB # min 1MB
>
> # Pour les operations de tri
> work_mem = 16MB # min 64kB
>
> # memoire partagee utilisee par une transaction typique.
> wal_buffers = 1024kB # min 32kB
>
> autovacuum = off # enable autovacuum subprocess?
>
>
> Un cron effectue des analyze sur les tables à intervalles choisis.
>
> On effectue un update sur une table de 5 millions de lignes, de taille
> environ 3Go, portant sur environ 50000 lignes, selon des critères
> utilisant un index.
> En exécutant plusieurs fois à la suite le même update (donc à partir du
> second plus aucune ligne n'est mise à jour) on observe des temps très
> longs pour finalement tomber à quelques millisecondes (qui est le
> résultat attendu).
>
> Que se passe-t'il d'après vous ?

la première fois, le job est fait avec beaucoup d'entrés-sorties
correspondant à des delete suivi d'insert
la 2e fois, toutes les lignes utiles sont ramenées dans le cache disque
de l'OS
la 3e fois, comme tout est monté dans la mémoire (il ne s'est rien apssé
entretemps), c'est instantané.

--
Jean-Max Reymond
CKR Solutions http://www.ckr-solutions.com

Le Friday 16 May 2008, Valérie SCHNEIDER a écrit :
> Bonjour,

Bonjour,

voir réponses plus bas.

> Voilà : il s'agit d'une base PG 8.3.1 sur serveur linux RedHat 5.1 64
> bit avec 4 Go de RAM :
>
> Date: mer mai 14 09:38:14 GMT 2008
> Système Linux: Linux TDIFINTG 2.6.18-53.el5xen #1 SMP
> Wed Oct 10 16:48:44 EDT 2007 x86_64 x86_64 x86_64
> GNU/Linux
> Redhat-Release: Red Hat Enterprise Linux Server release 5.1 (Tikanga)
> Version Postgresql: 8.3.1
>
> Au niveau de postgresql .conf :
> # - Memory -
> shared_buffers = 1024MB # min 128kB or
> max_connections*16kB
>
> # - Checkpoints -
> checkpoint_segments = 10 # in logfile segments, min 1,
> 16MB each
>
> # pour les vacuum
> maintenance_work_mem = 256MB # min 1MB
>
> # Pour les operations de tri
> work_mem = 16MB # min 64kB
>
> # memoire partagee utilisee par une transaction typique.
> wal_buffers = 1024kB # min 32kB
>
> autovacuum = off # enable autovacuum subprocess?
>
>
> Un cron effectue des analyze sur les tables à intervalles choisis.
>
> On effectue un update sur une table de 5 millions de lignes, de taille
> environ 3Go, portant sur environ 50000 lignes, selon des critères
> utilisant un index.
> En exécutant plusieurs fois à la suite le même update (donc à partir du
> second plus aucune ligne n'est mise à jour) on observe des temps très
> longs pour finalement tomber à quelques millisecondes (qui est le
> résultat attendu).
>
> Que se passe-t'il d'après vous ?

Plusieurs possibilités me viennent à l'esprit, je suppose que vous les avez
déjà écarté mais je les donne au cas où :

* la charge du serveur/des accès disques est la meme pendant chaque update ?

* requetes SQL concurrentielles ? (pas de verrous)

* mouvement du cache disque ou des shared_buffer.

Connaitre les volumes de l'index et de la table serait un plus.
Vous pourriez activer 'log_checkpoints'.
Et enfin des outils comme iostat/vmstat pourraient s'avérer utiles également.

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

Jean-Max Reymond a écrit :
> Valérie SCHNEIDER a écrit :
>> Bonjour,
>>
>> Nous observons un comportement curieux d'une série d'update sur une base
>> PG. Je suis preneur d'explication si vous en avez ...
>>
>> Voilà : il s'agit d'une base PG 8.3.1 sur serveur linux RedHat 5.1 64
>> bit avec 4 Go de RAM :
>>
>> Date: mer mai 14 09:38:14 GMT 2008
>> Système Linux: Linux TDIFINTG 2.6.18-53.el5xen #1 SMP
>> Wed Oct 10 16:48:44 EDT 2007 x86_64 x86_64 x86_64
>> GNU/Linux
>> Redhat-Release: Red Hat Enterprise Linux Server release 5.1 (Tikanga)
>> Version Postgresql: 8.3.1
>>
>> Au niveau de postgresql .conf :
>> # - Memory -
>> shared_buffers = 1024MB # min 128kB or
>> max_connections*16kB
>>
>> # - Checkpoints -
>> checkpoint_segments = 10 # in logfile segments, min 1,
>> 16MB each
>>
>> # pour les vacuum
>> maintenance_work_mem = 256MB # min 1MB
>>
>> # Pour les operations de tri
>> work_mem = 16MB # min 64kB
>>
>> # memoire partagee utilisee par une transaction typique.
>> wal_buffers = 1024kB # min 32kB
>>
>> autovacuum = off # enable autovacuum subprocess?
>>
>>
>> Un cron effectue des analyze sur les tables à intervalles choisis.
>>
>> On effectue un update sur une table de 5 millions de lignes, de taille
>> environ 3Go, portant sur environ 50000 lignes, selon des critères
>> utilisant un index.
>> En exécutant plusieurs fois à la suite le même update (donc à partir du
>> second plus aucune ligne n'est mise à jour) on observe des temps très
>> longs pour finalement tomber à quelques millisecondes (qui est le
>> résultat attendu).
>>
>> Que se passe-t'il d'après vous ?
>
> la première fois, le job est fait avec beaucoup d'entrés-sorties
> correspondant à des delete suivi d'insert
> la 2e fois, toutes les lignes utiles sont ramenées dans le cache disque
> de l'OS
> la 3e fois, comme tout est monté dans la mémoire (il ne s'est rien apssé
> entretemps), c'est instantané.
>

À part que la troisième fois prend du temps. Si je reprends ici les
chiffres données :

1. 858616.959 ms
2. 11440.215 ms
3. 365160.313 ms
4. 2.954 ms
5. 2.680 ms

Je pense qu'on manque d'informations, notamment quelle est la durée
entre chaque exécution. Si chaque exécution se suit à une ou deux
secondes près, le 3 peut s'expliquer par le déclenchement de bgwriter.

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com

Encore rebonjour,

Je pense qu'on a trouvé notre problème : l'update est :
update diffusion_2008_05_13 set state = 3001 where state = 2101 and
next_try_channel like 'FTP' and next_try_key like 'uatos_a+host_atos_a
+datos_a+21';

Or dans les conditions on compare un champ à une chaine ... qui contient
des "_" : et vlan... (on a mal lu : le "filter" est correct, mais le
index condition limite au début de chaine jusqu'au premier "_").

explain analyze update diffusion_2008_05_13 set state = 3001 where
state = 2101 and next_try_channel like 'FTP' and next_try_key like
'uatos_a+host_atos_a+datos_a+21';

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using indx_diffusion_2008_05_13_channel on
diffusion_2008_05_13 (cost=0.00..10.41 rows=1 width=7379) (actual
time=12.712..169200.761 rows=48041 loops=1)
Index Cond: ((state = 2101) AND ((next_try_channel)::text ~=~
'FTP'::text) AND ((next_try_key)::text ~>=~ 'uatos'::text) AND
((next_try_key)::text ~<~ 'uatot'::text))
Filter: (((next_try_channel)::text ~~ 'FTP'::text) AND
((next_try_key)::text ~~ 'uatos_a+host_atos_a+datos_a+21'::text))

Euh, désolée, j'ai posé la question trop rapidement... La prochaine fois
je détaillerai méticuleusement le plan d'exécution avant de poster...

Merci encore, Valérie.

Le vendredi 16 mai 2008 à 12:24 +0000, Valérie SCHNEIDER a écrit :
> Rebonjour,
> Je crois que je me suis mal exprimée : le comportement que je voudrais
> comprendre, c'est pourquoi, en exécutant plusieurs fois le même update
> (à la suite, l'un après l'autre, dans la même session psql), le temps
> d'exécution ne soit pas quasi-immédiat à partir du second update
> (puisque plus aucune ligne à updater et qu'on passe par l'index -ce que
> confirme les explain analyze).
> Valérie.
>
> Le vendredi 16 mai 2008 à 09:15 +0000, Valérie SCHNEIDER a écrit :
> > Bonjour,
> >
> > Nous observons un comportement curieux d'une série d'update sur une base
> > PG. Je suis preneur d'explication si vous en avez ...
> >
> > Voilà : il s'agit d'une base PG 8.3.1 sur serveur linux RedHat 5.1 64
> > bit avec 4 Go de RAM :
> >
> > Date: mer mai 14 09:38:14 GMT 2008
> > Système Linux: Linux TDIFINTG 2.6.18-53.el5xen #1 SMP
> > Wed Oct 10 16:48:44 EDT 2007 x86_64 x86_64 x86_64
> > GNU/Linux
> > Redhat-Release: Red Hat Enterprise Linux Server release 5.1 (Tikanga)
> > Version Postgresql: 8.3.1
> >
> > Au niveau de postgresql .conf :
> > # - Memory -
> > shared_buffers = 1024MB # min 128kB or
> > max_connections*16kB
> >
> > # - Checkpoints -
> > checkpoint_segments = 10 # in logfile segments, min 1,
> > 16MB each
> >
> > # pour les vacuum
> > maintenance_work_mem = 256MB # min 1MB
> >
> > # Pour les operations de tri
> > work_mem = 16MB # min 64kB
> >
> > # memoire partagee utilisee par une transaction typique.
> > wal_buffers = 1024kB # min 32kB
> >
> > autovacuum = off # enable autovacuum subprocess?
> >
> >
> > Un cron effectue des analyze sur les tables à intervalles choisis.
> >
> > On effectue un update sur une table de 5 millions de lignes, de taille
> > environ 3Go, portant sur environ 50000 lignes, selon des critères
> > utilisant un index.
> > En exécutant plusieurs fois à la suite le même update (donc à partir du
> > second plus aucune ligne n'est mise à jour) on observe des temps très
> > longs pour finalement tomber à quelques millisecondes (qui est le
> > résultat attendu).
> >
> > Que se passe-t'il d'après vous ?
> >
> >
> > Ci-dessous en pièce jointe la description de la table, des index, et une
> > série d'explain analyze update.
> >
> >
> > Merci !
> > Valérie.
> >
> --
>
> ********************************************************************
> * Les points de vue exprimes sont strictement personnels et *
> * n'engagent pas la responsabilite de METEO-FRANCE. *
> ********************************************************************
> * Valerie SCHNEIDER Tel : +33 (0)5 61 07 81 91 *
> * METEO-FRANCE / DSI/DEV Fax : +33 (0)5 61 07 81 09 *
> * 42, avenue G. Coriolis Email : Valerie(dot)Schneider(at)meteo(dot)fr *
> * 31057 TOULOUSE Cedex 1 - FRANCE http://www.meteo.fr *
> ********************************************************************
>
>
--

********************************************************************
* Les points de vue exprimes sont strictement personnels et *
* n'engagent pas la responsabilite de METEO-FRANCE. *
********************************************************************
* Valerie SCHNEIDER Tel : +33 (0)5 61 07 81 91 *
* METEO-FRANCE / DSI/DEV Fax : +33 (0)5 61 07 81 09 *
* 42, avenue G. Coriolis Email : Valerie(dot)Schneider(at)meteo(dot)fr *
* 31057 TOULOUSE Cedex 1 - FRANCE http://www.meteo.fr *
********************************************************************

On Fri, 16 May 2008 13:33:05 +0200
Guillaume Lelarge <guillaume(at)lelarge(dot)info> wrote:

> Alain a écrit :
> > [...]
> > - PG stocke temporairement les modifs en mémoire, puis ensuite dans /tmp,
> > combien de temps ? existe-t'il quelque chose style "sync" ?
>
> Excuse-moi mais tu tiens ça d'où ?
>
Voila 7 ou 8 ans je cherchait à comprendre comment ça marche
(les SGBD en général et PG en particulier) comme ça marche +tot
je cherche moins ;-)
J'ai lu plein de trucs, en français ou en anglais, peut-être pas tout compris
ni tout retenu ?
Résumé : PG stocke temporairement les modifs en mémoire, ensuite dans le swap
(sur disque, tempo aussi. Jusqu'à quand ?)
Une doc qui m'avait paru intéressante disait, en résumé, que ce n'était
pas nécessaire d'agrandir l'espace mémoire PG sous Linux, car Linux gére
très bien son swap, donc la mémoire allouée et ensuite /tmp qui semble
géré de façon particuliére. J'ai peut-être TOUT FAUX ?

AMHA, aussi bon que soit Linux, et quelle que soit la façon dont il gére ses
caches sur disque, ça ne peut pas être instantané. D'autre part, enregistrer
dans le cache disque (de PG) puis ensuite dans des "journaux" (/disque ?)
et vérifier tout ça lors de la transaction suivante, ça prend du temps ...
Comment réduire ce temps, qui diminue avec le temps, la petite expérience que j'ai
c'est de faire un VACUUM avant (s'il risque d'y avoie eu beaucoup de mise à jour
précédemment) et après une grosse mise à jour en batch, pour ne pas pénaliser les
suivants.

Je n'essaie pas de faire un cours de physique quantique, ni étalage de mes connaisances,
bien modestes, juste d'aider quelqu'un de surpris par 15 mn .. 3 ms.
Les 15 mn ne me paraissent pas aberrants pour le volume ( faudrait essayer de le faire
à la mimine avec un fichier type CISAM), les <3 ms ÈPOUSTOUFLANTS : là, je me demande
comment fait PG.

> Les modifications réalisées par des opérations de type
> INSERT/UPDATE/DELETE sont immédiatement faite dans le cache disque de
> PostgreSQL, donc en mémoire. S'il n'a pas assez de place dans ce cache
> disque, les tampons de cache les moins utilisées sont immédiatement
> enregistrées dans les journaux de transaction. Les tampons en question
> sont ensuite libérés puis réutilisés pour d'autres pages disque. Jamais
> (à ma connaissance) PostgreSQL ne stocke des choses dans /tmp (en dehors
> de la socket Unix par exemple).
>
>
> --
> Guillaume.
> http://www.postgresqlfr.org
> http://dalibo.com
>

--
Alain <eurlix(dot)alain(at)free(dot)fr>