X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id D0A839DD654 for ; Fri, 24 Mar 2006 20:53:18 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 90962-09 for ; Fri, 24 Mar 2006 20:53:21 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.227]) by postgresql.org (Postfix) with ESMTP id 679479DD653 for ; Fri, 24 Mar 2006 20:53:16 -0400 (AST) Received: by wproxy.gmail.com with SMTP id i23so916949wra for ; Fri, 24 Mar 2006 16:53:20 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=Xb/BwmEQjMEdhKWXIws+Wf118bTDkYBxo5tuFacaPtyyQciUvdBVeZ99t1MR5YP+3HCvN6pckf6XWp9xsKRkEim0EKG6G8mQf4uuu30HDrcWDarU86Ii3bXJx5DlVVz/+aNyzoGeMdhUci/32JNXiQL1OlSaOjzYjA7e0+A6wGg= Received: by 10.65.156.11 with SMTP id i11mr1247510qbo; Fri, 24 Mar 2006 16:53:19 -0800 (PST) Received: by 10.65.52.17 with HTTP; Fri, 24 Mar 2006 16:53:19 -0800 (PST) Message-ID: <1c66bda80603241653o79be7adaq8975e5d6a9498227@mail.gmail.com> Date: Sat, 25 Mar 2006 12:53:19 +1200 From: adey To: "Tom Lane" Subject: Re: Bloated pg_shdepend_depender_index Cc: "Peter Eisentraut" , pgsql-admin@postgresql.org, "Gregory Maxwell" In-Reply-To: <4553.1143212521@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_8536_14243539.1143247999821" References: <27588.1143175698@sss.pgh.pa.us> <200603241150.20021.peter_e@gmx.net> <4553.1143212521@sss.pgh.pa.us> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.911 required=5 tests=[AWL=-0.035, HTML_10_20=0.945, HTML_MESSAGE=0.001] X-Spam-Score: 0.911 X-Spam-Level: X-Archive-Number: 200603/252 X-Sequence-Number: 21009 ------=_Part_8536_14243539.1143247999821 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Two questions in this regard please? 1) Is tuple theory not the root of this problem 2) Vacuum does much the same as a traditional database reorg, and online reorgs are a reality now 1) If I understand tuple theory correctly, copies of rows are created through normal Postgres processing, that expire after a period, leaving multiple copies of redundant data to be vacuumed dead, then vacuumed out (o= r both). Most databases have been built using one copy of a row with sophisticated locking control mechanism that Postgres has some of anyway, and the industry has developed methods and designs to exploit locking to best advantage. Even with tuples, locking is still evident in Postgres. OR 2) Can vacuum full not be redesigned to run online without locking tables and users, like a conventional online reorg, eg: work on 1 data page at a time instead of locking the whole table with a shorter period at the end to lock the table and "compress" the remaining populated data pages and releas= e disk space back to the OS; or one data file at a time, and have vacuum full *per table* reduce / tidy up the wraparound value, thereby avoiding a full DB vacuum for longer periods. In this way vacuum can be performed regularly and be less intrusive. Nowadays 24x7 is more of a reality for systems and w= e can't afford to take systems down for many hours to perform regular maintenance. (It would be extremely helpful to DBA's with little OS experience or access to have more automation in PGAdmin, especially task scheduling and alerting, so SQL can be scheduled in PGAmin instead of crontab, which is usually a sysadmin function). On 3/25/06, Tom Lane wrote: > > Peter Eisentraut writes: > > Am Freitag, 24. M=E4rz 2006 05:48 schrieb Tom Lane: > >> Well, the VACUUM FULL algorithm is incapable of shrinking indexes --- > >> the only way is REINDEX, or something else that reconstructs indexes > >> from scratch, such as CLUSTER. One of the things we need to look into > >> is putting more smarts into VACUUM so that it automatically does > >> something reasonable when faced with extreme cases like these. > > > If the user is running VACUUM FULL, he has presumably determined that > the > > table is too bloated to be recovered in a graceful way, and quite likel= y > the > > indexes are going to be bloated similarly. So seemingly one might as > well > > launch a reindexing on the table after VACUUM FULL has done its thing. > > Whether that should be automatic is another question but perhaps the > advice > > should be documented somewhere? > > Actually, I wonder whether VACUUM FULL shouldn't be thrown away and > replaced by something else entirely. That algorithm only really works > nicely when just a small percentage of the rows need to be moved to > re-compact the table --- if you're moving lots of rows, it makes the > index bloat situation *worse* not better because of the transient need > for index entries pointing to both copies of moved rows. Lazy VACUUM > has become the de-facto standard for situations where there's not a huge > amount of empty space, and so it's not clear where the sweet spot is for > VACUUM FULL anymore. If you've got enough disk space, a rewrite (like > CLUSTER or ALTER TABLE) is going to blow the doors off VACUUM FULL, > let alone VACUUM FULL plus REINDEX. Not to mention that for > sufficiently huge tables, VACUUM FULL fails outright because it runs out > of RAM. > > We need to fix CLUSTER to make it MVCC-safe (ie, not discard > recently-dead rows), and it'd be nice to have something like it that > didn't worry about ordering but just did a seqscan of the source table. > Then I'd be inclined to recommend that instead of VACUUM FULL for most > cases of severe bloat. > > Unfortunately this all breaks down for shared system catalogs and the > core (nailed-in) catalogs, because we can't change their relfilenodes > and so the crash-safe CLUSTER/REINDEX approach doesn't work. We still > need a new idea or two there. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > ------=_Part_8536_14243539.1143247999821 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
Two questions in this regard please?
1) Is tuple theory not the root of this problem
2) Vacuum does much the same as a traditional database reorg, and onli= ne reorgs are a reality now
 
