Hi Rusty,
The "type" is not in a index. The number of checkpoint segement is 64 and PG version is 8.3.3
After turn on log, I found something about checkpoints.
LOG: 00000: checkpoint complete: wrote 174943 buffers (26.7%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=207.895 s, sync=12.282 s, total=220.205 s
LOCATION: LogCheckpointEnd, xlog.c:5640
LOG: 00000: checkpoint starting: xlog
LOCATION: LogCheckpointStart, xlog.c:5604
LOG: 00000: duration: 11060.593 ms statement: select * from pg_stat_bgwriter;
LOCATION: exec_simple_query, postgres.c:1063
LOG: 00000: checkpoint complete: wrote 173152 buffers (26.4%); 0 transaction log file(s) added, 0 removed, 64 recycled; write=217.455 s, sync=5.059 s, total=222.874 s
LOCATION: LogCheckpointEnd, xlog.c:5640
LOG: 00000: checkpoint starting: xlog
LOCATION: LogCheckpointStart, xlog.c:5604
postgres=# select * from pg_stat_bgwriter;
checkpoints_timed | checkpoints_req | buffers_checkpoint | buffers_clean | maxwritten_clean | buffers_backend | buffers_alloc
-------------------+-----------------+--------------------+---------------+------------------+-----------------+---------------
292 | 93 | 16898561 | 243176 | 2303 | 3989550 | 3694189
(1 row)
Is checkpoint too frequency lead the problem?
If it’s, how to solve it ?
-----邮件原件-----
发件人: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] 代表 Rusty Conover
发送时间: 2008年6月25日 13:02
收件人: jay
抄送: pgsql-performance@postgresql.org
主题: Re: [PERFORM] Postgresql update op is very very slow
On Jun 24, 2008, at 9:12 PM, jay wrote:
>
> I've a table with about 34601755 rows ,when I execute 'update
> msg_table set
> type=0;' is very very slow, cost several hours, but still not
> complete?
>
> Why postgresql is so slowly? Is the PG MVCC problem?
>
> But I try it on Mysql, the same table and rows, it only cost about 340
> seconds.
>
> Any idea for the problem?
>
>
> My machine config:
> Memory 8G, 8 piece 15K disk , 2CPU(Quad-Core) AMD
> OS: Red Hat AS4
>
> My postgres.conf main parameter is following:
>
>
Hi Jay,
Is the "type" used in an index? Have you properly increased your
number of checkpoint segments? Any warnings in in your log file about
excessive checkpointing?
Cheers,
Rusty
--
Rusty Conover
InfoGears Inc.
http://www.infogears.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
