pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedUpdate performance degrades over time
4+ messages / 2 participants
[nested] [flat]
* Update performance degrades over time
@ 2008-05-14 22:31 Subbiah Stalin-XCGF84 <SSubbiah@Motorola.com>
0 siblings, 1 reply; 4+ messages in thread
From: Subbiah Stalin-XCGF84 @ 2008-05-14 22:31 UTC (permalink / raw)
To: pgsql-performance
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.
No IO waits seen during load tests and cpu usage on the server seem to
be 85% idle. This is a v445 sol10 with 4 cpu box attached to SAN
storage.
Here is the update statement and table/index/instance stats.
shared_buffers=4000MB
max_fsm_pages = 2048000
maintenance_work_mem = 512MB
checkpoint_segments = 128
effective_cache_size = 4000MB
update tablexy set col2=$1,col9=$2, col10=$3,col11=$4,col3=$5 WHERE
ID=$6;
Bloated
relname | relowner | relpages | reltuples
------------------------------+----------+----------+-----------
tablexy | 10 | 207423 | 502627
ix_tablexy_col1_col2 | 10 | 38043 | 502627
ix_tablexy_col3 | 10 | 13944 | 502627
ix_tablexy_col4 | 10 | 17841 | 502627
ix_tablexy_col5 | 10 | 19669 | 502627
ix_tablexy_col6 | 10 | 3865 | 502627
ix_tablexy_col7 | 10 | 12359 | 502627
ix_tablexy_col8_col7 | 10 | 26965 | 502627
ct_tablexy_id_u1 | 10 | 6090 | 502627
Recreating tablexy (compact),
relname | relowner | relpages | reltuples
------------------------------+----------+----------+-----------
tablexy | 10 | 41777 | 501233
ix_tablexy_col3 | 10 | 2137 | 501233
ix_tablexy_col8_col7 | 10 | 4157 | 501233
ix_tablexy_col6 | 10 | 1932 | 501233
ix_tablexy_col7 | 10 | 1935 | 501233
ix_tablexy_col1_col2 | 10 | 1933 | 501233
ix_tablexy_col5 | 10 | 2415 | 501233
ix_tablexy_col6 | 10 | 1377 | 501233
ct_tablexy_id_u1 | 10 | 3046 | 501233
Thanks,
Stalin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Update performance degrades over time
@ 2008-05-15 13:56 Jeffrey Baker <jwbaker@gmail.com>
parent: Subbiah Stalin-XCGF84 <SSubbiah@Motorola.com>
0 siblings, 1 reply; 4+ messages in thread
From: Jeffrey Baker @ 2008-05-15 13:56 UTC (permalink / raw)
To: Subbiah Stalin-XCGF84 <SSubbiah@motorola.com>; pgsql-performance
On Wed, May 14, 2008 at 6:31 PM, Subbiah Stalin-XCGF84
<SSubbiah@motorola.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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Update performance degrades over time
@ 2008-05-15 16:28 Subbiah Stalin-XCGF84 <SSubbiah@Motorola.com>
parent: Jeffrey Baker <jwbaker@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Subbiah Stalin-XCGF84 @ 2008-05-15 16:28 UTC (permalink / raw)
To: Jeffrey Baker <jwbaker@gmail.com>; pgsql-performance
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@gmail.com]
Sent: Thursday, May 15, 2008 6:56 AM
To: Subbiah Stalin-XCGF84; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Update performance degrades over time
On Wed, May 14, 2008 at 6:31 PM, Subbiah Stalin-XCGF84
<SSubbiah@motorola.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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Update performance degrades over time
@ 2008-05-15 19:27 Subbiah Stalin-XCGF84 <SSubbiah@Motorola.com>
parent: Subbiah Stalin-XCGF84 <SSubbiah@Motorola.com>
0 siblings, 0 replies; 4+ messages in thread
From: Subbiah Stalin-XCGF84 @ 2008-05-15 19:27 UTC (permalink / raw)
To: Jeffrey Baker <jwbaker@gmail.com>; pgsql-performance
Any system catalog views I can check for wait events causing slower
response times.
Thanks in advance.
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Subbiah
Stalin
Sent: Thursday, May 15, 2008 9:28 AM
To: Jeffrey Baker; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] 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@gmail.com]
Sent: Thursday, May 15, 2008 6:56 AM
To: Subbiah Stalin-XCGF84; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Update performance degrades over time
On Wed, May 14, 2008 at 6:31 PM, Subbiah Stalin-XCGF84
<SSubbiah@motorola.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
--
Sent via pgsql-performance mailing list
(pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2008-05-15 19:27 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-05-14 22:31 Update performance degrades over time Subbiah Stalin-XCGF84 <SSubbiah@Motorola.com>
2008-05-15 13:56 ` Jeffrey Baker <jwbaker@gmail.com>
2008-05-15 16:28 ` Subbiah Stalin-XCGF84 <SSubbiah@Motorola.com>
2008-05-15 19:27 ` Subbiah Stalin-XCGF84 <SSubbiah@Motorola.com>
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