Обсуждение: Postgres 7.4 VACUUM FULL multiple AccessExclusiveLocks?
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 | granted ------------+------------+-------------+-------+---------------------+--------- 2434930929 | 2434930890 | | 18356 | AccessExclusiveLock | t 2434930929 | 2434930890 | | 18356 | AccessExclusiveLock | t (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 bytes. 4596598 pages are or will become empty, including 0 at the end of the table. 4597196 pages containing 35231061344 free bytes are potential move destinations. 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.) Thanks, Mike White
On Jan 3, 2008 5:12 PM, Mike White <mikewhite22@yahoo.com> wrote: > 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. I wonder why you have to do it every 6 months... Is this one of those things where slowly the table bloats because fsm is too small or vacuum is too infrequent? Or is this a case of some kind of fragmentation? I've got plenty of heavily updated tables that USED to live in a 7.4 and I never needed to run vacuum full on it, because autovacuum (with slightly more aggressive settings than default) was plenty on that db to keep the table clean. So I'm wondering if there's some preventative maintenance that would stop you needing to do this. > 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. OK. I will tell you to update. 7.4.7 had many data eating bugs that were fixed by 7.4.18. > 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. :( Been there, done that, got the t-shirt :-( > > <dbname>=# select * from pg_locks where mode = 'AccessExclusiveLock'; > relation | database | transaction | pid | mode | > granted > ------------+------------+-------------+-------+---------------------+--------- > 2434930929 | 2434930890 | | 18356 | AccessExclusiveLock | > t > 2434930929 | 2434930890 | | 18356 | AccessExclusiveLock | > t I'm guessing that the richer view that exists in 8.2 might show differences, but the simpler view in 7.4 doesn't. You might try adapting parts of the 8.2 view into 7.4 and seeing if that helps.
Mike White <mikewhite22@yahoo.com> writes: > 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? I think it's just the regular and session versions of the lock. Don't recall at the moment whether it's easier to tell them apart in newer releases. > Also, please don't tell me to upgrade. You should *definitely* be on 7.4.x where x is considerably greater than 7. This is a painless update and failing to do it borders on negligence. Read the release notes at http://www.postgresql.org/docs/7.4/static/release.html regards, tom lane