"Phoenix Kiula" <phoenix.kiula@gmail.com> writes:
> Basically, what I am missing is some info on actually tweaking the
> postgresql.conf to suit my system.
No, that's *not* what you're missing. I'm not sure what the problem
is in your system, but I'm pretty sure that everything you have
frantically been tweaking is unrelated if not outright
counterproductive. You need to stop tweaking and start some methodical
evidence-gathering to figure out what the problem actually is.
Here are some things I would suggest trying:
1. Do a VACUUM VERBOSE when the system is fast, and save the output.
When the system is slow, do another VACUUM VERBOSE, and compare file
sizes to see if anything seems markedly bloated. (It might be less
labor-intensive to copy pg_class.relname, reltuples, relpages columns
into another table for safekeeping after the first VACUUM, and use SQL
queries to look for markedly different sizes after the second VACUUM.)
2. Set up a task to dump the results of
select * from pg_locks, pg_stat_activity where pid = procpid
into a log file every few seconds. Compare what you see when things
are fast with when they are slow. In particular you should fairly
easily be able to tell if the slow queries are waiting long for locks.
3. Log the output of "vmstat 1" over time, compare fast and slow
periods.
regards, tom lane