Forums PostgreSQL.fr

edlm

Membre

Re : ptimisation de requête update

Mais la requête ci-dessus fait un simple update, qui plus est sur 0 ligne. C'est pourquoi je ne comprends pas ces écritures dans le journal de transaction.
Sauf si il y a une activité par ailleurs sur la base en parallèle...

---

Éric

f.ravel

Membre

Re : ptimisation de requête update

Pour poursuivre l'optimisation du serveur que j'utilise, je me pose également une autre question :

La requête "type" sur laquelle nous avons réalisé des tests a démontré une nette amélioration des temps de calcul.

Les tâches que je réalise en ce moment m'amènent à lancer des enchaînements de ce même genre de requêtes. (vous me direz si c'est une bonne ou une mauvaise idée de procéder de la sorte ? Je ne suis pas administrateur de données de formation :-) )

Requête 1
;
Requête 2
;
Requête ...

Là aussi je me demande si, au bout de plusieurs heures calcul, il n'y a pas des opérations de maintenance réalisées en automatique par le serveur (des vacuum ou je ne sais quoi d'autre) qui viennent interférer dans la réalisation de la requête ?
L'idée étant, dans mon cas précis ou je suis le seul accédant, et ayant de nombreuses tâches de mises à jour à réaliser en ce moment, de désactiver temporairement celles-ci et me laisser les lancer à la fin de chaque traitement chaîné ?

Concernant les 0 lignes écrites, voici le dernier test réalisé de ma requête (suppression des champs, recréation de ceux-ci pour partir sur des champs vides). J'ai vérifié les champs mis à jour et ils y a bien de la donnée écrite. Le query plan dit le contraire ?

QUERY PLAN
Update on edi_parc_uf  (cost=835946.26..1332168.33 rows=977854 width=1517) (actual time=1095770.487..1095770.487 rows=0 loops=1)
  Buffers: shared hit=32783420 read=746508 dirtied=917352, temp read=157351 written=157337
  CTE ident_surf_bati_uf
    ->  HashAggregate  (cost=339152.96..348931.50 rows=977854 width=76) (actual time=82749.792..84263.735 rows=2064209 loops=1)
          Buffers: shared hit=63567 read=266475
          ->  Seq Scan on edi_parc  (cost=0.00..298628.54 rows=3241954 width=76) (actual time=9.489..71975.019 rows=3241824 loops=1)
                Buffers: shared read=266209
  ->  Hash Join  (cost=487014.76..983236.83 rows=977854 width=1517) (actual time=117747.933..187374.810 rows=2064208 loops=1)
        Hash Cond: (ident_surf_bati_uf.id_uf = edi_parc_uf.id_centr)
        Buffers: shared hit=164512 read=400398, temp read=157351 written=157337
        ->  CTE Scan on ident_surf_bati_uf  (cost=0.00..19557.08 rows=977854 width=576) (actual time=82749.807..85493.955 rows=2064209 loops=1)
              Buffers: shared hit=63567 read=266475
        ->  Hash  (cost=154669.56..154669.56 rows=2065056 width=1185) (actual time=34997.763..34997.763 rows=2065094 loops=1)
              Buckets: 65536  Batches: 8  Memory Usage: 131834kB
              Buffers: shared hit=36628 read=133923, temp written=112380
              ->  Seq Scan on edi_parc_uf  (cost=0.00..154669.56 rows=2065056 width=1185) (actual time=12.052..30452.669 rows=2065094 loops=1)
                    Buffers: shared hit=96 read=133923
Total runtime: 1096110.130 ms

Dernière modification par f.ravel (11/02/2016 10:35:50)

ruizsebastien

Membre

Re : ptimisation de requête update

Bonjour,

Vous pouvez régler l'autovacuum et l'autoanalyze pour chacune des tables qui sont en jeu dans vos requêtes. Cela permettra d'éviter que ces 2 process automatiques se déclenchent trop souvent (avec les I/O qui vont avec).

Vous devez faire comme ceci pour chaque table :
ALTER TABLE edi_parc_uf SET (
  autovacuum_enabled = true,
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_analyze_scale_factor = 0.01
);

