Thread: Vacuum command
I've had trouble in the past with the vacuum command taking a long time to complete. This past vacuum went for 5 days before I CTRL-C'd it. I'm wondering if there is a bug. I have PostgreSQL 6.3.2 on a Red Hat 5.1 system (PII/400 MHz, 256 Meg RAM, 512 Meg Swap, 18 Gig Hard Ultra-wide SCSI Drive). The database consists of about 30 tables and is 1.2 Gig in total size. When I ran 'vacuum verbose analyze' the last time, the vacuum kept working for 5 days until I killed it. I had no other programs running on the system at the time. Also, I noticed that although 'top' showed that the vacuum was using 98% of the system resources, the harddrive activity was quiet during that 5 day period (as if nothing was actually happening). However, I then tried 'vacuum verbose analyze table_name' to vacuum just one table at a time. When I did it one table at a time, I was able to vacuum all 30 tables in less than 1 day. I know that others have had there run in with long vacuums. I am wondering whether there is a bug when the vacuum switches from one table to the next? -Tony
>
> I've had trouble in the past with the vacuum command taking a long time
> to complete. This past vacuum went for 5 days before I CTRL-C'd it. I'm
> wondering if there is a bug.
>
> I have PostgreSQL 6.3.2 on a Red Hat 5.1 system (PII/400 MHz, 256 Meg
> RAM, 512 Meg Swap, 18 Gig Hard Ultra-wide SCSI Drive). The database
> consists of about 30 tables and is 1.2 Gig in total size.
>
> When I ran 'vacuum verbose analyze' the last time, the vacuum kept
> working for 5 days until I killed it. I had no other programs running on
> the system at the time. Also, I noticed that although 'top' showed that
> the vacuum was using 98% of the system resources, the harddrive activity
> was quiet during that 5 day period (as if nothing was actually
> happening).
That behaviour reminds me of a similar situation, where an
index of one of the user tables was corrupt, causing vacuum
to loop on that (all blocks cached so no HD activity).
Dropping/recreating the index in question solved that
problem.
But the difference this time is that it does not occur when
you manually vacuum all the user tables. So if it's the same
reason (corrupt index), this time it must be one of a system
catalog.
Can you dump/initdb/reload your database?
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #
That makes sense. Is there a way that is less drastic than dumping, re-initdb, and reloading the database? It would probably take a day or two to do this. Does anybody know if I can fix the catalog in another way? -Tony Jan Wieck wrote: > > That behaviour reminds me of a similar situation, where an > index of one of the user tables was corrupt, causing vacuum > to loop on that (all blocks cached so no HD activity). > > Dropping/recreating the index in question solved that > problem. > > But the difference this time is that it does not occur when > you manually vacuum all the user tables. So if it's the same > reason (corrupt index), this time it must be one of a system > catalog. > > Can you dump/initdb/reload your database? > > Jan > > -- > > #======================================================================# > # It's easier to get forgiveness for being wrong than for being right. # > # Let's break this rule - forgive me. # > #======================================== jwieck@debis.com (Jan Wieck) #
>
> That makes sense. Is there a way that is less drastic than dumping,
> re-initdb, and reloading the database? It would probably take a day or
> two to do this. Does anybody know if I can fix the catalog in another
> way?
>
> -Tony
Not any more. In earlier days, there was a reindexdb command,
which used the bootstrap code to drop and recreate the
indices for the system catalog's. But that disappeared
sometime. I think there's no chance, because dropping of
catalog indices isn't allowed.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #