Discussion :

[viny]

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

[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

[viny]

Je peux te communiquer le mien ?

[scheu]

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

[viny]

voici un extrait :

Code :

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

max_connections = 300

Code :

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

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 :

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

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 :

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

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 :

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

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

[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) ?

[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...

[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...

[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 ?

[viny]

- 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 :

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

Est-ce bien celà ?

Merci

[scheu]

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

[viny]

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

merci

[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

[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 ...

[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...

[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, ...)

[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.

[scheu]

Depuis un client postgresql :

Code :

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

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

[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.

[viny]

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