On 7/5/06, andy rost <[EMAIL PROTECTED]> wrote:
fsync = on # turns forced synchronization
have you tried turning this off and measuring performance?
stats_command_string = on
I would turn this off unless you absoltely require it. It is
expensive for what it does.
a) All 4 CPUs are nearly always 0% idle;
b) The system load level is nearly always in excess of 20;
I am guessing your system is spending all it's time syncing. If so,
it's solvable (again, just run fsync=off for a bit and compare).
c) the output from vmstat -w 10 looks like:
procs memory page disks faults cpu
r b w avm fre flt re pi po fr sr aa0 aa1 in sy cs us
sy id
21 0 3 1242976 327936 2766 0 0 0 2264 0 2 2 17397 140332
104846 18 82 1
is that 100k context switches over 10 seconds or one second? that
might be something to check out. pg 8.1 is regarded as the solution
to any cs problem, though.
NOTE - small user demands and high system demands
d) Running top indicates a significant number or sblock states and
occasional smwai states;
e) ps auxww | grep postgres doesn't show anything abnormal;
f) ESQL applications are very slow.
We VACUUM ANALYZE user databases every four hours. We VACUUM template1
every 4 hours. We make a copy of the current WAL every minute. We create
a PIT recovery archive daily daily. None of these, individually seem to
place much strain on the server.
your server should be able to handle this easily.
Hopefully I've supplied enough information to start diagnosing the
problem. Any ideas, thoughts, suggestions are greatly appreciated ...
can you please approximate roughly how many transactions per second
your server is handling while you are getting the 20 load condition
(and, if possible, broken down into read and write transactions)?
merlin
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
