Performance en update postgres

Version imprimable

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:

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:

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.

22/02/2018, 12h12
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.

Le script de création de la table :

Code:

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 ?

22/02/2018, 15h24
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 !) :?
22/02/2018, 16h00
SQLpro

Citation:

Envoyé par Darkcristal

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 +
22/02/2018, 16h37
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 +
22/02/2018, 18h57
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 ?
22/02/2018, 20h27
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
  
  Citation:
  
  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
@+
23/02/2018, 10h09
Darkcristal

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

Citation:

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)

Citation:

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 !

Citation:

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

Aucun trigger.

Citation:

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

Citation:

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)

Citation:

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 !

Citation:

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.

Citation:

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.

Citation:

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.
23/02/2018, 11h10
SQLpro

Citation:

Envoyé par Darkcristal

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

Citation:

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 +
23/02/2018, 13h42
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 ?
23/02/2018, 13h45
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 +
23/02/2018, 13h46
SQLpro

Citation:

Envoyé par 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 ?

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

A +
23/02/2018, 15h31
alassanediakite

Salut

Citation:

Envoyé par Darkcristal

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.
@+
01/03/2018, 18h30
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.