Discussion :

[titi_38]

Bonjour,

J'ai écrit un bout de logiciel pour insérer des lignes dans une table (j'utilise la commande "copy from stdin"). La table est définie comme ci dessous:

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
CREATE TABLE dataflow
(
    num_id_cycle 	int4,
    ipproto 		int2,
    ipLocal 		inet NOT NULL,
    direction 		char,
    ipExtern  		inet NOT NULL,
    portLocal 		int4,
    portExtern	 	int4,
    tcpFlags 		smallint,
    incTraffic 		int8,
    outTraffic 		int8,
    incPkts 		int8,
    outPkts 	  	int8,
    firstTimestamp 	TIMESTAMP WITHOUT TIME ZONE,
    lastTimestamp 	TIMESTAMP WITHOUT TIME ZONE,
    ipoutsideCountryId 	int2,
 
    PRIMARY KEY (num_id_cycle,ipLocal,ipExtern,ipproto,portLocal,portExtern),
    constraint fk_num_cycle FOREIGN KEY (num_id_cycle) REFERENCES collect_cycle (num_id_cycle)
);

Mon programme insère plusieurs milliers de ligne chaque 1/4 d'heure; et toutes les 24 heures, il fait le ménage:

Code :

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

1
2
3
4
DELETE FROM dataflow WHERE num_id_cycle < xy; 
-- where xy is the num_id_cycle of 24hours old data;
 
VACUUM ANALYZE dataflow

Ainsi, il y a toujours moins de 24heures de données dans la base de données.
Cependant, le disque se remplit, et régulièrement, au bout de 2 ou 3 mois, le serveur postgresql se plante car le disque est plein !

Pourquoi est-ce que le taille sur le disque augmente (alors que le nombre d'entrée dans la table n'augmente pas) ?

Merci d'avance pour votre aide et vos conseils. Amicalement
Thierry

[juvamine]

n'y a t il pas un journal de transactions (comme dans sqlserver) qui gonfle régulièrement ?

[estofilo]

Le journal de transactions est recyclé automatiquement sauf si archive_mode est à on.
Il est possible que ce soit l'index ou les index qui grossissent.
Tu peux faire un REINDEX TABLE dataflow après le VACUUM pour éviter ça.

Sinon suivre l'évolution des tailles avec les fonctions pg_database_size(), pg_relation_size() et pg_total_relation_size()

[titi_38]

Salut,
J'ai suivi vos conseils, merci... mais j'ai un peu de mal à interpréter le résultat !
Il efface 49619262 raws... ce qui fait augmenter le pg_database_size, pg_relation_size, et pg_total_relation_size !!! Il y a un bug, ou quelqu'un peut m'expliquer ?! Merci.
Amicalement
T.

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
db=> DELETE FROM dataflow WHERE num_id_cycle < (SELECT MIN(num_id_cycle) FROM collect_cycle WHERE date_trunc('hour',date) = date_trunc('hour','now'::timestamp - interval '1 days'));
select pg_database_size(16385), pg_relation_size(16394), pg_total_relation_size(16394);
DELETE 49619262
db=> select pg_database_size(16385), pg_relation_size(16394), pg_total_relation_size(16394);
 pg_database_size | pg_relation_size | pg_total_relation_size 
------------------+------------------+------------------------
      22989305616 |      14742257664 |            22485639168
(1 row)
 
db=> VACUUM ANALYZE dataflow;
VACUUM
db=> select pg_database_size(16385), pg_relation_size(16394), pg_total_relation_size(16394);
 pg_database_size | pg_relation_size | pg_total_relation_size 
------------------+------------------+------------------------
      23433369360 |      15027437568 |            22922969088
(1 row)

[estofilo]

Il faut faire le REINDEX

[titi_38]

Effectivement... (voir ci dessous)
Mais c'est pas terrible. La logique aurait voulu que ça fasse partie du VACUUM !
Et pour moi, c'est un vrai problème: pour faire un REINDEX, la table doit rester verrouillée en lecture/écriture pendant presque 1/2 heure(!!), or je dois insérer des données tous les 1/4 h...

db=> select pg_database_size(16385), pg_relation_size(16394), pg_total_relation_size(16394);
 pg_database_size | pg_relation_size | pg_total_relation_size 
