Forums PostgreSQL.fr

Ontologia

Membre

Optimisation particulière pour statistique

Bonjour, je travaille sur une base assez grosse, chargée une seul fois pour toute, avec pour objectif de réaliser pas mal de statistiques sur les données de celle-ci (que des select).
Je cherche à configurer les paramètres de postgresql.conf au mieux afin d'avoir les meilleurs perfs possibles.
Je viens vers vous pour avoir quelques conseils issus de votre expérience éventuelle sur ce genre de cas très particulier.

Mes contraintes/caractéristiques :

Hardware : Macbook = Core Duo 1,8 Ghz , 2 Go de mémoire, DD 7200 tours (perfs honorables, pointes à 40 Mo/s en random access)
Base : 2 tables, l'une de 300 k lignes, l'autre de 5 millions.  *** Tables chargée une seule et dernières fois : plus d'ajout ***,
Objectifs : Etablir toutes sortes de statistiques sur mes données => uniquement des select ; bcp d'aggrégations, calculs...

Sur ma table de 5 millions de lignes, j'ai 4 données de base + un lien ident vers un autre table.
Lors de la construction, j'essaye  d'updater une dizaine de champs pour chaque ligne de cette table
A l'heure ou j'en suis, les requetes d'update sont extrêmement longue : Au bout d'une journée, toujours pas finies !!

Ma question :
J'ai consulté la doc pour améliorer les params dans postgresql.conf, mais je me demande s'il y a qq chose à améliorer.
J'ai senti une nette amélioration sur les requetes d'insert into select.
Mes params postgresql.conf (j'ai laissé les params désactivé avec #)

Résumé :

shared_buffers = 490MB                  # min 128kB
                                        # (change requires restart)
temp_buffers = 8MB                      # min 800kB

max_connections = 3

effective_cache_size = 1024MB

J'ai activé les Genetic Query Optimizer (peut être une mauvaise idée ) ?

Le fichier de conf (settings liés à l'optim)

# - Memory -

shared_buffers = 490MB                  # min 128kB
                                        # (change requires restart)
temp_buffers = 8MB                      # min 800kB
#max_prepared_transactions = 0          # zero disables the feature
                                        # (change requires restart)
# Note:  Increasing max_prepared_transactions costs ~600 bytes of shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
# It is not advisable to set max_prepared_transactions nonzero unless you
# actively intend to use prepared transactions.
#work_mem = 1MB                         # min 64kB
#maintenance_work_mem = 16MB            # min 1MB
#max_stack_depth = 2MB                  # min 100kB
# - Kernel Resource Usage -

#max_files_per_process = 1000           # min 25
                                        # (change requires restart)
#shared_preload_libraries = ''          # (change requires restart)

# - Cost-Based Vacuum Delay -

#vacuum_cost_delay = 0ms                # 0-100 milliseconds
#vacuum_cost_page_hit = 1               # 0-10000 credits
#vacuum_cost_page_miss = 10             # 0-10000 credits
#vacuum_cost_page_dirty = 20            # 0-10000 credits
#vacuum_cost_limit = 200                # 1-10000 credits

# - Background Writer -

#bgwriter_delay = 200ms                 # 10-10000ms between rounds
#bgwriter_lru_maxpages = 100            # 0-1000 max buffers written/round
#bgwriter_lru_multiplier = 2.0          # 0-10.0 multipler on buffers scanned/round

# - Asynchronous Behavior -

#effective_io_concurrency = 1           # 1-1000. 0 disables prefetching
#fsync = on                             # turns forced synchronization on or off
synchronous_commit = on         # immediate fsync at commit
#wal_sync_method = fsync                # the default is the first option
                                        # supported by the operating system:
                                        #   open_datasync
                                        #   fdatasync
                                        #   fsync
                                        #   fsync_writethrough
                                        #   open_sync
#full_page_writes = on                  # recover from partial page writes
wal_buffers = 1024kB                    # min 32kB
                                        # (change requires restart)
#wal_writer_delay = 200ms               # 1-10000 milliseconds

#commit_delay = 0                       # range 0-100000, in microseconds
#commit_siblings = 5                    # range 1-1000
effective_cache_size = 1024MB

# - Genetic Query Optimizer -

geqo = on
geqo_threshold = 12
geqo_effort = 5                 # range 1-10
geqo_pool_size = 0                      # selects default based on effort
geqo_generations = 0                    # selects default based on effort
geqo_selection_bias = 2.0               # range 1.5-2.0

je n'ai pas touché au reste...

Merci !

Marc Cousin

Membre

Re : Optimisation particulière pour statistique

Rien de choquant à première vue dans la conf. Comme la plupart du temps, les problèmes de performance proviennent probablement plutôt du code ou du schéma.

Êtes vous sur de :
- Faire des transactions explicites (et pas une transaction par insert/update)
- Que vous avez bien les index dont vous avez besoin ?

---

Marc.

Ontologia

Membre

Re : Optimisation particulière pour statistique

Rien de choquant à première vue dans la conf. Comme la plupart du temps, les problèmes de performance proviennent probablement plutôt du code ou du schéma.

Êtes vous sur de :
- Faire des transactions explicites (et pas une transaction par insert/update)

Au début j'ai lancé tout mon script dans pgadmin, avec juste un commit à la fin, que j'ai arrêté en cours.
Ensuite je les ai ensuite lancé "as is"

Qu'est-ce qui est mieux ?

Entre temps, je me suis essayé à faire un petit échantillon limité d'update :
Cette requette d'update est simple : elle calcule une variance sur 3 valeurs (donc divisions, addition soustraction, multiplications) sur chaque ligne.
En gros, j'obtiens 8ms par lignes, soit environ 11h de calcul pour mes 5 millions de lignes.
ce qui est bizare, c'est que le processeur ne fait pas grand chose pendant ce temps là, et que les accès disques plafonnent à 200 à 500 ko/s en lecture et écriture..

- Que vous avez bien les index dont vous avez besoin ?

J'ai créé toutes sortes d'index, en hash et Btree sur à peu près tout les champs.
Mais j'ai un seul index Btree pour les 3 valeurs qui me servent à calculer ma variance ( l'update cité plus haut).
peut être devrais en créer un pour chaque ?

Marc Cousin

Membre

Re : Optimisation particulière pour statistique

Le commit à la fin n'est pas suffisant : il faut un begin au début de la série d'ordres pour indiquer que vous voulez commencer une transaction.

8ms par ligne, cela ressemble au temps d'écriture du journal sur votre disque dur IDE, donc au coût de la transaction.

Évitez les index hash ils ne servent à rien dans les versions actuelles : plus lents et ne supportent pas les crash.

Ne crééz que les index nécessaires : plus vous en aurez, plus il y en aura à mettre à jour. Il vous faut donc des index mais pas trop, c'est l'art du dba

Pour le moment tout s'oriente sur le fait que vous avez un commit implicite à chaque ligne (à cause de l'absence de begin au début de votre script).

