Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id B9CDE2E00D3 for ; Fri, 22 Feb 2008 03:57:45 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 11777-08 for ; Fri, 22 Feb 2008 03:57:43 -0400 (AST) Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.230]) by postgresql.org (Postfix) with ESMTP id D51352E00CD for ; Fri, 22 Feb 2008 03:57:42 -0400 (AST) Received: by wx-out-0506.google.com with SMTP id r21so291989wxc.28 for ; Thu, 21 Feb 2008 23:57:42 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references:x-google-sender-auth; bh=8nQe2OzATq6B8aVPQy6DCfq+g0wHZSC9KSddf+V57ek=; b=ZBGcvKSlgEfzu2dA+5CM1+ZhtqR6U0FymeqBJuk68I9uT7G4aFvCQU2SSLhWXsGuEUoZvWQLa0UoEGqoF3lw8X0monBMSQqMeNXIzZoZpGjql5+AkNqYIcaMzihVi5a9UQYhga0sK7DZ8O1ZYrzuJwPUSdp0MTAHnV8M9mYFo24= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references:x-google-sender-auth; b=NEG3LCdmarIQaCAtzZ0iR4f1cC9+652L1ZhIycwL9e0IaVYCUCbNI6sVrcoTi25/EFXGBvx66qjuAdKx6T45bwbdBIR8xA+hHqpIYsZf/dlfEltXw9qV1dwPL1OpjVXsnhdd6fnlNRl61QdVkaFmQPKgog1svW5rJmGnj6way5Y= Received: by 10.114.80.4 with SMTP id d4mr4816383wab.44.1203667061205; Thu, 21 Feb 2008 23:57:41 -0800 (PST) Received: by 10.114.177.8 with HTTP; Thu, 21 Feb 2008 23:57:41 -0800 (PST) Message-ID: Date: Fri, 22 Feb 2008 10:57:41 +0300 From: if To: Decibel! Subject: Re: vaccuming very large table problem Cc: pgsql-admin@postgresql.org In-Reply-To: <6F927D71-9FF4-41D3-B4D3-1033AD3CD60F@decibel.org> MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <6F927D71-9FF4-41D3-B4D3-1033AD3CD60F@decibel.org> X-Google-Sender-Auth: 5d77b239d85a6fd9 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200802/274 X-Sequence-Number: 28348 On Fri, Feb 22, 2008 at 2:30 AM, Decibel! wrote: > > 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 > > > Thank you for answering! The catch-22 was that we still had about 20G of free space (it's 4-5 days to "X" hour left, speaking in time ), and vacuuming the whole pg_largeobject would take more than 5 days even under no other load, and we have plenty of load on worktime, so we couldn't do this anyway. Deleting stuff was inefficient too, it's just _too_ slow. I still do not understand schema fully, but there are lots of constraints, and simple "delete message" action was in fact rescanning all the database. The index itself on pg_largeobject was using more than 8 gigs of space, so it was a one, big, unbelievable real mess ;-) Also, this is vendor patched postgres instance, and improve something without access to the source is way beyond my skills. Here's how i solved this, if someone interested: oid2name helped to locate files on disk. i simply got binary from debian postgres-7.4 package. i did a quick select and found which files i can simply remove (i.e relation pg_largeobject resides in 1G files $pgbase/base/16404.x, where x between 1 and 570), so i just deleted first 400 or so (previously checked up that there's only old data there), renamed ".x" part accordingly and started postmaster. Then some reindexing/vacuuming, and voila ;-) And yes, upgrading is class "B" priority now. Comparing 8.3 to 7.4.8 is like comparing Postgres to Mysql, imo ;-) regards, if