pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feed答复: [PERFORM] Postgresql update op is very very slow
3+ messages / 2 participants
[nested] [flat]
* 答复: [PERFORM] Postgresql update op is very very slow
@ 2008-06-25 07:39 ` jay <jackem.mojx@alibaba-inc.com>
1 sibling, 0 replies; 3+ messages in thread
From: jay @ 2008-06-25 07:39 UTC (permalink / raw)
To: 'Rusty Conover' <rconover@infogears.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* 答复: [PERFORM] Postgresql update op is very very slow
@ 2008-06-26 10:04 ` jay <jackem.mojx@alibaba-inc.com>
2008-06-26 15:04 ` Re: 答复: [PERFORM] Postgresql update op is very very slow Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 3+ messages in thread
From: jay @ 2008-06-26 10:04 UTC (permalink / raw)
To: 'Heikki Linnakangas' <heikki@enterprisedb.com>; +Cc: pgsql-performance
I know the problem, because there are about 35 million rows , which
cost about 12G disk space and checkpoint segments use 64, but update
operation is in one transaction which lead fast fill up the checkpoint
segments and lead do checkpoints frequently, but checkpoints will cost lots
resources, so update operation become slowly and slowly and bgwrite won't
write because it's not commit yet.
Create a new table maybe a quick solution, but it's not appropriated in some
cases.
If we can do commit very 1000 row per round, it may resolve the
problem.
But PG not support transaction within function yet?
-----邮件原件-----
发件人: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] 代表 Heikki Linnakangas
发送时间: 2008年6月25日 18:11
收件人: jay
抄送: pgsql-performance@postgresql.org
主题: Re: [PERFORM] Postgresql update op is very very slow
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?
Possibly. Because of MVCC, a full-table update will actually create a
new version of each row.
I presume that's a one-off query, or a seldom-run batch operation, and
not something your application needs to do often. In that case, you
could drop all indexes, and recreate them after the update, which should
help a lot:
BEGIN;
DROP INDEX <index name>, <index name 2>, ...; -- for each index
UPDATE msg_table SET type = 0;
CREATE INDEX ... -- Recreate indexes
COMMIT;
Or even better, instead of using UPDATE, do a SELECT INTO a new table,
drop the old one, and rename the new one in its place. That has the
advantage that the new table doesn't contain the old row version, so you
don't need to vacuum right away to reclaim the space.
Actually, there's an even more clever trick to do roughly the same thing:
ALTER TABLE msg_table ALTER COLUMN type TYPE int4 USING 0;
(assuming type is int4, replace with the actual data type if necessary)
This will rewrite the table, similar to a DROP + CREATE, and rebuild all
indexes. But all in one command.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: 答复: [PERFORM] Postgresql update op is very very slow
2008-06-26 10:04 ` 答复: [PERFORM] Postgresql update op is very very slow jay <jackem.mojx@alibaba-inc.com>
@ 2008-06-26 15:04 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2008-06-26 15:04 UTC (permalink / raw)
To: jay <jackem.mojx@alibaba-inc.com>; +Cc: 'Heikki Linnakangas' <heikki@enterprisedb.com>; pgsql-performance
"jay" <jackem.mojx@alibaba-inc.com> writes:
> I know the problem, because there are about 35 million rows , which
> cost about 12G disk space and checkpoint segments use 64, but update
> operation is in one transaction which lead fast fill up the checkpoint
> segments and lead do checkpoints frequently, but checkpoints will cost lots
> resources, so update operation become slowly and slowly and bgwrite won't
> write because it's not commit yet.
> Create a new table maybe a quick solution, but it's not appropriated in some
> cases.
> If we can do commit very 1000 row per round, it may resolve the
> problem.
No, that's utterly unrelated. Transaction boundaries have nothing to do
with checkpoints.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2008-06-26 15:04 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-06-25 07:39 ` jay <jackem.mojx@alibaba-inc.com>
2008-06-26 10:04 ` jay <jackem.mojx@alibaba-inc.com>
2008-06-26 15:04 ` Tom Lane <tgl@sss.pgh.pa.us>
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