Re: slow full table update

От:
Тема: Re: slow full table update
Дата: ,
Msg-id: 200811121725.30238@centrum.cz
(см: обсуждение, исходный текст)
Ответ на: Re: slow full table update  ()
Ответы: Re: slow full table update  ()
Список: pgsql-performance

Скрыть дерево обсуждения

slow full table update  (<>, )
 Re: slow full table update  (, )
  Re: slow full table update  (<>, )
   Re: slow full table update  (, )
    Re: slow full table update  (<>, )
     Re: slow full table update  (Richard Huxton, )
      Re: slow full table update  ("Vladimir Sitnikov", )
      Re: slow full table update  (Tom Lane, )
       Re: slow full table update  (Tomas Vondra, )
 Re: slow full table update  ("Scott Marlowe", )
  Re: slow full table update  (<>, )
   Re: slow full table update  ("Scott Marlowe", )
    Re: slow full table update  (Tomas Vondra, )
   Re: slow full table update  (PFC, )

Hi,

thank you for your reply.

Here is some aditional information:

the problem is on every tables with small and large rows too.
autovacuum is running.

relpages    reltuples
6213              54743

tables are almost write-only
Munin Graphs shows that problems is with I/O bottleneck.

I found out that
Update 100 rows takes 0.3s
but update 1000 rows takes 50s

Is this better information?

Thanks for any help.

best regards
Marek Fiala
______________________________________________________________
> Od: 
> Komu: 
> CC: 
> Datum: 10.11.2008 17:42
> Předmět: Re: [PERFORM] slow full table update
>
>Sorry, but you have to provide much more information about the table. The
>information you've provided is really not sufficient - the rows might be
>large or small. I guess it's the second option, with a lots of dead rows.
>
>Try this:
>
>ANALYZE table;
>SELECT relpages, reltuples FROM pg_class WHERE relname = 'table';
>
>Anyway, is the autovacuum running? What are the parameters? Try to execute
>
>VACUUM table;
>
>and then run the two commands above. That might 'clean' the table and
>improve the update performance. Don't forget each such UPDATE will
>actually create a copy of all the modified rows (that's how PostgreSQL
>works), so if you don't run VACUUM periodically or autovacuum demon, then
>the table will bloat (occupy much more disk space than it should).
>
>If it does not help, try do determine if the UPDATE is CPU or disk bound.
>I'd guess there are problems with I/O bottleneck (due to the bloating).
>
>regards
>Tomas
>
>> Hi,
>>
>> I have table with cca 60.000 rows and
>> when I run query as:
>>  Update table SET column=0;
>> after 10 minutes i must stop query, but it still running :(
>>
>> I've Postgres 8.1 with all default settings in postgres.conf
>>
>> Where is the problem?
>>
>> Thak you for any tips.
>>
>> best regards.
>> Marek Fiala
>>
>>
>>
>>
>>
>>
>>
>> --
>> Sent via pgsql-performance mailing list ()
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
>>
>
>
>
>--
>Sent via pgsql-performance mailing list ()
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-performance
>



В списке pgsql-performance по дате сообщения:

От: Tom Lane
Дата:
Сообщение: Re: Performance Question
От: Franck Routier
Дата:
Сообщение: Disk usage question