Date: Wed, 16 Apr 2008 10:21:38 +0200
To: "Thomas Spreng" <spreng@socket.ch>
Subject: Re: Oddly slow queries
From: PFC <lists@peufeu.com>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
References: <3D7728ED-3D36-47E6-9266-351073ED4E11@socket.ch>
	<op.t9odybcgcigqcu@apollo13.peufeu.com>
	<AE4F81FF-C8BE-44BC-A79D-48EC5BF20162@socket.ch>
Content-Transfer-Encoding: Quoted-Printable
Message-ID: <op.t9o2uca7cigqcu@apollo13.peufeu.com>
In-Reply-To: <AE4F81FF-C8BE-44BC-A79D-48EC5BF20162@socket.ch>
User-Agent: Opera Mail/9.24 (Linux)

On Wed, 16 Apr 2008 06:07:04 +0200, Thomas Spreng <spreng@socket.ch> wro=
te:

>
> On 16.04.2008, at 01:24, PFC wrote:
>>
>>> The queries in question (select's) occasionally take up to 5 mins ev=
en  =

>>> if they take ~2-3 sec under "normal" conditions, there are no  =

>>> sequencial scans done in those queries. There are not many users  =

>>> connected (around 3, maybe) to this database usually since it's stil=
l  =

>>> in a testing phase. I tried to hunt down the problem by playing arou=
nd  =

>>> with resource usage cfg options but it didn't really made a differen=
ce.
>>
>> 	Could that be caused by a CHECKPOINT ?
>
>
> actually there are a few log (around 12 per day) entries concerning  =

> checkpoints:
>
> LOG:  checkpoints are occurring too frequently (10 seconds apart)
> HINT:  Consider increasing the configuration parameter  =

> "checkpoint_segments".
>
> But wouldn't that only affect write performance? The main problems I'm=
  =

> concerned about affect SELECT queries.

	OK, so if you get 12 of those per day, this means your checkpoint  =

interval isn't set to 10 seconds... I hope...
	Those probably correspond to some large update or insert query that com=
es  =

 from a cron or archive job ?... or a developer doing tests or filling a=
  =

table...

	So, if it is checkpointing every 10 seconds it means you have a pretty =
 =

high write load at that time ; and having to checkpoint and flush the  =

dirty pages makes it worse, so it is possible that your disk(s) choke on=
  =

writes, also killing the selects in the process.

	-> Set your checkpoint log segments to a much higher value
	-> Set your checkpoint timeout to a higher value (5 minutes or  =

something), to be tuned afterwards
	-> Tune bgwriter settings to taste (this means you need a realistic loa=
d,  =

not a test load)
	-> Use separate disk(s) for the xlog
	-> For the love of God, don't keep the RAID5 for production !
	(RAID5 + 1 small write =3D N reads + N writes, N=3D3 in your case)
	Since this is a test server I would suggest RAID1 for the OS and databa=
se  =

files and the third disk for the xlog, if it dies you just recreate the =
 =

DB...