Discussion :

[tnarol]

Salut,

J'essaye PostGreSQL sur un poste en local depuis une semaine. Il y a quelques détails qui interpellent le novice en SGBD que je suis :

- Quand je lance une requête, elle fait carburer un seul de mes coeurs de processeurs à fond. Je suis surpris que les requêtes ne soient pas automatiquement parallélisées. Est-ce possible et sinon quels sont les moyens d'optimiser les temps de réponse à part augmenter la vitesse unitaire d'un coeur ?

- J'ai chargé une table qui tenait initialement dans un fichier de 1,2 Go, dans le format de stockage PostGreSQL je crois que ça prenait environ 2 à 3 Go. Puis j'ai fait diverses modifications qui n'ont pas modifié le nombre de cellules de la table, j'ai même plutôt vidé des cellules. J'ai l'impression que ces modifs prennent à chaque fois plus d'espace disque : mon répertoire data fait maintenant 10 Go ! Que se passe t-il ?

Merci de vos éclairages.

[SQLpro]

Expliquez comment fonctionne un SGBDR à un néophyte en 3 lignes relève de la gageure... Mon cours que je donne en école d'ingé et au cnam nécessite plusieurs jours afin de commencer à comprendre tous les mécanismes en jeu...
Sachez d'abord qu'un SGBDR est un système à lui tout seul et n'utilise que très peu l'OS car il gère lui même les disques les CPU et la mémoire.

Si son comportement est telle que vous le dite, il y a différentes raisons possible à cela :
1) la configuration système est peu adéquate
2) cela lui suffit
Donc, sans de plus amples d'informations sur :

• la structure des tables
• l'indexation
• les paramètres physiques du serveur
• ...

la distribution des données
bref toutes choses qui peut influer sur sa façon de traiter les données, cette question restera vaine !

A +

[tnarol]

Merci,

Je vais essayer d'apporter quelques précisions sur ma config et de reformuler ma question concernant les perfs.

Je suis je pense dans la configuration la plus basique qu'on puisse imaginer : j'ai un ordi de bureau à 2 coeurs sur lequel j'ai installé le serveur et je manipule une unique table de taille respectable (SELECT / UPDATE principalement).

Ma question reformulée :

Supposons que je veuille continuer à faire la même chose mais plus vite qu'actuellement. Quelles sont mes options techniques ? Possibilité de forcer l'exécution sur plusieurs processeurs ? Possibilité de paralléliser le stockage et les requêtes ?

[SQLpro]

Une unique table c'est sans doute déjà là, la plus grosse partie du problème. Sachez que la première optimisation et donc, le plus importante en terme de gain, consiste à modéliser votre base en respectant les principes du modèle relationnel et donc les formes normales.
Sachez que lorsque je fais des audits de bases de données, je commence à flinguer dès que la table à plus de 20 colonnes, ou que la ligne de table dépasse 400 octets...

Commencez donc par nous dire : ce que fait votre application et comment est structurée votre table.

A +

[estofilo]

1) une requête n'utilise qu'un seul cpu et un seul coeur. La parallélisation des requêtes est un problème très difficile et les concepteurs de postgresql ne s'y sont pas attaqué jusqu'à présent.

2) quand on modifie une ligne d'une table, en interne ça réécrit une nouvelle version de cette ligne, ce qui augmente l'espace occupé. La ligne "morte" sera marqué comme réutilisable dès que possible par le processus autovacuum.
Il est possible de forçer le dégonflage de la table par VACUUM FULL mais c'est une opération lourde qui n'est bénéfique que quand on est sûr que cet espace ne pourra pas être réutilisé automatiquement par les mises à jour ultérieures sur la table en question.

Avec la version 8.3 le nombre des lignes en question peut être estimé par

Code :

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

select pg_stat_get_dead_tuples('latable'::regclass:oid);

[tnarol]

Une unique table c'est sans doute déjà là, la plus grosse partie du problème.... Le plus importante en terme de gain, consiste à modéliser votre base en respectant les principes du modèle relationnel et donc les formes normales...

Commencez donc par nous dire : ce que fait votre application et comment est structurée votre table.

Je suis bien conscient de cela. Pour l'instant mon utilisation se limite à retravailler des données résultant d'extractions avec lesquelles je n'ai aucune interaction (changer des encodages ou des formatages, faire des regroupements par catégorie, vider des valeurs non significatives, faire des jointures avec une autre table). Je n'ai donc pas vocation à modéliser quoi que ce soit, je cherche juste l'outil le plus approprié pour faire le boulot efficacement. Il se trouve qu'en dehors de l'aspect performance qui laisse encore à désirer je trouve PostGreSQL assez pratique à utiliser dans ce contexte. J'aime bien le fait que l'ensemble des manipulations effectuées soient traçables en code SQL.

[tnarol]

Il est possible de forçer le dégonflage de la table par VACUUM FULL mais c'est une opération lourde qui n'est bénéfique que quand on est sûr que cet espace ne pourra pas être réutilisé automatiquement par les mises à jour ultérieures sur la table en question.

