Date: Mon, 18 Dec 2006 16:18:05 -0500
From: Bill Moran <wmoran@collaborativefusion.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Advice on selecting good values for work_mem?
Message-Id: <20061218161805.04699b10.wmoran@collaborativefusion.com>
In-Reply-To: <16388.1165510978@sss.pgh.pa.us>
References: <20061207113531.98095b27.wmoran@collaborativefusion.com>
	<16388.1165510978@sss.pgh.pa.us>
Organization: Collaborative Fusion
Mime-Version: 1.0
Content-Type: multipart/mixed; boundary="=_wingspan-37795-1166476689-0001-2"

This is a MIME-formatted message.  If you see this text it means that your
E-mail software does not support MIME-formatted messages.

--=_wingspan-37795-1166476689-0001-2
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

In response to Tom Lane <tgl@sss.pgh.pa.us>:

> Bill Moran <wmoran@collaborativefusion.com> writes:
> > Does the creation of a temp file trigger any logging?
> 
> No; but it wouldn't be hard to add some if you wanted.  I'd do it at
> deletion, not creation, so you could log the size the file reached.
> See FileClose() in src/backend/storage/file/fd.c.

Is this along the lines of what you were thinking?  Is this acceptable
to get pulled into the tree (maintaining local patches sucks ;)  I've
only been using this patch a day and I'm already giddy about how much
it helps tuning work memory sizes ...

-- 
Bill Moran
Collaborative Fusion Inc.

--=_wingspan-37795-1166476689-0001-2
Content-Type: text/x-diff; name="fd.diff"; charset=iso-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="fd.diff"

*** fd.c.prev	Mon Dec 18 16:09:51 2006
--- fd.c	Mon Dec 18 16:09:31 2006
***************
*** 939,944 ****
--- 939,945 ----
  FileClose(File file)
  {
  	Vfd		   *vfdP;
+ 	struct stat  u	filestats;
  
  	Assert(FileIsValid(file));
  
***************
*** 968,973 ****
--- 969,982 ----
  	{
  		/* reset flag so that die() interrupt won't cause problems */
  		vfdP->fdstate &= ~FD_TEMPORARY;
+ 	if (fstat(vfdP->fd, &filestats)) {
+ 		ereport(WARNING,
+ 			(errmsg("A temporary file of %d bytes was used",
+ 				filestats.st_size),
+ 				errhint("You many need to increase work_mem.")));
+ 	} else {
+ 		elog(ERROR, "Could not stat \"%s\": %m", vfdP->fileName);
+ 	}
  		if (unlink(vfdP->fileName))
  			elog(LOG, "failed to unlink \"%s\": %m",
  				 vfdP->fileName);

--=_wingspan-37795-1166476689-0001-2--