Re: slow full table update

Поиск
Список
Период
Сортировка
От
Тема Re: slow full table update
Дата
Msg-id 200811121725.30238@centrum.cz
обсуждение исходный текст
Ответ на Re: slow full table update  (tv@fuzzy.cz)
Ответы Re: slow full table update  (tv@fuzzy.cz)
Список pgsql-performance
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: tv@fuzzy.cz
> Komu: firerox@centrum.cz
> CC: pgsql-performance@postgresql.org
> 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 (pgsql-performance@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
>>
>
>
>
>--
>Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-performance
>


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

Предыдущее
От: "Julien Theulier"
Дата:
Сообщение: Re: Index usage with sub select or outer joins
Следующее
От: - -
Дата:
Сообщение: Performance Question