From: Tom Lane <tgl@sss.pgh.pa.us>
To: Marinos Yannikos <mjy@geizhals.at>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: 8.3.9 - latency spikes with Linux (and tuning for consistently low latency)
Date: Thu, 15 Apr 2010 13:38:00 -0400
Message-ID: <4674.1271353080@sss.pgh.pa.us> (raw)
In-Reply-To: <4BC742C8.2050301@geizhals.at>
References: <4BC742C8.2050301@geizhals.at>
Marinos Yannikos <mjy@geizhals.at> writes:
> we are seeing latency spikes in the 2-3 second range (sometimes 8-10s) for
> queries that usually take 3-4ms on our systems and I am running out of things to
> try to get rid of them.
Have you checked whether the spikes correlate with checkpoints? Turn
on log_checkpoints and watch for awhile. If so, fooling with the
checkpoint parameters might give some relief. However, 8.3 already has
the spread-checkpoint code so I'm not sure how much more win can be had
there.
More generally, you should watch vmstat/iostat output and see if you
can correlate the spikes with I/O activity, CPU peaks, etc.
A different line of thought is that maybe the delays have to do with lock
contention --- log_lock_waits might help you identify that.
> fsync=off
That's pretty scary.
> work_mem=500MB
Yipes. I don't think you have enough RAM for that to be safe.
> commit_delay=100000 (*)
This is probably not a good idea.
> autovacuum=off (*)
Nor this.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, mjy@geizhals.at
Subject: Re: 8.3.9 - latency spikes with Linux (and tuning for consistently low latency)
In-Reply-To: <4674.1271353080@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox