Forums PostgreSQL.fr

David

Membre

Hang serveur pendant DELETE+COPY

Bonjour.
Je suis nouvel utilisateur PG ce qui fait que je maitrise pas les aspects noyaux de Postgres.
J'espère donc que vous pourrez m'aider.

Je suis confronté à un problème durant le chargement d'une table de données d'environ 200 millions de lignes.
J'ai lancé la semaine dernière un ordre DELETE+COPY sur cette table pour environ 1 million de lignes. Cette opération a été assez longue ce qui a pénalisé la suite des ordres de chargement (batchs en exécution séquentielle obligatoire).
Suite à ces problèmes de performances, j'ai donc pris l'option de recréer cette table avec des partitions (une centaine).
Les performances ont été très significatives.

Cette semaine, durant le chargement d'un fichier plus volumineux (10 millions de lignes à effacer puis à insérer), l'opération a conduit à un hang du serveur. Pour debug, j'ai réitéré l'opération avec le même fichier et il est apparu que la taille mémoire utilisée par le process postmaster croit jusqu'à monopoliser la taille max du serveur (16Go) ce qui conduit à une indisponibilité !

Je précise que le fichier de données est tout d'abord inséré dans une table temporaire pour traitement partiel (COPY dans TEMPORARY TABLE), puis suivi d'un delete dans la table cible en fonction de clés de cette table temporaire, et ensuite un insert as select ... pour transvaser les données.

Existe-t-il un moyen de limiter la consommation mémoire ? Au niveau transaction puis au niveau global du postmaster (notamment pour éviter le hang serveur) ?

Ma config système:
RHEL 2.6.9
Postgres 8.3.3
RAM 16Go

Quelques paramètres PG:

bd=> show shared_buffers ;
shared_buffers
----------------
1GB
(1 row)

bd=> show maintenance_work_mem ;
maintenance_work_mem
----------------------
1GB
(1 row)

bd=> show temp_buffers ;
temp_buffers
--------------
1024
(1 row)

Marc Cousin

Membre

Re : Hang serveur pendant DELETE+COPY

Le seul paramètre que vous n'avez pas fourni est le 'work_mem'.
Toutefois, à part bug (mais peu probable, nous serions nombreux à l'avoir vu), il est peu probable qu'un processus Postgres se mette à consommer 16 Go de mémoire tout seul (sauf cas très particuliers)

- Que vaut work_mem
- Quelle requête était en cours quand le problème s'est produit ?
- Auriez vous des triggers ou des contraintes d'intégrité sur la table sur laquelle vous faites le delete ? Avec des cascade ?
- Comment avez vous obtenu ce chiffre de 16 Go ? Quelle commande, quel résultat ?

---

Marc.

David

Membre

Re : Hang serveur pendant DELETE+COPY

Bonjour Marc.
Pour être plus précis, en fait l'admin système m'a indiqué qu'un process postmaster a occupé la quasi totalité de la mémoire du serveur juste avant le blocage (environ 10 Go et non 16 comme j'ai pu noter).

Pour valider le fait que cette commande a pu conduire au crash du serveur, j'ai recommencé l'opération mais en scrutant régulièrement la mémoire occupée par le postmaster.
Après 5 minutes de traitement, j'ai arrêté le chargement car la consommation mémoire était bien en train d'augmenter et de façon linéaire. Ci-dessous, la commande top (4.8Go occupée avant mon CTRL+C).

top - 14:19:10 up 5 days,  4:10, 12 users,  load average: 1.47, 1.75, 1.60
Tasks: 348 total,   1 running, 347 sleeping,   0 stopped,   0 zombie
Cpu(s):  1.5% us,  0.8% sy,  0.0% ni, 73.8% id, 23.7% wa,  0.0% hi,  0.2% si
Mem:  16413588k total, 16391264k used,    22324k free,   105404k buffers
Swap:  8388472k total,      428k used,  8388044k free, 11368332k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
29965 postgres  15   0 5304m 4.8g 971m D    6 30.8   3:05.00 postmaster
17776 bd          15   0 37488 2012 1216 S    2  0.0   1:26.45 sshd
17777 bd          15   0 82348  16m  11m S    1  0.1   0:32.80 konsole
17850 root       16   0  6416 1224  752 S    1  0.0   1:16.84 top

[postgres log]$ ps -ef |grep 29965
postgres 29965  6214  2 13:50 ?        00:00:37 postgres: client bd [local] COPY

Pour rappel, cette table de données contient environ 200millions de lignes. Pour les raisons de performances évoquées précédemment, je l'ai donc partitionnée. J'ai créé 100 tables héritées d'une table primaire sur laquelle j'ai posé un trigger pour l'insert des données en fonction des clés de partitionnement. Pour les besoins applicatifs, j'ai créé une PK et 2 index sur chacune des partitions.

Le script utilisé fait :
1 - CREATE TEMPORARY TABLE tmp as select * from BIG_TABLE where 1=2;   => pour récupérer la structure de la table de 200Mlignes
2 - copie ensuite le contenu d'un fichier de données dans cette table temporaire via la commande COPY
3 - efface le contenu de la table de 200Ml avec les lignes contenues dans la table temporaire (DELETE)
4 - insére les lignes de la table temporaire dans BIG_TABLE

Pour finir, la valeur du paramètre work_mem=100MB

Marc Cousin

Membre

Re : Hang serveur pendant DELETE+COPY

Pouvez vous fournir la définition du trigger ? Y a t'il des contraintes d'intégrité ?
Ces choses peuvent remplir une file d'attente à tester en fin de transaction, qui pourrait engendrer ce genre de choses.

