Re: very long updates very small tables

2011/3/30, Lars Feistner <feistner(at)uni-heidelberg(dot)de>:
> Hello Kevin,
>
>
> On 03/29/2011 09:28 PM, Kevin Grittner wrote:
>> Lars Feistner<feistner(at)uni-heidelberg(dot)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(at)uni-heidelberg(dot)de
> Fon: +49-6221-56-8269
> Fax: +49-6221-56-7175
>
> WWW: http://www.ims-m.de
> http://www.kompmed.de
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

Hi. try to log all statements for an hour and show us it. And Postgresql.conf .

------------
pasman