Skip site navigation (1) Skip section navigation (2)

答复: [PERFORM] Postgresql update op is

From: "jay" <jackem(dot)mojx(at)alibaba-inc(dot)com>
To: "'Rusty Conover'" <rconover(at)infogears(dot)com>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: 答复: [PERFORM] Postgresql update op is
Date: 2008-06-25 07:39:31
Message-ID: 00c101c8d696$9b979460$0544000a@hz.ali.com (view raw or flat)
Thread:
Lists: 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(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

In response to

Responses

pgsql-performance by date

Next:From: Amol PujariDate: 2008-06-25 07:46:12
Subject: PostgreSQL and Ruby on Rails - better accessibility
Previous:From: bijayant kumarDate: 2008-06-25 05:41:40
Subject: Re: [SOLVED] Postgresql is very slow

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group