Re: Tunning postgresql

On Wed, 2003-11-19 at 14:25, Josué Maldonado wrote:
> Hello list,
>
> I have 7.3.4 on RH 8, server hardware is a dual processor Intel Xeon 2.4
> Ghz, 2G RAM. I was reading about tunning and would like to get some help
> from you, I changed some of the default values and the performance
> increased a little but I think still I can get more from that box.
>
> What should be the right values to set in kernel and postgresql.conf to
> get maximum performance, actually kernel share memory is:
> cat /proc/sys/kernel/shmmax
> 268435456
> cat /proc/sys/kernel/shmall
> 268435456
>
> postgresql.conf contains these configurations modified:
>
> shared_buffers = 17000 # min max_connections*2 or 16, 8KB each
> max_fsm_relations = 400 # min 10, fsm is free space map, ~40
> max_fsm_pages = 80000 # min 1000, fsm is free space map, ~6
> max_locks_per_transaction = 64 # min 10
> sort_mem = 16384 # min 64, size in KB
> effective_cache_size = 1700000 # typically 8KB each
>
> Still don't understand very well how to combine these parameters to gain
> maximun performance for postgresql, any help or comment about this
> would be very appreciated.
>
> Thanks,
>

Speaking from long experimentation, you're much, much better off making
sure your indices and queries are optimal that messing around with
buffer space. Buffer space tuning might get you a few percent
performance once you pick a reasonable value; query tuning can get you
orders of magnitude.

Stephen