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