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


19 Feb 2002 21:37:45 +1300
Mark kirkwood <markir@slingshot.co.nz>
Transient Disk Usage Higher In 7.2 ?
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
>


Tue, 19 Feb 2002 17:03:29 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: [SQL] Transient Disk Usage Higher In 7.2 ?
Mark kirkwood <markir@slingshot.co.nz> writes:
> Is there an "expectation" that 7.2 will use temp space more aggressively
> than 7.1 ?

No.

You sure they're not simply failing to allow for the fact they've now
loaded a duplicate database onto the machine?

			regards, tom lane

Tue, 19 Feb 2002 09:54:57 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Transient Disk Usage Higher In 7.2 ? 
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
> >
> 
> 



23 Feb 2002 13:29:56 +1300
Mark kirkwood <markir@slingshot.co.nz>
Re: [SQL] Transient Disk Usage Higher In 7.2 ?