Message-ID: <4D9CB14F.8040901@fuzzy.cz>
Date: Wed, 06 Apr 2011 20:30:39 +0200
From: Tomas Vondra <tv@fuzzy.cz>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.2.13) Gecko/20101219 Lightning/1.0b3pre Thunderbird/3.1.7
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: very long updates very small tables
References: <4D91EEFA.90707@uni-heidelberg.de>
	<4D91EC7E020000250003BEEC@gw.wicourts.gov>
	<4D92DD2C.8010305@uni-heidelberg.de>
	<4D931A0C020000250003BF80@gw.wicourts.gov>
	<4D997D3D.1080104@uni-heidelberg.de>
	<4D999038020000250003C207@gw.wicourts.gov>
In-Reply-To: <4D999038020000250003C207@gw.wicourts.gov>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Dne 4.4.2011 16:32, Kevin Grittner napsal(a):
> Nothing there makes a write glut on checkpoint less likely to be the
> cause.  Without a BBU write-back cache it is actually *more* likely,
> and having enough RAM to hold the whole database makes it *more*
> likely.  If you haven't placed your pg_xlog directory on a separate
> file system, it is also more likely.
>  
> Turning on logging of checkpoint activity and checking whether that
> correlates with your problem times is strongly indicated.
>  
> -Kevin

Checkpoints would be my first guess too, but the whole database is just
500MB. Lars, how did you get this number? Did you measure the amount of
disk space occupied or somehow else?

BTW how much memory is there (total RAM and dedicated to shared
buffers)? How many checkpoint segments are there?

Have you monitored the overall behavior of the system (what processes
are running etc.) when the problems occur? I don't have much experience
with Windows but tools from sysinternals are reasonable.

And yet another idea - have you tried to use the stats collected by
PostgreSQL? I mean the pg_stat_ tables, especially pg_stat_bgwriter and
maybe pg_stat_all_tables. Those numbers are cummulative, so do two
snapshot when the problems are happening and subtract them to get an
idea of what's going on.

regards
Tomas