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
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