From: tv@fuzzy.cz
To: pgsql-performance@postgresql.org
Subject: Re: slow full table update
Date: Wed, 12 Nov 2008 17:47:59 +0100 (CET)
Message-ID: <63509.89.102.139.23.1226508479.squirrel@sq.gransy.com> (raw)
In-Reply-To: <200811121725.30238@centrum.cz>
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>
<200811121725.30238@centrum.cz>
Hi,
so the table occupies about 50 MB, i.e. each row has about 1 kB, right?
Updating 1000 rows should means about 1MB of data to be updated.
There might be a problem with execution plan of the updates - I guess the
100 rows update uses index scan and the 1000 rows update might use seq
scan.
Anyway the table is not too big, so I wouldn't expect such I/O bottleneck
on a properly tuned system. Have you checked the postgresql.conf settings?
What are the values for
1) shared_buffers - 8kB pages used as a buffer (try to increase this a
little, for example to 1000, i.e. 8MB, or even more)
2) checkpoint_segments - number of 16MB checkpoint segments, aka
transaction logs, this usually improves the write / update performance a
lot, so try to increase the default value (3) to at least 8
3) wal_buffers - 8kB pages used to store WAL (minimal effect usually, but
try to increase it to 16 - 64, just to be sure)
There is a nicely annotated config, with recommendations on how to set the
values based on usage etc. See this:
http://www.powerpostgresql.com/Downloads/annotated_conf_80.htmlhttp://www.powerpostgresql.com/PerfList
regards
Tomas
> 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
>>
>
>
> --
> 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: tv@fuzzy.cz
Subject: Re: slow full table update
In-Reply-To: <63509.89.102.139.23.1226508479.squirrel@sq.gransy.com>
* 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