Thread: Re: VACUUM FULL
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"
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"
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
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
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
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"
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
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" ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
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
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" ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)