pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedVACUUM FULL
22+ messages / 7 participants
[nested] [flat]
* VACUUM FULL
@ 2002-05-11 00:18 Brian McCane <bmccane@mccons.net>
1 sibling, 2 replies; 22+ messages in thread
From: Brian McCane @ 2002-05-11 00:18 UTC (permalink / raw)
To: pgsql-admin
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"
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: VACUUM FULL
@ 2002-05-11 00:38 Dan Langille <dan@langille.org>
1 sibling, 0 replies; 22+ messages in thread
From: Dan Langille @ 2002-05-11 00:38 UTC (permalink / raw)
To: Brian McCane <bmccane@mccons.net>; +Cc: pgsql-admin
On 10 May 2002 at 19:18, Brian McCane wrote:
> 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 :).
Have you tried an ANALYZE as well? It wasn't mentioned.
See http://www.postgresql.org/idocs/index.php?routine-vacuuming.html
PostgreSQL's VACUUM command must be run on a regular basis for several
reasons:
1. To recover disk space occupied by updated or deleted rows.
2. To update data statistics used by the PostgreSQL query planner.
3. To protect against loss of very old data due to transaction ID
wraparound.
Read 8.2.2. Updating planner statistics
--
Dan Langille
The FreeBSD Diary - http://freebsddiary.org/ - practical examples
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: VACUUM FULL
@ 2002-05-11 02:04 Tom Lane <tgl@sss.pgh.pa.us>
parent: Brian McCane <bmccane@mccons.net>
1 sibling, 0 replies; 22+ messages in thread
From: Tom Lane @ 2002-05-11 02:04 UTC (permalink / raw)
To: Brian McCane <bmccane@mccons.net>; +Cc: pgsql-admin
Brian McCane <bmccane@mccons.net> writes:
> 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 would have to assume that the change is due to a change in plan, and
not directly due to VACUUM having compacted out a small part of the
space.
Did you do VACUUM ANALYZE, or just plain VACUUM? When was the last time
you did VACUUM (or ANALYZE) on this table? What plan is being used now?
Do you have any idea what plan was used before?
regards, tom lane
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: VACUUM FULL
@ 2002-05-11 05:27 Rajesh Kumar Mallah. <mallah@trade-india.com>
parent: Brian McCane <bmccane@mccons.net>
1 sibling, 1 reply; 22+ messages in thread
From: Rajesh Kumar Mallah. @ 2002-05-11 05:27 UTC (permalink / raw)
To: Brian McCane <bmccane@mccons.net>; pgsql-admin
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.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: VACUUM FULL
@ 2002-05-11 16:53 Brian McCane <bmccane@mccons.net>
parent: Rajesh Kumar Mallah. <mallah@trade-india.com>
0 siblings, 1 reply; 22+ messages in thread
From: Brian McCane @ 2002-05-11 16:53 UTC (permalink / raw)
To: Rajesh Kumar Mallah. <mallah@trade-india.com>; +Cc: pgsql-admin
I have tried contrib/tsearch, but I do fairly complex score computation
based on word placement (headers,body,graphics,metatags, etc). And also
do additional computations on the scores when the user does a search. It
is very quick to use something like tsearch to figure out which pages
match my criterion, but very slow to then compute a valid score for each
page. Instead, in an attempt to speed things up, a score is computed for
each distinct word on a page, and that score is stored in this table.
Then when a search is requested, I use previously existing search
information and data from this table to compute the new results.
The upshot of all this computing is that I get fairly relevant results,
but I have to do a lot of math to get there. I have been considering
combining the two methods, doing something like a full text search to find
pages that meet my criterion, and then using this table to actually
compute a pages score.
- brian
On Sat, 11 May 2002, Rajesh Kumar Mallah. wrote:
>
> 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.
>
>
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"
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: VACUUM FULL
@ 2002-05-11 17:10 Brian McCane <bmccane@mccons.net>
0 siblings, 3 replies; 22+ messages in thread
From: Brian McCane @ 2002-05-11 17:10 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
On Sat, 11 May 2002, Tom Lane wrote:
>
> Brian McCane <bmccane@mccons.net> writes:
> > It appeared while the VACUUM FULL was occurring, that the index files were
> > sort of rewritten in place.
>
> No, it just has to make new index entries for the rows it moves. It
> seems highly unlikely to me that this would somehow improve the index
> structure substantially. Puzzling...
>
> regards, tom lane
>
This is what I thought that it was doing to the indexes as well. I
believe, but I am not sure because I didn't record an 'ls -l' of all the
tables involved, that my datafile shrunk about 2x the freed space that I
got back (ie. about 2 less 1GB files). I guess this could be from the
rewritten index information possibly growing the actual index files a
little? Would I possibly see another performance boost if I rebuilt the
indexes involved like I used to in 7.1.3? I still have the Perl script
sitting around here some place, but assumed from things said by you and
Bruce in the past few months about 7.2.x that it wouldn't be necessary
anymore. I guess I'll give it a try this weekend and see what happens.
Also, I disabled my nightly VACUUM ANALYZE from cron while I was doing
this because I was unsure what would happen with multiple VACUUM's
running. And looking back at the command that I ran, it was only a
"VACUUM FULL foo". This would mean that no statistics were updated,
right? If so, all of my post-vacuum testing has been with pre-vacuum
stats. Just to be sure, (and because I just realized I had forgot), I
re-enabled the VACUUM ANALYZE and checked my stats. They are nearly
identical.
stranger and stranger ;)
- 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"
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: VACUUM FULL
@ 2002-05-11 22:39 Brian McCane <bmccane@mccons.net>
parent: Brian McCane <bmccane@mccons.net>
2 siblings, 1 reply; 22+ messages in thread
From: Brian McCane @ 2002-05-11 22:39 UTC (permalink / raw)
To: pgsql-admin
Okay, now I am a little scared. Before I started rebuilding indexes, I
decided to do the VACUUM FULL thing on all of my tables. On my two most
referenced tables I am getting:
ERROR: No one parent tuple was found
What does this mean? Can I fix it without a full dump/restore? Where did
it come from? All these questions and many more are begging for an
answer. One of the tables has 38 foreign key references to its primary
key, the other has 12. Only a couple of the other tables even have a
reference. This may not have anything to do with the problem, it is just
an observation.
Anyway, any help in fixing this would be greatly appreciated. I have
already had the site half way down for almost 3 days because of the VACUUM
FULL on the big table, so I would prefer not to spend a couple of days
doing a dump/restore. Could I dump the tables, disable their triggers,
truncate them, restore the data, and re-enable the triggers?
- 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"
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: VACUUM FULL
@ 2002-05-12 05:24 Rajesh Kumar Mallah. <mallah@trade-india.com>
parent: Brian McCane <bmccane@mccons.net>
0 siblings, 0 replies; 22+ messages in thread
From: Rajesh Kumar Mallah. @ 2002-05-12 05:24 UTC (permalink / raw)
To: Brian McCane <bmccane@mccons.net>; +Cc: pgsql-admin
Hi Brian,
Yes contrib/tsearch does not provide relevence ranking at the
moment,
But OpenFTS-perl/tcl (openfts.sf.net)
(from which tsearch is derieved) do provide
relevence ranking and those are also very fast.
and they work in similar fashion as you have metioned
in your reply,
also OpenFTS does stemming in you search phrases.
but i understand applicability of those solutions
are also subject to your current requirements.
regds
mallah.
On Saturday 11 May 2002 10:23 pm, Brian McCane wrote:
> I have tried contrib/tsearch, but I do fairly complex score computation
> based on word placement (headers,body,graphics,metatags, etc). And also
> do additional computations on the scores when the user does a search. It
> is very quick to use something like tsearch to figure out which pages
> match my criterion, but very slow to then compute a valid score for each
> page. Instead, in an attempt to speed things up, a score is computed for
> each distinct word on a page, and that score is stored in this table.
> Then when a search is requested, I use previously existing search
> information and data from this table to compute the new results.
>
> The upshot of all this computing is that I get fairly relevant results,
> but I have to do a lot of math to get there. I have been considering
> combining the two methods, doing something like a full text search to find
> pages that meet my criterion, and then using this table to actually
> compute a pages score.
>
> - brian
>
> On Sat, 11 May 2002, Rajesh Kumar Mallah. wrote:
> > 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"
> > >
> > >
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.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: VACUUM FULL
@ 2002-05-12 15:22 Tom Lane <tgl@sss.pgh.pa.us>
parent: Brian McCane <bmccane@mccons.net>
2 siblings, 0 replies; 22+ messages in thread
From: Tom Lane @ 2002-05-12 15:22 UTC (permalink / raw)
To: Brian McCane <bmccane@mccons.net>; +Cc: pgsql-admin
Brian McCane <bmccane@mccons.net> writes:
> Would I possibly see another performance boost if I rebuilt the
> indexes involved like I used to in 7.1.3? I still have the Perl script
> sitting around here some place, but assumed from things said by you and
> Bruce in the past few months about 7.2.x that it wouldn't be necessary
> anymore. I guess I'll give it a try this weekend and see what happens.
An occasional index rebuild (using either REINDEX or DROP/CREATE INDEX)
is still worth doing for heavily updated tables, especially if the range
of indexed values changes over time. That'll remain true until someone
gets around to teaching btree indexes how to collapse out free space...
regards, tom lane
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: VACUUM FULL
@ 2002-05-12 15:46 Tom Lane <tgl@sss.pgh.pa.us>
parent: Brian McCane <bmccane@mccons.net>
2 siblings, 0 replies; 22+ messages in thread
From: Tom Lane @ 2002-05-12 15:46 UTC (permalink / raw)
To: Brian McCane <bmccane@mccons.net>; +Cc: pgsql-admin
Brian McCane <bmccane@mccons.net> writes:
> Also, I disabled my nightly VACUUM ANALYZE from cron while I was doing
> this because I was unsure what would happen with multiple VACUUM's
> running.
Multiple VACUUMs work fine (except possibly for driving your system
load to the moon ;-)). Somewhere back around 6.5 I think we had
problems with that due to sloppy interlocking, but it's been fixed
for a long time.
regards, tom lane
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: VACUUM FULL
@ 2002-05-12 16:04 Tom Lane <tgl@sss.pgh.pa.us>
parent: Brian McCane <bmccane@mccons.net>
0 siblings, 2 replies; 22+ messages in thread
From: Tom Lane @ 2002-05-12 16:04 UTC (permalink / raw)
To: Brian McCane <bmccane@mccons.net>; +Cc: pgsql-admin
Brian McCane <bmccane@mccons.net> writes:
> Okay, now I am a little scared. Before I started rebuilding indexes, I
> decided to do the VACUUM FULL thing on all of my tables. On my two most
> referenced tables I am getting:
> ERROR: No one parent tuple was found
Oh, that's interesting. We've gotten sporadic reports of that error
message but no one's ever submitted a reproducible case. Don't suppose
you want to trace through VACUUM FULL with a debugger (or let someone
else do so) to see why it's getting confused?
FWIW, I don't think you need fear data loss. VACUUM FULL has some, um,
quite baroque code to deal with moving update chains as a unit, and it's
just reporting that it couldn't figure out how to move what looked like
a tuple chain.
AFAIK this situation can only occur when there are open transactions
that can still see some already-replaced tuple. So the error would
probably go away if you closed all your open transactions.
regards, tom lane
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: VACUUM FULL
@ 2002-05-12 17:49 Brian McCane <bmccane@mccons.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 22+ messages in thread
From: Brian McCane @ 2002-05-12 17:49 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
Tom,
I was going to do a pg_ctl stop/start cycle to try to fix the
error, however....
What do I need to do to debug? I am on FreeBSD 5.0. I assume I can use
GDB to connect to a running backend, but have not done any serious
debugging in gdb for 4-5 years. If it is too ugly, I might consider
allowing someone into the machine, but it doesn't make me excited to
contemplate it.
- brian
On Sun, 12 May 2002, Tom Lane wrote:
>
> Brian McCane <bmccane@mccons.net> writes:
> > Okay, now I am a little scared. Before I started rebuilding indexes, I
> > decided to do the VACUUM FULL thing on all of my tables. On my two most
> > referenced tables I am getting:
>
> > ERROR: No one parent tuple was found
>
> Oh, that's interesting. We've gotten sporadic reports of that error
> message but no one's ever submitted a reproducible case. Don't suppose
> you want to trace through VACUUM FULL with a debugger (or let someone
> else do so) to see why it's getting confused?
>
> FWIW, I don't think you need fear data loss. VACUUM FULL has some, um,
> quite baroque code to deal with moving update chains as a unit, and it's
> just reporting that it couldn't figure out how to move what looked like
> a tuple chain.
>
> AFAIK this situation can only occur when there are open transactions
> that can still see some already-replaced tuple. So the error would
> probably go away if you closed all your open transactions.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
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"
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: VACUUM FULL
@ 2002-05-12 21:43 Dan Langille <dan@langille.org>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 22+ messages in thread
From: Dan Langille @ 2002-05-12 21:43 UTC (permalink / raw)
To: Brian McCane <bmccane@mccons.net>; +Cc: pgsql-admin
On 12 May 2002 at 12:49, Brian McCane wrote:
> I am on FreeBSD 5.0.
There is a reason why you must be on 5.0? That is not recommended for
"sane" people.
5.0 is -current and not the best place to be running important
applications At present 5.0 is -current, which is the version which
changes daily. It's really only for people who are developing FreeBSD.
It frequently contains works in progress and experimental changes.
see <http://www.freebsd.org/handbook/current-stable.html;
--
Dan Langille
The FreeBSD Diary - http://freebsddiary.org/ - practical examples
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: VACUUM FULL
@ 2002-05-13 13:20 Brian McCane <bmccane@mccons.net>
parent: Dan Langille <dan@langille.org>
1 sibling, 0 replies; 22+ messages in thread
From: Brian McCane @ 2002-05-13 13:20 UTC (permalink / raw)
To: Dan Langille <dan@langille.org>; +Cc: pgsql-admin
On Sun, 12 May 2002, Dan Langille wrote:
>
> On 12 May 2002 at 12:49, Brian McCane wrote:
>
> > I am on FreeBSD 5.0.
>
> There is a reason why you must be on 5.0? That is not recommended for
> "sane" people.
>
> 5.0 is -current and not the best place to be running important
> applications At present 5.0 is -current, which is the version which
> changes daily. It's really only for people who are developing FreeBSD.
> It frequently contains works in progress and experimental changes.
>
> see <http://www.freebsd.org/handbook/current-stable.html;
> --
> Dan Langille
> The FreeBSD Diary - http://freebsddiary.org/ - practical examples
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
Trust me, I am fully aware of this :) I have been running BSD on my
machine Since Bill and his wife announed the release of 386BSD 0.1 in an
article in (I think) Dr. Dobbs. I tend to pick and choose my times when I
upgrade my OS, and the version I am now running is several months old.
When I see a new feature I like/need, and have seen no major complaints in
a while, I back up a week and grab a copy. Unfortunately, it has been a
while since I have been able to do this.
My experience has always been that the current is usually stable if you
are careful and don't get too exotic. Also, it tends to have much better
performance, once you turn off all the debugging/monitoring code in the
kernel, then the older releases. In addition, whenever they fix possible
DOS attacks, etc, they are implemented there first. At one point I needed
these because of a DOS attack that was hitting my machines.
have fun,
- 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"
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: VACUUM FULL
@ 2002-05-13 14:36 Dan Langille <dan@langille.org>
parent: Dan Langille <dan@langille.org>
1 sibling, 0 replies; 22+ messages in thread
From: Dan Langille @ 2002-05-13 14:36 UTC (permalink / raw)
To: Brian McCane <bmccane@mccons.net>; +Cc: pgsql-admin
On 13 May 2002 at 8:20, Brian McCane wrote:
> On Sun, 12 May 2002, Dan Langille wrote:
>
> >
> > On 12 May 2002 at 12:49, Brian McCane wrote:
> >
> > > I am on FreeBSD 5.0.
> >
> > There is a reason why you must be on 5.0? That is not recommended for
> > "sane" people.
> >
> > 5.0 is -current and not the best place to be running important
> > applications At present 5.0 is -current, which is the version which
> > changes daily. It's really only for people who are developing FreeBSD.
> > It frequently contains works in progress and experimental changes.
> Trust me, I am fully aware of this :) I have been running BSD on my
> machine Since Bill and his wife announed the release of 386BSD 0.1 in an
> article in (I think) Dr. Dobbs. I tend to pick and choose my times when I
> upgrade my OS, and the version I am now running is several months old.
> When I see a new feature I like/need, and have seen no major complaints in
> a while, I back up a week and grab a copy. Unfortunately, it has been a
> while since I have been able to do this.
You are clearly qualified.... ;)
Too frequently we see people who jump into -current not knowing what they
are getting into.
Personally, I have too many other things to do to attempt -current.
--
Dan Langille
The FreeBSD Diary - http://freebsddiary.org/ - practical examples
^ permalink raw reply [nested|flat] 22+ messages in thread
* vacuum full...
@ 2009-02-19 16:35 Jessica Richard <rjessil@yahoo.com>
0 siblings, 1 reply; 22+ messages in thread
From: Jessica Richard @ 2009-02-19 16:35 UTC (permalink / raw)
To: pgsql-admin
I am running "vacuum full" via a shell script for a list of large databases now... and I may run out of my scheduled system down time....
If I don't finish all databases and kill the script in the middle... am I going to cause any table corruptions since "vacuum full" is rebuilding the tables and indexes....
thanks,
Jessica
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: vacuum full...
@ 2009-02-19 16:37 Scott Marlowe <scott.marlowe@gmail.com>
parent: Jessica Richard <rjessil@yahoo.com>
0 siblings, 1 reply; 22+ messages in thread
From: Scott Marlowe @ 2009-02-19 16:37 UTC (permalink / raw)
To: Jessica Richard <rjessil@yahoo.com>; +Cc: pgsql-admin
On Thu, Feb 19, 2009 at 9:35 AM, Jessica Richard <rjessil@yahoo.com> wrote:
> I am running "vacuum full" via a shell script for a list of large databases
> now... and I may run out of my scheduled system down time....
>
> If I don't finish all databases and kill the script in the middle... am I
> going to cause any table corruptions since "vacuum full" is rebuilding the
> tables and indexes....
No. But may I ask why you are running vacuum full? Is it a regular
thing. Do all your tables in all your dbs actually need it?
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: vacuum full...
@ 2009-02-19 18:01 Rafael Domiciano <rafael.domiciano@gmail.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 22+ messages in thread
From: Rafael Domiciano @ 2009-02-19 18:01 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Jessica Richard <rjessil@yahoo.com>; pgsql-admin
I used to run vacuum full in one of my bases, but now i'm not running
anymore vacuum full, just vacuum analyze in the past 1 month, but the number
of necessary pages is increasing every day, now it's in 311264... there is
any problem this get increasing?
When I runned Reindex few days ago, this number get not decreased.
I now that vacuum full reorders the relation, but it's the only thing that
it does?
Thnks,
Rafael Domiciano
On Thu, Feb 19, 2009 at 1:37 PM, Scott Marlowe <scott.marlowe@gmail.com>wrote:
> On Thu, Feb 19, 2009 at 9:35 AM, Jessica Richard <rjessil@yahoo.com>
> wrote:
> > I am running "vacuum full" via a shell script for a list of large
> databases
> > now... and I may run out of my scheduled system down time....
> >
> > If I don't finish all databases and kill the script in the middle... am I
> > going to cause any table corruptions since "vacuum full" is rebuilding
> the
> > tables and indexes....
>
> No. But may I ask why you are running vacuum full? Is it a regular
> thing. Do all your tables in all your dbs actually need it?
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: vacuum full...
@ 2009-02-19 20:11 Scott Marlowe <scott.marlowe@gmail.com>
parent: Rafael Domiciano <rafael.domiciano@gmail.com>
0 siblings, 1 reply; 22+ messages in thread
From: Scott Marlowe @ 2009-02-19 20:11 UTC (permalink / raw)
To: Rafael Domiciano <rafael.domiciano@gmail.com>; +Cc: Jessica Richard <rjessil@yahoo.com>; pgsql-admin
On Thu, Feb 19, 2009 at 11:01 AM, Rafael Domiciano
<rafael.domiciano@gmail.com> wrote:
> I used to run vacuum full in one of my bases, but now i'm not running
> anymore vacuum full, just vacuum analyze in the past 1 month, but the number
> of necessary pages is increasing every day, now it's in 311264... there is
> any problem this get increasing?
> When I runned Reindex few days ago, this number get not decreased.
> I now that vacuum full reorders the relation, but it's the only thing that
> it does?
Same question I had for the other poster, why are you not running
autovacuum? Has it proven itself not up to the task?
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: vacuum full...
@ 2009-02-20 11:36 Rafael Domiciano <rafael.domiciano@gmail.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 22+ messages in thread
From: Rafael Domiciano @ 2009-02-20 11:36 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Jessica Richard <rjessil@yahoo.com>; pgsql-admin
I don't have too much delete and update in the middle of the day to run
autovacuum, but in the night, before vacuum analyze, there is some proccess
that does delete and update, then run vacuum analyze.
On Thu, Feb 19, 2009 at 5:11 PM, Scott Marlowe <scott.marlowe@gmail.com>wrote:
> On Thu, Feb 19, 2009 at 11:01 AM, Rafael Domiciano
> <rafael.domiciano@gmail.com> wrote:
> > I used to run vacuum full in one of my bases, but now i'm not running
> > anymore vacuum full, just vacuum analyze in the past 1 month, but the
> number
> > of necessary pages is increasing every day, now it's in 311264... there
> is
> > any problem this get increasing?
> > When I runned Reindex few days ago, this number get not decreased.
> > I now that vacuum full reorders the relation, but it's the only thing
> that
> > it does?
>
> Same question I had for the other poster, why are you not running
> autovacuum? Has it proven itself not up to the task?
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Vacuum Full
@ 2009-03-31 16:04 Rafael Domiciano <rafael.domiciano@gmail.com>
0 siblings, 1 reply; 22+ messages in thread
From: Rafael Domiciano @ 2009-03-31 16:04 UTC (permalink / raw)
To: pgsql-admin
Hello People,
I have some doubts about Vacuum Full. There We go:
1) The Only thing that *Vacuum Full* (Only Full, not Analyze) is to clean
"dead space" on the disc, and reorganize the relation at the physical level?
If it's true, so doing this may speed up *select's*, while the Postgres will
going to do less hit in the disc. I'm right?
2) Doing only *Vacuum Analyze* I have a enlargement of the parameter *
max_fsm_pages*. Does it have any impact in the operation?
Should I increment the value in the conf to be so large than the Vacuum
Verbose shows me?
3) There are differences in performace of Vacuum Full between versions 8.1.4
and 8.3.7?
As soon as possible we are going to migrate the Postgres to 8.3.7, just
waiting the finish of the tests of the software in the new version.
4) The right way to run Vacuum and Reindex is: Vacuum and Reindex or Reindex
and Vacuum?
Running Vacuum I have a Index Bloat, right?! So I have to run Reindex afet
Vacuum?!
If true I'll change the script to make first Vacuum and then Reindex.
5) Does have any way to see how much is left to finish vacuum?
Example:
select * from vacuum_finish;
Relation Total Bytes Total Bytes Done Total Bytes Remaining
Table01 1000 600 400
Or Something like that!
Thnks for responses,
Rafael Domiciano
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Vacuum Full
@ 2009-03-31 16:20 Scott Marlowe <scott.marlowe@gmail.com>
parent: Rafael Domiciano <rafael.domiciano@gmail.com>
0 siblings, 0 replies; 22+ messages in thread
From: Scott Marlowe @ 2009-03-31 16:20 UTC (permalink / raw)
To: Rafael Domiciano <rafael.domiciano@gmail.com>; +Cc: pgsql-admin
On Tue, Mar 31, 2009 at 10:04 AM, Rafael Domiciano
<rafael.domiciano@gmail.com> wrote:
> Hello People,
>
> I have some doubts about Vacuum Full. There We go:
> 1) The Only thing that Vacuum Full (Only Full, not Analyze) is to clean
> "dead space" on the disc, and reorganize the relation at the physical level?
> If it's true, so doing this may speed up select's, while the Postgres will
> going to do less hit in the disc. I'm right?
Generally speaking, vacuum full should not normally be needed.
Regular vacuuming should free up enough free space that the table
reaches an equilibrium where it has some small percentage of available
space (5 to 15% or so) and stay there.
> 2) Doing only Vacuum Analyze I have a enlargement of the parameter
> max_fsm_pages. Does it have any impact in the operation?
> Should I increment the value in the conf to be so large than the Vacuum
> Verbose shows me?
If the needed fsm settings need to keep increasing then something is wrong.
> 3) There are differences in performace of Vacuum Full between versions 8.1.4
> and 8.3.7?
> As soon as possible we are going to migrate the Postgres to 8.3.7, just
> waiting the finish of the tests of the software in the new version.
Yeah, 8.3 is faster.
> 4) The right way to run Vacuum and Reindex is: Vacuum and Reindex or Reindex
> and Vacuum?
> Running Vacuum I have a Index Bloat, right?! So I have to run Reindex afet
> Vacuum?!
> If true I'll change the script to make first Vacuum and then Reindex.
yep, vacuum full, then reindex.
> 5) Does have any way to see how much is left to finish vacuum?
Not that I know of.
I notice you don't mention autovacuum. Are you running it? And if
not, why not?
^ permalink raw reply [nested|flat] 22+ messages in thread
end of thread, other threads:[~2009-03-31 16:20 UTC | newest]
Thread overview: 22+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-05-11 00:18 ` Brian McCane <bmccane@mccons.net>
2002-05-11 02:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-05-11 05:27 ` Rajesh Kumar Mallah. <mallah@trade-india.com>
2002-05-11 16:53 ` Brian McCane <bmccane@mccons.net>
2002-05-12 05:24 ` Rajesh Kumar Mallah. <mallah@trade-india.com>
2002-05-11 00:38 ` Dan Langille <dan@langille.org>
2002-05-11 17:10 Re: VACUUM FULL Brian McCane <bmccane@mccons.net>
2002-05-11 22:39 ` Brian McCane <bmccane@mccons.net>
2002-05-12 16:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-05-12 17:49 ` Brian McCane <bmccane@mccons.net>
2002-05-12 21:43 ` Dan Langille <dan@langille.org>
2002-05-13 13:20 ` Brian McCane <bmccane@mccons.net>
2002-05-13 14:36 ` Dan Langille <dan@langille.org>
2002-05-12 15:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-05-12 15:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-02-19 16:35 vacuum full... Jessica Richard <rjessil@yahoo.com>
2009-02-19 16:37 ` Re: vacuum full... Scott Marlowe <scott.marlowe@gmail.com>
2009-02-19 18:01 ` Re: vacuum full... Rafael Domiciano <rafael.domiciano@gmail.com>
2009-02-19 20:11 ` Re: vacuum full... Scott Marlowe <scott.marlowe@gmail.com>
2009-02-20 11:36 ` Re: vacuum full... Rafael Domiciano <rafael.domiciano@gmail.com>
2009-03-31 16:04 Vacuum Full Rafael Domiciano <rafael.domiciano@gmail.com>
2009-03-31 16:20 ` Re: Vacuum Full Scott Marlowe <scott.marlowe@gmail.com>
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