Re: very long updates very small tables

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