Received: from smtp-1.attica.net.nz (pop3-1.attica.net.nz [202.180.66.199]) by postgresql.org (Postfix) with SMTP id C50BC475461 for ; Fri, 22 Feb 2002 19:29:59 -0500 (EST) Received: (qmail 77130 invoked from network); 23 Feb 2002 00:30:00 -0000 Received: from 202-180-68-221.nas1.wlg.attica.net.nz (202.180.68.221) by pop3-1.attica.net.nz with SMTP; 23 Feb 2002 00:30:00 -0000 Subject: Re: [SQL] Transient Disk Usage Higher In 7.2 ? From: Mark kirkwood To: Christopher Kings-Lynne , Tom Lane Cc: Hackers , pgsql-sql In-Reply-To: References: Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Evolution/0.13 (Preview Release) Date: 23 Feb 2002 13:29:56 +1300 Message-Id: <1014424198.1378.50.camel@spikey.slithery.org> Mime-Version: 1.0 X-Archive-Number: 200202/1011 X-Sequence-Number: 19290 After examining the machine concerned : i) Found swap space ~ 1.3 * ram (its Linux 2.4) and it was getting down to zero during a sample query (Its been increased to ~ 3 * ram) ii) The dataset being queried are quite large (~ 30G) and there is about 8G of spare filesystem space. This was being used at a rate that suggested it _could_ be insufficient to complete the query (using the particular plan chosen anyway) iii) I misunderstood the 7.1.3 comparison - that query was executed on a _different_ machine (with correctly sized swap + more free disk, and more ram as well) iv) The 7.2 machine could do with more ram (has 256M) So it looks like a machine tuning issue, rather than a 7.2 one. There is an element of query tuning too I think as at least one _big_ table is being (unnecessarily?) seqscanned. They are re-running the queries with the swap change and some monitor scripts to see what is actually being exhausted. Thank you all for the suggestions Regards Mark >On Tue, 2002-02-19 at 22:03, Christopher Kings-Lynne wrote: > Reposted to -hackers. > > Are you able to post the actual SQL query they are trying to execute? If > not the exact one with the exact data, an analagous one? > > Chris > > > -----Original Message----- > > From: pgsql-sql-owner@postgresql.org > > [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Mark kirkwood > > Sent: Tuesday, 19 February 2002 4:38 PM > > To: pgsql-sql > > Subject: [SQL] Transient Disk Usage Higher In 7.2 ? > > > > > > Dear list, > > > > I have a customer who is testing 7.2 before upgrading (from 7.1.3) > > > > They are getting errors like : > > > > cannot extend tablename - No space left on device check free disk space > > or > > write to hashjoin temp file failed > > > > However when they attempt the same query with a 7.1.3 installation (On > > the same machine), they do not get these. > > (I dont know how different the query plans are between the 7.1 and 7.2 > > tests... see below) > > > > I am informed that there is "quite a bit" of free disk on the > > machine.(no numbers I am afraid... I am going in to have a look on > > Thursday). > > > > Is there an "expectation" that 7.2 will use temp space more aggressively > > than 7.1 ? > > > > (They are using a vanilla Mandrake Linux - either 8.0 or 8.1...) > > > > regards and apologies about the lack of specifics :-(.... > > > > Mark > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > > > >