Discussion :

[Darkcristal]

Bonjour,

j'ai une question concertant les performances postgres en update sur une table.

J'ai une table contenant environ 22 millions de ligne, 21 colonnes et 5 index. Je fait un VACUUM ANALYZE sur la table et des REINDEX des 5 index. Toutes les stats sont à jour.

J'ai une colonne qui est "null" sur la totalité des données, je fais un simple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

update schema.table set champ = '-1';

et il prend 75 minutes ! sans aucune clause where. Comment expliquer cette lenteur ?

Second effet kisskool, une fois l'update réalisé la table est complètement en vrac et ne répond même plus dans des temps raisonnable à un count(*), je suis obligé de refaire un VACUUM ANALYZE qui met 10min ! A partir de là ma table est de nouveau opérationnelle.

Je précise que j'ai un serveur postgres conséquent avec 128Go de ram et 12 cpu. Quelques éléments de configuration :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
work_mem = 128MB
maintenance_work_mem = 4GB
effective_cache_size = 48GB
wal_buffers = 16MB
shared_buffers = 32GB
max_connections = 200
autovacuum = off (nous chargeons les données la nuit puis vacuum analyze et création des index vers 6h matin, aucun update/insert/delete en journée)

Peut être que la conf n'est pas bonne n'étant pas expert en Postgres ? ou que j'ai raté quelque chose ?

Merci pour vos retours.

[ced]

Bonjour,

Sans plus d'info sur la table (script de création par exemple), c'est assez difficile à dire.
Par contre, désactiver l'autovacuum, c'est une très mauvaise idée. Il n'y a pas que les stables que vous avez créées qui ont besoin régulièrement d'un vacuum et d'un analyze, il y a aussi toutes les tables système. Je vous encourage fortement à réactiver l'autovacuum.

[Darkcristal]

Le script de création de la table :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
CREATE TABLE dwh.cpt_affectation (
	affectation_id int4 NOT NULL,
	adhesion_id int4 NULL,
	numero_contrat_id varchar(20) NULL,
	rang_contrat_id int4 NULL,
	numero_maj_id int4 NULL,
	cotisation_id int4 NULL,
	version_cotisation_nu int4 NULL,
	prime_id varchar(20) NULL,
	redacteur_cd varchar(10) NOT NULL,
	mouvement_id varchar(20) NOT NULL,
	personne_id varchar(20) NULL,
	role_cd bpchar(2) NULL,
	affectation_dt date NULL,
	montant_ht_nu numeric(20,5) NULL,
	sens_cd bpchar(1) NULL,
	contentieux_cd bpchar(1) NULL,
	sid_frachr_in int4 NULL,
	sid_publie_in int4 NULL,
	sid_creation_ts timestamp NULL,
	sid_maj_ts timestamp NULL,
	sid_publie_ts timestamp NULL
)
WITH (
	OIDS=FALSE
);
CREATE INDEX xfk_cpt_affectation ON dwh.cpt_affectation (mouvement_id);
CREATE INDEX xfk_cpt_affectation2 ON dwh.cpt_affectation (redacteur_cd);
CREATE INDEX xfk_cpt_affectation3 ON dwh.cpt_affectation (numero_contrat_id,rang_contrat_id,numero_maj_id,cotisation_id,version_cotisation_nu,prime_id);
CREATE INDEX xfk_cpt_affectation4 ON dwh.cpt_affectation (personne_id,role_cd);
CREATE INDEX xfk_cpt_affectation5 ON dwh.cpt_affectation (adhesion_id);
CREATE INDEX xpk_cpt_affectation ON dwh.cpt_affectation (affectation_id);

J'update le champ "prime_id" qui est "null" sur la totalité de la table.

Concernant l'autovacuum est ce qu'on peut le réactiver sauf sur notre schéma ?

[SQLpro]

Ce temps sont normaux et liés à la mauvaise conception de PostGreSQL dont la gestion du MVCC est une plaie...

Pour information voici ce que j'ai écrit à ce sujet dans un audit pour une entreprise :

"
2.5 - Une plaie appelée MVCC

Pour gérer le verrouillage optimiste, il est nécessaire de gérer de multiples versions des lignes des tables. Le choix fait par PostGreSQL de gérer ces multiples lignes au sein même de la page en mémoire, s’il est apparu comme facile à implémenter se relève en fait désastreux à l’exploitation.
Notons qu’Oracle gère cela dans un tablespace dédié à cet usage , donc de manière externe aux données vivantes, tout comme SQL Server qui lui, les gèrent dans la base systèmes tempdb consacrée aux objets temporaires .
En cas de pluralités de transactions simultanées visant les mêmes données, le MVCC duplique les lignes pour chacun des accès avec une marque de version (estampillage). Tout ceci se faisant au sein même de la page originelle, il en résulte généralement un split de page : la page étant pleine, on ajoute une nouvelle page et déporte la moitié des données vers la nouvelle. Lors de la finalisation des transactions seules les données modifiées ayant la même version que celle d’origine gagnent à persister dans leurs modifications, les autres étant perdues .
Le constat est alors simple : les pages de données de la base ne cessent d’enfler et de se fragmenter car un tel nettoyage, s’il était fait de manière synchrone, pèserait d’un poids trop important sur la transaction.
La conclusion est simple : PostGreSQL génère naturellement des pages fortement fragmentés du fait du versionnement des lignes ce qui pourrit le cache, comme les disques, de pages artificiellement « boursoufflées » et tout particulièrement si la base est fortement transactionnelle. Mais il y a un remède… qui s’avère parfois pire !

