From owner-freebsd-questions@FreeBSD.ORG Wed Mar 20 16:02:49 2013 Return-Path: Delivered-To: freebsd-questions@freebsd.org Received: from mx1.freebsd.org (mx1.FreeBSD.org [8.8.178.115]) by hub.freebsd.org (Postfix) with ESMTP id AB59EFB0 for ; Wed, 20 Mar 2013 16:02:49 +0000 (UTC) (envelope-from emorrasg@yahoo.es) Received: from nm4-vm0.bullet.mail.ird.yahoo.com (nm4-vm0.bullet.mail.ird.yahoo.com [77.238.189.211]) by mx1.freebsd.org (Postfix) with SMTP id E51FE133 for ; Wed, 20 Mar 2013 16:02:47 +0000 (UTC) Received: from [212.82.105.224] by nm4.bullet.mail.ird.yahoo.com with NNFMP; 20 Mar 2013 16:02:45 -0000 Received: from [46.228.39.68] by tm20.bullet.mail.ird.yahoo.com with NNFMP; 20 Mar 2013 16:02:45 -0000 Received: from [127.0.0.1] by smtp105.mail.ir2.yahoo.com with NNFMP; 20 Mar 2013 16:02:45 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.es; s=s1024; t=1363795365; bh=7a/NeVlhpsndrBrkdN2W4HhLFbcJiaJzfbih1bVMIjw=; h=X-Yahoo-Newman-Id:X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:X-Rocket-Received:Date:From:To:Subject:Message-Id:In-Reply-To:References:X-Mailer:Mime-Version:Content-Type:Content-Transfer-Encoding; b=u42Hf0VS9Psz/ZEF+B0AWSgkapChyfVF8AcAkdkLfDzhVSSQVaSEU/PCTfSYsIUfojt5dRtcyytlBsG8+r9ecEkoQMP9thmLMi8ZaYzRj9A0R+niJwQU0JrsH4j7dZzehB1oNa4IFl8FRSjQdhKa+thK5uhnxik/7ikU/3hpemA= X-Yahoo-Newman-Id: 322594.16178.bm@smtp105.mail.ir2.yahoo.com X-Yahoo-Newman-Property: ymail-3 X-YMail-OSG: oPPY83cVM1mxZP65h4hciAcaoAteFld_7v45CBqa98.mley xBGeJNIbBizJrTaL88LDE9wO47w0wUt.yiyLXsOMPVrzUDEOvA.3Fy9Eqk93 vHhKoRsiRDRnhCVAGdCw8PXS3V_c_7km0QOE5lypSX3Bgou1f1QxJuhSDr6g 6lAQ99EFlnCHZ9IYD9saoc.H8zKD4rJQFMYsh2jhw7SYDZxLyJRlAXOgKjCL S0bUv9DpT_ggCGvTgRZ.Hdgutz3jgA9v7up7p7aDBRPUe.H8wnF9FURdU.7E bgg2SzhbyVqcQ815oA7IL8MLoXZy7L5yeC1U1C3cSKRCPYgkWEZLyI8kB.yc IMLzVRX742a0i7znHqV6cC40uoftwKI3n_82QhVCFfrrpDLj6Zx7um56mAGB FE6Ki52DfO8LX X-Yahoo-SMTP: mX392iiswBAeJNdO_s.EW62LZDJR X-Rocket-Received: from camibar.emorras.eu (emorrasg@85.219.45.167 with plain) by smtp105.mail.ir2.yahoo.com with SMTP; 20 Mar 2013 16:02:42 +0000 UTC Date: Wed, 20 Mar 2013 17:02:41 +0100 From: Eduardo Morras To: freebsd-questions@freebsd.org Subject: Re: "Leaking" disk space Message-Id: <20130320170241.278d4fb515536cb6669ae0bf@yahoo.es> In-Reply-To: References: X-Mailer: Sylpheed 3.3.0 (GTK+ 2.24.6; amd64-portbld-freebsd9.1) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-BeenThere: freebsd-questions@freebsd.org X-Mailman-Version: 2.1.14 Precedence: list List-Id: User questions List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Wed, 20 Mar 2013 16:02:49 -0000 On Wed, 20 Mar 2013 15:23:18 +0000 Dan Thomas 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