pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Kevin Johnson <Kevin.Johnson@noaa.gov>
To: Jim Nasby <jimn@enterprisedb.com>
Cc: Bruno Wolff III <bruno@wolff.to>
Cc: pgsql-admin@postgresql.org, Sean Webb <Sean.Webb@noaa.gov>
Subject: Re: Disk space consumed by pk not returned after vacuum or
Date: Fri, 06 Oct 2006 06:39:40 -0400
Message-ID: <4526326C.2000009@noaa.gov> (raw)
In-Reply-To: <D55BE3CB-D3EF-4089-BFAA-BDD166CD2A5C@enterprisedb.com>
References: <45080D4C.2020709@noaa.gov>
	<20060922170557.GB571@wolff.to>
	<452531AB.2050709@noaa.gov>
	<D55BE3CB-D3EF-4089-BFAA-BDD166CD2A5C@enterprisedb.com>

You are correct, it is 7.4 we are running on a RHEL3 system.
The database itself is vacuumed via cron 6 times a day. 

Jim Nasby wrote:
> I didn't see you mention what version you're running; index bloat 
> shouldn't be a big issue in 7.4 and above. You also didn't mention how 
> often you're vacuuming the table. If you don't vacuum the table 
> frequently enough, you're going to get bloat, plain and simple.
>
> On Oct 5, 2006, at 11:24 AM, Kevin Johnson wrote:
>> Thank you for the suggestion, Bruno.  The clustering did the trick in 
>> reducing the current disk usage, however eventually the disk space 
>> get consumed once more.  I fear that we may just need to update the 
>> version of Postgres to help alleviate index bloat!
>>
>> Bruno Wolff III wrote:
>>> On Wed, Sep 13, 2006 at 09:53:16 -0400, Kevin Johnson 
>>> <Kevin.Johnson@noaa.gov> wrote:
>>>> We have a database, which consistently consumes more and more of 
>>>> the disk space in it's lvol until it reaches 100%. So far, we have 
>>>> tried to run a full vacuum on the database, with limited success. 
>>>> Eventually, we had to drop and reload the database with the same 
>>>> data inside. It brought the disk usage down to 73%. It then began 
>>>> to creep once more toward 100%. After some research, I was able to 
>>>> use the pg_class catalog to find that the items which are expanding 
>>>> the quickest are  primary key (btree) indexes. I attempted to run a 
>>>> REINDEX on one of the tables with the pk taking up the largest 
>>>> amount of space. The usage according to pg_class dropped 
>>>> dramatically, however the disk space was not returned to the 
>>>> system. So I attempted another full vacuum afterwards, and still 
>>>> nothing was returned to the system. These tables are updated 
>>>> extremely frequently (although their total number of rows is close 
>>>> to constant), which is my guess as to why the pk indexes increase 
>>>> so rapidly in terms of their disk usage. Unfortunately, PostgreSQL 
>>>> knowledge is limited, and I was wondering if anyone had experienced 
>>>> something similar / knows what else we can do to return this disk 
>>>> space back to the system?
>>> This is possibly index bloat due to new keys always being larger 
>>> than existing keys. This was fixed in later releases. There is still 
>>> some potential for bloat due to fragmentation, but I believe that 
>>> has a constant bound. You might try using the cluster command. I 
>>> think that will both clean up the indexes and remove dead rows and 
>>> do it faster than using a vacuum full and reindexing. The downside 
>>> is that the table will be unavailable during the cluster which might 
>>> be a deal breaker for you.
> -- 
> Jim Nasby                                            jim@nasby.net
> EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)
>
>
> -- 
> Jim Nasby                                    jimn@enterprisedb.com
> EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)
>
>

-- 
Kevin Johnson
Raytheon
AWIPS Sr. Systems Engineer
NWS Network Control Facility
p: 301.713.9362x325
f: 301.713.1905

Attachments:

Kevin.Johnson.vcf (text/x-vcard, 284B) download begin:vcard fn:Kevin Johnson n:Johnson;Kevin org:Raytheon AWIPS Team adr:SMCC II;;;1325 East West Highway;Silver Spring;MD;20910 email;internet:Kevin.Johnson@noaa.gov title:Sr. Systems Engineer tel;work:301.713.9362 x325 tel;cell:301.787.0648 x-mozilla-html:TRUE version:2.1 end:vcard

view thread (7+ messages)  latest in thread

Message-ID: <4526326C.2000009@noaa.gov>
Permalink:  ../4526326C.2000009@noaa.gov/
Also on:    postgresql.org/message-id/4526326C.2000009@noaa.gov

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: Kevin.Johnson@noaa.gov, jimn@enterprisedb.com, bruno@wolff.to, Sean.Webb@noaa.gov
  Subject: Re: Disk space consumed by pk not returned after vacuum or
  In-Reply-To: <4526326C.2000009@noaa.gov>
  References: <45080D4C.2020709@noaa.gov> <20060922170557.GB571@wolff.to> <452531AB.2050709@noaa.gov> <D55BE3CB-D3EF-4089-BFAA-BDD166CD2A5C@enterprisedb.com> <4526326C.2000009@noaa.gov>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox