Thread: Vacuum command

Vacuum command

From
"G. Anthony Reina"
Date:
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

Re: [ADMIN] Vacuum command

From
jwieck@debis.com (Jan Wieck)
Date:
>
> 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) #

Re: [ADMIN] Vacuum command

From
"G. Anthony Reina"
Date:
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) #

Re: [ADMIN] Vacuum command

From
jwieck@debis.com (Jan Wieck)
Date:
>
> 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) #