---

Marc.

gleu

Administrateur

Re : Hang serveur pendant DELETE+COPY

Je me rappelle avoir lu que les triggers pouvaient demander énormément en mémoire.

Pour la création de la table temporaire, un « CREATE TEMPORARY TABLE tmp (LIKE BIG_TABLE); » me semble préférable.

Le COPY posant problème vient de l'opération 2 ?

---

Guillaume.

David

Membre

Re : Hang serveur pendant DELETE+COPY

Bonjour à tous
Suite des infos.

Il apparait effectivement que la mémoire augmente durant le COPY.
Pour la création de la table temporaire, j'ai noté votre conseil. Quel est l'apport de cette commande ?
Sinon, je vous communique l'ordre de création de la table et des objets associés.

1 - L'ordre de création de la table "mère"
CREATE TABLE mastertab  (
          id_client integer not null,
    "dat" timestamp NOT NULL,
    "datrecu" timestamp NOT NULL,
          puis
          ............
          200 colonnes composées principalement de smallint er real
          ............
);

2 - L'ordre de création des partitions (1 par département administratif)
CREATE TABLE tab_part01 (CHECK (id_client >= 01000000 and id_client <= 01999999)) INHERITS (mastertab);
CREATE TABLE tab_part02 (CHECK (id_client >= 02000000 and id_client <= 02999999)) INHERITS (mastertab);
CREATE TABLE tab_part03 (CHECK (id_client >= 03000000 and id_client <= 03999999)) INHERITS (mastertab);
CREATE TABLE tab_part04 (CHECK (id_client >= 04000000 and id_client <= 04999999)) INHERITS (mastertab);
.............
.............
.............
CREATE TABLE tab_part97 (CHECK (id_client >= 97000000 and id_client <= 97999999)) INHERITS (mastertab);
CREATE TABLE tab_part98 (CHECK (id_client >= 98000000 and id_client <= 98999999)) INHERITS (mastertab);
CREATE TABLE tab_part99 (CHECK (id_client >= 99000000 and id_client <= 400999999)) INHERITS (mastertab);

3 - L'ordre de création de la fonction appelée par le trigger d'insert créé sur la table mère (voir l'ordre de création du trigger au N°4)
CREATE OR REPLACE FUNCTION insert_master_tab()
RETURNS TRIGGER AS $$
BEGIN
    IF    ( NEW.id_client >= 01000000 AND NEW.id_client <=01999999 ) THEN INSERT INTO prod.tab_part01 VALUES (NEW.*);
    ELSIF ( NEW.id_client >= 02000000 AND NEW.id_client <=02999999 ) THEN INSERT INTO prod.tab_part02 VALUES (NEW.*);
    ELSIF ( NEW.id_client >= 03000000 AND NEW.id_client <=03999999 ) THEN INSERT INTO prod.tab_part03 VALUES (NEW.*);
.............
.............
.............
    ELSIF ( NEW.id_client >= 98000000 AND NEW.id_client <=98999999 ) THEN INSERT INTO prod.tab_part98 VALUES (NEW.*);
    ELSIF ( NEW.id_client >= 99000000 )                              THEN INSERT INTO prod.tab_part99 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'Impossible d inserer une donnee dans MASTERTAB';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

4 - L'ordre de création du trigger
CREATE TRIGGER trg_insert_mastertab
BEFORE INSERT ON mastertab
FOR EACH ROW
EXECUTE PROCEDURE insert_master_tab();

5 - Création des PK et index (3 par partitions)
ALTER TABLE  tab_part01 add constraint pk_tab_part01 primary key (id_client,dat);
CREATE INDEX i_tab_part01_dat     on tab_part01(dat);
CREATE INDEX i_tab_part01_datrecu on tab_part01(datrecu);
ALTER TABLE  tab_part02 add constraint pk_tab_part02 primary key (id_client,dat);
CREATE INDEX i_tab_part02_dat     on tab_part02(dat);
CREATE INDEX i_tab_part02_datrecu on tab_part02(datrecu);
.............
.............
.............
ALTER TABLE  tab_part99 add constraint pk_tab_part99 primary key (id_client,dat);
CREATE INDEX i_tab_part99_dat     on tab_part99(dat);
CREATE INDEX i_tab_part99_datrecu on tab_part99(datrecu);

Bonne journée, David.

Marc Cousin

Membre

Re : Hang serveur pendant DELETE+COPY

Ça ressemble à ça : les triggers et contraintes (check ici) ont tendance à consommer énormément de RAM si on se retrouve à en avoir beaucoup dans une seule transaction. Il y a d'ailleurs un patch en cours de développement pour résoudre ce problème.

En attendant, à part faire des transactions plus courtes, je ne vois pas quoi vous proposer…

---

Marc.

David

Membre

Re : Hang serveur pendant DELETE+COPY

Bonjour Marc.
Question patch, cela concerne-t-il la dernière version ou la version que j'ai utilisée pour ces tests (8.3.3) ?
Puis-je espérer mieux avec la version 8.4 sur laquelle je vais reconstruire ma base aujourd'hui ?

Marc Cousin

Membre

Re : Hang serveur pendant DELETE+COPY

Non rien à espérer pour le moment. Aucune date annoncée à ma connaissance, et ça ne sera de toutes façons pas quelque chose qui sera rajouté sur une version stable.

---

Marc.

David

Membre

Re : Hang serveur pendant DELETE+COPY

Ok bien reçu. On va donc essayer de tronçonner au plus juste pour économiser de la RAM.
Merci pour vos interventions.
David.