Re: slow full table update

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(at)fuzzy(dot)cz
> Komu: firerox(at)centrum(dot)cz
> CC: pgsql-performance(at)postgresql(dot)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(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
>>
>
>
>
>--
>Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-performance
>