pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedvacuum full seems to hang on very small table
3+ messages / 3 participants
[nested] [flat]
* vacuum full seems to hang on very small table
@ 2006-04-04 14:59 Dan Harris <fbsd@drivefaster.net>
0 siblings, 2 replies; 3+ messages in thread
From: Dan Harris @ 2006-04-04 14:59 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: vacuum full seems to hang on very small table
@ 2006-04-04 15:07 Brad Nicholson <bnichols@ca.afilias.info>
parent: Dan Harris <fbsd@drivefaster.net>
1 sibling, 0 replies; 3+ messages in thread
From: Brad Nicholson @ 2006-04-04 15:07 UTC (permalink / raw)
To: Dan Harris <fbsd@drivefaster.net>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: vacuum full seems to hang on very small table
@ 2006-04-04 15:35 Simon Riggs <simon@2ndquadrant.com>
parent: Dan Harris <fbsd@drivefaster.net>
1 sibling, 0 replies; 3+ messages in thread
From: Simon Riggs @ 2006-04-04 15:35 UTC (permalink / raw)
To: Dan Harris <fbsd@drivefaster.net>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-04-04 15:35 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-04-04 14:59 vacuum full seems to hang on very small table Dan Harris <fbsd@drivefaster.net>
2006-04-04 15:07 ` Brad Nicholson <bnichols@ca.afilias.info>
2006-04-04 15:35 ` Simon Riggs <simon@2ndquadrant.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox