Forums PostgreSQL.fr

gom

Membre

[Version 8.3] INSERT très lent : 1 ligne toutes les 3,2 secondes !

Bonjour à tous,

Pour vous situer le contexte je ne suis pas DBA, je suis Ingénieur Décisionnel et je travaille notamment avec BO Data Integrator.

Un administrateur PostgreSQL de ma boîte m'avait conseillé il y a quelques mois déjà ceci :

Dans le postgresql.conf de chaque serveur, modifier la ligne #autovacuum_analyze_scale_factor = 0.1 vers autovacuum_analyze_scale_factor = 0.01

J'ai à nouveau des problèmes sur ce serveur et je viens de constater que Postgresql.conf est dans cet état là :

#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------

#autovacuum = on			# Enable autovacuum subprocess?  'on' 
					# requires track_counts to also be on.
#log_autovacuum_min_duration = -1	# -1 disables, 0 logs all actions and
					# their durations, > 0 logs only
					# actions running at least that time.
#autovacuum_max_workers = 3		# max number of autovacuum subprocesses
#autovacuum_naptime = 1min		# time between autovacuum runs
#autovacuum_vacuum_threshold = 50	# min number of row updates before
					# vacuum
#autovacuum_analyze_threshold = 50	# min number of row updates before 
					# analyze
#autovacuum_vacuum_scale_factor = 0.2	# fraction of table size before vacuum
autovacuum_analyze_scale_factor = 0.01	# fraction of table size before analyze
#autovacuum_freeze_max_age = 200000000	# maximum XID age before forced vacuum
					# (change requires restart)
#autovacuum_vacuum_cost_delay = 20	# default vacuum cost delay for
					# autovacuum, -1 means use
					# vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1	# default vacuum cost limit for
					# autovacuum, -1 means use
					# vacuum_cost_limit

Sachant que tout est commenté, sauf la ligne que j'ai ajouté ... elle ne sert à rien, exact ? Si oui, suffit-il que je décommente #autovacuum = on en autovacuum = on ?

Gôm

Dernière modification par gom (09/03/2010 13:06:43)

gleu

Administrateur

Re : [Version 8.3] INSERT très lent : 1 ligne toutes les 3,2 secondes !

