Date: Wed, 20 Mar 2013 16:55:34 +0000 From: Dan Thomas <godders@gmail.com> To: Eduardo Morras <emorrasg@yahoo.es> Cc: freebsd-questions@freebsd.org Subject: Re: "Leaking" disk space Message-ID: <CAG8duQ0MWGg9MmDNwFJV90DXXNUfJc2YMRkV3z0BZGk8ZrMnEw@mail.gmail.com> In-Reply-To: <20130320170241.278d4fb515536cb6669ae0bf@yahoo.es> References: <CAG8duQ1cjVWbWDUBEoEH6uLEWkU5mqgs%2BnHjDGuvPMvpW8qLSQ@mail.gmail.com> <20130320170241.278d4fb515536cb6669ae0bf@yahoo.es>
next in thread | previous in thread | raw e-mail | index | archive | help
> a) Where do you have the wal files?
pg_xlog is symlinked to /usr/local/pglog/pg_xlog (ie, out of the
partition mounted as /usr/local/pgsql which is exhibiting this
behaviour).
b) Are you sure that unused/old wal files are erased?
As above, but yes they seem to be being deleted properly
c) Do you have any postgres log level activated (like the ones used
for long queries)?
Yes we have slow query logging enabled. pg_log is symlinked out of
that partition to /usr/local/pglog/pg_log as well.
d) Does your queries have GROUP BY on very big data sets? Those create
big temporal data files.
Yes we do a lot of that! However there are definitely no unlinked
files, and the problem doesn't go away when pg is shut down. However a
reboot does fix it.
e) With question a) and b), do you use streaming replication?
Yes we do. This problem is not present on the warm standby servers
that are being streamed to. We have failed over to the warm standbys
previously (we're currently doing this regularly to work around the
problem without too much downtime). Once we switch the warm standby to
primary, it begins leaking space.
On 20 March 2013 16:02, Eduardo Morras <emorrasg@yahoo.es> wrote:
> On Wed, 20 Mar 2013 15:23:18 +0000
> Dan Thomas <godders@gmail.com> wrote:
>
>> Hi Guys,
>>
>> We're seeing a problem with some of our FreeBSD/PostgreSQL servers
>> "leaking" quite significant amounts of disk space:
>>
>> > df -h /usr/local/pgsql/
>> Filesystem Size Used Avail Capacity Mounted on
>> /dev/mfid1s1d 1.1T 772G 222G 78% /usr/local/pgsql
>>
>> > du -sh /usr/local/pgsql/
>> 741G /usr/local/pgsql/
>>
>> Stopping Postgres doesn't fix it, but rebooting does which points at
>> the OS rather than PG to me. However, the leak is only apparent in the
>> dedicated pgsql partition, and only on our database servers, so
>> PostgreSQL seems to at least be involved. The partition itself is a
>> relatively standard UFS partition:
>>
>> > grep /usr/local/pgsql /etc/fstab
>> /dev/mfid1s1d /usr/local/pgsql ufs rw 2 2
>>
>> > tunefs -p /usr/local/pgsql/
>> tunefs: POSIX.1e ACLs: (-a) disabled
>> tunefs: NFSv4 ACLs: (-N) disabled
>> tunefs: MAC multilabel: (-l) disabled
>> tunefs: soft updates: (-n) enabled
>> tunefs: gjournal: (-J) disabled
>> tunefs: trim: (-t) disabled
>> tunefs: maximum blocks per file in a cylinder group: (-e) 2048
>> tunefs: average file size: (-f) 16384
>> tunefs: average number of files in a directory: (-s) 64
>> tunefs: minimum percentage of free space: (-m) 8%
>> tunefs: optimization preference: (-o) time
>> tunefs: volume label: (-L)
>>
>> LSOF isn't showing any open files:
>>
>> > lsof +L /usr/local/pgsql/ | awk '{ print $8 }' | grep 0 | wc -l
>> 0
>>
>> We're not creating filesystem snapshots:
>>
>> > find /usr/local/pgsql/ -flags snapshot
>> >
>>
>> Not all of our servers are leaking space, it's only the more
>> recently-installed systems. Here's a quick breakdown of versions:
>>
>> FreeBSD PostgreSQL Leaking?
>> 8.0 8.4.4 no
>> 8.2 9.0.4 no
>> 8.3 9.1.4 yes
>> 8.3 9.2.3 yes
>> 9.1 9.2.3 yes
>>
>> Any ideas what's going on here, or where we could start debugging?
>
> Somethings to check:
>
> a) Where do you have the wal files?
> b) Are you sure that unused/old wal files are erased?
> c) Do you have any postgres log level activated (like the ones used for long queries)?
> d) Does your queries have GROUP BY on very big data sets? Those create big temporal data files.
> e) With question a) and b), do you use streaming replication?
>
>>
>> Thanks,
>>
>> Dan
>
> --- ---
> Eduardo Morras <emorrasg@yahoo.es>
> _______________________________________________
> freebsd-questions@freebsd.org mailing list
> http://lists.freebsd.org/mailman/listinfo/freebsd-questions
> To unsubscribe, send any mail to "freebsd-questions-unsubscribe@freebsd.org"
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?CAG8duQ0MWGg9MmDNwFJV90DXXNUfJc2YMRkV3z0BZGk8ZrMnEw>