pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Transient Disk Usage Higher In 7.2 ?
4+ messages / 3 participants
[nested] [flat]

* Transient Disk Usage Higher In 7.2 ?
@ 2002-02-19 08:37  Mark kirkwood <markir@slingshot.co.nz>
  0 siblings, 2 replies; 4+ messages in thread

From: Mark kirkwood @ 2002-02-19 08:37 UTC (permalink / raw)
  To: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: [SQL] Transient Disk Usage Higher In 7.2 ?
@ 2002-02-19 09:03  Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  parent: Mark kirkwood <markir@slingshot.co.nz>
  1 sibling, 1 reply; 4+ messages in thread

From: Christopher Kings-Lynne @ 2002-02-19 09:03 UTC (permalink / raw)
  To: Mark kirkwood <markir@slingshot.co.nz>; +Cc: Hackers <pgsql-hackers@postgresql.org>

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
>




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Transient Disk Usage Higher In 7.2 ?
@ 2002-02-19 14:54  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Mark kirkwood <markir@slingshot.co.nz>
  1 sibling, 0 replies; 4+ messages in thread

From: Tom Lane @ 2002-02-19 14:54 UTC (permalink / raw)
  To: Mark kirkwood <markir@slingshot.co.nz>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: [SQL] Transient Disk Usage Higher In 7.2 ?
@ 2002-02-23 00:29  Mark kirkwood <markir@slingshot.co.nz>
  parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  0 siblings, 0 replies; 4+ messages in thread

From: Mark kirkwood @ 2002-02-23 00:29 UTC (permalink / raw)
  To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Hackers <pgsql-hackers@postgresql.org>; pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2002-02-23 00:29 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-02-19 08:37 Transient Disk Usage Higher In 7.2 ? Mark kirkwood <markir@slingshot.co.nz>
2002-02-19 09:03 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-02-23 00:29   ` Mark kirkwood <markir@slingshot.co.nz>
2002-02-19 14:54 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox