From: firerox@centrum.cz
To: tv@fuzzy.cz
Cc: pgsql-performance@postgresql.org
Subject: Re: slow full table update
Date: Wed, 12 Nov 2008 17:25:49 +0100
Message-ID: <200811121725.30238@centrum.cz> (raw)
In-Reply-To: <61593.89.102.139.23.1226335300.squirrel@sq.gransy.com>
References: <200811101728.3048@centrum.cz>
<200811101729.14977@centrum.cz>
<200811101730.11057@centrum.cz>
<200811101730.15902@centrum.cz>
<61593.89.102.139.23.1226335300.squirrel@sq.gransy.com>
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
>
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: firerox@centrum.cz, tv@fuzzy.cz
Subject: Re: slow full table update
In-Reply-To: <200811121725.30238@centrum.cz>
* 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