À noter qu’une des conséquences importantes sur les performances de ce désastreux état des pages est que le comptage d’occurrence dans PostGreSQL est incommensurablement plus lent que dans d’autres SGDBR… À lire :
https://www.periscopedata.com/blog/c...ver-and-oracle

Pour se défaire des lignes obsolètes générées par le MVCC, il existe bien deux commandes de nom VACUUM FULL ou CLUSTER mais ces dernières posent des verrous exclusifs le temps de faire le ménage, ce qui provoque immanquablement blocages et verrous mortels.
On trouve aussi une « extension » de nom pg_repack, resucée d’un autre outil de nom pg_reorg (à quand le prochain ?...) censé remédier à la chose. Mais ses multiples bugs non résolus comme ses limites en font un outil suspect pour ne pas dire inutilisable sur une grosse production.

En pratique il n’est donc pas possible de faire le ménage de ces tables alors que la base est en production avec une charge soutenue. Il faut donc avoir des heures creuses. Certains SI « débranchent » même les serveurs PostGreSQL le temps d’exécuter les tâches de maintenance…
"

En conclusion PG n'est pas taillé ni pour des gros volumes de données ni pour du 24/24 7/7 à pleine charge...

A +

PS : je m'attends à avoir un maximum de points négatifs de la part des intégristes de PostGreSQL.... (comme d'habitude !)

[SQLpro]

Second effet kisskool, une fois l'update réalisé la table est complètement en vrac et ne répond même plus dans des temps raisonnable à un count(*), je suis obligé de refaire un VACUUM ANALYZE qui met 10min ! A partir de là ma table est de nouveau opérationnelle.

Pour information voici comment les autres SGBDR font pour faire un COUNT(*) incommensurablement plus rapide :
Ils lisent 4 octets dans chacune des entêtes de pages de la table. Ces 4 octets indiquant combien de lignes figurent dans la page. Comme il ne stockent pas de lignes autres que celles vivantes, cette valeur est toujours juste. Tandis que PostGreSQL est obligé de parcourir toutes les lignes de la page pour comptez les bonnes lignes et ignorer les mauvaises du fait du MVCC...
Si en plus tu fait un UPDATE, toutes tes pages sont victimes de split donc tu en lit juste deux fois plus... Ton temps devrait être approximativement le double.
Maintenant si tu es sur la version 9.6 ou + tu peut activer le parallélisme pour tenter de réduire un temps soit peu ce temps de traitement...

A +

[SQLpro]

Une dernière petite question : avez-vous configurer correctement le serveur ?

Notamment :
effective_cache_size = 2/3 de la RAM
shared_buffers = 1/3 de la RAM
Voir aussi : work_mem...

https://wiki.postgresql.org/wiki/Per...e_Optimization

A +

[ced]

La table en question a-t-elle des triggers ? Ça peut expliquer pourquoi la mise à jour est longue.
Avez-vous essayé d'enlever les index pour les remettre ensuite ?
Ou encore : créer une nouvelle table à partir de la première, puis remplacer l'ancienne par la nouvelle en supprimant l'ancienne et en renommant la nouvelle comme l'ancienne...

Que donne un EXPLAIN de l'UPDATE par rapport à un EXPLAIN d'un SELECT sur la même table ?

[alassanediakite]

Salut
Proposition...

• si pas de insert, update, delete dans la journée alors il faut mettre la table en UNLOGGED
• vue la taille des données il serait mieux de bien aligner les colonnes:int4..., timestamp..., bpchar..., numeric..., varchar

Questions...

• la mise à jour concerne-t-il les données nouvellement chargées? si oui, faire la mise à jour en même temps que (ou avant) le chargement. cela évitera la mise à jour de l'index

  dwh.cpt_affectation (numero_contrat_id,rang_contrat_id,numero_maj_id,cotisation_id,version_cotisation_nu,prime_id)

• les champs xxx_id contiennent ils réellement des données de type entier?, si oui il faut alors changer le type en INT

@+

[Darkcristal]

Merci pour tous vos retours. Je vais répondre à toutes les questions

