Re: very long updates very small tables

Поиск
Список
Период
Сортировка
От Lars Feistner
Тема Re: very long updates very small tables
Дата
Msg-id 4D92DD2C.8010305@uni-heidelberg.de
обсуждение исходный текст
Ответ на Re: very long updates very small tables  ("Kevin Grittner" <Kevin.Grittner@wicourts.gov>)
Ответы Re: very long updates very small tables  (pasman pasmański <pasman.p@gmail.com>)
Re: very long updates very small tables  ("Kevin Grittner" <Kevin.Grittner@wicourts.gov>)
Список pgsql-performance
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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

В списке pgsql-performance по дате отправления:

Предыдущее
От: Craig James
Дата:
Сообщение: Re: multiple table scan performance
Следующее
От: Greg Smith
Дата:
Сообщение: Re: Why Index is not used