From: Rajesh Kumar Mallah. <mallah@trade-india.com>
To: Brian McCane <bmccane@mccons.net>
To: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: VACUUM FULL
Date: Sat, 11 May 2002 10:57:50 +0530
Message-ID: <200205111057.50407.mallah@trade-india.com> (raw)
In-Reply-To: <20020510185436.F62674-100000@fw.mccons.net>
References: <20020510185436.F62674-100000@fw.mccons.net>
Hi Brian ,
are you performing full text search in any case?
Apart from optimizing the TABLE/INDEXES (thru VACUUM measures)
i feel using text indexes provided by contrib/tsearch can also
lead to significant improvement in search performance.
regds
mallah.
On Saturday 11 May 2002 05:48 am, Brian McCane wrote:
> Okay, I guess I misunderstood something about "VACUUM FULL". My
> understanding was that a VACUUM (without FULL), marked unused records for
> reuse. VACUUM FULL moved records from the end of a file to the holes
> where unused records were stored and truncated the file to free disk
> space. So far so good, but....
>
> I have had continued loss of performance on one of my largest tables
> (600,000,000 records). There are 4 fields in a record like such:
>
> CREATE TABLE foo (
> a int not null references bar(a) on delete cascade on update no action,
> b int not null references baz(b) on delete cascade on update no action,
> c int,
> d smallint,
> primary key(a, b)) ;
> CREATE INDEX foo_ac ON foo (a,c) ;
> CREATE INDEX foo_ad on foo (a,d) ;
> And there are 3 triggers which fire before insert/delete/update.
>
> I INSERT/UPDATE/DELETE approximately 300,000 records per day, but this
> number is increasing on a daily basis as I make changes which improve the
> performance of my data gathering tools (spiders ;). Two days ago, it had
> reached the point where a search for a 3-word term (ie. free news servers)
> took about 60 seconds. I have just spent 48 hours running a VACUUM FULL
> on my table, and now the same search takes < 10 seconds. I assume that
> the increase in performance is due to the decrease in table/index size
> which added up to approximate 1GB of freed space on the machine, which was
> approximately 4% of the original size of the table and all its indices.
> But, a 4% decrease in size should not add up to a 84% increase in
> performance (is that right? I always get the ratio confused :).
>
> If all that VACUUM FULL did was move records from file 12345678.6 to file
> 12345678, the database would still being doing a large number of random
> accesses on the table. However, if VACUUM FULL clusters the data
> according to the primary key, it would still be doing a large number of
> random access on the table, because the primary key has almost nothing to
> do with how I actually access the data in real life. So, is VACUUM FULL
> looking somewhere in pg_statistics (or pg_stat_user_indexes), to determine
> which index I actually use most (foo_ad), and then clustering the data
> that way, or is there some other agent at work here.
>
> - brian
>
>
> Wm. Brian McCane | Life is full of doors that won't open
> Search http://recall.maxbaud.net/ | when you knock, equally spaced amid
> those Usenet http://freenews.maxbaud.net/ | that open when you don't want
> them to. Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of
> Amber"
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
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 using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: mallah@trade-india.com, bmccane@mccons.net
Subject: Re: VACUUM FULL
In-Reply-To: <200205111057.50407.mallah@trade-india.com>
* 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