les scale_factor sont à calculer selon la volumétrie des tables et le taux de modification (insert, update, delete).
Par exemple 0.01 = déclenchement après 1% de modification dans la table.
0.1 = déclenchement après 10% de modification dans la table.
Donc si vous avez une table de 10 millions de lignes et un scale_factor de 0.1, l'autovacuum se déclenchera après que 1 millions de lignes aient été modifiées.
A vous de jouer avec ces options pour trouver la bonne limite.

Vous devez jouer aussi avec autovacuum_vacuum_threshold et autovacuum_analyze_threshold qui sont d'autres seuils de déclenchement mais cette fois au nombre de lignes (50 lignes modifiées par défaut).

Vous pouvez modifier tout ça avec pgadmin ou avec un alter table.

Doc à consulter pour plus de détails :
http://docs.postgresql.fr/9.4/runtime-c … acuum.html

Pour voir si l'autovacuum se déclenche trop souvent vous pouvez voir avec cette requête :
SELECT relname, n_tup_upd, n_live_tup, last_autoanalyze, last_autovacuum, autovacuum_count, autoanalyze_count FROM pg_stat_all_tables
WHERE schemaname = 'votre_shema'
ORDER BY relname;

Cordialement.

Dernière modification par ruizsebastien (11/02/2016 11:13:03)

---

Cordialement,

Sébastien.

f.ravel

Membre

Re : ptimisation de requête update

Merci pour vos conseils, je vais m'y plonger demain, aujourd'hui je n'ai ou y travailler.

gleu

Administrateur

Re : ptimisation de requête update

L'idée étant, dans mon cas précis ou je suis le seul accédant, et ayant de nombreuses tâches de mises à jour à réaliser en ce moment, de désactiver temporairement celles-ci et me laisser les lancer à la fin de chaque traitement chaîné ?

Ça peut être une bonne stratégie.

Concernant les 0 lignes écrites, voici le dernier test réalisé de ma requête (suppression des champs, recréation de ceux-ci pour partir sur des champs vides). J'ai vérifié les champs mis à jour et ils y a bien de la donnée écrite. Le query plan dit le contraire ?

Non, le plan affiché par EXPLAIN montre qu'il y a des écritures. Par contre, aucune ligne n'est modifiée, ce n'est pas la même chose.

Et pour infos, l'autovacuum se déclenche généralement pas assez souvent (et non pas le contraire comme aimerait le croire beaucoup).

---

Guillaume.

f.ravel

Membre

Re : ptimisation de requête update

Bonjour,

J'ai donc mené de nombreux tests pour optimiser et essayer de mieux comprendre le fonctionnement de postgrès à l'aide de tous vos conseils.

Requête utilisée pour les tests :

UPDATE cad_2015.edi_parc_uf
  SET age_prop_dest_av_imp_2015 = 2015 - dat_nais_prop_dest_av_imp
WHERE dat_nais_prop_dest_av_imp IS NOT NULL
  AND dat_nais_prop_dest_av_imp <> 0
;
UPDATE cad_2015.edi_parc_uf
SET type_prop = 'NR'
;
UPDATE cad_2015.edi_parc_uf
  SET type_prop = edi_parc.type_prop
FROM 
  cad_2015.edi_parc
WHERE 
  edi_parc.id_uf = edi_parc_uf.id_centr
;
UPDATE cad_2015.edi_parc_uf
SET nom_prop_desti_av_imp = 'NR'
;
UPDATE cad_2015.edi_parc_uf
  SET nom_prop_desti_av_imp = edi_parc.nom_prop_desti_av_imp
FROM 
  cad_2015.edi_parc
WHERE 
  edi_parc.id_uf = edi_parc_uf.id_centr
;

1) Tout manuel
J'ai donc commencé par refuser les autovacuum sur la table à mette à jour

