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
> >
>
>