Discussion :

[clavier12AZQSWX]

bonjour,


je gère une base de données d'un ERP (270 tables en tout...) sous postgreSQL.

Le soir la base de données fait environs 1.4Go .
Chaque matin/nuit on procède à un vacum complet et à un réindex complet aussi. Après cette opération la base ne fait que 120 Mo. (soit 10x fois moins).

Dans l'entreprise, les collègues (30 travaillent dessus) disent que le matin l'ERP est rapide et vers la fin de journée il rame.
Je suppose que cela est dû à la base de données qui devient trop grosse par rapport aux ressources du serveur.
Ou bien, cette croissance dans la journée est-elle due à une mauvaise structure de la base (trop d'indexs par ci et là) ou à autre chose ?

La base de l'erp subit bcp de miseàjour (update) et insertion dans la journée et très peu de suppression. Et évidement constament des SELECT.

que puis-je y faire ?

merci de votre conseil.

[Rei Ichido]

Le "clean" n'est pas forcément pertinent, mais par contre on peut s'interroger sur l'ERP qui, si j'ai bien compris, génère 90% d'espace inutile en 1 jour ?

Il faudrait surtout tracer les requêtes pour voir lesquelles deviennent lentes et/ou particulièrement consommatrices, et se pencher sur elles.

=> À noter que outre les insertions / suppressions en base, il se peut aussi que les lenteurs viennent de ce que les utilisateurs ne font tout simplement pas la même chose le matin et le soir.

[sevyc64]

Tout dépend de l'ERP et de sa structuration mais 1.4Go pour une base d'ERP ne me semble pas énorme (j'en connais de plus de 50Go sous SQLServer), par contre 120Mo ressemble plus à une base vierge.

Une base de données à besoin d'espace pour fonctionner. Ce qui compte n'est pas la taille qu'elle peut prendre en un jour, ou en quelques heures, mais son évolution au fil des jours et de son utilisation.
Par exemple, ta base peut prendre 1.4Go le premier jour, puis que quelques 10ènes de Mo les jours suivant.

Il vaut mieux que tu essaye de regarder quelle activité, quelles requêtes prennent du temps, que se passe-t-il sur le serveur ou sur le réseau à ce moment-là.
Si tu as la possibilité (je ne connais pas Postgre) fait une trace des plans d’exécutions et regarde quelle requête rame.

[StringBuilder]

Je trouve étrange que la base génère 90% d'espace inutile pendant la journée.

C'est comme si des requêtes s'amusaient toute la journée à supprimer et insérer les données DÉJÀ EXISTANTES.

Aussi, le FILL FACTOR des tables ne serait-il pas mal paramétré ? Un SGBD prévoit qu'on peut insérer des lignes entre deux lignes d'une table. Et il va donc laisser des espaces vides pour éviter de devoir tout décaler.

Je me demande si votre base ne fait pas ça, mais en se disant "tiens, on sait jamais, on pourrait insérer 10 lignes entre chaque lignes existantes, donc je passe ma journée à tout décaler.

[StringBuilder]

Rei Ichido & sevyc64 > Vu le comportement, j'ai surtout l'impression que la base fragmente comme une malade (peut-être même les fichiers sur le disque), et du coup les IO deviennent très lent à la fin de la journée, car il faut parcourir 10 fois plus de distance sur le disque pour lire la même chose.

[punkoff]

l'auto-vaccuum est activé ?

[clavier12AZQSWX]

j'ai fait une erreur de frappe : c'est donc :

beacuoup de modifications et de suppression , très peu d'insertions . Et évidement constament des SELECT.


j'ai désactivé l'autovacum et le fait faire la nuit (ou quand j'ai le temps à midi par moi-même). En fait l'activité constante de l'autovacum générait des ralentissement constant durant l'activité des collègues. Je suppose que bcp de tables/index ou relations étaient mises en attente à cause de lui.
Migrer la base de donner d'un ERP n'est pas aussi simple que ça, vous pensez bien qu'il y a aussi l'applicatif qui doit suivre. J'ai déjà essayé de le passer en 8.4 mais il y a des requêtes non supportées qu'il faut faire réécrire et valider.
Par contre en 8.2, aucun souci, mais le gain de perf est moindre..

j'ai déjà essayer de trouver les requêtes les plus lentes en loguant ce qui dure plus d'une seconde mais il y en a trop.

En fait il faudrait que psotgresql fasse ce que Mysql fait très bien : il affiche le nombre de fois qu'une requête est utilisée. Mais ça je n'ai pas trouvé comment l'obtenir avec postgres.

De même j'aurai besoin de savoir quels indexes ne servent à rien car l'ERP en crée tout seul en fonction de critère stupide.
En regardant la ta taille de certains index avec pgadmin, dès fois c'est plus gros que la taille elle-même ce qui je pense est complète anormale non ?

[StringBuilder]

De même j'aurai besoin de savoir quels indexes ne servent à rien car l'ERP en crée tout seul en fonction de critère stupide.

Ouh là...

Je pense qu'avant de se coltiner des heures d'analyse des requêtes et du SGBD, il faudrait commencer par modifier l'ERP afin qu'il ne touche pas à la structure de la base : c'est pas à lui de créer des index (surtout si les algos utilisés sont stupides)

La gloutonnerie de l'ERP qui multiplie par 10 la taille de la base de données en quelques heures vient tout simplement de là !

D'autant que j'imagine que l'ERP ne se limite pas à la création d'index, j'imagine qu'il doit bien trifouiller d'autres choses dans la base, genre des modifications de tables non ?

[punkoff]

Donc votre problème de grossissement de base vient de là.

L'auto-vaccum permet de libérer l'espace suite à un delete (update aussi ? je ne sasi pas).

[scheu]

Les stats de ta base (analyze) sont-elles bien calculées régulièrement, et quand ?
Le cas classique c'est que les stats postgresql indiquent que les tables sont quasiment vides, or quand l'ERP les charge, elles contiennent des lignes, et les SELECT dessus font des full scan (croyant que les tables sont vides) au lieu d'utiliser les indexes (ou pire pour les jointures, font des "nested loop" au lieu de "hash join")

[SQLpro]

Cela est généralement du à MVCC qui stocke de multiples versions des lignes du fait ds mises à jour suppressions... etc.
J'ai écrit un article publié dans PHP solution récemment pour montrer comment fonctionnait le système de stockage de PG et je mettais l’accent sur le fait que MVCC pose des problèmes de perf.

Seule solution de contournement, activer le processus de tâche de fond AUTOVACUUM.

Si vous avez en journée des heures creuses vous pouvez faire un VACUMM FULL ou reconstruire les principales tables avec CLUSTER

A +