Message-ID: <4D92DD2C.8010305@uni-heidelberg.de>
Date: Wed, 30 Mar 2011 09:35:08 +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>
In-Reply-To: <4D91EC7E020000250003BEEC@gw.wicourts.gov>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

Hello Kevin,


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:
>
> (1)  The tables are horribly bloated.  If autovacuum is off or not
> aggressive enough, things can degenerate to this level.
>
Some tables are auto vacuumed regularly others are not. The specific 
table extjs_recentlist was never autovacuumed. So i would think that 
updates on this table should be always very slow, but they are not. Only 
every 4 or 5th day for maybe half an hour and then everything is fine 
again. And;-) there is no anti virus installed.
> (2)  Memory is over-committed and your machine is thrashing.
>
We can rule this out. There is enough memory installed and the database 
is less than 500MB.
> (3)  There are explicit LOCK commands in the software which is
> contributing to the blocking.
We use the the jdbc driver. The jdbc driver might do some locking but we 
don't.
>
> (4)  There is some external delay within the transaction, such as
> waiting for user input while the transaction is open.
>
No, no user interaction within a transaction.
> Maybe there's a combination of the above at play.  Can you rule any
> of these out?
>
> -Kevin
>
So, i will try to get the autovacuum to be more aggressive and will 
report again if nothing changes.

Thanks 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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~