答复: [PERFORM] Postgresql update op is very very slow

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(at)postgresql(dot)org
[mailto:pgsql-performance-owner(at)postgresql(dot)org] 代表 Rusty Conover
发送时间: 2008年6月25日 13:02
收件人: jay
抄送: pgsql-performance(at)postgresql(dot)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(at)postgresql(dot)org)

To make changes to your subscription:

http://www.postgresql.org/mailpref/pgsql-performance