Skip site navigation (1) Skip section navigation (2)

Postgres 7.4 VACUUM FULL multiple AccessExclusiveLocks?

From: Mike White <mikewhite22(at)yahoo(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Postgres 7.4 VACUUM FULL multiple AccessExclusiveLocks?
Date: 2008-01-03 23:12:37
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-admin
I'm performing a VACUUM FULL on a 7.4.7 db table, one that is not huge
at any given time, but which has a lot of churn.  Just the kind of
usage pattern that 7.4 loves.  :)  This is something that we have to do
every 6 months or so, and it always takes a long time.  Today, however,
I've noticed that the pg_lock table shows two AccessExclusiveLock
entries that are completely identical.  Is this a sign of internal
deadlock, or is this behavior expected during VACUUM FULL operation?

Also, please don't tell me to upgrade.  We're happily running 8.2 on
some production machines, but I am tied to 7.4 on this particular box
for reasons beyond my control.  :(

<dbname>=# select * from pg_locks where mode = 'AccessExclusiveLock';
  relation  |  database  | transaction |  pid  |        mode         |
 2434930929 | 2434930890 |             | 18356 | AccessExclusiveLock |
 2434930929 | 2434930890 |             | 18356 | AccessExclusiveLock |
(2 rows)

miro_stats=# VACUUM FULL VERBOSE tsdb_archive_state;
INFO:  vacuuming "public.tsdb_archive_state"
INFO:  "tsdb_archive_state": found 0 removable, 59138 nonremovable row
versions in 4597419 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 60 to 60 bytes long.
There were 583813020 unused item pointers.
Total free space (including removable row versions) is 35231071156
4596598 pages are or will become empty, including 0 at the end of the
4597196 pages containing 35231061344 free bytes are potential move
CPU 99.57s/20.04u sec elapsed 3948.67 sec.
INFO:  index "tsdb_archive_state_pkey" now contains 59138 row versions
in 132 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.02u sec elapsed 0.02 sec.
(Hanging here for about 4 hours.)

Mike White


pgsql-admin by date

Next:From: Scott MarloweDate: 2008-01-04 02:23:23
Subject: Re: Need some info on Postgresql
Previous:From: Alvaro HerreraDate: 2008-01-03 22:54:23
Subject: Re: best practices for separating data and logs

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group