Postgres Pro
Downloads
Home   >   mailing lists

Thread: vacuum full seems to hang on very small table

vacuum full seems to hang on very small table

From
Dan Harris
Date:
I have a table with 1 live row that I found has 115000 dead rows in it (
from a testing run ).  I'm trying to VACUUM FULL the table and it has
run for over 18 hours without completion.  Considering the hardware on
this box and the fact that performance seems reasonable in all other
aspects, I'm confused as to why this would happen.  The database other
than this table is quite large ( 70 gigs on disk ) and I would expect to
take days to complete but I just did 'vacuum full table_stats'.  That
should only do that table, correct?  I'm running 8.0.3.

                  Table "public.table_stats"
       Column        |            Type             | Modifiers
---------------------+-----------------------------+-----------
 count_cfs           | integer                     |
 count_ncfs          | integer                     |
 count_unitactivity  | integer                     |
 count_eventactivity | integer                     |
 min_eventmain       | timestamp without time zone |
 max_eventmain       | timestamp without time zone |
 min_eventactivity   | timestamp without time zone |
 max_eventactivity   | timestamp without time zone |
 geocoding_hitrate   | double precision            |
 recent_load         | timestamp without time zone |
 count_eventmain     | integer                     |


This is the table structure.

Any ideas where to begin troubleshooting this?

Thanks.

Re: vacuum full seems to hang on very small table

From
Brad Nicholson
Date:
Dan Harris wrote:
> I have a table with 1 live row that I found has 115000 dead rows in it (
> from a testing run ).  I'm trying to VACUUM FULL the table and it has
> run for over 18 hours without completion.  Considering the hardware on
> this box and the fact that performance seems reasonable in all other
> aspects, I'm confused as to why this would happen.  The database other
> than this table is quite large ( 70 gigs on disk ) and I would expect to
> take days to complete but I just did 'vacuum full table_stats'.  That
> should only do that table, correct?  I'm running 8.0.3.

VACUUM FULL requires an exclusive lock on the table that it's vacuuming.
 Chances are something else has a lock on the table is blocking the
vacuum from obtaining the necessary lock.  Check pg_locks for ungranted
locks, you'll probably find that the request from the vacuum is ungranted.
--
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.

Re: vacuum full seems to hang on very small table

From
Simon Riggs
Date:
On Tue, 2006-04-04 at 08:59 -0600, Dan Harris wrote:
> I have a table with 1 live row that I found has 115000 dead rows in it (
> from a testing run ).  I'm trying to VACUUM FULL the table and it has
> run for over 18 hours without completion.  Considering the hardware on
> this box and the fact that performance seems reasonable in all other
> aspects, I'm confused as to why this would happen.  The database other
> than this table is quite large ( 70 gigs on disk ) and I would expect to
> take days to complete but I just did 'vacuum full table_stats'.  That
> should only do that table, correct?  I'm running 8.0.3.

Read this http://www.postgresql.org/docs/8.0/static/release-8-0-5.html
and you'll probably decide to upgrade.

Best Regards, Simon Riggs