X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 224FA9FB2E1 for ; Tue, 8 Aug 2006 23:54:31 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 10518-07-2 for ; Wed, 9 Aug 2006 02:54:26 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id C9FB29FB1CC for ; Tue, 8 Aug 2006 23:54:25 -0300 (ADT) Received: from [192.168.42.113] (natpool.bovine.net [67.100.216.14]) (using TLSv1 with cipher RC4-SHA (128/128 bits)) (No client certificate requested) by noel.decibel.org (Postfix) with ESMTP id 5CAA156437; Tue, 8 Aug 2006 21:54:24 -0500 (CDT) In-Reply-To: <20060808033612.34782.qmail@web57211.mail.re3.yahoo.com> References: <20060808033612.34782.qmail@web57211.mail.re3.yahoo.com> Mime-Version: 1.0 (Apple Message framework v752.2) Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: Cc: Tom Lane , pgsql admin Content-Transfer-Encoding: 7bit From: Jim Nasby Subject: Re: disk usage spike Date: Tue, 8 Aug 2006 11:53:37 -0500 To: Geoff Parker X-Mailer: Apple Mail (2.752.2) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200608/104 X-Sequence-Number: 22647 On Aug 7, 2006, at 10:36 PM, Geoff Parker wrote: > Unfortunately I didn't notice the problem until I saw the logs > today, and by then it was back tor normal. > > Is it safe to assume this is abnormal behaviour? Probably, unless you had a transaction running for 2 days that was using space in pgsql_tmp. Another possibility would be if you killed a backend that had stuff in pgsql_tmp and it didn't clean that up for some reason, but I don't think it should be possible for that to happen. There's also the remote possibility that a table bloated due to a lot of rows being updated, and that table later ended up with a bunch of empty pages at the end of the table, which vacuum then removed. > ----- Original Message ---- > From: Tom Lane > To: Geoff Parker > Cc: pgsql admin > Sent: Tuesday, August 8, 2006 12:12:44 PM > Subject: Re: [ADMIN] disk usage spike > > Geoff Parker writes: > > I have a database with about 155GB of binary data in large > objects and 15GB of data in other tables. In compressed form, it > consumes about 60GB on disk. On friday, the amount of disk usage > increased to 240GB (over a period of 5 minutes), stayed at 240GB > for 2 days, and then returned to 60GB (again over a period of about > 5 minutes). > > It's impossible to say much with only that amount of information. > If we > knew what part of the database had bloated (table? index? WAL log?) we > could perhaps offer some wisdom. Next time, use "du" and and related > tools to see where the space went. > > regards, tom lane > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: explain analyze is your friend > > -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461