From: jay <jackem.mojx@alibaba-inc.com>
To: 'Rusty Conover' <rconover@infogears.com>
Cc: pgsql-performance@postgresql.org
Subject: 答复: [PERFORM] Postgresql update op is very very slow
Date: Wed, 25 Jun 2008 15:39:31 +0800
Message-ID: <00c101c8d696$9b979460$0544000a@hz.ali.com> (raw)
In-Reply-To: <F70DB945-81FB-4725-84E4-D1A8A90B6490@infogears.com>
References: <473587.97248.qm@web32701.mail.mud.yahoo.com>
<op.uc83c7m2cigqcu@apollo13.peufeu.com>
<004b01c8d671$3dec1630$0544000a@hz.ali.com>
<F70DB945-81FB-4725-84E4-D1A8A90B6490@infogears.com>
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
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: jackem.mojx@alibaba-inc.com, rconover@infogears.com
Subject: Re: 答复: [PERFORM] Postgresql update op is very very slow
In-Reply-To: <00c101c8d696$9b979460$0544000a@hz.ali.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