savoir configurer postgresql

Version imprimable

22/04/2008, 14h39
viny

savoir configurer postgresql

Bonjour,

je recherche une personne experte en administration postgresql capable de configurer le fichier conf. Car ma base est sur un serveur dédié avec le Web mais c'est le fichier de config par défaut qui est installé et les perfs sont loin d'être au rendez-vous...

j'ai un quad core avec 4 giga de ram

Merci pour vous conseils
22/04/2008, 15h14
scheu

Le fichier postgresql.conf par défaut est effectivement pour des basses configs

Je ne suis pas expert mais pour ma part, pour un serveur similaire au tien (applis webs avec 4 Go de RAM voire plus), j'ai comme paramètres (qui ne sont pas par défaut) :

- shared_buffers = 1024MB
- work_mem = 64MB
- temp_buffers = 50MB
- maintenance_work_mem = 256MB
- max_connections à 100 (attention, si tu l'augmentes, ça augmente la mémoire utilisée)
- wal_buffers = 512kB --> pour les WAL
- checkpoint_segments = 32 --> pour les WAL
- stats_block_level = on --> pour les stats
- stats_row_level = on --> pour les stats

Je lance aussi toutes les nuits un "reindexdb -a" et un "vacuumdb -a -v -z"
Après ça dépend du temps que ça prend sur ta base, 1 fois par semaine c'est des fois suffisant, ou bien alors active l'autovacuum si tu es au moins en 8.2

Si tu as plusieurs axes physiques ou disques différents, penses aussi à répartir les données sur les disques en utilisant les tablespaces
22/04/2008, 15h21
viny

Je peux te communiquer le mien ?
22/04/2008, 15h22
scheu

Bien sûr, indiques tes paramètres autres que ceux par défaut

voici un extrait :

Code:

max_connections = 300

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
 
#---------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#---------------------------------------------------------------------------
 
# - Memory -
 
shared_buffers = 800			# min 16 or max_connections*2, 8KB each
temp_buffers =  400		# min 100, 8KB each
max_prepared_transactions = 5		# can be 0 or more
# note: increasing max_prepared_transactions costs ~600 bytes of shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
work_mem = 2048			# min 64, size in KB
maintenance_work_mem = 65536		# min 1024, size in KB
max_stack_depth = 2048			# min 100, size in KB
 
# - Free Space Map -
 
max_fsm_pages = 20000			# min max_fsm_relations*16, 6 bytes each
max_fsm_relations = 1000		# min 100, ~70 bytes each
 
# - Kernel Resource Usage -
 
max_files_per_process = 1000		# min 25
#preload_libraries = ''
 
# - Cost-Based Vacuum Delay -
 
vacuum_cost_delay = 10			# 0-1000 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		# 0-10000 credits
 
# - Background writer -
 
#bgwriter_delay = 200			# 10-10000 milliseconds between rounds
#bgwriter_lru_percent = 1.0		# 0-100% of LRU buffers scanned/round
#bgwriter_lru_maxpages = 5		# 0-1000 buffers max written/round
#bgwriter_all_percent = 0.333		# 0-100% of all buffers scanned/round
#bgwriter_all_maxpages = 5		# 0-1000 buffers max written/round

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
#---------------------------------------------------------------------------
# WRITE AHEAD LOG
#---------------------------------------------------------------------------
 
# - Settings -
 
#fsync = on				# turns forced synchronization on or off
#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 = 8			# min 4, 8KB each
#commit_delay = 0			# range 0-100000, in microseconds
#commit_siblings = 5			# range 1-1000
 
# - Checkpoints -
 
checkpoint_segments = 8		# in logfile segments, min 1, 16MB each
checkpoint_timeout = 300		# range 30-3600, in seconds
checkpoint_warning = 30		# in seconds, 0 is off
 
# - Archiving -
 
#archive_command = ''			# command to use to archive a logfile 
					# segment

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
 
#---------------------------------------------------------------------------
# QUERY TUNING
#---------------------------------------------------------------------------
 
# - Planner Method Configuration -
 
#enable_bitmapscan = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_indexscan = on
#enable_mergejoin = on
#enable_nestloop = on
#enable_seqscan = on
#enable_sort = on
#enable_tidscan = on
 
# - Planner Cost Constants -
 
effective_cache_size = 10000		# typically 8KB each
random_page_cost = 4			# units are one sequential page fetch 
					# cost
#cpu_tuple_cost = 0.01			# (same)
#cpu_index_tuple_cost = 0.001		# (same)
#cpu_operator_cost = 0.0025		# (same)
 
# - 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
 
# - Other Planner Options -
 
#default_statistics_target = 10		# range 1-1000
#constraint_exclusion = off
#from_collapse_limit = 8
#join_collapse_limit = 8		# 1 disables collapsing of explicit 
					# JOINs

Code:

1
2
3
4
5
6
7
8
9
10
 
#---------------------------------------------------------------------------
# LOCK MANAGEMENT
#---------------------------------------------------------------------------
 
deadlock_timeout = 1000		# in milliseconds
max_locks_per_transaction = 64		# min 10
# note: each lock table slot uses ~220 bytes of shared memory, and there are
# max_locks_per_transaction * (max_connections + max_prepared_transactions)
# lock table slots.

J'ai un quad core avec 4 Go de ram dont un giga est alloué à un autre serveur de chat

Un temps d'exécution de requête de l'ordre de 3 secondes pour afficher 900 lignes.

J'ai optimisé au mieux mes requêtes reste à savoir si le fichier de config est optimisé aussi

merci

22/04/2008, 17h58
scheu

Tes zones mémoire paraissent faiblardes

Essaie déjà d'augmenter ces paramètres comme ceci (avec tes 4 Go de RAM, tu auras encore de la marge) :
- shared_buffers = 512MB
- work_mem = 64MB
- temp_buffers = 50MB
- maintenance_work_mem = 256MB

Ca rame juste pour un select sur une table complète de 900 lignes ?
Tu as de la supervision OS (utilisations CPU, mémoire, swap, I/O) ?
22/04/2008, 18h06
viny

La requête "ma region" renvoie environ 950 lignes quand c'est l'ile france... sur un total de 3600 environ car on renvoie soit les hommes ou femmes et les profils qui correspondent à ce que tu recherches.

mais bon c'est un Select avec 4 à 5 jointures...
22/04/2008, 20h29
viny

Quand tu dis cela :

- shared_buffers = 512MB
- work_mem = 64MB
- temp_buffers = 50MB
- maintenance_work_mem = 256MB

la taille pour ces données est en KB donc faut mettre quoi exactement...
22/04/2008, 22h42
kedare

salut
sur un vieux serveur avec 2x P3 1.4Ghz, et 1go de ram, 2DD Scsi en raid 0+1 sous ubuntu server edition, vous me conseillez quoi comme config ? :)
23/04/2008, 09h32
viny

