Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 2EB2F9FA1FE for ; Fri, 1 Dec 2006 10:47:27 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 76376-02 for ; Fri, 1 Dec 2006 10:47:15 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from server.linux27.com (server.linux27.com [72.232.77.42]) by postgresql.org (Postfix) with ESMTP id 4A9AC9FA178 for ; Fri, 1 Dec 2006 10:47:21 -0400 (AST) Received: from [201.67.231.52] (port=10258 helo=PC1) by server.linux27.com with esmtpa (Exim 4.52) id 1Gq9fb-0002om-Ak; Fri, 01 Dec 2006 12:47:19 -0200 Reply-To: From: "Carlos H. Reimer" To: "Mark Kirkwood" Cc: Subject: RES: Bad iostat numbers Date: Fri, 1 Dec 2006 12:47:28 -0200 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 8bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) In-Reply-To: <456F89AC.9070207@paradise.net.nz> Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962 X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - server.linux27.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [0 0] / [47 12] X-AntiAbuse: Sender Address Domain - opendb.com.br X-Source: X-Source-Args: X-Source-Dir: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/5 X-Sequence-Number: 22061 Hi, If you look the iostat data it shows that the system is doing much more writes than reads. It is strange, because if you look in the pg_stat tables we see a complete different scenario. Much more reads than writes. I was monitoring the presence of temporary files in the data directory what could denote big sorts, but nothing there too. But I think it is explained because of the high number of indexes present in those tables. One write in the base table, many others in the indexes. Well, about the server behaviour, it has not changed suddenly but the performance is becoming worse day by day. Reimer > -----Mensagem original----- > De: Mark Kirkwood [mailto:markir@paradise.net.nz] > Enviada em: quinta-feira, 30 de novembro de 2006 23:47 > Para: carlos.reimer@opendb.com.br > Cc: pgsql-performance@postgresql.org > Assunto: Re: [PERFORM] Bad iostat numbers > > > Carlos H. Reimer wrote: > > While collecting performance data I discovered very bad numbers in the > > I/O subsystem and I would like to know if I´m thinking correctly. > > > > Here is a typical iostat -x: > > > > > > avg-cpu: %user %nice %system %iowait %idle > > > > 50.40 0.00 0.50 1.10 48.00 > > > > > > > > Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s > > avgrq-sz avgqu-sz await svctm %util > > > > sda 0.00 7.80 0.40 6.40 41.60 113.60 20.80 > > 56.80 22.82 570697.50 10.59 147.06 100.00 > > > > sdb 0.20 7.80 0.60 6.40 40.00 113.60 20.00 > > 56.80 21.94 570697.50 9.83 142.86 100.00 > > > > md1 0.00 0.00 1.20 13.40 81.60 107.20 40.80 > > 53.60 12.93 0.00 0.00 0.00 0.00 > > > > md0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 > > 0.00 0.00 0.00 0.00 0.00 0.00 > > > > > > > > Are they not saturated? > > > > They look it (if I'm reading your typical numbers correctly) - %util 100 > and svctime in the region of 100 ms! > > On the face of it, looks like you need something better than a RAID1 > setup - probably RAID10 (RAID5 is probably no good as you are writing > more than you are reading it seems). However read on... > > If this is a sudden change in system behavior, then it is probably worth > trying to figure out what is causing it (i.e which queries) - for > instance it might be that you have some new queries that are doing disk > based sorts (this would mean you really need more memory rather than > better disk...) > > Cheers > > Mark > > > > >