ALTER TABLE cad_2015.edi_parc_uf SET (
  autovacuum_enabled = FALSE;

J'ai ensuite exécuté mes différentes requêtes unes à unes en les sélectionnant dans pgAdmin. Après chaque exécution j'ai lancé un vacuum analyze. Les résultats sont très bons et chaque requête prend de 10 à 35 minutes pour se réaliser, en 1h30 tout est fait. (personnellement ça me convient très bien en termes de performances, mais pas à l'usage, de nombreuses requêtes de ce type à lancer).

2) Tout automatisé - test 1
Même configuration que précédemment, sauf que j'ai lancé toutes les requêtes d'un coup. Là c'est a nouveau sans fin... j'ai fini par stopper la requête au bout de plusieurs heures. Les performances de postgrès s'effondrent. Aucun vacuum pour autant de requêtes enchaînées sur cette même table n'est vraiment pas une bonne idée.

relname	    n_tup_upd	n_live_tup	   last_autoanalyze	last_autovacuum	autovacuum_count	autoanalyze_count
edi_parc_uf   7035669	0			                                            0	                        0

3) Tout automatisé - test 2 (vacuums peu fréquents)
Cette fois-ci test de paramétrage des vacuums de la table :

ALTER TABLE cad_2015.edi_parc_uf SET (
  autovacuum_enabled = TRUE,
  autovacuum_vacuum_scale_factor = 1,
  autovacuum_analyze_scale_factor = 1,
  autovacuum_vacuum_threshold = 3000000,
  autovacuum_analyze_threshold = 3000000
);

Résultats identiques au point 2), mauvaise idée également.
J'en ai relancé un autre avec des valeurs un peu moins élevées mais sans plus de succès. Requête stoppée.
Pour info :

relname	    n_tup_upd	n_live_tup  	last_autoanalyze	                last_autovacuum	                    autovacuum_count       autoanalyze_count
edi_parc_uf   11092034      3002889	      2016-02-12 11:56:03.3+01         2016-02-12 11:47:51.423+01                2                    1

4) Tout automatisé - test 3 (vacuums fréquents)

ALTER TABLE cad_2015.edi_parc_uf SET (
  autovacuum_enabled = TRUE,
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 20000,
  autovacuum_analyze_threshold = 20000
);

Résultats identiques au point 2), la requête tourne depuis plus de 3 heures...

J'ai comme l'impression que cet enchaînement de requêtes, tant qu'il ne s'est pas intégralement réalisé :
  - verrouille la tables,
  - que les autovacuum ne peuvent s'effectuer qu'après libération de ces vérous
  - et que, donc, on se retrouve dans la même situation que pour le 2) ??? 

Dans l'absolu j'aurais pu insérer des vacuums dans mon fichier de requêtes pour qu'elles se réalisent comment dans mon cas 1) mais les vacuum ne sont pas tolérés dans cette configuration... à moins que je ne m'y prenne pas correctement.

Comment faites-vous pour mettre à jour de grosses bases de données, sur de nombreux champs ? Comme moi avec mes requêtes enchaînées... ou bien totalement différemment ?

ruizsebastien

Membre

Re : ptimisation de requête update

Bonjour,

Il faudrait essayer avec des commits entre chaque update :

UPDATE cad_2015.edi_parc_uf
  SET age_prop_dest_av_imp_2015 = 2015 - dat_nais_prop_dest_av_imp
WHERE dat_nais_prop_dest_av_imp IS NOT NULL
  AND dat_nais_prop_dest_av_imp <> 0
;
commit;
UPDATE cad_2015.edi_parc_uf
SET type_prop = 'NR'
;
commit;
UPDATE cad_2015.edi_parc_uf
  SET type_prop = edi_parc.type_prop
FROM 
  cad_2015.edi_parc
WHERE 
  edi_parc.id_uf = edi_parc_uf.id_centr
;
UPDATE cad_2015.edi_parc_uf
SET nom_prop_desti_av_imp = 'NR'
;
commit;
UPDATE cad_2015.edi_parc_uf
  SET nom_prop_desti_av_imp = edi_parc.nom_prop_desti_av_imp
FROM 
  cad_2015.edi_parc
WHERE 
  edi_parc.id_uf = edi_parc_uf.id_centr