---

Marc.

Ontologia

Membre

Re : Optimisation particulière pour statistique

Le commit à la fin n'est pas suffisant : il faut un begin au début de la série d'ordres pour indiquer que vous voulez commencer une transaction.

8ms par ligne, cela ressemble au temps d'écriture du journal sur votre disque dur IDE, donc au coût de la transaction.

Évitez les index hash ils ne servent à rien dans les versions actuelles : plus lents et ne supportent pas les crash.

Ne crééz que les index nécessaires : plus vous en aurez, plus il y en aura à mettre à jour. Il vous faut donc des index mais pas trop, c'est l'art du dba

Pour le moment tout s'oriente sur le fait que vous avez un commit implicite à chaque ligne (à cause de l'absence de begin au début de votre script).

J'ai donc supprimé les hash, ou plutôt, je les ai mis à la fin, et j'ai mis des index btree (très peu) que sur les valeurs sur lesquelles je calcul ma variance.
J'ai mis deux blocs de transaction ( begin; /*code*/ commit; si j'ai bien lu la doc), avec un vacuum entre les deux.
Je lance ensuite le bloc de transaction avec les update dedans (le 2eme bloc).
On verra demain ce que ça aura donné.

Merci en tout cas :-)

Marc Cousin

Membre

Re : Optimisation particulière pour statistique

Quelles nouvelles ?

---

Marc.

Ontologia

Membre

Re : Optimisation particulière pour statistique

Quelles nouvelles ?

Ca a bien marché :-)  je suis tombé selon les cas sur des temps de moins d'1 ms par insert.

En ce moment je reconstruit les indexs, et ça travaille depuis 10h. Sachant qu'il n'y aura dorénavant plus d'insert, je peux en mettre plein !