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
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
Show quoted text
-----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 failedHowever 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?
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
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
Show quoted text
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 failedHowever 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?