;
commit;

Parce que sinon, le commit se fait à la fin de tous les updates.
Alors que dans le cas de pgadmin le commit est implicite (enfin il me semble...)

---

Cordialement,

Sébastien.

gleu

Administrateur

Re : ptimisation de requête update

On va reformuler un peu ce que dit Sébastien

Si le script est exécuté par pgAdmin, il y a un BEGIN automatique au début et un COMMIT final. Du coup, l'autovacuum n'a aucune statistiques sur ce qui se passe sur cette table.

Si vous placez des COMMIT (et des BEGIN, ce que Sébastien a oublié) entre chaque UPDATE, les statistiques ne seront pas plus envoyées vu que pgAdmin envoie ça comme une seule et même chaîne. Or l'envoi des statistiques ne se fait, à ma connaissance, qu'à la fin de l'exécution de la chaîne.

Bref, votre problème, c'est pgAdmin. Utilisez psql.

---

Guillaume.

ruizsebastien

Membre

Re : ptimisation de requête update

oui merci guillaume pour les précisions.
J'avais oublié de dire que les commandes que j'ai citées plus haut sont à exécuter avec psql (tel quel).

Dernière modification par ruizsebastien (16/02/2016 18:44:26)

---

Cordialement,

Sébastien.

f.ravel

Membre

Re : ptimisation de requête update

Bonjour,

J'ai donc essayé d'appliquer vos différents conseils et réalisé de nombreux test... ce qui explique mon temps de réponse.
En effet, les BEGIN et COMIT sont sans effet dans pgAdmin.
Concernant psql je ne maîtrise vraiment pas beaucoup (désolé...) donc ça m'a pris un peu de temps pour arriver quelque peu à mes fins.

Premier test avec le Shell de psql et un fichier .sql contenant les commandes :
- Je réussi à me connecter à la base cadastre_test
- Je tape cette commande => \i d:\rqte_psql.sql -u postgres -w
- J'ai le message d'erreur suivant => d: : permission denied
- J'ai laissé tombé

Second test avec psql dans une invite de commande et le même fichier .sql de commandes :
- Je tape cette commande => psql -d cadastre_test -p 5432 -h localhost -U postgres -w -f d:\rqte_psql.sql
- La commande se lance mais un premier problème apparaît => erreur de syntaxe sur ou près de « I »eUPDATE » (ça sent les problèmes d'encodage ?) .... ATTENTION: aucune transaction en cours  ... COMMIT...
- La première ligne de mon fichier sql pose donc systématiquement problème, la 1ère transaction ne se fait pas, le 1er COMMIT arrive, je vois un vacuum s'effectuer sur la table à partir de la fenêtre "état du serveur" de pgAdmin (ce qui est une bonne chose), la seconde requête contenu dans mon .sql se lance par la suite car elle est visible dans l'état du serveur côté pgAdmin... SUPER !!!

Donc :
- Est-ce que je fait bien les choses en lançant mes commandes via mon rqte_psql.sql ? Faut-il faire différemment ?
- Si c'est bien la bonne méthode, pourquoi la première ligne dudit fichier pose systématiquement problème ? J'ai donc essayé de mettre les BEGIN en plus des COMMIT, l'erreur se produit alors avec I »eBEGIN mais au moins les UPDATE qui suivent se réalisent et la requête va jusqu'à son terme :-)

Désolé pour toutes ces questions mais tous ces outils ne sont vraiment pas simple d'usage de prime abord :-)

gleu

Administrateur

Re : ptimisation de requête update

Concernant l'erreur de syntaxe, si vous avez enregistré le script SQL à partir de pgAdmin, alors vous avez un BOM en début de fichier qu'il faut supprimer. Le mieux est d'enregistrer le script à partir d'un éditeur de texte ou des dernières versions de pgAdmin qui permettent de ne pas enregistrer le BOM.

Le message "Aucune transaction en cours" est dû au fait qu'il faut ajouter un BEGIN en début de transaction (ce que j'avais mentionné plus haut).

---

Guillaume.

ruizsebastien

Membre

Re : ptimisation de requête update

