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)
In-reply-to: <4BC742C8.2050301@geizhals.at> 
References: <4BC742C8.2050301@geizhals.at>
Comments: In-reply-to Marinos Yannikos <mjy@geizhals.at>
	message dated "Thu, 15 Apr 2010 18:46:00 +0200"
Date: Thu, 15 Apr 2010 13:38:00 -0400
Message-ID: <4674.1271353080@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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