pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Decibel! <decibel@decibel.org>
To: zeylienospam@gmail.com
Cc: pgsql-admin@postgresql.org
Subject: Re: vaccuming very large table problem
Date: Thu, 21 Feb 2008 17:30:03 -0600
Message-ID: <6F927D71-9FF4-41D3-B4D3-1033AD3CD60F@decibel.org> (raw)
In-Reply-To: <f37dba8b0802150256x4bfe638duc921ef686b623e04@mail.gmail.com>
References: <f37dba8b0802150256x4bfe638duc921ef686b623e04@mail.gmail.com>

On Feb 15, 2008, at 4:56 AM, if wrote:
> We use postgresql as a backend to our email gateway, and keep al
> emails for in database. Using postgres version 7.4.8 (yes, i know it's
> old), and rather specific table schema (the application was desined
> that way) -- all emails split into 2kb parts and fed up into
> pg_largeobject. So, long story short, i now have a catch-22 situation
> -- database using about 0.7TB and we are running out of space ;-)
> I can delete some old stuff but i cannot run full vacuum to reclaim
> disk space (i takes way more than full weekend) and i also cannot
> dump/restore as there's no free space (2x database)
>
> So, with this restrictions aplied, i figured out that i can somehow
> zero out all old entries in pg_largeobject or even physically delete
> these files, and rebuild all neccesary indexes.
>
> What is the best way to do this?
> IMO, dd'ing /dev/zero to this files will cause postgres to
> reinitialize these empty blocks, and after this will still need to
> vacuum full over 0.7TB, am i right?
> And if i delete them, then start postmaster, there'll be lots of
> complaining but will the latest data be saved?
>
> How can i delete, for instance, first 70% of data reasonably fast?

You're still inserting new email, right? If so, why are you worried  
about reclaiming space? Just delete some stuff, let vacuum clean it  
up, and make sure that your FSM is big enough (easiest way to do that  
is to run vacuumdb -av).

You'll also want to periodically reindex, especially in 7.4.

And yes, upgrade. At a minimum you need to get to the lastest 7.4,  
which doesn't require anything special.
-- 
Decibel!, aka Jim C. Nasby, Database Architect  decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Attachments:

  smime.p7s (application/pkcs7-signature, 2.5K) download

view thread (3+ messages)  latest in thread

Message-ID: <6F927D71-9FF4-41D3-B4D3-1033AD3CD60F@decibel.org>
Permalink:  ../6F927D71-9FF4-41D3-B4D3-1033AD3CD60F@decibel.org/
Also on:    postgresql.org/message-id/6F927D71-9FF4-41D3-B4D3-1033AD3CD60F@decibel.org

 · 

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: decibel@decibel.org, zeylienospam@gmail.com
  Subject: Re: vaccuming very large table problem
  In-Reply-To: <6F927D71-9FF4-41D3-B4D3-1033AD3CD60F@decibel.org>
  References: <f37dba8b0802150256x4bfe638duc921ef686b623e04@mail.gmail.com> <6F927D71-9FF4-41D3-B4D3-1033AD3CD60F@decibel.org>

* 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