Citation:

- shared_buffers = 512MB
- work_mem = 64MB
- temp_buffers = 50MB
- maintenance_work_mem = 256MB

Donc les valeurs à mettre en tenant compte de l'unité de chaque valeur, c'est :

Citation:

- shared_buffers = 64000
- work_mem = 64000
- temp_buffers = 6250
- maintenance_work_mem = 256000

Est-ce bien celà ?

Merci
23/04/2008, 09h52
scheu

Citation:

Envoyé par viny

Donc les valeurs à mettre en tenant compte de l'unité de chaque valeur, c'est :

Est-ce bien celà ?

Merci

Tu peux mettre les valeurs en MB ça marche aussi
23/04/2008, 09h57
viny

OK, je ne savais pas...bon on va tester cela aujourd'hui et je te tiens au courant..

merci
24/04/2008, 16h55
viny

J'ai mis les valeurs souhaitées mais j'avoue que cela n'apporte aucune amélioration sur la requête la plus longue...

Je suis déçu par le manque significatif de perf:?
24/04/2008, 17h40
scheu

Tu as bien reloadé la conf après (pg_ctl reload) ?

C'est quoi comme problèmes de perfs ? Sur quels types de requêtes ?

Car là c'est vague, cela peut venir de la RAM, de la CPU, des I/O disques, des plans d'exécution foireux, etc ... ;)
24/04/2008, 18h00
viny

Je suis en infogérance donc c'est mon hébergeur qui a fait les modifs. Après effectivement les problèmes ça peut venir de partout...mais aujourd'hui je peux pas te dire si c'est des pbs de mémoire de disques etc...

Que puis-je te donner comme info ?

Je te l'ai dit c'est un pb sur un select qui va chercher 1000 personnes maxi dans une région avec une requete qui 4 jointures

Ma base à 15000 membres... et 3700 dans la région la plus importante

2 secondes pour afficher 880 personnes je trouve cela long maintenant pour postgres c'est peut être le nirvana...
25/04/2008, 09h19
scheu

Tu peux te connecter en psql (mode console) sur ta base ?
Que donne "show shared_buffers" ?
Si tu as des lenteurs juste pour un accès full (select *) sur une table, ça vient sûrement du paramétrage de postgresql ou des ressources du serveur
Si tu as des lenteurs seulement sur des requêtes complexes (jointures, etc ...), ça peut venir de l'optimisation (indexes, plan d'exécution, ...)
25/04/2008, 09h26
viny

Tu peux me donner les commandes exactes car je ne suis pas administrateur système ni dba alors pour moi c pas facile du tout.
25/04/2008, 10h38
scheu

Depuis un client postgresql :

Code:

psql -h <serveur> -p <port> <base>

Exemple : psql -h 192.168.0.1 -p 5432 BDDTEST

Ensuite, depuis la console : "show shared_buffers" pour vérifier que les nouveaux paramètres ont bien été pris en compte
21/05/2008, 12h45
jnore

Effectivement c'est long pour si peu de ligne.
Personnellement, je pense qu'il doit manquer des index sur ta ou tes tables.
Si tu relies (via ta requête) des champs qui ne sont pas indexés, tu vas jouer les prolongations c'est sûr!!!

Sur une config plus petite que la tiennne et sur la manipulation de plusieurs tables à plus de 100 000 lignes, j'ai des temps de réponses largement inférieurs à 500 mSec.

Ne pas avoir d'index pour la BDD, c'est comme si toi tu devais chercher un mot dans le dictionnaire alors que tous les mots y sont mélangés!

Certes ca mange de la place mais pas de comparaison pour les perfs.
21/05/2008, 12h48
viny

Non le problème n'est pas là. J'ai des index comme il faut