Le message "Aucune transaction en cours" est dû au fait qu'il faut ajouter 
un BEGIN en début de transaction (ce que j'avais mentionné plus haut).

le message "Aucune transaction en cours" signifie plutôt que la première commande (update) est ignorée à cause du BOM, du coup la deuxième instruction (commit) est valide et est exécutée mais comme la première instruction a été annulée, il n'y a rien à commiter.
Donc pas besoin de begin, il suffit de supprimer le BOM au début du fichier et ça va marcher (avec psql).

Dernière modification par ruizsebastien (17/02/2016 10:56:04)

---

Cordialement,

Sébastien.

f.ravel

Membre

Re : ptimisation de requête update

Bonjour,

Alors, en effet le "BOM" est la source de la première erreur (« I »eUPDATE »). Et celle-ci venait bien de l'enregistrement via pgAdmin.
Et, parce que je pense que ça ne coûte rien d'être rigoureux dans ses requêtes, j'ai encadré chacune de mes requêtes d'un BEGIN - COMMIT. Depuis, tout tourne sans erreur !!! Super.

Résultat des courses et de tous vos précieux conseils :
- l'optimisation de la configuration de la base de données.
- l'optimisation du comportement des vacuums sur les tables éditées
- le passage en psql et les BEGIN/COMMIT
...ont énormément amélioré mes mises à jours. Une requête chaînée qui pouvait tourner pendant des jours... est traitée en quelques heures !!! Un grand merci à vous !!!

J'abuse peut-être, mais je vais quand même poser une dernière question sur le comportement actuel de ma requête psql (chaîne de mises à jours d'une même table).
D'après ce que j'observe via l'évolution de la fenêtre psql et le moniteur d'activité de pgAdmin :

BEGIN => début de la première transaction
Requête 1 => réalisation de la requête 1
COMMIT => fin de la transaction => initiation d'un vacuum automatique
BEGIN => début de la seconde transaction, alors que le vacuum est en train de se réaliser
Requête 2 => réalisation de la requête 2 en parallèle du vacuum
COMMIT...etc.

J'ai donc l'impression qu'à partir de la requête 2 celle-ci se réalise, au début, frontalement avec le vacuum (les deux sont visibles dans le moniteur d'activité de pgAdmin) et jusqu'à ce que celui-ci se finisse... ce qui fait sérieusement chuter les performance de mise à jour.
Le vacuum est nécessaire car sinon, au fur et à mesure des requêtes qui s'enchaînent, les performance s'effondrent.
Y-a-t-il un moyen de rendre le vacuum "exclusif" sans mettre à mal l'enchaînement des BEGIN suivants ?

gleu

Administrateur

Re : ptimisation de requête update

le message "Aucune transaction en cours" signifie plutôt que la première commande (update) est ignorée à cause du BOM, du coup la deuxième instruction (commit) est valide et est exécutée mais comme la première instruction a été annulée, il n'y a rien à commiter.

Non. Un COMMIT renverra toujours une erreur (un WARNING très exactement) s'il n'y a pas eu de BEGIN avant. Ça fonctionnera avec pgAdmin parce que ce dernier fait un BEGIN avant d'exécuter les requêtes, mais essayez avec psql et avec tout autre outil qui ne cache pas les requêtes qu'il exécute et vous aurez le même problème. La preuve :

postgres=# SELECT 1;
 ?column? 
----------
        1
(1 row)

postgres=# COMMIT;
WARNING:  there is no transaction in progress
COMMIT

Y-a-t-il un moyen de rendre le vacuum "exclusif" sans mettre à mal l'enchaînement des BEGIN suivants ?

Non. Le seul moyen de contourner ça est de désactiver l'autovacuum sur les tables en question et de faire manuellement le VACUUM ANALYZE entre chaque (ensemble d') opérations.

---

Guillaume.

f.ravel

Membre

Re : ptimisation de requête update

Ok :-)

En tous cas merci pour tout, vous m'avez enlevé une belle épine du pied !!! Désormais tout est franchement plus efficace, c'est le jour et la nuit.