Forums PostgreSQL.fr

e.papet

Membre

épuration pg_largeobject

Bonjour à tous,

Je souhaite une bonne et heureuses année 2018 à toute l'équipe de Postgresql.fr.

Je suis architecte logiciel et je travaille avec postgresql depuis plus de 10 ans.
Je dois trouver une solution pour récupérer de l'espace disque sur une instance postgresql en version 9.1 (qui n'est plus maintenue).
Le contexte :
- base qui a plus de 6 ans d'exploitation
- application critique pas d’interruption possible
- volume de la base 1,2 To
- table pg_largeobject 993 Go (estimation après épuration des orphelins 150 Go)
- architecture en réplication  Warm stand By

J'ai hérité du problème.
Le problème de la volumétrie est lié au fait que les images, les documents sont des champs de type LOB (je déconseille cette solution).
Les delete et update ne suppriment pas les images et les documents de la table pg_largeogbject (pas de unlink ) générant des orphelins (depuis plus de 6 ans), la volumétrie de base ne cesse de grossir.

J'ai effectué un vaccumlo sur la table pg_lrageobject ( plus de 20 heures)
J'ai effectué un test sur un autre serveur après copie physique et synchronisation de la base (rien que ça plus de 20 heures) d'un vacuumlo et un full vacuum ( plus de 40 heures), la base est passé de 1,3 Go à 300 Go.

Le  full vacuum c'est l locke la table pg_largeobject et cela n'est pas possible sur l'instance de production.
J'utilise de pg_repack pour effectuer les réorganisations avec un minimum de lock.
Mais impossible de lancé pg_repack sur la table pg_largeobject car elle ne possède pas de primary key (étonnant quand même)

Je suis à cours de solution, c'est pour cela que viens demander conseille auprès de la communauté.

Je vous remercie par avance.

Cordialement
Eric Papet

gleu

Administrateur

Re : épuration pg_largeobject

Vous êtes à cours de solution parce que vous avez fait le tour de ce qui était possible. Le seul moyen est le VACUUM FULL de la table pg_largeobject malgré le temps d'immobilisation que cela vous imposera. Et après, assurez-vous que, quand une ligne est supprimée de la table utilisateur qui référence pg_largeobject, cela supprime aussi le large object. Vous pouvez utiliser un trigger pour ça si cela vous simplifie les choses.

Mais en attendant, votre seul moyen pour nettoyer la base actuelle, c'est un VACUUM FULL.

---

Guillaume.

dverite

Membre

Re : épuration pg_largeobject

d'un vacuumlo et un full vacuum ( plus de 40 heures), la base est passé de 1,3 Go à 300 Go

Je suppose que vous vouliez dire de 1,3 To à 300 Go.

Quoiqu'il en soit effectivement les options pour dégonfler pg_largeobject sont limitées.
Par exemple il y a eu cette question  sur  la liste -general en anglais, sans alternative trouvée au VACUUM FULL.

Admettons que vacuumlo ait fait son travail, c.a.d appeler lo_unlink() avec tous les OIDs non référencés.
Pour se faire une idée, quel est après ça l'état des lieux en termes de :

- combien d'objets restent:  SELECT count(*) FROM pg_largeobject_metadata

- quelle taille utile ils font:  SELECT sum(octet_length(data)) FROM pg_largeobject

- quelle taille disque fait la table: SELECT pg_relation_size('pg_largeobject');

- quelle taille pour la table+son index: SELECT pg_total_relation_size('pg_largeobject')

Sinon pg_largeobject possède bien une clef primaire sur la valeur composite (loid, pageno), mais peut-être que ça ne convient
pas à pg_repack?

\d+ pg_largeobject
                  Table "pg_catalog.pg_largeobject"
 Column |  Type   | Modifiers | Storage  | Stats target | Description 
--------+---------+-----------+----------+--------------+-------------
 loid   | oid     | not null  | plain    |              | 
 pageno | integer | not null  | plain    |              | 
 data   | bytea   | not null  | extended |              | 
Indexes:
    "pg_largeobject_loid_pn_index" UNIQUE, btree (loid, pageno)

---

@DanielVerite
http://blog-postgresql.verite.pro/

e.papet

Membre

Re : épuration pg_largeobject

Bonjour Messieurs,

Merci pour ces réponses.
Oui c'est bien 1,3 To et 300 Go.

Concernant pg_largeobject il existe un "primary index" sur 2 champs "loid et pageno" mais cela n'est pas vue comme un primary key pour pg_repack.
J'ai posté un message sur le forum de pg_repack sans réponse pour l’instant, mais je vais orienter le prochain  post sur l'utilisation de pg_repack sur des tables possédant un unique index et pas une primary key.
Je vous tiendrai au courant des réponses de pg_repack à travers se post.

Je vous remercie encore pour ces précieuses information

@EricPapet

Indaa

Membre

Re : épuration pg_largeobject

Bonjour ,

e.papet avez vous des nouvelles concernant votre problème ?

Cela m’intéresse énormément.

Merci

e.papet

Membre

Re : épuration pg_largeobject

Bonjour Indaa,

Toujours pas de réponse de la part de pg_repack.
Par contre sur une copie de ma base, j'ai effectué un vaccumlo sur pg_latgeobject (24 Heures environ) sans lock et un vaccumdb sur pg_largeobject (26 heures environ) je suis passer de 1,2 To à 300 Go.

@
Eric Papet