------------------+------------------+------------------------
      24113665808 |      15027437568 |            23482236928
(1 row)

db=> REINDEX TABLE dataflow;
select pg_database_size(16385), pg_relation_size(16394), pg_total_relation_size(16394);

REINDEX
znets=> select pg_database_size(16385), pg_relation_size(16394), pg_total_relation_size(16394);
 pg_database_size | pg_relation_size | pg_total_relation_size 
------------------+------------------+------------------------
      20088559376 |      15027437568 |            19457130496
(1 row)

[estofilo]

Comme la clef primaire porte sur 6 champs, l'index doit être en plus assez coûteux.
Sur un problème de ce genre, personnellement j'évaluerais deux pistes:
1) partitionnement avec suppression des partitions obsolètes. Dans ce cas plus besoin ni de delete ni de vacuum ni de reindex. La difficulté est de trouver la bonne clef de partitionnement mais ton num_id_cycle a l'air d'un bon candidat.
2) si impossible de partitionner, voir s'il est possible de se passer de l'index.

[titi_38]

Merci pour ta réponse.

1) Non, un partitionnement des données n'est plus envisageable à ce stade du projet (et c'est très contraignant...)
2) Comment ? Je supprime la clé primaire de la table ?

... lors de la conception, j'ai pas imaginé une seconde que le VACUUM ne faisait pas complètement le ménage et qu'il allait falloir que je lance des REINDEX sur les index généré d'après la clé publique des tables !!
Si je vire la clé primaire et que je crée quelques index, chacun sur 1 seul champ... le REINDEX sera plus rapide ?

Merci.

[estofilo]

Le ménage complet se fait VACUUM FULL + REINDEX mais c'est une opération très coûteuse dès qu'il y a un volume important. VACUUM seul repère simplement les trous utilisables plus tard mais ne les élimine pas sauf s'ils sont à la fin de la table.

Si la clef primaire n'est pas indispensable, alors autant la supprimer.
Ensuite s'il y a d'autres index nécessaires, voir s'il peuvent être créés avec le type hash au lieu de btree (dans ce cas chaque index doit être mono-colonne). D'après la doc c'est plutôt l'index btree qui est sujet à la dilatation en taille.

[titi_38]

Le concept du VACUUM est très bien et très clair...
Mais le REINDEX c'est du grand n'importe quoi ! Ca sert à quoi de gérer un index de manière transparente, s'il n'est pas mis à jour lors des DELETE ??? Encore une chance qu'il faille pas faire un REINDEX qui bloque la table 1/4h après chaque INSERT...
Enfin bref, je vais essayer de bricoler un truc du coup... Merci pour ton aide

[scheu]

J'ai déjà aussi rencontré ce genre de problème
Le hic sous Postgresql c'est que quand on fait un "create index" ou "reindex" ça locke la table
Pour le partitionnement n'en parlons même pas c'est une usine à gaz avec des contraintes/triggers partout
N'y a-t-il aucun créneau horaire pendant lequel la table n'est pas utilisée pour pouvoir faire cette opération ?
Si non, tu peux éventuellement essayer d'augmenter le paramètre maintenance_work_mem pour accélérer le reindex ou le create index si tu le droppes à chaque fois

[estofilo]

A noter que pour CREATE INDEX, il y a une option CONCURRENTLY qui fait que la table n'est pas verrouillée pendant l'opération.

[vmolines]

D'après la documentation, il est possible de réindexer sans verrouiller les tables : http://www.postgresql.org/docs/9.0/s...X-CONCURRENTLY

Par contre ce n'est pas une option de la commande REINDEX et il faut supprimer et recréer l'index pour bénéficier de l'option.

edit : Cramé de 10mn . Ca m'apprendra à laisser une réponse non validée pendant un quart d'heure .

[scheu]

Je ne connaissais pas l'option CONCURRENTLY, merci à vous deux pour l'info
Ça oblige à faire un drop/create des index et PK mais bon c'est mieux que rien ...

[titi_38]

Salut,
Oui, finalement, j'ai réglé le problème en faisant des drops index et des create index concurrently. C'est plus rapide qu'un reindex, et ça évite de locker la table.
Merci pour votre aide et vos conseils. Amicalement
Thierry