From: Lars Feistner <feistner@uni-heidelberg.de>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: pgsql-performance@postgresql.org
Subject: Re: very long updates very small tables
Date: Wed, 30 Mar 2011 09:35:08 +0200
Message-ID: <4D92DD2C.8010305@uni-heidelberg.de> (raw)
In-Reply-To: <4D91EC7E020000250003BEEC@gw.wicourts.gov>
References: <4D91EEFA.90707@uni-heidelberg.de>
<4D91EC7E020000250003BEEC@gw.wicourts.gov>
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.dehttp://www.kompmed.de
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: feistner@uni-heidelberg.de, Kevin.Grittner@wicourts.gov
Subject: Re: very long updates very small tables
In-Reply-To: <4D92DD2C.8010305@uni-heidelberg.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox