Hi guys,

Does anyone else have this problem?

We have softupdates turned on on our data dir.  (Soon to be turned off 
due to these issues).

The partition is 12GB.  'df' says that we're using 12 and a bit GB but 
'du' says we're using 2GB (which we really are).

It seems that perhaps softupdates is caching some stuff, or preventing 
something from being written properly, etc.

The funny thing is that this was never a problem until we upgraded to 
7.4.  Has something changed in the way file writes or syncs are done?

Chris


Tue, 30 Mar 2004 09:54:19 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
PostgreSQL and FreeBSD SoftUpdates

what version of FreeBSD are you using?  I'm running 4.9-STABLE with
softupdates on my db file system ...

On Tue, 30 Mar 2004, Christopher Kings-Lynne wrote:

> Hi guys,
>
> Does anyone else have this problem?
>
> We have softupdates turned on on our data dir.  (Soon to be turned off
> due to these issues).
>
> The partition is 12GB.  'df' says that we're using 12 and a bit GB but
> 'du' says we're using 2GB (which we really are).
>
> It seems that perhaps softupdates is caching some stuff, or preventing
> something from being written properly, etc.
>
> The funny thing is that this was never a problem until we upgraded to
> 7.4.  Has something changed in the way file writes or syncs are done?
>
> Chris
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>

----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664

Mon, 29 Mar 2004 22:40:27 -0400 (AST)
"Marc G. Fournier" <scrappy@postgresql.org>
Re: PostgreSQL and FreeBSD SoftUpdates
> what version of FreeBSD are you using?  I'm running 4.9-STABLE with
> softupdates on my db file system ...

FreeBSD goddard.calorieking.com 4.9-STABLE FreeBSD 4.9-STABLE #2: Mon 
Jan 26 23:23:17 EST 2004 
wg@goddard.calorieking.com:/usr/obj/usr/src/sys/GODDARD  i386

We're not 100% sure it's softupdates, but we can't see anything else 
that it could be.

Chris


Tue, 30 Mar 2004 10:44:21 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: PostgreSQL and FreeBSD SoftUpdates
On Tue, 30 Mar 2004, Christopher Kings-Lynne wrote:

> > what version of FreeBSD are you using?  I'm running 4.9-STABLE with
> > softupdates on my db file system ...
>
> FreeBSD goddard.calorieking.com 4.9-STABLE FreeBSD 4.9-STABLE #2: Mon
> Jan 26 23:23:17 EST 2004
> wg@goddard.calorieking.com:/usr/obj/usr/src/sys/GODDARD  i386
>
> We're not 100% sure it's softupdates, but we can't see anything else
> that it could be.

Right off the top of my head, it almost sounds like a file is being held
open after its been deleted ... we went through that with the new aspseek
a little while back, where 170gig just disappeared overnight, but du
showed hardly any disk space being used ...

Does restarting the database server (not rebooting, just restarting the
postmaster) free up the disk space?

----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664

Mon, 29 Mar 2004 23:28:15 -0400 (AST)
"Marc G. Fournier" <scrappy@postgresql.org>
Re: PostgreSQL and FreeBSD SoftUpdates
> Right off the top of my head, it almost sounds like a file is being held
> open after its been deleted ... we went through that with the new aspseek
> a little while back, where 170gig just disappeared overnight, but du
> showed hardly any disk space being used ...
> 
> Does restarting the database server (not rebooting, just restarting the
> postmaster) free up the disk space?

No - have to reboot.  That's probably because of softupdates though.

Chris

Tue, 30 Mar 2004 11:40:27 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: PostgreSQL and FreeBSD SoftUpdates
On Tue, 30 Mar 2004, Christopher Kings-Lynne wrote:

> > Right off the top of my head, it almost sounds like a file is being held
> > open after its been deleted ... we went through that with the new aspseek
> > a little while back, where 170gig just disappeared overnight, but du
> > showed hardly any disk space being used ...
> >
> > Does restarting the database server (not rebooting, just restarting the
> > postmaster) free up the disk space?
>
> No - have to reboot.  That's probably because of softupdates though.

'k, *shouldn't* require a reboot ... but, what I'd try is to do what
you've thought .. disable softupdates and see if you can recreate ... if
killing off the process auto-reclaims the space fast, then it sounds like
a stale file being held open (log file being rotated improperly?) ...

----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664

Tue, 30 Mar 2004 00:06:19 -0400 (AST)
"Marc G. Fournier" <scrappy@postgresql.org>
Re: PostgreSQL and FreeBSD SoftUpdates
> 'k, *shouldn't* require a reboot ... but, what I'd try is to do what
> you've thought .. disable softupdates and see if you can recreate ... if
> killing off the process auto-reclaims the space fast, then it sounds like
> a stale file being held open (log file being rotated improperly?) ...

Log file's on a different partition...

Chris


Tue, 30 Mar 2004 12:21:05 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: PostgreSQL and FreeBSD SoftUpdates
>>> Right off the top of my head, it almost sounds like a file is being 
>>> held
>>> open after its been deleted ... we went through that with the new 
>>> aspseek
>>> a little while back, where 170gig just disappeared overnight, but du
>>> showed hardly any disk space being used ...
>>>
>>> Does restarting the database server (not rebooting, just restarting 
>>> the
>>> postmaster) free up the disk space?
>>
>> No - have to reboot.  That's probably because of softupdates though.
>
> 'k, *shouldn't* require a reboot ... but, what I'd try is to do what
> you've thought .. disable softupdates and see if you can recreate ... 
> if
> killing off the process auto-reclaims the space fast, then it sounds 
> like
> a stale file being held open (log file being rotated improperly?) ...

Install the latest version of lsof(8) and see if there are any stale 
files being held open.  I've got databases on FreeBSD 4.X and 5.X with 
softupdates on both and haven't had a problem.  I'm wondering if your 
database is doing something "exotic" that hasn't been tickled.  The 
first thing that comes to mind is, are you using deferred constraints?  
Second, if it is a soft updates issue, then a reboot isn't necessary 
(as Marc says)... you should be able to stop the database and type df 
-k && sync && sleep 30 && df -k see space being freed up.  -sc

-- 
Sean Chittenden


Mon, 29 Mar 2004 20:27:54 -0800
Sean Chittenden <sean@chittenden.org>
Re: PostgreSQL and FreeBSD SoftUpdates