1) If I understand tuple theory correctly, copies of rows are created = through normal Postgres processing, that expire after a period, leaving mul= tiple copies of redundant data to be vacuumed dead, then vacuumed out (or b= oth). Most databases have been built using one copy of a row with sophistic= ated locking control mechanism that Postgres has some of anyway, and the in= dustry has developed methods and designs to exploit locking to best ad= vantage. Even with tuples, locking is still evident in Postgres.
OR
2) Can vacuum full not be redesigned to run online without lockin= g tables and users, like a conventional online reorg, eg: work on 1 data pa= ge at a time instead of locking the whole table with a shorter period at th= e end to lock the table and "compress" the remaining populated da= ta pages and release disk space back to the OS; or one data file at a time,= and have vacuum full=20 per table reduce / tidy up the wraparound value, ther= eby avoiding a full DB vacuum for longer periods. In this way vacuum can be= performed regularly and be less intrusive. Nowadays 24x7 is more of a real= ity for systems and we can't afford to take systems down for many hours to = perform regular maintenance.
 
(It would be extremely helpful to DBA's with little OS experience or a= ccess to have more automation in PGAdmin, especially task scheduling a= nd alerting, so SQL can be scheduled in PGAmin instead of crontab, which is= usually a sysadmin function).
 


 
On 3/25/06, = Tom Lane <tgl@sss.pgh.pa.us= > wrote:
Peter Eisentraut <peter_e@gmx.net> writes:
> Am Freitag,= 24. M=E4rz 2006 05:48 schrieb Tom Lane:
>> Well, the VACUUM FULL algorithm is incapable of shrinking inde= xes ---
>> the only way is REINDEX, or something else that reconst= ructs indexes
>> from scratch, such as CLUSTER.  One of = the things we need to look into
>> is putting more smarts into VACUUM so that it automatically do= es
>> something reasonable when faced with extreme cases like thes= e.

> If the user is running VACUUM FULL, he has presumably determ= ined that the
> table is too bloated to be recovered in a graceful way, and quite = likely the
> indexes are going to be bloated similarly.  So= seemingly one might as well
> launch a reindexing on the table after= VACUUM FULL has done its thing.
> Whether that should be automatic is another question but perhaps t= he advice
> should be documented somewhere?

Actually, I wonder= whether VACUUM FULL shouldn't be thrown away and
replaced by something = else entirely.  That algorithm only really works
nicely when just a small percentage of the rows need to be moved to
= re-compact the table --- if you're moving lots of rows, it makes the
ind= ex bloat situation *worse* not better because of the transient need
for index entries pointing to both copies of moved rows.  Lazy VA= CUUM
has become the de-facto standard for situations where there's not a= huge
amount of empty space, and so it's not clear where the sweet spot = is for
VACUUM FULL anymore.  If you've got enough disk space, a rewr= ite (like
CLUSTER or ALTER TABLE) is going to blow the doors off VACUUM = FULL,
let alone VACUUM FULL plus REINDEX.  Not to mention that= for
sufficiently huge tables, VACUUM FULL fails outright because it run= s out
of RAM.

We need to fix CLUSTER to make it MVCC-safe (ie, not dis= card
recently-dead rows), and it'd be nice to have something like it tha= t
didn't worry about ordering but just did a seqscan of the source table= .
Then I'd be inclined to recommend that instead of VACUUM FULL for most<= br>cases of severe bloat.

Unfortunately this all breaks down for sha= red system catalogs and the
core (nailed-in) catalogs, because we can't = change their relfilenodes
and so the crash-safe CLUSTER/REINDEX approach doesn't work.  = ;We still
need a new idea or two there.

    &= nbsp;           &nbs= p;      regards, tom lane

-----------------= ----------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

------=_Part_8536_14243539.1143247999821--