Effective Cache Size

Hi,

I have been following a thread on this list "Inconsistent performance"
and had a few questions especially the bits about effective_cache_size.
I have read some of the docs, and some other threads on this setting,
and it seems to used by the planner to either choose a sequential or
index scan. So it will not necessarily increase performance I suppose
but instead choose the most optimal plan. Is this correct?

We are not that we are suffering massive performance issues at the
moment but it is expected that our database is going to grow
considerably in the next couple of years, both in terms of load and
size.

Also what would an appropriate setting be?

From what I read of Scott Marlowes email, and from the information below
I reckon it should be somewhere in the region of 240,000.

Danger maths ahead. Beware!!!!

<maths>
  141816K  buff
+ 1781764K cached
-----------------
  1923580K total

effective_cache_size = 1923580 / 8 = 240447.5
</maths>

Here is some information on the server in question. If any more
information is required then please say. It is a dedicated PG machine
with no other services being hosted off it. As you can see from the
uptime, its load average is 0.00, and is currently so chilled its almost
frozen!!!!! That will change though :-(


Hardware
========
Dual PIII 1.4GHz
2Gb RAM
1Tb SAN with hardware RAID 5 using 1Gbps Fibre channel.


OS
==
Linux webbasedth5 2.4.18-18.7.xsmp #1 SMP Wed Nov 13 19:01:42 EST 2002
i686
Red Hat Linux release 7.3 (Valhalla)


PG
==
PostgreSQL 7.3.1 on i686-pc-linux-gnu, compiled by GCC 2.96


Database
========
This includes all indexes and tables. I can provide more information on
how this is chopped up if needed.

Size   : 1,141.305 Mb
Tuples : 13,416,397


Uptime
======
11:15am  up 197 days, 16:50,  1 user,  load average: 0.00, 0.00, 0.00


Top
===
Mem:  2064836K av, 2018648K used,   46188K free,       0K shrd,  141816K
buff
Swap: 2096472K av,    4656K used, 2091816K free                 1781764K
cached


Postgresql.conf (all defaults except)
=====================================
max_connections = 1000
shared_buffers = 16000 (128 Mb)
max_fsm_relations = 5000
max_fsm_pages = 500000
vacuum_mem = 65535



Kind Regards,

Nick Barr


This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender. You
should not copy it or use it for any purpose nor disclose or distribute
its contents to any other person.