X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id E3F7BDA7DE for ; Tue, 22 Nov 2005 03:20:52 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 07741-08 for ; Tue, 22 Nov 2005 07:20:52 +0000 (GMT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from floppy.pyrenet.fr (news.pyrenet.fr [194.116.145.2]) by svr1.postgresql.org (Postfix) with ESMTP id 0E17EDA269 for ; Tue, 22 Nov 2005 03:20:50 -0400 (AST) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id CC1C431059; Tue, 22 Nov 2005 08:20:50 +0100 (MET) From: "Qingqing Zhou" X-Newsgroups: pgsql.performance Subject: Re: weird performances problem Date: Tue, 22 Nov 2005 02:21:12 -0500 Organization: Hub.Org Networking Services Lines: 39 Message-ID: References: <437CC21D.8070402@openwide.fr> X-Complaints-To: usenet@news.hub.org X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2900.2180 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-RFC2646: Format=Flowed; Original To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.086 required=5 tests=[AWL=0.086] X-Spam-Score: 0.086 X-Spam-Level: X-Archive-Number: 200511/433 X-Sequence-Number: 15690 "Guillaume Smet" wrote > [root@bd root]# iostat 10 > > Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn > sda 7.20 0.00 92.00 0 920 > sda1 0.00 0.00 0.00 0 0 > sda2 6.40 0.00 78.40 0 784 > sda3 0.00 0.00 0.00 0 0 > sda4 0.00 0.00 0.00 0 0 > sda5 0.00 0.00 0.00 0 0 > sda6 0.80 0.00 13.60 0 136 > sdb 5.00 0.00 165.60 0 1656 > sdb1 5.00 0.00 165.60 0 1656 > > Nested Loop (cost=0.00..13.52 rows=2 width=1119) (actual > time=155.286..155.305 rows=1 loops=1) > -> Index Scan using pk_newslang on newslang nl (cost=0.00..3.87 rows=1 > width=1004) (actual time=44.575..44.579 rows=1 loops=1) > Index Cond: (((codelang)::text = 'FRA'::text) AND (3498704 = > numnews)) > -> Nested Loop Left Join (cost=0.00..9.61 rows=2 width=119) (actual > time=110.648..110.660 rows=1 loops=1) > -> Index Scan using pk_news on news n (cost=0.00..3.31 rows=2 > width=98) (actual time=0.169..0.174 rows=1 loops=1) > Index Cond: (numnews = 3498704) > -> Index Scan using pk_photo on photo p (cost=0.00..3.14 rows=1 > width=25) (actual time=110.451..110.454 rows=1 loops=1) > Index Cond: (p.numphoto = "outer".numphoto) > Total runtime: 155.514 ms > Someone is doing a massive *write* at this time, which makes your query *read* quite slow. Can you find out which process is doing write? Regards, Qingqing