Message-ID: <4D997D3D.1080104@uni-heidelberg.de>
Date: Mon, 04 Apr 2011 10:11:41 +0200
From: Lars Feistner <feistner@uni-heidelberg.de>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.14) Gecko/20110223 Thunderbird/3.1.8
MIME-Version: 1.0
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
CC: 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>
In-Reply-To: <4D931A0C020000250003BF80@gw.wicourts.gov>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit


On 03/30/2011 06:54 PM, Kevin Grittner wrote:
> Lars Feistner<feistner@uni-heidelberg.de>  wrote:
>> On 03/29/2011 09:28 PM, Kevin Grittner wrote:
>>> Lars Feistner<feistner@uni-heidelberg.de>   wrote:
>>>
>>>> The log tells me that certain update statements take sometimes
>>>> about 3-10 minutes. But we are talking about updates on tables
>>>> with 1000 to 10000 rows and updates that are supposed to update
>>>> 1 row.
>>>
>>> The top possibilities that come to my mind are:
>
>> [all eliminated as possibilities]
>
> If you haven't already done so, you should probably turn on
> checkpoint logging to see if this corresponds to checkpoint
> activity.  If it does, you can try cranking up how aggressive your
> background writer is, and perhaps limiting your shared_buffers to
> something around the size of your RAID controller's BBU cache.  (I
> hope you have a RAID controller with BBU cache configured for
> write-back, anyway.)
>
> -Kevin
>

Hello Kevin,

i am sorry to disappoint you here. As I said in my first E-Mail we don't 
have much traffic and the database fits easily into memory. The traffic 
might increase, at least it was increasing the last 12 months. The 
database will always fit into memory.
No, we don't have a raid and thus we don't have a bbu. Actually we 
started off with a big SAN that our data centre offered. But sometimes 
this SAN was a bit slow and when we first encountered the very long 
updates i thought there was a connection between the long running 
updates and the slowliness of the SAN, so i started to use the local 
disk (we are talking about one disk not disks) for the database. I am 
still seeing the long running inserts and updates. I am still following 
the auto vacuum trail, it does still not run frequently enough. Thanks a 
lot for the replies so far. I will keep you guys informed about my next 
steps and the results.

Thanx a lot
Lars

-- 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Lars Feistner

Kompetenzzentrum für Prüfungen in der Medizin
Medizinische Fakultät Heidelberg,
Im Neuenheimer Feld 346, Raum 013
69120 Heidelberg

E-Mail: feistner@uni-heidelberg.de
Fon:   +49-6221-56-8269
Fax:   +49-6221-56-7175

WWW:   http://www.ims-m.de
        http://www.kompmed.de
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~