Re: Volumes importants - lenteur.

Bonjour,
je voudrai savoir s'il existe une limite de bonne pratique dans la
taille d'une table postgresql. Je travaille sur des données
géographiques avec Postgres / Postgis et stocke des points géographiques
auxquels sont associés par exemple une mesure de température. Jusqu'ici
ma plus grosse table représentait 27 millions de lignes avec des temps
d'accès restant raisonnables. Je viens d'intégrer de nouvelles données
pour un total d'environ 300 millions de lignes et là ça devient
catastrophique. La structure de la table :

X (integer) champ indexé btree
Y (integer) champ indexé btree
Mesure (smallint)
the_geom (geometry point) indexé gist

La clé primaire est constituée du couple x,y

Le constat :

des vaccum analyze qui n'en finissent pas.
un select count (*) qui dure qui dure qui dure ...

La conf :

Un serveur debian / etch / postgresql 8.3 avec 4 Go de ram.
Postgresql :

shared_buffers = 1024MB
work_mem = 18MB
maintenance_work_mem = 176MB (par ailleurs j'ai passé la
commande suivante 'alter user postgres set maintenance_work_mem
to '512MB'; ' - je ne sais d'ailleurs pas comment afficher si
c'est pris en compte)
max_fsm_pages = 1560000 (valeur fixée suite à la demande de
postgresql lors de vacuum)
wal_buffers = 8MB

j'espère que quelques âmes charitables spécialistes pourront me donner
des pistes. Merci par avance.
Alain.

Bonjour,

J'aurai tendance à dire que votre volumétrie commence à devenir vraiment
trop importante pour stocker les données sur une seule table. D'autant
plus que, de ma propre expérience, les données carto sont assez
volumineuses.
A mon avis, vous devriez gagner à partitionner la table en question. La
volumétrie des partitions sera réduite et par conséquent les opérations
de maintenance plus efficaces (vacuum et analyze).

Pour mieux vous rendre compte de la volumétrie, vous pouvez utiliser les
fonctions de calcul de la taille des objets de la base
(http://docs.postgresql.fr/8.3/functions-admin.html#functions-admin-dbsize)
- notamment pg_relation_size et pg_total_relation_size.

Pour en revenir à votre configuration, vous pouvez la commande SHOW pour
afficher la valeur d'une variable GUC.

Pour améliorer vos écritures, vous pouvez augmenter checkpoint_segments
à 10. Et augmenter également le paramètre effective_cache_size pour
refléter la taille totale des mémoires caches de votre base
(shared_buffers + RAM libre + taille du cache Linux) - à la louche 3Go
sur votre système.

Par ailleurs, la valeur de work_mem me semble très élevée, mais elle
peut correspondre à vos besoins. Cela m'amène à vous poser ces
différentes questions :
- Combien de clients se connectent en même à votre base de données ?
- Avez-vous encore de la mémoire libre ?
- Votre serveur est-il surchargé (iowait + swap) ?

Voilà dans un premier temps ce qui me vient à l'esprit.

Cordialement,
Thomas Reiss

Le 12/04/2011 11:50, Alain Benard a écrit :
> Bonjour,
> je voudrai savoir s'il existe une limite de bonne pratique dans la
> taille d'une table postgresql. Je travaille sur des données
> géographiques avec Postgres / Postgis et stocke des points
> géographiques auxquels sont associés par exemple une mesure de
> température. Jusqu'ici ma plus grosse table représentait 27 millions
> de lignes avec des temps d'accès restant raisonnables. Je viens
> d'intégrer de nouvelles données pour un total d'environ 300 millions
> de lignes et là ça devient catastrophique. La structure de la table :
>
> X (integer) champ indexé btree
> Y (integer) champ indexé btree
> Mesure (smallint)
> the_geom (geometry point) indexé gist
>
> La clé primaire est constituée du couple x,y
>
> Le constat :
>
> des vaccum analyze qui n'en finissent pas.
> un select count (*) qui dure qui dure qui dure ...
>
> La conf :
>
> Un serveur debian / etch / postgresql 8.3 avec 4 Go de ram.
> Postgresql :
>
> shared_buffers = 1024MB
> work_mem = 18MB
> maintenance_work_mem = 176MB (par ailleurs j'ai passé la
> commande suivante 'alter user postgres set
> maintenance_work_mem to '512MB'; ' - je ne sais d'ailleurs pas
> comment afficher si c'est pris en compte)
> max_fsm_pages = 1560000 (valeur fixée suite à la demande de
> postgresql lors de vacuum)
> wal_buffers = 8MB
>
> j'espère que quelques âmes charitables spécialistes pourront me donner
> des pistes. Merci par avance.
> Alain.
>
>
>
>
>
>

Bonsoir,
merci Thomas pour ces éléments de réponse. Effectivement mon fichier
source de 6 Go occupe 136 Go (index compris - 53 Go pour le seul index
de l'objet géométrique) sous postgresql. Le checkpoint_segments est déjà
à 16 avec un effective_cache_size = 2GB. Le work_mem a été passé à 18 Mb
sur les conseil de l'outil pgtune. Coté mémoire libre je n'ai pas
l'impression de manquer de mémoire (le serveur est dédié à postgresql et
ne subit aucune charge significative). Pour info un vaccum analyse
démarré hier à 21H10 n'est pas terminé ce soir à 19H00. Je pense
effectivement que postgresql n'est pas capable de gérer cette volumétrie
en une seule table et j'espère qu'un découpage me permettra de m'en sortir.
Le SHOW fonctionne bien ;-) . Merci encore. Si quelqun a d'autres pistes
n'hésitez pas.
Alain.

Le 12/04/2011 13:48, Thomas Reiss a écrit :
> Bonjour,
>
> J'aurai tendance à dire que votre volumétrie commence à devenir
> vraiment trop importante pour stocker les données sur une seule table.
> D'autant plus que, de ma propre expérience, les données carto sont
> assez volumineuses.
> A mon avis, vous devriez gagner à partitionner la table en question.
> La volumétrie des partitions sera réduite et par conséquent les
> opérations de maintenance plus efficaces (vacuum et analyze).
>
> Pour mieux vous rendre compte de la volumétrie, vous pouvez utiliser
> les fonctions de calcul de la taille des objets de la base
> (http://docs.postgresql.fr/8.3/functions-admin.html#functions-admin-dbsize)
> - notamment pg_relation_size et pg_total_relation_size.
>
> Pour en revenir à votre configuration, vous pouvez la commande SHOW
> pour afficher la valeur d'une variable GUC.
>
> Pour améliorer vos écritures, vous pouvez augmenter
> checkpoint_segments à 10. Et augmenter également le paramètre
> effective_cache_size pour refléter la taille totale des mémoires
> caches de votre base (shared_buffers + RAM libre + taille du cache
> Linux) - à la louche 3Go sur votre système.
>
> Par ailleurs, la valeur de work_mem me semble très élevée, mais elle
> peut correspondre à vos besoins. Cela m'amène à vous poser ces
> différentes questions :
> - Combien de clients se connectent en même à votre base de données ?
> - Avez-vous encore de la mémoire libre ?
> - Votre serveur est-il surchargé (iowait + swap) ?
>
> Voilà dans un premier temps ce qui me vient à l'esprit.
>
> Cordialement,
> Thomas Reiss
>
>
> Le 12/04/2011 11:50, Alain Benard a écrit :
>> Bonjour,
>> je voudrai savoir s'il existe une limite de bonne pratique dans la
>> taille d'une table postgresql. Je travaille sur des données
>> géographiques avec Postgres / Postgis et stocke des points
>> géographiques auxquels sont associés par exemple une mesure de
>> température. Jusqu'ici ma plus grosse table représentait 27 millions
>> de lignes avec des temps d'accès restant raisonnables. Je viens
>> d'intégrer de nouvelles données pour un total d'environ 300 millions
>> de lignes et là ça devient catastrophique. La structure de la table :
>>
>> X (integer) champ indexé btree
>> Y (integer) champ indexé btree
>> Mesure (smallint)
>> the_geom (geometry point) indexé gist
>>
>> La clé primaire est constituée du couple x,y
>>
>> Le constat :
>>
>> des vaccum analyze qui n'en finissent pas.
>> un select count (*) qui dure qui dure qui dure ...
>>
>> La conf :
>>
>> Un serveur debian / etch / postgresql 8.3 avec 4 Go de ram.
>> Postgresql :
>>
>> shared_buffers = 1024MB
>> work_mem = 18MB
>> maintenance_work_mem = 176MB (par ailleurs j'ai passé la
>> commande suivante 'alter user postgres set
>> maintenance_work_mem to '512MB'; ' - je ne sais d'ailleurs
>> pas comment afficher si c'est pris en compte)
>> max_fsm_pages = 1560000 (valeur fixée suite à la demande
>> de postgresql lors de vacuum)
>> wal_buffers = 8MB
>>
>> j'espère que quelques âmes charitables spécialistes pourront me
>> donner des pistes. Merci par avance.
>> Alain.
>>
>>
>>
>>
>>
>>
>

Le 12 avril 2011 19:32, Alain Benard <alain(dot)benard(at)nancy(dot)inra(dot)fr> a écrit :
> Bonsoir,
> merci Thomas pour ces éléments de réponse. Effectivement mon fichier source
> de 6 Go occupe 136 Go (index compris - 53 Go pour le seul index de l'objet
> géométrique) sous postgresql. Le checkpoint_segments est déjà à 16 avec un
> effective_cache_size = 2GB. Le work_mem a été passé à 18 Mb sur les conseil
> de l'outil pgtune. Coté mémoire libre je n'ai pas l'impression de manquer de
> mémoire (le serveur est dédié à postgresql et ne subit aucune charge
> significative). Pour info un vaccum analyse démarré hier à 21H10 n'est pas
> terminé ce soir à 19H00. Je pense effectivement que postgresql n'est pas
> capable de gérer cette volumétrie en une seule table et j'espère qu'un
> découpage me permettra de m'en sortir.

En particulier avec du 'the_geom' au milieu.

Habituellement 100millions de lignes pour une table est un nombre qui
commence a etre limite, principalement en raison des taches de
maintenances.

Vu les volumes en base, et votre volume de ram, je suis assez
pessimiste sur les performances que vous pouvez espérer obtenir au
final... tout dépend de vos requetes bien sur.

> Le SHOW fonctionne bien ;-) . Merci encore. Si quelqun a d'autres pistes
> n'hésitez pas.
> Alain.
>
> Le 12/04/2011 13:48, Thomas Reiss a écrit :
>
> Bonjour,
>
> J'aurai tendance à dire que votre volumétrie commence à devenir vraiment
> trop importante pour stocker les données sur une seule table. D'autant plus
> que, de ma propre expérience, les données carto sont assez volumineuses.
> A mon avis, vous devriez gagner à partitionner la table en question. La
> volumétrie des partitions sera réduite et par conséquent les opérations de
> maintenance plus efficaces (vacuum et analyze).
>
> Pour mieux vous rendre compte de la volumétrie, vous pouvez utiliser les
> fonctions de calcul de la taille des objets de la base
> (http://docs.postgresql.fr/8.3/functions-admin.html#functions-admin-dbsize)
> - notamment pg_relation_size et pg_total_relation_size.
>
> Pour en revenir à votre configuration, vous pouvez la commande SHOW pour
> afficher la valeur d'une variable GUC.
>
> Pour améliorer vos écritures, vous pouvez augmenter checkpoint_segments à
> 10. Et augmenter également le paramètre effective_cache_size pour refléter
> la taille totale des mémoires caches de votre base (shared_buffers + RAM
> libre + taille du cache Linux) - à la louche 3Go sur votre système.
>
> Par ailleurs, la valeur de work_mem me semble très élevée, mais elle peut
> correspondre à vos besoins. Cela m'amène à vous poser ces différentes
> questions :
> - Combien de clients se connectent en même à votre base de données ?
> - Avez-vous encore de la mémoire libre ?
> - Votre serveur est-il surchargé (iowait + swap) ?
>
> Voilà dans un premier temps ce qui me vient à l'esprit.
>
> Cordialement,
> Thomas Reiss
>
>
> Le 12/04/2011 11:50, Alain Benard a écrit :
>
> Bonjour,
> je voudrai savoir s'il existe une limite de bonne pratique dans la taille
> d'une table postgresql. Je travaille sur des données géographiques avec
> Postgres / Postgis et stocke des points géographiques auxquels sont associés
> par exemple une mesure de température. Jusqu'ici ma plus grosse table
> représentait 27 millions de lignes avec des temps d'accès restant
> raisonnables. Je viens d'intégrer de nouvelles données pour un total
> d'environ 300 millions de lignes et là ça devient catastrophique. La
> structure de la table :
>
> X (integer)    champ indexé btree
> Y (integer)    champ indexé btree
> Mesure (smallint)
> the_geom (geometry point)    indexé gist
>
> La clé primaire est constituée du couple x,y
>
> Le constat :
>
> des vaccum analyze qui n'en finissent pas.
> un select count (*) qui dure qui dure qui dure ...
>
> La conf :
>
> Un serveur debian / etch / postgresql 8.3 avec 4 Go de ram.
> Postgresql :
>
> shared_buffers = 1024MB
> work_mem = 18MB
> maintenance_work_mem = 176MB (par ailleurs j'ai passé la commande suivante
> 'alter user postgres set maintenance_work_mem to '512MB'; ' - je ne sais
> d'ailleurs pas comment afficher si c'est pris en compte)
> max_fsm_pages = 1560000    (valeur fixée suite à la demande de postgresql
> lors de vacuum)
> wal_buffers = 8MB
>
> j'espère que quelques âmes charitables spécialistes pourront me donner des
> pistes. Merci par avance.
> Alain.
>
>
>
>
>
>
>
>
>
> --
> Sent via pgsql-fr-generale mailing list (pgsql-fr-generale(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-fr-generale
>
>

--
Cédric Villemain               2ndQuadrant
http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support

En fait vous êtes un peu aux limites de PG. Le problème de PG c'est
qu'il ne sait pas multithreader une même requête. C'est son gros point
noir pour traiter de forts volumes de données, à la différence de Oracle
ou MS SQL Server qui savent parfaitement multithreader une même requête,
voir même trop (si il y a 32 processeurs, les requêtes candidates au
multithreading vont s'exécuter sur les 32 coeurs) et il faut les brider
en limitant le parallélisme...

Effectivement si vous pouvez découper votre table en plusieurs petites,
ce sera déjà un peu quelques chose. Ensuite rien ne vous empêche de
créer une vue agrégeant les tables à l'aide d'un UNION ALL et pour les
mise à jour passer par des triggers de mise à jour (via les règles).
http://docs.postgresqlfr.org/8.0/rules-update.html

A +

--
Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
*********************** http://www.sqlspot.com *************************

Le 12/04/2011 19:32, Alain Benard a écrit :
> Bonsoir,
> merci Thomas pour ces éléments de réponse. Effectivement mon fichier
> source de 6 Go occupe 136 Go (index compris - 53 Go pour le seul index
> de l'objet géométrique) sous postgresql. Le checkpoint_segments est déjà
> à 16 avec un effective_cache_size = 2GB. Le work_mem a été passé à 18 Mb
> sur les conseil de l'outil pgtune. Coté mémoire libre je n'ai pas
> l'impression de manquer de mémoire (le serveur est dédié à postgresql et
> ne subit aucune charge significative). Pour info un vaccum analyse
> démarré hier à 21H10 n'est pas terminé ce soir à 19H00. Je pense
> effectivement que postgresql n'est pas capable de gérer cette volumétrie
> en une seule table et j'espère qu'un découpage me permettra de m'en sortir.
> Le SHOW fonctionne bien ;-) . Merci encore. Si quelqun a d'autres pistes
> n'hésitez pas.
> Alain.
>
> Le 12/04/2011 13:48, Thomas Reiss a écrit :
>> Bonjour,
>>
>> J'aurai tendance à dire que votre volumétrie commence à devenir
>> vraiment trop importante pour stocker les données sur une seule table.
>> D'autant plus que, de ma propre expérience, les données carto sont
>> assez volumineuses.
>> A mon avis, vous devriez gagner à partitionner la table en question.
>> La volumétrie des partitions sera réduite et par conséquent les
>> opérations de maintenance plus efficaces (vacuum et analyze).
>>
>> Pour mieux vous rendre compte de la volumétrie, vous pouvez utiliser
>> les fonctions de calcul de la taille des objets de la base
>> (http://docs.postgresql.fr/8.3/functions-admin.html#functions-admin-dbsize)
>> - notamment pg_relation_size et pg_total_relation_size.
>>
>> Pour en revenir à votre configuration, vous pouvez la commande SHOW
>> pour afficher la valeur d'une variable GUC.
>>
>> Pour améliorer vos écritures, vous pouvez augmenter
>> checkpoint_segments à 10. Et augmenter également le paramètre
>> effective_cache_size pour refléter la taille totale des mémoires
>> caches de votre base (shared_buffers + RAM libre + taille du cache
>> Linux) - à la louche 3Go sur votre système.
>>
>> Par ailleurs, la valeur de work_mem me semble très élevée, mais elle
>> peut correspondre à vos besoins. Cela m'amène à vous poser ces
>> différentes questions :
>> - Combien de clients se connectent en même à votre base de données ?
>> - Avez-vous encore de la mémoire libre ?
>> - Votre serveur est-il surchargé (iowait + swap) ?
>>
>> Voilà dans un premier temps ce qui me vient à l'esprit.
>>
>> Cordialement,
>> Thomas Reiss
>>
>>
>> Le 12/04/2011 11:50, Alain Benard a écrit :
>>> Bonjour,
>>> je voudrai savoir s'il existe une limite de bonne pratique dans la
>>> taille d'une table postgresql. Je travaille sur des données
>>> géographiques avec Postgres / Postgis et stocke des points
>>> géographiques auxquels sont associés par exemple une mesure de
>>> température. Jusqu'ici ma plus grosse table représentait 27 millions
>>> de lignes avec des temps d'accès restant raisonnables. Je viens
>>> d'intégrer de nouvelles données pour un total d'environ 300 millions
>>> de lignes et là ça devient catastrophique. La structure de la table :
>>>
>>> X (integer) champ indexé btree
>>> Y (integer) champ indexé btree
>>> Mesure (smallint)
>>> the_geom (geometry point) indexé gist
>>>
>>> La clé primaire est constituée du couple x,y
>>>
>>> Le constat :
>>>
>>> des vaccum analyze qui n'en finissent pas.
>>> un select count (*) qui dure qui dure qui dure ...
>>>
>>> La conf :
>>>
>>> Un serveur debian / etch / postgresql 8.3 avec 4 Go de ram.
>>> Postgresql :
>>>
>>> shared_buffers = 1024MB
>>> work_mem = 18MB
>>> maintenance_work_mem = 176MB (par ailleurs j'ai passé la
>>> commande suivante 'alter user postgres set
>>> maintenance_work_mem to '512MB'; ' - je ne sais d'ailleurs
>>> pas comment afficher si c'est pris en compte)
>>> max_fsm_pages = 1560000 (valeur fixée suite à la demande de
>>> postgresql lors de vacuum)
>>> wal_buffers = 8MB
>>>
>>> j'espère que quelques âmes charitables spécialistes pourront me
>>> donner des pistes. Merci par avance.
>>> Alain.
>>>
>>>
>>>
>>>
>>>
>>>
>>
>
>
>
>

Le 12 avril 2011 22:29, F. BROUARD / SQLpro <sqlpro(at)club-internet(dot)fr> a écrit :
> En fait vous êtes un peu aux limites de PG. Le problème de PG c'est qu'il ne
> sait pas multithreader une même requête. C'est son gros point noir pour
> traiter de forts volumes de données, à la différence de Oracle ou MS SQL
> Server qui savent parfaitement multithreader une même requête, voir même
> trop (si il y a 32 processeurs, les requêtes candidates au multithreading
> vont s'exécuter sur les 32 coeurs) et il faut les brider en limitant le
> parallélisme...
>
> Effectivement si vous pouvez découper votre table en plusieurs petites, ce
> sera déjà un peu quelques chose. Ensuite rien ne vous empêche de créer une
> vue agrégeant les tables à l'aide d'un UNION ALL et pour les mise à jour
> passer par des triggers de mise à jour (via les règles).
> http://docs.postgresqlfr.org/8.0/rules-update.html

Il existe une notion d'héritage dans PostgreSQL:
http://doc.postgresql.fr/9.0/ddl-partitioning.html

>
> A +
>
>
> --
> Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66
> Le site sur le langage SQL et les SGBDR  :  http://sqlpro.developpez.com
> Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence
> Audit, conseil, expertise, formation, modélisation, tuning, optimisation
> *********************** http://www.sqlspot.com *************************
>
>
> Le 12/04/2011 19:32, Alain Benard a écrit :
>>
>> Bonsoir,
>> merci Thomas pour ces éléments de réponse. Effectivement mon fichier
>> source de 6 Go occupe 136 Go (index compris - 53 Go pour le seul index
>> de l'objet géométrique) sous postgresql. Le checkpoint_segments est déjà
>> à 16 avec un effective_cache_size = 2GB. Le work_mem a été passé à 18 Mb
>> sur les conseil de l'outil pgtune. Coté mémoire libre je n'ai pas
>> l'impression de manquer de mémoire (le serveur est dédié à postgresql et
>> ne subit aucune charge significative). Pour info un vaccum analyse
>> démarré hier à 21H10 n'est pas terminé ce soir à 19H00. Je pense
>> effectivement que postgresql n'est pas capable de gérer cette volumétrie
>> en une seule table et j'espère qu'un découpage me permettra de m'en
>> sortir.
>> Le SHOW fonctionne bien ;-) . Merci encore. Si quelqun a d'autres pistes
>> n'hésitez pas.
>> Alain.
>>
>> Le 12/04/2011 13:48, Thomas Reiss a écrit :
>>>
>>> Bonjour,
>>>
>>> J'aurai tendance à dire que votre volumétrie commence à devenir
>>> vraiment trop importante pour stocker les données sur une seule table.
>>> D'autant plus que, de ma propre expérience, les données carto sont
>>> assez volumineuses.
>>> A mon avis, vous devriez gagner à partitionner la table en question.
>>> La volumétrie des partitions sera réduite et par conséquent les
>>> opérations de maintenance plus efficaces (vacuum et analyze).
>>>
>>> Pour mieux vous rendre compte de la volumétrie, vous pouvez utiliser
>>> les fonctions de calcul de la taille des objets de la base
>>>
>>> (http://docs.postgresql.fr/8.3/functions-admin.html#functions-admin-dbsize)
>>> - notamment pg_relation_size et pg_total_relation_size.
>>>
>>> Pour en revenir à votre configuration, vous pouvez la commande SHOW
>>> pour afficher la valeur d'une variable GUC.
>>>
>>> Pour améliorer vos écritures, vous pouvez augmenter
>>> checkpoint_segments à 10. Et augmenter également le paramètre
>>> effective_cache_size pour refléter la taille totale des mémoires
>>> caches de votre base (shared_buffers + RAM libre + taille du cache
>>> Linux) - à la louche 3Go sur votre système.
>>>
>>> Par ailleurs, la valeur de work_mem me semble très élevée, mais elle
>>> peut correspondre à vos besoins. Cela m'amène à vous poser ces
>>> différentes questions :
>>> - Combien de clients se connectent en même à votre base de données ?
>>> - Avez-vous encore de la mémoire libre ?
>>> - Votre serveur est-il surchargé (iowait + swap) ?
>>>
>>> Voilà dans un premier temps ce qui me vient à l'esprit.
>>>
>>> Cordialement,
>>> Thomas Reiss
>>>
>>>
>>> Le 12/04/2011 11:50, Alain Benard a écrit :
>>>>
>>>> Bonjour,
>>>> je voudrai savoir s'il existe une limite de bonne pratique dans la
>>>> taille d'une table postgresql. Je travaille sur des données
>>>> géographiques avec Postgres / Postgis et stocke des points
>>>> géographiques auxquels sont associés par exemple une mesure de
>>>> température. Jusqu'ici ma plus grosse table représentait 27 millions
>>>> de lignes avec des temps d'accès restant raisonnables. Je viens
>>>> d'intégrer de nouvelles données pour un total d'environ 300 millions
>>>> de lignes et là ça devient catastrophique. La structure de la table :
>>>>
>>>>    X (integer) champ indexé btree
>>>>    Y (integer) champ indexé btree
>>>>    Mesure (smallint)
>>>>    the_geom (geometry point) indexé gist
>>>>
>>>>    La clé primaire est constituée du couple x,y
>>>>
>>>> Le constat :
>>>>
>>>>    des vaccum analyze qui n'en finissent pas.
>>>>    un select count (*) qui dure qui dure qui dure ...
>>>>
>>>> La conf :
>>>>
>>>>    Un serveur debian / etch / postgresql 8.3 avec 4 Go de ram.
>>>>    Postgresql :
>>>>
>>>>        shared_buffers = 1024MB
>>>>        work_mem = 18MB
>>>>        maintenance_work_mem = 176MB (par ailleurs j'ai passé la
>>>>        commande suivante 'alter user postgres set
>>>>        maintenance_work_mem to '512MB'; ' - je ne sais d'ailleurs
>>>>        pas comment afficher si c'est pris en compte)
>>>>        max_fsm_pages = 1560000 (valeur fixée suite à la demande de
>>>>        postgresql lors de vacuum)
>>>>        wal_buffers = 8MB
>>>>
>>>> j'espère que quelques âmes charitables spécialistes pourront me
>>>> donner des pistes. Merci par avance.
>>>> Alain.
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>
>>
>>
>>
>
>
> --
> Sent via pgsql-fr-generale mailing list (pgsql-fr-generale(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-fr-generale
>

--
Cédric Villemain               2ndQuadrant
http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support

Bonjour à tous,

Le 12/04/2011 22:29, F. BROUARD / SQLpro a écrit :
> En fait vous êtes un peu aux limites de PG. Le problème de PG c'est
> qu'il ne sait pas multithreader une même requête. C'est son gros point
> noir pour traiter de forts volumes de données, à la différence de Oracle
> ou MS SQL Server qui savent parfaitement multithreader une même requête,
> voir même trop (si il y a 32 processeurs, les requêtes candidates au
> multithreading vont s'exécuter sur les 32 coeurs) et il faut les brider
> en limitant le parallélisme...

PostgreSQL ne peut pas faire de parallélisme tout seul.

En revanche, plusieurs logiciels autour de PostgreSQL savent le faire:

* pgpool-II, via le "Parallel Mode" (libre)
* pl/proxy, dans une certaine mesure (libre)
* GridSQL (libre)
* Greeenplum (propriétaire)
* d'autres? (j'imagine que oui, la liste complètera !)

Des échos que j'ai pu avoir de ci de là, toutes ces technos marchent
très bien, mais ne sont pas vraiment comparables, une étude vous sera
nécessaire pour savoir quel est le meilleur outil pour vous.

> Effectivement si vous pouvez découper votre table en plusieurs petites,
> ce sera déjà un peu quelques chose.

+1 pour tenter le partitioning.

> Ensuite rien ne vous empêche de
> créer une vue agrégeant les tables à l'aide d'un UNION ALL et pour les
> mise à jour passer par des triggers de mise à jour (via les règles).
> http://docs.postgresqlfr.org/8.0/rules-update.html

Burk. Comme l'a dit Cédric, il n'y a pas besoin de faire cela. La table
partitionnée est alors la table "mère", dont héritent toutes les tables
"filles" pour la structure (uniquement, les tables filles peuvent avoir
des index en plus, par exemple, et la table mère pas d'index).

Ainsi, on interroge, insère, modifie ou efface les enregistrement
directement sur la table mère, les triggers (à préférer à la technique
des rules!) se chargeant d'écrire dans la bonne table fille, et le
mécanisme de constraint exclusion se chargeant de lire la(ou les)
tables(s) fille(s) concernée(s) par la requête de lecture...

My 2 cents,

--
Jean-Paul Argudo
www.PostgreSQLFr.org
www.Dalibo.com

Alain Benard wrote:

> des vaccum analyze qui n'en finissent pas.
> un select count (*) qui dure qui dure qui dure ...

Il serait intéressant de voir le résultat de vmstat pendant que tournent les
requêtes interminables.

Le serveur ayant 4Go de RAM, il faut quand même voir que c'est une config
obsolète pour le volume de données mentionné.
Si en plus le sous-système disque n'est pas tip-top, comme par ex. une simple
paire de disques en RAID1 comme on en trouve fréquemment dans les serveurs
pas spécialement destinés à de la BDD, on peut d'autant moins s'attendre à de
bonnes perfs.

Sinon côté postgres, le partitionnement n'apportera quelque chose que si la
clef de partitionnement est le critère directeur des requêtes.

Cordialement,
--
Daniel

Bonjour

J'ai un problème de manipulation des archives de la liste...

j'aurai aimé retrouver tout le fil de cette discussion, histoire de voir si mon problème est équivalent (j'ai 3 000 000 enregistrements dans 1 table de 4 GO, les update sont très lent et le select - avec psql - part dans les choux avec 'mémoire épuisée pour le résultat de la requête" bien que le gestionnaire de tache montre qu'il n'utilise que 1 G0 des 5 G0 de ma machine).

Dans mon cas, je pense qu'il y a des paramètres à changer au niveau du fichier de config... mais je ne sais pas trop lesquel

Merci

MCQ

-----Message d'origine-----
De : pgsql-fr-generale-owner(at)postgresql(dot)org [mailto:pgsql-fr-generale-owner(at)postgresql(dot)org] De la part de Jean-Paul Argudo
Envoyé : mercredi 13 avril 2011 10:20
À : pgsql-fr-generale(at)postgresql(dot)org
Objet : Re: [pgsql-fr-generale] Volumes importants - lenteur.

Bonjour à tous,

Le 12/04/2011 22:29, F. BROUARD / SQLpro a écrit :
> En fait vous êtes un peu aux limites de PG. Le problème de PG c'est
> qu'il ne sait pas multithreader une même requête. C'est son gros point
> noir pour traiter de forts volumes de données, à la différence de Oracle
> ou MS SQL Server qui savent parfaitement multithreader une même requête,
> voir même trop (si il y a 32 processeurs, les requêtes candidates au
> multithreading vont s'exécuter sur les 32 coeurs) et il faut les brider
> en limitant le parallélisme...

PostgreSQL ne peut pas faire de parallélisme tout seul.

En revanche, plusieurs logiciels autour de PostgreSQL savent le faire:

* pgpool-II, via le "Parallel Mode" (libre)
* pl/proxy, dans une certaine mesure (libre)
* GridSQL (libre)
* Greeenplum (propriétaire)
* d'autres? (j'imagine que oui, la liste complètera !)

Des échos que j'ai pu avoir de ci de là, toutes ces technos marchent
très bien, mais ne sont pas vraiment comparables, une étude vous sera
nécessaire pour savoir quel est le meilleur outil pour vous.

> Effectivement si vous pouvez découper votre table en plusieurs petites,
> ce sera déjà un peu quelques chose.

+1 pour tenter le partitioning.

> Ensuite rien ne vous empêche de
> créer une vue agrégeant les tables à l'aide d'un UNION ALL et pour les
> mise à jour passer par des triggers de mise à jour (via les règles).
> http://docs.postgresqlfr.org/8.0/rules-update.html

Burk. Comme l'a dit Cédric, il n'y a pas besoin de faire cela. La table
partitionnée est alors la table "mère", dont héritent toutes les tables
"filles" pour la structure (uniquement, les tables filles peuvent avoir
des index en plus, par exemple, et la table mère pas d'index).

Ainsi, on interroge, insère, modifie ou efface les enregistrement
directement sur la table mère, les triggers (à préférer à la technique
des rules!) se chargeant d'écrire dans la bonne table fille, et le
mécanisme de constraint exclusion se chargeant de lire la(ou les)
tables(s) fille(s) concernée(s) par la requête de lecture...

My 2 cents,

--
Jean-Paul Argudo
www.PostgreSQLFr.org
www.Dalibo.com

Bonsoir,

"Marie-Claude QUIDOZ" <Marie-Claude(dot)QUIDOZ(at)cefe(dot)cnrs(dot)fr> writes:
> j'aurai aimé retrouver tout le fil de cette discussion, histoire de voir si
> mon problème est équivalent (j'ai 3 000 000 enregistrements dans 1 table de
> 4 GO, les update sont très lent et le select - avec psql - part dans les
> choux avec 'mémoire épuisée pour le résultat de la requête" bien que le
> gestionnaire de tache montre qu'il n'utilise que 1 G0 des 5 G0 de ma
> machine).

Le client psql va tenter de récupérer l'ensemble du jeu de résultat
avant de l'afficher, et le mettre en mémoire côté client. Il est
possible d'éviter cela en utilisant un curseur, qui laisse le résultat
en mémoire côté serveur et transfère les données vers le client au fur
et à mesure des demandes.

Si la machine cliente ne sature pas en mémoire, c'est peut être le
serveur qui ne suit pas. Est-il nécessaire de monter l'ensemble des
données en mémoire ?

Il faudrait en savoir plus pour mieux répondre, cependant,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

Dimitri Fontaine wrote:

> Le client psql va tenter de récupérer l'ensemble du jeu de résultat
> avant de l'afficher, et le mettre en mémoire côté client. Il est
> possible d'éviter cela en utilisant un curseur, qui laisse le résultat
> en mémoire côté serveur et transfère les données vers le client au fur
> et à mesure des demandes.

Ou bien encore utiliser la variable FETCH_COUNT de psql:
\set FETCH_COUNT 1000

Cordialement,
--
Daniel

Bonjour,

> J'ai un problème de manipulation des archives de la liste...

http://search.postgresql.org/search?q=Volumes+importants+-+lenteur&m=1&l=27&d=-1&s=d

>
> j'aurai aimé retrouver tout le fil de cette discussion, histoire de voir si mon problème est équivalent (j'ai 3 000 000 enregistrements dans 1 table de 4 GO, les update sont très lent et le select - avec psql - part dans les choux avec 'mémoire épuisée pour le résultat de la requête" bien que le gestionnaire de tache montre qu'il n'utilise que 1 G0 des 5 G0 de ma machine).
>
> Dans mon cas, je pense qu'il y a des paramètres à changer au niveau du fichier de config... mais je ne sais pas trop lesquel

Sans regarder pour quelle raison windows a pas voulu utiliser plus de
1GB, je conseille d'utiliser \COPY dans psql, par exemple, qui va
utiliser un flux de données..et semble plus pertinent avec de tels
volumes (au moins on peut relire le fichier sans devoir transférer 1GB
de nouveau)

--
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation

Bonjour

Merci pour vos réponses. je vais peut être poser ma question différemment.

J'ai besoin de traiter des tables de plusieurs millions d'enregistrement (max 10 000 000 a ce jour), j'ai regardé la doc de postgresql. il semblerait qu'il n'y ait pas de limite pour le nombre d'enregistrement d'une table (j'ai trouvé uniquement des limites pour la taille de cette table). D'où mes essais de manipulation mais qui ne sont pas très concluants.. d'où mes doutes ...mais je pense que Postgresql permet de gérer de telles masses d'informations.

En espérant que ca soit plus clair

A+

MCQ

Marie-Claude Quidoz
CEFE - UMR5175
Marie-Claude(dot)Quidoz(at)cefe(dot)cnrs(dot)fr

________________________________

De: pgsql-fr-generale-owner(at)postgresql(dot)org de la part de Dimitri Fontaine
Date: lun. 17/10/2011 18:32
À: Marie-Claude QUIDOZ
Cc: pgsql-fr-generale(at)postgresql(dot)org
Objet : Re: [pgsql-fr-generale] Volumes importants - lenteur.

Bonsoir,

"Marie-Claude QUIDOZ" <Marie-Claude(dot)QUIDOZ(at)cefe(dot)cnrs(dot)fr> writes:
> j'aurai aimé retrouver tout le fil de cette discussion, histoire de voir si
> mon problème est équivalent (j'ai 3 000 000 enregistrements dans 1 table de
> 4 GO, les update sont très lent et le select - avec psql - part dans les
> choux avec 'mémoire épuisée pour le résultat de la requête" bien que le
> gestionnaire de tache montre qu'il n'utilise que 1 G0 des 5 G0 de ma
> machine).

Le client psql va tenter de récupérer l'ensemble du jeu de résultat
avant de l'afficher, et le mettre en mémoire côté client. Il est
possible d'éviter cela en utilisant un curseur, qui laisse le résultat
en mémoire côté serveur et transfère les données vers le client au fur
et à mesure des demandes.

Si la machine cliente ne sature pas en mémoire, c'est peut être le
serveur qui ne suit pas. Est-il nécessaire de monter l'ensemble des
données en mémoire ?

Il faudrait en savoir plus pour mieux répondre, cependant,
--
Dimitri Fontaine
http://2ndQuadrant.fr <http://2ndquadrant.fr/> PostgreSQL : Expertise, Formation et Support

--
Sent via pgsql-fr-generale mailing list (pgsql-fr-generale(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-fr-generale