Re: Update performance degrades over time

Yes we are updating one of indexed timestamp columns which gets unique
value on every update. We tried setting autovacuum_vacuum_scale_factor =
0.1 from default to make autovacuum bit aggressive, we see bloating on
both table and it's indexes but it's creeping up slowly though.

Anyways, even with slower bloating, I still see update performance to
degrade with 15 sec response time captured by setting
log_min_duration_stmt. Looks like bloating isn't causing slower updates.
Any help/ideas to tune this is appreciated.

Explain plan seems reasonable for the update statement.

update tablexy set col2=$1,col9=$2, col10=$3,col11=$4,col3=$5 WHERE
ID=$6;

QUERY PLAN

------------------------------------------------------------------------
----------------------------------------------------
Index Scan using ct_tablexy_id_u1 on tablexy (cost=0.00..8.51 rows=1
width=194) (actual time=0.162..0.166 rows=1 loops=1)
Index Cond: ((id)::text = '32xka8axki8'::text)

Thanks in advance.

Stalin

-----Original Message-----
From: Jeffrey Baker [mailto:jwbaker(at)gmail(dot)com]
Sent: Thursday, May 15, 2008 6:56 AM
To: Subbiah Stalin-XCGF84; pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] Update performance degrades over time

On Wed, May 14, 2008 at 6:31 PM, Subbiah Stalin-XCGF84
<SSubbiah(at)motorola(dot)com> wrote:
> Hi All,
>
> We are doing some load tests with our application running postgres
> 8.2.4. At times we see updates on a table taking longer (around
> 11-16secs) than expected sub-second response time. The table in
> question is getting updated constantly through the load tests. In
> checking the table size including indexes, they seem to be bloated got

> it confirmed after recreating it (stats below). We have autovacuum
> enabled with default parameters. I thought autovaccum would avoid
> bloating issues but looks like its not aggressive enough. Wondering if

> table/index bloating is causing update slowness in over a period of
> time. Any ideas how to troubleshoot this further.

Sometimes it is necessary to not only VACUUM, but also REINDEX. If your
update changes an indexed column to a new, distinct value, you can
easily get index bloat.

Also, you should check to see if you have any old, open transactions on
the same instance. If you do, it's possible that VACUUM will have no
beneficial effect.

-jwb