Re: "Leaking" disk space on FreeBSD servers - Mailing list pgsql-general

> How long does it take for you to accumulate this "leak"?

It grows at between 2 and 4 gigabytes per day on average. It seems to
be related to load on the database, as it grows slower over the
weekends when the servers are under less load. Here's a graph that
shows growth of one server (from reboot to about ~30gb difference)
over the last couple of weeks:
http://i.imgur.com/AwzQ46j.png

The flatter parts are the weekends, but otherwise it's reasonably constant.

> That is really the only way you get these discrepancies. lsof *should* have showed them to you.
> Try fstat in case here's some bug in lsof.

After a bit of messing around with fstat and find (it doesn't make it
easy!) I've confirmed all the inodes fstat is reporting exist on disk,
which backs up lsof. I also confirmed both lsof and fstat were
detecting unlinked files by manually holding a file open and unlinking
it. Can't see much evidence that it's an open file.

> Also, your tunefs output seems to be not from FreeBSD 9.1

That example was from an 8.3 machine (it's the one I'm testing with as
it's got the biggest disk usage deficit). The 9.1 box has this:

    tunefs: soft update journaling: (-j)                       enabled

..but is still exhibiting this behaviour.

> FWIW, I do not observe this behaviour

We actually have another FreeBSD8.3/PG9.1 machine under different (but
similar) load that *doesn't* demonstrate this behaviour. There's
nothing obvious in the differences in usage patterns that we can see
(we're not using any exotic features or anything), but it certainly
suggests that it's *something* related to PG or our usage of it.

On 20 March 2013 14:11, Vick Khera <vivek@khera.org> wrote:
>
> On Wed, Mar 20, 2013 at 7:49 AM, Dan Thomas <godders@gmail.com> wrote:
>>
>> Not all of our servers are leaking space, it's only the more
>> recently-installed systems. Here's a quick breakdown of versions:
>
>
> FWIW, I do not observe this behavior. My database has very heavy write load,
> and old data is purged after it is aged about 7 months, so I do get lots of
> fragmentation. However, I do not have any disk space "phantom" loss.
>
> How long does it take for you to accumulate this "leak"?  My first instinct
> is that you have unlinked files still referenced by some application. That
> is really the only way you get these discrepancies. lsof *should* have
> showed them to you.  Try fstat in case there's some bug in lsof.
>
> Also, your tunefs output seems to be not from FreeBSD 9.1. Specifically, it
> is not emitting this line:
>
> tunefs: soft update journaling: (-j)                       disabled
>
> It is a very useful option to turn on for large file systems. I can recover
> a 6TB file system in about 5 seconds on a crash reboot with that on.
>
>
>
> [root@d04]# ps axuw34214
> USER    PID %CPU %MEM     VSZ    RSS TT  STAT STARTED    TIME COMMAND
> pgsql 34214  0.0  0.5 5426964 154484  0- S    28Feb13 1:30.66
> /usr/local/bin/postgres -D /u/data/postgres
> [root@d04]# df -h /u/data
> Filesystem          Size    Used   Avail Capacity  Mounted on
> /dev/ufs/ramdisk    707G    137G    513G    21%    /u/data
> [root@d04]# du -sh /u/data
> 137G /u/data
> [root@d04]# uname -a
> FreeBSD d04.m1e.net 9.1-RELEASE FreeBSD 9.1-RELEASE #1 r243808: Mon Dec  3
> 09:56:27 EST 2012
> vivek@lorax.kcilink.com:/usr/obj/u/lorax1/usr9/src/sys/KCI64  amd64
> [root@d04]# uptime
>  9:50AM  up 74 days, 17:36, 1 user, load averages: 0.21, 0.18, 0.17
> [root@d04]# psql --version
> psql (PostgreSQL) 9.2.3
> [root@d04]#