Aaron Krowne wrote:
> Given this scenario, can anyone advise? I am particularly puzzled as to
> why everything I tried initially helped, but always degenerated rather
> rapidly to a near standstill. It seems to me that everything should be
> able to be cached in memory with no problem, perhaps I need to force
> this more explicitly.
Basic guidance:
- Keep shared memory use reasonable; your final settings of 64M shared
buffers and 16M sort_mem sound OK. In any case, be sure you're not
disk-swapping.
- If you don't already, run VACUUM ANALYZE on some regular schedule
(how often depends on your data turnover rate)
- Possibly consider running REINDEX periodically
- Post the SQL and EXPLAIN ANALYZE output for the queries causing the
worst of your woes to the list
Explanations of these can be found by searching the list archives and
reading the related sections of the manual.
A few questions:
- What version of Postgres?
- Have you run VACUUM FULL ANALYZE lately (or at least VACUUM ANALYZE)?
- Does the database see mostly SELECTs and INSERTs, or are there many
UPDATEs and/or DELETEs too?
- Are all queries slow, or particular ones?
HTH,
Joe