Re: very long updates very small tables

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