problems with new vacuum (??) - Mailing list pgsql-hackers

Over the last two days I have been struggling with running vacuum on a 
7.2b4 database that is running in a production environment.  This was 
essentially the first time I ran vacuum on this database since it was 
upgraded to 7.2.  This database is characterized by one large table that 
has many inserts and deletes, however generally contains zero rows.  So 
over the course of the last few weeks this table had grown in size to 
about 2.5G (or more correctly the corresponding toast table grew that 
large).

So the first problem I had was that the vaccum (regular vacuum not full 
vacuum) took a very long time on this table (2+ hours).  Now I would 
expect it to take a while, so that in and of itself isn't a problem. 
But while this vacuum was running the rest of the system was performing 
very poorly.  Opperations that usually are subsecond, where taking 
minutes to complete.  At first I thought there was some sort of locking 
problem, but these opperations did complete, but after a very long time.

In looking at the log files from this time, I noticed that while the 
vacuum process was running, there were a lot of the following messages 
in the log file:

2001-12-31 22:16:40 [20655]  DEBUG:  recycled transaction log file 
000000010000009A

The interesting thing (at least in my mind) is that these messages were 
produced by all of the other postgres processes, not by the vacuum 
process.  (And by the way what do they mean?)



The second issue I noticed was that the vacuum process later just hung.  Since I didn't think the new vacuum was
supposedto hang (since I 
 
thought it tried a best effort and if it couldn't lock something it 
would just skip it).

2001-12-31 22:18:04 [19945]  NOTICE:  --Relation xyf_files--
2001-12-31 22:21:51 [20673]  DEBUG:  recycled transaction log file 
000000010000009C
2001-12-31 22:21:51 [20673]  DEBUG:  recycled transaction log file 
000000010000009D
2001-12-31 22:21:51 [20673]  DEBUG:  recycled transaction log file 
000000010000009B
2001-12-31 22:31:54 [20711]  DEBUG:  recycled transaction log file 
000000010000009F
2001-12-31 22:31:54 [20711]  DEBUG:  recycled transaction log file 
000000010000009E
2002-01-01 07:30:58 [19945]  ERROR:  Query was cancelled.

It hung until I cancelled the vacuum with a ^c.  So then I tried to 
rerun the vacuum and it hung in the same spot, this time for 1.5 hours 
before I killed it.

Thinking that maybe there was some sort of problem with this table I ran 
a vacuum full (after restarting the database to make sure no other 
processes would be locking the full vacuum) and it ran to completion.

Now after the full vacuum the the regular vacuum runs almost instantly 
with no further problems.

Is there a bug here, I don't know, but I thought it was interesting 
enough to post what I just saw.

thanks,
--Barry