Non, les valeurs commentés sont les valeurs par défaut (à moins qu'un petit malin les ait modifié). Le plus simple est de se connecter à PostgreSQL et de lancer "show autovacuum" pour voir s'il est ou non activé. La vue pg_settings donne toutes les infos nécessaires sur les paramètres.

Quant au titre, il y a plusieurs possibilités pour cette lenteur : 1. une mauvaise configuration (du serveur, de PostgreSQL, de l'applicatif) 2. un mauvais applicatif.

---

Guillaume.

gom

Membre

Re : [Version 8.3] INSERT très lent : 1 ligne toutes les 3,2 secondes !

"autovacuum";"on"
"autovacuum_analyze_scale_factor";"0.01"
"autovacuum_analyze_threshold";"50"
"autovacuum_freeze_max_age";"200000000"
"autovacuum_max_workers";"3"
"autovacuum_naptime";"60"
"autovacuum_vacuum_cost_delay";"20"
"autovacuum_vacuum_cost_limit";"-1"
"autovacuum_vacuum_scale_factor";"0.2"
"autovacuum_vacuum_threshold";"50"

Donc, le paramétrage est bien tel qu'il m'avait été conseillé de le régler.

Auriez-vous une piste à me donner concernant votre "1." ?

Je ne vois pas pourquoi j'aurais un problème côté applicatif, car mon traitement BODI n'a pas changé et tout fonctionnait très bien la dernière fois que j'ai eu à le lancer.

Gôm

gom

Membre

Re : [Version 8.3] INSERT très lent : 1 ligne toutes les 3,2 secondes !

Voici les écarts que je constate entre ma base de PROD et de DEV :

"checkpoint_completion_target";"0.9"
"checkpoint_segments";"256"
"checkpoint_timeout";"3600"
"default_statistics_target";"100"
"effective_cache_size";"688128";"8kB"
"log_checkpoints";"on"
"log_connections";"on"
"log_disconnections";"on"
"log_line_prefix";"%t [%p]: [%l-1] "
"log_min_duration_statement";"-1"
"log_temp_files";"0";"kB"
"maintenance_work_mem";"524288";"kB"
"max_fsm_pages";"873800"
"max_fsm_relations";"1000"
"max_prepared_transactions";"0"
"shared_buffers";"4096";"8kB"
"temp_tablespaces";""
"vacuum_cost_limit";"200"
"wal_buffers";"128";"8kB"
"work_mem";"307200";"kB"

"checkpoint_completion_target";"0.5"
"checkpoint_segments";"32"
"checkpoint_timeout";"300"
"default_statistics_target";"1000"
"effective_cache_size";"393216";"8kB"
"log_checkpoints";"off"
"log_connections";"off"
"log_disconnections";"off"
"log_line_prefix";"%t "
"log_min_duration_statement";"3000"
"log_temp_files";"-1";"kB"
"maintenance_work_mem";"16384";"kB"
"max_fsm_pages";"2048000"
"max_fsm_relations";"20000"
"max_prepared_transactions";"5"
"shared_buffers";"128000";"8kB"
"temp_tablespaces";"temp"
"vacuum_cost_limit";"2000"
"wal_buffers";"64";"8kB"
"work_mem";"65536";"kB"

Serveur de PROD :

Système d'exploitation : Windows 2003 Server SP2
Processeur : Intel Xeon 2GHz
Mémoire vive : 8 Go

Serveur de DEV :

Système d'exploitation : Windows 2003 Server SP2
Processeur : Intel Xeon 2GHz
Mémoire vive : 3,75 Go

Gôm

Marc Cousin

Membre

Re : [Version 8.3] INSERT très lent : 1 ligne toutes les 3,2 secondes !

Et laquelle des deux bases fonctionne ?

Les paramétrages sont très différents. A première vue le shared_buffers est bien trop gros sur la base de dev, pour une machine sous Windows, et la machine de production est réglée pour de bonnes performances pour les insertions.

---

Marc.

gom

Membre

Re : [Version 8.3] INSERT très lent : 1 ligne toutes les 3,2 secondes !

La base de PROD fonctionne mais sans plus ... On va passer sous Linux, car c'est apparemment préférable, à voir.

Pour mon problème du jour, c'est la base de DEV qui est concernée.

Au niveau de l'utilisation de notre machine de DEV elle devrait être optimisée également pour les insertions, car le fait d'avoir des temps d'interrogations un peu plus longs qu'en PROD n'est pas gênant (surtout que cette machine a moitié moins de RAM, donc bon ...).

Gôm

Dernière modification par gom (09/03/2010 15:04:38)

Marc Cousin

Membre

Re : [Version 8.3] INSERT très lent : 1 ligne toutes les 3,2 secondes !

Bon, de toutes façons, une ligne toutes les 3 secondes, c'est énorme. À mon avis, il faut commencer par chercher du côté des requêtes générées.
À votre place, je commencerais par demander à postgresql de tracer tous les ordres de votre session et leur durée (log_statement à all, log_duration à on), et faire un test de chargement, afin de savoir ce qui ne va pas. Je parierais sur une requête autour de l'insert (vérification d'existence, rapprochement avec des données d'une autre table, etc…)
L'idéal serait de pouvoir le faire au niveau de votre session, histoire de ne voir que vos ordres à vous (si d'autres personnes utilisent cette base de dev).
Si c'est le cas, et que vous pouvez initialiser votre job de chargement en passant des ordres SQL dans vos sessions à la base côté BODI, passez ces deux ordres au début des sessions :

SET log_statement to 'all';
SET log_duration to on;

Sinon, modifiez ces deux options dans le postgresql.conf de la base de développement, et faites un reload de la configuration.

Ensuite, trouvez l'ordre ou les ordres le plus lent, nous les analyserons ensemble.

---

Marc.

gom

Membre

Re : [Version 8.3] INSERT très lent : 1 ligne toutes les 3,2 secondes !

Merci pour votre aide. J'ai modifié le postgresql.conf et fait un Reload.

Où dois-je aller pour analyser les ordres les plus lents ?

Dernière modification par gom (09/03/2010 15:30:44)

Marc Cousin

Membre

Re : [Version 8.3] INSERT très lent : 1 ligne toutes les 3,2 secondes !

Le fichier doit se trouver au même endroit que le postgresql.conf, dans un sous répertoire pg_log. Il devrait y avoir un fichier à plusieurs fichiers de trace par jour.

---

Marc.

gom

Membre

Re : [Version 8.3] INSERT très lent : 1 ligne toutes les 3,2 secondes !

Je crois que j'ai trouvé. Il fallait que je consulte "postgresql-2010-03-09_000000.log" n'est-ce pas ?

Ce fichier contient toutes mes requêtes. Ce sont essentiellement des requêtes UPDATE, elles durent toutes entre 310 et 330 ms. (Cette table contient un peu plus de 500.000 lignes pour 21 colonnes.)

Sinon au lancement de mon Job BODI, j'ai des centaines de lignes comme celle là :

2010-03-09 14:28:57 CET LOG:  durée : 0.000 ms
2010-03-09 14:28:57 CET LOG:  exécute fetch à partir de <unnamed>/SQL_CUR057CF120: BEGIN;declare "SQL_CUR057CF120" cursor with hold for select col1_xi, col1 from mon_schema.ma_table for read only;fetch 1000 in "SQL_CUR057CF120"
2010-03-09 14:28:57 CET LOG:  durée : 0.000 ms

Gôm

Dernière modification par gom (09/03/2010 15:59:45)

gleu

Administrateur

Re : [Version 8.3] INSERT très lent : 1 ligne toutes les 3,2 secondes !

Si vous n'avez que des durées à 0ms, ce n'est pas PostgreSQL la source de la lenteur. D'où tirez-vous votre chiffre de 3,2 secondes par ligne insérée ?

---

Guillaume.

gom

Membre

Re : [Version 8.3] INSERT très lent : 1 ligne toutes les 3,2 secondes !

Les Commit dans BODI se font par paquet de 1000 lignes et les statistiques indiquaient une moyenne d'une ligne insérée toute les 3,2 secondes.

10.000 lignes /2.847 secondes = 3,5 secondes en moyenne depuis mon lancement à 14h28.

Un problème avec le Commit ?

Dernière modification par gom (09/03/2010 16:28:10)

gleu

Administrateur

Re : [Version 8.3] INSERT très lent : 1 ligne toutes les 3,2 secondes !

Il faudrait vérifier sur toutes les durées indiquées si certaines (la majorité) sont bien à 3s.

---

Guillaume.

gom

Membre

Re : [Version 8.3] INSERT très lent : 1 ligne toutes les 3,2 secondes !

Je ne comprends pas d'où vient le problème.

Quoi qu'il en soit si je change le "shared_buffers" comme disait Marc Cousin, que dois-je mettre ? La moitié de la valeur du serveur de PROD, car il y a 2 fois moins de RAM ? A moins que la RAM n'est rien à voir la dedans ...

En sachant que la priorité sur cette machine de DEV, ce sont les INSERT et les UPDATE et non les SELECT. Bon bien sûr il ne faut pas non plus que les problèmes de délai que j'ai avec mes INSERT et UPDATE se reportent sur les SELECT !

Gôm

Dernière modification par gom (09/03/2010 18:16:32)

gleu

Administrateur

Re : [Version 8.3] INSERT très lent : 1 ligne toutes les 3,2 secondes !

Avez-vous vérifié les autres durées indiquées dans les traces ? notamment celles pour les INSERT ?

---

Guillaume.

gom

Membre

Re : [Version 8.3] INSERT très lent : 1 ligne toutes les 3,2 secondes !

Je n'ai que des UPDATE et ils durent tous entre 310 et 330 ms, ce qui fait 3 lignes mises à jour chaque seconde ... alors que BODI me dit qu'il faut plus de 3 secondes pour mettre à jour une ligne !

Les statistiques remontées par BODI sont peut être fausses (à moins que ce ne soit moi qui comprenne rien au Log de PostgreSQL ), mais le pire c'est que dans les faits je vois bien que mon traitement met plus de temps que d'habitude !

Dernière modification par gom (09/03/2010 19:02:42)

Marc Cousin

Membre

Re : [Version 8.3] INSERT très lent : 1 ligne toutes les 3,2 secondes !

De toutes façons, si chaque update modifie une seule ligne et met dans les 300ms, le temps est anormal.

Quel est cet update ? Si c'est un update simple avec une simple clause where, les colonnes sont elles correctement indexées ? (donnez nous la requête vue dans la log et la description complète, avec les index, de la ou des tables impliquées)

---

Marc.

gom

Membre

Re : [Version 8.3] INSERT très lent : 1 ligne toutes les 3,2 secondes !

C'est un UPDATE simple.

Noooooooooon j'hallucine !!!

Les Index présents en PROD n'existent pas en DEV !!! Du coup je n'ai aucun Index sur la table où ont lieu les UPDATE !!!

Marc Cousin

Membre

Re : [Version 8.3] INSERT très lent : 1 ligne toutes les 3,2 secondes !

Ça va marcher beaucoup moins bien

---

Marc.

gom

Membre

Re : [Version 8.3] INSERT très lent : 1 ligne toutes les 3,2 secondes !

Bon, j'ai fait un ANALYSE et le REINDEX est en cours sur cette table. Une fois fini je relance mon Job BODI ... et je rentre chez moi !!!

Dois-je toucher au "shared_buffers" ?

gom

Membre

Re : [Version 8.3] INSERT très lent : 1 ligne toutes les 3,2 secondes !

Petite précision ... les UPDATE se font désormais en 0 ms selon le Log PostgreSQL ... 900 lignes par seconde selon BODI ! Ouf je suis sauvé !

Marc Cousin

Membre

Re : [Version 8.3] INSERT très lent : 1 ligne toutes les 3,2 secondes !

Sous Windows, pas la peine d'avoir plus de 64 Mo de shared buffers, c'est contre productif.

Ça ne veut pas dire que Postgres n'aura que 64Mo de cache. Il utilisera celui du système d'exploitation en même temps.

---

Marc.

gom

Membre

Re : [Version 8.3] INSERT très lent : 1 ligne toutes les 3,2 secondes !

Bonjour,

Je mets la même valeur qu'en PROD : 4096 (32 Mo si je ne dis pas de bêtises) ou 8192, le maximum que vous me conseillez ? Si 8192, alors autant mettre également cette valeur en PROD, non ?

Petites questions sur ce paramètre :

Est-ce qu'il faut mettre une valeur en fonction de la quantité de RAM ?

Est-ce qu'il existe une quantité de RAM à partir de laquelle, on peut mettre systématiquement 64 Mo ?

Merci en tout cas pour vos précieux conseils et le temps que vous donnez à la communauté.

Gôm

Marc Cousin

Membre

Re : [Version 8.3] INSERT très lent : 1 ligne toutes les 3,2 secondes !

Mettez comme en prod, 4096.

Sur ce paramètre :

En théorie il faut mettre en fonction de la RAM (c'est ce qui est recommandé sous Unix, entre 1/4 et 1/3 de la RAM). Mais sous Windows, on a des problèmes de performance si le shared_buffers dépasse 64 Mo.

Donc sous Windows, mettez systématiquement 32 ou 64 Mo (vous ne verrez pas de différence entre les 2 je pense). J'imagine mal un serveur sous Windows 2003 avec moins de 3x64 Mo de RAM

---

Marc.

gom

Membre

Re : [Version 8.3] INSERT très lent : 1 ligne toutes les 3,2 secondes !

OK, va pour 32 Mo.

Merci pour les explications.

Gôm

PS : Y avait aucune autre valeur suspecte dans les paramètres ?