Avec la version 8.3 le nombre des lignes en question peut être estimé par

Code :

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

select pg_stat_get_dead_tuples('latable'::regclass:oid);

J'ai une version 8.2.xxx (pas réussi à installer la 8.3). Etant donné mon utilisation exposée dans mon précédent message je pense que je peux faire sans problème un VACUUM FULL. Je ne suis pas sûr d'ailleurs d'avoir compris le mécanisme de mise à jour et la raison pour laquelle il ne peux pas libérer l'espace non utilisé quitte à le reprendre en cas de réutilisation.

[estofilo]

Il est assez délicat de rendre au système de fichiers des bouts d'espace qui se trouvent éparpillés dans un gros fichier. D'ailleurs ça peut aussi être contre-productif pour les perfs à cause de la fragmentation induite.
Si tu fais un VACUUM FULL, il va réécrire toute la table. Il est bon dans ce cas de faire ensuite un REINDEX de la table, ce qui a un effet similaire pour les index.

[estofilo]

C'est dommage de ne pas avoir la 8.3 car il y a un mécanisme nouveau dans cette version ("heap-only-tuple" update) qui justement améliore cet aspect de réutilisation d'espace disque, en tout cas pour les UPDATE qui ne touchent pas aux index.

[tnarol]

Il est assez délicat de rendre au système de fichiers des bouts d'espace qui se trouvent éparpillés dans un gros fichier. D'ailleurs ça peut aussi être contre-productif pour les perfs à cause de la fragmentation induite.
Si tu fais un VACUUM FULL, il va réécrire toute la table. Il est bon dans ce cas de faire ensuite un REINDEX de la table, ce qui a un effet similaire pour les index.

OK je vois le principe, cela dit je ne vois pas ce qui empêche de réécrire au même endroit que l'ancienne donnée lors d'un UPDATE. Ca ne seraait utile que si on voulait garder un historique.

Au risque de choquer encore je vous annonce que je n'ai pas pris la peine de définir d'index ni même de clé primaire dans ma table. Si j'ai bien compris ce que ça fait c'est inutile pour mon utilisation, c'est seulement dans le cas où j'aurais des variables "pivot" autour desquelles s'articulent des traitements que ce serait rentable. On est d'accord là dessus ?

[estofilo]

Dans un SGBD avec transactions, tant que la transaction n'est pas commitée on ne peut pas abandonner les anciennes valeurs car il se peut qu'il y ait un rollback. De plus même une fois commitée, une autre transaction concurrente qui aurait démarré avant l'update doit pouvoir continuer à voir les anciennes valeurs tant qu'elle n'a pas fini.

Quant à l'absence d'index, pourquoi pas, tout dépend de l'usage qui est fait de la table. Il est difficile de juger une conception sans avoir une vision globale des besoins qu'elle être censée satisfaire. En tout cas les index ne servent pas qu'aux jointures, ils servent aussi à filtrer rapidement des conditions de sélection du genre WHERE colonne=constante

[tnarol]

Dans un SGBD avec transactions, tant que la transaction n'est pas commitée on ne peut pas abandonner les anciennes valeurs car il se peut qu'il y ait un rollback. De plus même une fois commitée, une autre transaction concurrente qui aurait démarré avant l'update doit pouvoir continuer à voir les anciennes valeurs tant qu'elle n'a pas fini.

Donc en fait ça fonctionne toujours de la même manière même si je n'ai pas commencé de bloc de transaction. L'idée, je suppose c'est de garder l'intégrité de la base en cas de plantage ou bien de répondre à une autre demande simultanée.

Quant à l'absence d'index, pourquoi pas, tout dépend de l'usage qui est fait de la table. Il est difficile de juger une conception sans avoir une vision globale des besoins qu'elle être censée satisfaire. En tout cas les index ne servent pas qu'aux jointures, ils servent aussi à filtrer rapidement des conditions de sélection du genre WHERE colonne=constante

Ce que je voulais dire c'est que dans mon cas, pour une variable donnée j'aurais rarement l'occasion de faire un grand nombre de sélections de ce genre. Donc le temps passé à l'indexer n'est probablement pas récupéré.

Sinon j'ai retrouvé 10 Go après un VACUUM FULL... merci

[SQLpro]

Au risque de choquer encore je vous annonce que je n'ai pas pris la peine de définir d'index ni même de clé primaire dans ma table. Si j'ai bien compris ce que ça fait c'est inutile pour mon utilisation, c'est seulement dans le cas où j'aurais des variables "pivot" autour desquelles s'articulent des traitements que ce serait rentable. On est d'accord là dessus ?

PAS DU TOUT D'ACCORD !!!!

En effet les SGBDR sont spécialement conçus pour travailler avec des tables possédant au moins une clef. Toute manipulation qu'elle qu'elle soit à l'exception d'uniques INSERT et rien d'autre n'en sera que dramatiquement plus lent....

A +