Une dernière petite question : avez-vous configurer correctement le serveur ?

Oui le serveur a été configuré. j'ai fait monter le effective_cache_size à 96GB (pour un serveur a 128Go).
shared_buffers = 32GB
work_mem = 128MB (je pense l'augmenter un petit peu et baisser le max_connections qui est actuellement à 200)

Maintenant si tu es sur la version 9.6 ou + tu peut activer le parallélisme pour tenter de réduire un temps soit peu ce temps de traitement...

Nous avons fait intervenir un dba postgres qui après avoir fait quelques tests avec le parallélisme nous a dit qu'il n'y aurait aucun gain de performance !

La table en question a-t-elle des triggers ?

Aucun trigger.

Avez-vous essayé d'enlever les index pour les remettre ensuite ?

L'ensemble des index sont supprimés avant chargement et reposés à la fin

Que donne un EXPLAIN de l'UPDATE par rapport à un EXPLAIN d'un SELECT sur la même table ?

Il fait un seq scan dans les 2 cas : Seq Scan on cpt_affectation (cost=0.00..1060727.84 rows=22883484 width=185)

si pas de insert, update, delete dans la journée alors il faut mettre la table en UNLOGGED

Toutes nos tables sont en UNLOGGED mais ça ne change pas beaucoup les performances !

les champs xxx_id contiennent ils réellement des données de type entier?, si oui il faut alors changer le type en INT

On passe au maximum les id en INT mais par exemple le champ "numero_contrat_id varchar(20) NULL" est un identifiant mais qui contient des caractères.

vue la taille des données il serait mieux de bien aligner les colonnes:int4..., timestamp..., bpchar..., numeric..., varchar

Je vais regarder ce point et voir si ca améliore un peu.

la mise à jour concerne-t-il les données nouvellement chargées? si oui, faire la mise à jour en même temps que (ou avant) le chargement. cela évitera la mise à jour de l'index

Je ne comprends pas ce point ?



Concernant les index et les vacuum analyze, est ce qu'il existe des précos pour savoir quand faire un REINDEX, quand faire un vacuum etc. ? Par exemple en utilisant n_dead_tup et n_live_tup je peux calculer un ratio de fragmentation sur une table mais je ne sais pas a combien fixer cette valeur ? pareil pour un index.


Merci pour votre aide.

[SQLpro]

Nous avons fait intervenir un dba postgres qui après avoir fait quelques tests avec le parallélisme nous a dit qu'il n'y aurait aucun gain de performance !

Faites des tests en vrai grandeur...

Concernant les index et les vacuum analyze, est ce qu'il existe des précos pour savoir quand faire un REINDEX, quand faire un vacuum etc. ? Par exemple en utilisant n_dead_tup et n_live_tup je peux calculer un ratio de fragmentation sur une table mais je ne sais pas a combien fixer cette valeur ? pareil pour un index..

Pour estimer la ratio de fragmentation :
https://www.openscg.com/2016/11/post...oat-estimates/
Si > 30 indispensable de réorganiser.
Si < 10, inutile.

A +

[Darkcristal]

Est-ce qu'on peut considérer qu'une table avec un ratio n_dead_tup/n_live_tup > 10% (par exemple) à besoin d'un "vacuum analyze" et que les index associés à cette table ont besoin d'un reindex ?

[SQLpro]

Si vous voulez diminuer les temps d'insertion, vous pouvez aussi jouer sur le journal de transaction :
1) le placer sur le stockage le plus rapide possible (par exemple RAID 10 à base de carte Fusion IO)
2) vous pouvez tenter la journalisation asynchrone (à vos risques et périls.)

A +

[SQLpro]

Est-ce qu'on peut considérer qu'une table avec un ratio n_dead_tup/n_live_tup > 10% (par exemple) à besoin d'un "vacuum analyze" et que les index associés à cette table ont besoin d'un reindex ?

Il faudrait aller voir plus finement chaque index et chaque table....

A +

[alassanediakite]

Salut

Je ne comprends pas ce point ?

D'après vous...
1->chargement des données
2->...
3->update
4->...
Je pense qu'il est mieux de modifier(depuis la source ou pendant le transfert) les données avant qu'elles n'arrivent dans la table.
Par ailleurs pour le vidage complet de la table TRUNCATE est mieux que DELETE.
@+

[Waldar]

Vous avez aussi un index sur la colonne que vous mettez à jour.
Dans ce genre de cas, il faut mieux supprimer l'index, faire votre mise à jour, et reconstruire l'index.

Il est fort possible que PostgreSQL fasse une update dans la table, un update dans l'index, un update dans la table, un update dans l'index, et cetera.

Sinon je suis nettement plus partisan de la solution de mettre la bonne valeur directement lors de l'alimentation de la table, ou de poser une vue sur votre table afin de changer votre null en ce que vous voulez sans avoir à modifier vos données.