consulta tunning pg8.4.4

From: Martin Li Causi <emlicausi(at)gmail(dot)com>
To: pgsql-es-ayuda <pgsql-es-ayuda(at)postgresql(dot)org>
Subject: consulta tunning pg8.4.4
Date: 2012-01-18 15:31:58
Message-ID: CAK2psAZib4ZqORC6ovNJYm9wC6VxJB=XNjmFQOh7w14v4+1Bsw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-es-ayuda

Hola a todos, tengo un server con 8G de ram, 1 Micro Intel P4/Xeon
3.00Ghz
4 Discos SCSI armado 2 Raid 1 por soft , una particion LVM para la Base

estas es lo que configure en postgresql.conf

max_connections = 50
shared_buffers = 2048MB
work_mem = 164MB
maintenance_work_mem = 512MB
max_files_per_process = 20000
vacuum_cost_delay = 20ms
effective_io_concurrency = 4
fsync = on
wal_buffers = 16MB
checkpoint_segments = 16
checkpoint_completion_target = 0.9
effective_cache_size = 6GB (free + cached)
default_statistics_target = 100
constraint_exclusion = on
autovacuum = on
log_autovacuum_min_duration = -1
autovacuum_max_workers = 3

con esta configuracion, corren uno proceso que en este servidor tarda 1 h
30 min, segun los dev en otro servidor de igual caracteristicas tarda 20
minutos
la db tiene el mismo tamaño
logue todas las consultas y no veo que tarden mas de 1 o 2 segundos (en el
otro server no tengo acceso)
por el lado del disco no esta el problema ya que mire con iostat y no esta
sobrecargado

me recomiendan cambiar algo mas?

aca les dejo el iostat de los disco

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 8.50 72.08 491.65 25556667 174311296
sda1 0.33 9.93 27.74 3520857 9835920
sda2 8.17 62.12 463.91 22023459 164475064
sda3 0.00 0.03 0.00 12079 312
sdb 8.47 65.14 491.65 23094829 174311296
sdb1 0.34 10.27 27.74 3640665 9835920
sdb2 8.13 54.87 463.91 19452105 164475064
sdb3 0.00 0.01 0.00 1787 312
sdc 19.27 697.00 531.98 247114955 188609032
sdc1 19.27 697.00 531.98 247114683 188609032
sdd 18.90 680.96 531.98 241429433 188609032
sdd1 18.90 680.96 531.98 241429161 188609032
md1 58.79 116.98 456.17 41475258 161730296
md3 82.00 1377.96 528.92 488543522 187522456
md2 0.00 0.04 0.00 13560 88
md0 5.99 20.20 27.68 7161200 9814960
dm-0 58.79 116.98 456.17 41474698 161730296
dm-1 82.00 1377.96 528.92 488542866 187522456

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 3.00 0.00 64.00 0 64
sda1 0.00 0.00 0.00 0 0
sda2 3.00 0.00 64.00 0 64
sda3 0.00 0.00 0.00 0 0
sdb 3.00 0.00 64.00 0 64
sdb1 0.00 0.00 0.00 0 0
sdb2 3.00 0.00 64.00 0 64
sdb3 0.00 0.00 0.00 0 0
sdc 110.00 0.00 3152.00 0 3152
sdc1 110.00 0.00 3152.00 0 3152
sdd 110.00 0.00 3152.00 0 3152
sdd1 110.00 0.00 3152.00 0 3152
md1 6.00 0.00 48.00 0 48
md3 392.00 0.00 3136.00 0 3136
md2 0.00 0.00 0.00 0 0
md0 0.00 0.00 0.00 0 0

Browse pgsql-es-ayuda by date

  From Date Subject
Next Message felipe guzman 2012-01-19 11:28:56 Re: Consulta sobre pgadmin
Previous Message ๏̯͡๏ Guido Barosio 2012-01-18 14:35:45 Re: Postgresql+ldap