Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 06C9C650F0F for ; Wed, 25 Jun 2008 04:41:46 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 23556-10 for ; Wed, 25 Jun 2008 04:41:32 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smtp.alibaba-inc.com (smtp.alibaba-inc.com [121.0.17.65]) by postgresql.org (Postfix) with ESMTP id 81DE9650EFC for ; Wed, 25 Jun 2008 04:41:14 -0300 (ADT) X-IronPort-AV: E=Sophos;i="4.27,700,1204473600"; d="scan'208,217";a="69781817" From: "jay" To: "'Rusty Conover'" Cc: References: <473587.97248.qm@web32701.mail.mud.yahoo.com> <004b01c8d671$3dec1630$0544000a@hz.ali.com> Subject: =?gb2312?B?tPC4tDogW1BFUkZPUk1dIFBvc3RncmVzcWwgdXBkYXRlIG9wIGlzIA==?= =?gb2312?B?dmVyeSB2ZXJ5IHNsb3c=?= Date: Wed, 25 Jun 2008 15:39:31 +0800 Message-ID: <00c101c8d696$9b979460$0544000a@hz.ali.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00C2_01C8D6D9.A9BAD460" X-Mailer: Microsoft Office Outlook 11 Thread-Index: AcjWjr8bsa9t+TQqRpK3jPGXKGd2mwAAsrag X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198 In-Reply-To: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.303 tagged_above=0 required=5 tests=AWL=-0.303, HTML_FONT_FACE_BAD=0.606, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200806/121 X-Sequence-Number: 30471 This is a multi-part message in MIME format. ------=_NextPart_000_00C2_01C8D6D9.A9BAD460 Content-Type: text/plain; charset="gb2312" Content-Transfer-Encoding: quoted-printable 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. =20 LOG: 00000: checkpoint complete: wrote 174943 buffers (26.7%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=3D207.895 s, sync=3D12.282 s, total=3D220.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=3D217.455 = s, sync=3D5.059 s, total=3D222.874 s LOCATION: LogCheckpointEnd, xlog.c:5640 LOG: 00000: checkpoint starting: xlog LOCATION: LogCheckpointStart, xlog.c:5604 =20 postgres=3D# select * from pg_stat_bgwriter; checkpoints_timed | checkpoints_req | buffers_checkpoint | = buffers_clean | maxwritten_clean | buffers_backend | buffers_alloc=20 -------------------+-----------------+--------------------+--------------= -+- -----------------+-----------------+--------------- 292 | 93 | 16898561 | = 243176 | 2303 | 3989550 | 3694189 (1 row) =20 Is checkpoint too frequency lead the problem? If it=A1=AFs, how to solve it ? =20 =20 =20 =20 -----=D3=CA=BC=FE=D4=AD=BC=FE----- =B7=A2=BC=FE=C8=CB: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] =B4=FA=B1=ED Rusty = Conover =B7=A2=CB=CD=CA=B1=BC=E4: 2008=C4=EA6=D4=C225=C8=D5 13:02 =CA=D5=BC=FE=C8=CB: jay =B3=AD=CB=CD: pgsql-performance@postgresql.org =D6=F7=CC=E2: Re: [PERFORM] Postgresql update op is very very slow =20 =20 On Jun 24, 2008, at 9:12 PM, jay wrote: =20 >=20 > I've a table with about 34601755 rows ,when I execute 'update =20 > msg_table set > type=3D0;' is very very slow, cost several hours, but still not =20 > complete? >=20 > Why postgresql is so slowly? Is the PG MVCC problem? >=20 > But I try it on Mysql, the same table and rows, it only cost about 340 > seconds. >=20 > Any idea for the problem? >=20 >=20 > My machine config: > Memory 8G, 8 piece 15K disk , 2CPU(Quad-Core) AMD =20 > OS: Red Hat AS4 >=20 > My postgres.conf main parameter is following: >=20 >=20 =20 Hi Jay, =20 Is the "type" used in an index? Have you properly increased your =20 number of checkpoint segments? Any warnings in in your log file about =20 excessive checkpointing? =20 Cheers, =20 Rusty -- Rusty Conover InfoGears Inc. http://www.infogears.com =20 =20 =20 =20 =20 =20 =20 --=20 Sent via pgsql-performance mailing list = (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance ------=_NextPart_000_00C2_01C8D6D9.A9BAD460 Content-Type: text/html; charset="gb2312" Content-Transfer-Encoding: quoted-printable

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=3D207.895 s, sync=3D12.282 s, total=3D220.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=3D217.455 = s, sync=3D5.059 s, total=3D222.874 s

LOCATION:  LogCheckpointEnd, = xlog.c:5640

LOG:  00000: checkpoint starting: = xlog

LOCATION:  LogCheckpointStart, = xlog.c:5604

 

postgres=3D# select * from = pg_stat_bgwriter;

 checkpoints_timed | checkpoints_req | buffers_checkpoint | buffers_clean | maxwritten_clean | buffers_backend | buffers_alloc =

-------------------+-----------------+--------------------+-------= --------+------------------+-----------------+---------------<= /span>

           =     292 |            =   93 |           = 16898561 |        243176 |            = 2303 |         3989550 |       = 3694189

(1 row)

 

     Is checkpoint too frequency lead the = problem?

If it=A1=AFs, how to solve it ?

 

 

 

 

-----=D3=CA=BC=FE=D4=AD=BC=FE-----
=B7=A2=BC=FE=C8=CB: = pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] =B4=FA=B1=ED = Rusty Conover
=B7=A2=CB=CD=CA=B1=BC=E4: 2008=C4=EA6=D4=C225=C8=D5 13:02
=CA=D5=BC=FE=C8=CB: jay
=B3=AD=CB=CD: = pgsql-performance@postgresql.org
=D6=F7=CC=E2: 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=3D0;' 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

------=_NextPart_000_00C2_01C8D6D9.A9BAD460--